If your SQL server is in trouble and you cannot connect then the next option is to connect with the Dedicated Administrators Connection (DAC).
Once connected, you can collect some information on what is happening. Since you are under pressure, it would be nice to have a stored procedure to execute that displays information? Below is my script that does just that.
You want to execute the code below in the MASTER database to create the sp_dba_DAC stored procedure. We do this in MASTER because you generally have access to this database when the server is extremely busy.
Now open a CMD/DOS window and execute the following.
sqlcmd -A -S<server/instance name> -dmaster -E -Q"sp_dba_DAC" -oc:\dac-results.txt
Replace the <server/instance name> with your server or instance name.
Once you execute this command, a connection is made to the server/instance and the stored procedure "sp_dba_DAC" located in the MASTER database is executed. A file called "dac-results.txt" is created in the root of C: and the connection is terminated.
Open the "dac-results.txt" file with notepad (or any other editor) and you can quickly see the information.
Below is what is collected:
Shows SQL Servers information
Shows top 5 high cpu used statemants
Shows who so logged in
Shows long running cursors
Shows idle sessions that have open transactions
Shows free space in tempdb database
Shows total disk allocated to tempdb database
Show active jobs
Shows clients connected
Shows running batch
Shows currently blocked requests
Shows last backup dates
Shows jobs that are still executing
Shows failed MS SQL jobs report
Shows disabled jobs
Shows avail free DB space
Shows total DB size (.MDF+.LDF)
Show hard drive space available
Remember to create this stored procedure on your servers before you need to connect with DAC. Add additional stored procedure(s) or T-SQL code to provide even more information.
Download script here: https://docs.google.com/file/d/0B07PMQYOhF2DZEpONk9NLW1FOXM/edit?usp=sharing
About Me - Who is DR DBA?
- Who am I?
- Hello. My name is Rudy Panigas and I am a Production Senior Microsoft SQL Server Database Administrator (DBA) with over 14 years of experience. Have published articles with SQL Central, am a leader for the my local PASS chapter in Toronto (TORPASS) and love to automate anything SQL including disaster recovery. I created this blog to share my views, knowledge and to discuss Microsoft SQL server in order to help out others in this field. Please test these scripts before using. I do not warranty and do not take any responsibility for it, its misuse or output it creates. You can reach me at this email address: firstname.lastname@example.org