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 database not going offline
You can use the following command to force a database to go offline if standard way is not working or is taking a long time:
- ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
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
When this still fails with the following error, you can fix it:
error = "ALTER DATABASE failed because a lock could not be placed on database 'dbname' Try again later."
you can run the following command to find out who is keeping a lock on your database:
- EXEC sp_who2
And use whatever SPID you find in the following command:
- KILL <SPID>
Then run the ALTER DATABASE command again. It should now work.
SQL Maintenance
Use following website for setup of good practice maintenance tasks.
SQL Instance Updates
Cumulative updates for SQL server do not show in windows / SQL server installer update checks
Access the Cumulative update packages here (SQL 2019 example )https://www.microsoft.com/en-us/download/details.aspx?id=100809
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)
- ^this command can cause loss of data which may then require to be restored if needed.
- ALTER DATABASE db_name SET MULTI_USER
Network Check
Check SQL conectivity: MS Github docs