SQLDatabases

SQL Databases

Space can be cleared from databases using the shrink database option

Right click database > tasks > shrink > Database

You can do the same for the files option then select log if you need to clear space for the log file

If desperate you can change the backup type of the database to "simple" then in the shrink files section select log file and then In Shrink action choose Reorganize pages before releasing unused space, set the desired size of the file and then click OK. After action is completed you can change backup to full and perform a backup as previous backups will not be ale to restore to the current state of the database.

SQL CPU usage query

Use following query to test the availble CPU cores for SQL server:

• SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers GO

SQL Maintenance

Use following website for setup of good practice maintenance tasks.

• https://ola.hallengren.com/

SQL Orphaned Users

---THESE COMMANDS ONLY WORK ON OLDER SQL INSTANCES---

List orphaned users:

• EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

• EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

• EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

---

If there is a user on a database that does not work it may need to have an SQL account created as it is orphaned. Create an SQL account then map the user to the database account. You can also use this command to do this:

• USE <database name>
• ALTER USER <Name of SQL account>
• WITH LOGIN = <Name of orphaned DB account>

SQL Suspect Database

If a database appears as suspect after server restore or other issue and the database is flagged as suspect you can try to recover the database with the following procedure.

Run the following SQL Queries on the database: (replace db_name with the name of the database)

Can start with

• EXEC sp_resetstatus 'db_name':

If this does not work then try the following;

• ALTER DATABASE db_name SET EMERGENCY
• DBCC CHECKDB ('db_name')
  • Check results to see if this may have fixed the database
• ALTER DATABASE db_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
• DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS)
• ALTER DATABASE db_name SET MULTI_USER