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>