SQL Server is not responding. What is action plan?

Connect using DAC via CMD or SSMS

Connect via CMD

SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster

Once you connect to the master database run the diagnostic quires to find the problem

Correct the issue and restart the server

Find the errors from sql log using

SQLCMD –A –SmyServer –q”Exec xp_readerrorlog” –o”C:\logout.txt”

A long running query blocking all processes and not allowing new connections

Write a query and put the script file on hard disk Ex: D:\Scripts\BlockingQuery.sql

use master;

select p.spid, t.text

from sysprocesses p

CROSS APPLY sys.dm_exec_sql_text (sql_handle) t

where p.blocked = 0

and p.spid in

( select p1.blocked

from sysprocesses p1

where p1.blocked > 0

and p1.waittime > 50 )

From command prompt run the script on sql server and get the result to a text file

SQLCMD -A – SMyServer -i”C:\SQLScripts\GetBlockers.sql” -o”C:\SQLScripts\blockers.txt”

Recently added some data files to temp db and after that SQL Server is not responding

This can occur when you specify new files in a directory to which the SQL Server service account does not have access.

Start the sql server in minimal configuration mode using the startup parameter “–f”. When we specify –f the sql server creates new tempdb files at default file locations and ignore the current tempdb data files configuration. Take care when using –f as it keep the server in single user mode.

Once the server is started change the tempdb configuration settings and restart the server in full mode by removing the flag -f

A database stays in a SUSPECT or RECOVERY_PENDING State

Try to resolve this using CheckDB and any other DBCC commands if you can.

Last and final option is put the db in emergency mode and run CHECKDB with repair_allow_data_loss

(Note: Try to avoid this unless you don’t have any option as you may lose large amounts of data)