SQL server DB status runnable, sleeping, suspended, running, pending and background meanings

Some of the most seen status of SPID's in SQL Server and what do they mean:

RUNNING:

This status means session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. What this actually means is, the client connected to SQL Server using this session has already submitted a query for SQL Server to process and SQL Server is currently processing the query. The query could be anywhere between generating a parser tree to performing a join to sorting the data... and it is consuming the CPU (Processor) cycles currently.

SUSPENDED:

It means that the request currently is not active because it is waiting on a resource. The resource can be an I/O for reading a page, A WAIT it can be communication on the network, or it is waiting for lock or a latch. It will become active once the task it is waiting for is completed. For example, if the query the has posted a I/O request to read data of a complete table tblStudents then this task will be suspended till the I/O is complete. Once I/O is completed (Data for table tblStudents is available in the memory), query will move into RUNNABLE queue.

RUNNABLE:

The SPID is in the runnable queue of a scheduler and waiting for a quantum to run on the scheduler. This means that requests got a worker thread assigned but they are not getting CPU time.

The RUNNABLE queue can be likened to a grocery analogy where there are multiple check out lines. The register clerk is the CPU. There is just one customer checking out e.g. “RUNNING” at any given register. The time spent in the checkout line represents CPU pressure. So this SPID is waiting for that customer who is running (with register clerk) to get out so that it can start RUNNING. You can use the query SELECT wait_type,waiting_tasks_count,signal_wait_time_ms FROM sys.dm_os_wait_stats ORDER BY signal_wait_time_ms DESC to find out the difference between the time the waiting thread was signaled and when it started running. This difference is the time spent in RUNNABLE queue. Some of the waits on the top of the list can be safely ignored.

PENDING:

The request is waiting for a worker to pick it up. This means the request is ready to run but there are no worker threads available to execute the requests in CPU. This doesn't mean that you have to increase 'Max. Worker threads", you have to check what the currently executing threads are doing and why they are not yielding back. I personally have seen more SPID's with status PENDING on issues which ended up in "Non-yielding Scheduler" and "Scheduler deadlock".

BACKGROUND:

The request is a background thread such as Resource Monitor or Deadlock Monitor.

SLEEPING:

There is no work to be done.

Database in Suspect Mode

Yesterday I had mail to check the database health after the recovery of servers from Storage crash issue. I found one particular database was in 'Suspect' mode. Performed steps to fix this. 

Database will go to Suspect mode usually if there is any issue with Server Reboot, Storage issue, Database Corruption due to Network issue etc. In this case Storage SAN drive crashed, restored the storage from the Snapshot. 

1. Check the database state

select state_desc, * from sys.databases where state_desc<>'online'

2. Found database was in Suspect Mode. 

First step is to Restore the database from the latest Backup file. and Run the DBCC Integrity command to check it. Made sure everything fine on that.          

As a part of troubleshoot on the issue database.

               

            Brought the database to Emergency Mode, and do the DBCC Integrity check.

Use Master

go

ALTER DATABASE DBNAME SET EMERGENCY

DBCC CHECKDB (DBNAME )

3. Based on the DBSS Result\suggestion performed the DBCC REPAIR_ALLOW_DATA_LOSS.  Depending on the DBCC Results and suggestions 

Use Master

go

ALTER DATABASE DBNAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

4. I was not able to access the database into single user mode, transaction was ending in the Deadlock. I changed the Deadlock priority and later was able to take the database into Single user mode.

SET DEADLOCK_PRIORITY HIGH 

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

or 

SET DEADLOCK_PRIORITY 10

GO

ALTER DATABASE DBNAME SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

DBCC CHECKDB ('DBNAME', REPAIR_ALLOW_DATA_LOSS)

            go

5. Then performed the the DBCC REPAIR_ALLOW_DATA_LOSS. Database went to Recovery mode. DBCC Repair Allow data Loss should have fixed the issue by allowing some data loss. But it didn't fix the issue. Hence continued using the Restore database. Later I just dropped the database and renamed the Restored database to original name. 

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBNAME'

GO

USE [master]

GO

/****** Object:  Database [DBNAME]    Script Date: 3/27/2019 12:29:38 PM ******/

DROP DATABASE [DBNAME]

GO

Unable to Kill SPID

                  

                       Recently one user reported he is facing some issue while deleting the table records. When I checked it there was a blocking. Immediately I tried to kill the session which was causing the issue. I used  KILL spid to kill the session. Though it was executed successfully, session was still running with 'suspended' status by holding a blocking on sessions. 

In order to kill the session, I alter the database to Single User mode. 

USE MASTER 

GO

ALTER DATABASE (DBNAME) SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

ALTER DATABASE (DBNAME) SET MULTI_USER WITH ROLLBACK IMMEDIATE 

GO

This resolved the issue by killing all the sessions. But it was on a NonProduction database. When doing it on Production database please be aware of affected users and the work to be redone. 

Shrink TempDB database

Its very common issue on day to day DBA activities, where TempDB databases will go out of space. Sometime it is cumbersome to shrink the database files to release the space. 

Below are some on the Methods to release the space on TempDB database. 

1. Shrink Database files 

use tempdb

GO

CHECKPOINT

GO

dbcc shrinkfile ('tempdev') -- shrink db file

