Skip to content
Reference Library
SQL Databases

SQL Databases

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 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

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
  • ALTER USER
  • WITH LOGIN =

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

Transaction Log Shipping

To fix when transaction log shipping may have failed one option is to complete a restore of the database to the secondary server from a backup of the primary.

First disable all the transaction shipping jobs on both SQL server instances

Then take full backup from primary database

Restore the backup over the secondary database with the “restore with standby” option

The log shipping jobs can then be re-enabled. They will clear out all the old log transaction files and start with a new chain.

SSHEnvironmentvariables