dbcc shrinkfile ('templog') -- shrink log file

2. Clean up the Cache to and shrink the database files. 

DBCC FREEPROCCACHE -- clean cache

DBCC DROPCLEANBUFFERS -- clean buffers

DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
DBCC FREESESSIONCACHE -- clean session cache
DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb

After this again Run the shrink commands

dbcc shrinkfile ('tempdev') -- shrink db file
dbcc shrinkfile ('templog') -- shrink log file
GO

3. Restart the SQL service
This is should be a last option. 

SQL SERVER CLR Error

Error:

Msg 6263, Level 16, State 1, Line 721

Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.

Solution ; We need to enable the CLR in the SQL Server configuration.

sp_configure 'show advanced options',1

go

sp_configure 'clr enabled',1

go

reconfigure

go

Error:

Msg 10314, Level 16, State 11, Line 2
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

Solution 1 : When the databases is restored\attached from the different instance this error might occurred as the CLR objects will be looking for the same owner SID which was created at the source. Changing the databases owner to the same as source and giving the required permission would fix the issue.

Alternatively we can use db owner as ‘sa’ also.

USE <DatabaseName>

GO

EXEC sp_changedbowner 'sa'

Solution 2 : The quickfix would be to set the database to trustworthy: 

ALTER DATABASE <DatabaseName> SET TRUSTWORTHY ON

Solution 3 : You can also navigate to Assemblies of a respective databases and change the Assembly property.  Set the permission to ‘External Access or unrestricted’

Error 945 : Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details

This is one of the common issue which I have seen. Today I received a ticker from QA team saying one of the database is not accessible. I checked with the team on what exactly they are doing on the database. I came to know the team was processing the data which increased the many databases sizes which lead to Disk space issue. 

Due to there was no free space on the disk for the database to grow further, one of the database went to shutdown mode. You can check the database status from below query. 

use master
select databaseproperty('DBName', 'isShutdown')

Fix/Solution/WorkAround:

1. Took the database offline and brought back online. Query for the same 

use master
alter database DBName set offline


use master
alter database DBName set online

2.  If possible add more hard drive space either by removing of unnecessary files from hard drive or add new hard drive with larger size.

3.  Check if the database is set to Autogrow on.

4. Check if the account which is trying to access the database has enough permission to perform operation.

5. Make sure that .mdf and .ldf file are not marked as read only on operating system file system level.

How to restore TDE eanabled database

Create a database master key and certificate in the master database.

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';

 USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO

Create a backup of the server certificate in the master database. 

BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'
 ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
   
Enable the TDE on user Database

use dbname
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO

ALTER DATABASE skyProduction
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GO

Backup the Encryption key

USE master
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\temp\dbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\dbcert_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'j3tt4IL!2' )
GO


-- Use to check status
USE master
GO

SELECT * FROM sys.certificates

-- encryption_state = 3 is encrypted
SELECT * FROM sys.dm_database_encryption_keys
  WHERE encryption_state = 3;
 
  SELECT *
FROM sys.dm_database_encryption_keys;

SELECT * FROM sys.databases


--- Restore database on other machine (Decrypt the Database)

--1. Create the Master Key on Target server (Password need not be same)

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2'


--2. After a master key has been created, create a certificate by importing the certificate we created earlier (Take the Backup of the certificate).
--Here the ‘Decryption By Password’ parameter is same as that of source server.

CREATE CERTIFICATE TDECert
FROM FILE = 'E:\Backup\Production Server keys\dbCerts\dbcert.cer'    
WITH PRIVATE KEY (FILE = 'E:\Backup\Production Server keys\dbCerts\dbcert_Key.pvk',
DECRYPTION BY PASSWORD = 'j3uwgq#)hqI*2')

--3. Restore the database as regular.


Using SQL Server Management Studio

1. Create a database master key and certificate in the master database. For more information, see Using Transact-SQL below.
2. Create a backup of the server certificate in the master database. For more information, see Using Transact-SQL below.
3. In Object Explorer, right-click the Databases folder and select New Database.
4. In the New Database dialog box, in the Database name box, enter the name of the new database.
5. In the Owner box, enter the name of the new database's owner. Alternately, click the ellipsis (…) to open the Select Database Owner dialog box. For more information on creating a new database, see Create a Database.
6. In Object Explorer, click the plus sign to expand the Databases folder.
7. Right-click the database you created, point to Tasks, and select Manage Database Encryption.
   The following options are available on the Manage Database Encryption dialog box.
   Encryption Algorithm
   Displays or sets the algorithm to use for database encryption. AES128 is the default algorithm. This field cannot be blank. For more information on encryption algorithms, see Choose an Encryption Algorithm.
   Use server certificate
   Sets the encryption to be secured by a certificate. Select one from the list. If you do not have the VIEW DEFINITION permission on server certificates, this list will be empty. If a certificate method of encryption is selected, this value cannot be empty. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.
   Use server asymmetric key
   Sets the encryption to be secured by an asymmetric key. Only available asymmetric keys are displayed. Only an asymmetric key protected by an EKM module can encrypt a database using TDE.
   Set Database Encryption On
   Alters the database to turn on (checked) or turn off (unchecked) TDE.
8. When finished, click OK.