I came across this newsletter in my archives that I wrote a long time ago. However I think that there is still a need for this information. I can’t guarantee that some of the features I discuss haven’t changed or even been deprecated since I wrote this but I believe everything I wrote about is still available in SQL Server.
What do you do when SQL Server won’t respond?
The most frustrating times in a DBA’s professional life are those times when he or she cannot connect to an unresponsive server or a database. A server may not be responding because a runaway process is absorbing all the resources or because an ill-advised configuration change prevents it from coming on-line or for many other reasons.
A database may hang in RECOVERY_PENDING or SUSPECT mode if a disk crash or some other event has corrupted it.
While these times are always nerve-wracking, the anxiety level can be reduced significantly by being prepared. There are tools that can help you out of these very tight places. Don’t wait until you have an emergency to learn how to use them.
Dedicated Admin Connection
The Dedicated Admin Connection (DAC) will enable an administrator to connect to an otherwise unresponsive SQL server. Connecting via the DAC allows you to run diagnostic queries to find out what’s going on and hopefully to correct the problem.
By default, DAC is not enabled for remote connections to the server. You must connect from the SQL server itself. If your server is in a hosted environment, it is a good idea to enable remote admin connections before you need them. Remember, you won’t be able to change the option when the server is unresponsive. Remember also that only one DAC is allowed to the server at any given time.
To enable remote admin connections, execute this query while logged in as a sysadmin:
exec sp_configure ‘remote admin connections’, 1
There are security issues associated with enabling this option so before making the change make sure it does not violate existing security policy.
You can use SQLCMD or Management Studio to make an admin connection. In many situations SQLCMD is the preferred option because of its smaller footprint. If you use Management Studio you need to preface the server name in the connection dialog with “ADMIN:” as in ADMIN:MYSERVER.
Because SQLCMD is closely associated with dedicated admin connections, it is a tool a DBA must be able to use when the need arises. It has a very light footprint compared to Management Studio and is sometimes the only way to connect to an unresponsive server.
SQLCMD is the successor to the previous command line interfaces for SQL Server, oSQL and iSQL. SQLCMD uses most of the same command line switches as its predecessors. If you are familiar with either, you should be comfortable using SQLCMD.
One important new option in SQLCMD is the -A switch. This switch establishes a Dedicated Admin connection.
If you are using Windows authentication to connect to SQL Server, open a Windows Command Prompt and type the following command:
SQLCMD -A -SMyServer –dmaster. Press ENTER.
Be aware that all command line switches are case sensitive. Use double quotes if a command line parameter has spaces or unusual characters.
If you do not specify user credentials as in the command above, SQLCMD attempts to connect via Windows authentication. If you need to log in with SQL Server credentials you will use -U and -P switches like this.
SQLCMD -A –U myadminlogin –P mypassword -SMyServer –dmaster
Once you are connected you can submit queries or call stored procedures just as you would in Management Studio. Query text can be broken into multiple lines. When you have entered the query text, press ENTER again to get a new line and type “GO”. When you press ENTER again, the query will execute and return results to the command window.
Other command line switches allow you to specify the path to a .SQL file to be executed or to specify a file to receive the query output. We will illustrate how the switches work in the use-cases below.
Case 1: A long-running query is blocking other processes and users can’t make new connections.
Open SQLCMD with an admin connection as shown above. Type in and execute the following query to find the root of the blocking chain
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 )
This query returns all spids that have been blocking other sessions for at least 50ms but are not blocked themselves. The query also returns the most recent command executed on the spid.
In some cases you may want to kill these spids with the KILL command. Knowing the code being executed will give you more information about whether or not you can safely kill the blocking session. Keep in mind that the rollback after you kill the process might take a considerable amount of time in itself.
You might think that this is a lot of typing to do with sweaty palms and a bunch of disgruntled users gathering outside your cube. This is where a little preparation and the -i command line switch will come in handy.
If you kept a set of your favorite diagnostic queries in SQL batch files, you could simply reference the file in the command line and it would be executed. Like this:
SQLCMD -A – SMyServer -i”C:\SQLScripts\GetBlockers.sql”
If you don’t like reading results that have been garbled in the command window, you could use the command line switch -o to send the output to a neat and easy to read text file:
SQLCMD -A – SMyServer -i”C:\SQLScripts\GetBlockers.sql” -o”C:\SQLScripts\blockers.txt”
Everyone will marvel at how well-prepared the dba was to deal with this problem.
Case 2: I added some data files to tempdb, now SQL Server won’t respond.
This can occur when you specify new files in a directory to which the SQL Server service account does not have access. The tool that will save the day here is the –f SQL Server startup parameter.
This parameter starts SQL Server in minimal configuration mode, creating a small tempdb in the default install directory. With this flag, SQL Server will ignore the current tempdb configuration when it starts up.
To set this flag, go into the SQL Server Configuration Manager. Select SQL Server Services then select Advanced. You can edit the existing startup parameters by selecting Startup Parameters. Add the –f flag then restart SQL Server. Connect to SQL Server using SQLCMD or Management Studio, correct the tempdb configuration then restart.
You might have difficulty connecting to SQL Server because the –f flag also sets the server to Single User Mode. If there are other users or applications attempting logins with admin level access, the single allowed connection is likely to be taken before you can get to it. In this case you might have to shut down all other access to the SQL Server.
In a complex environment it can be very difficult to shut down all applications and ad hoc user connections at their source. However, if you are working from the SQL Server itself, you might consider disabling the protocols supporting remote connections, usually tcp/ip or named pipes. This can be done in SQL Configuration Manager.
Processes like Management Studio or SQLCMD that are running on the server itself will still be able to connect using Shared Memory but remote connections will not be able to connect to the SQL server. This gives you a much better chance to get the single allowed connection.
Case 3: I have no idea what might be causing the problem
If you don’t have a good idea what the problem might be, perhaps a look at the SQL Error Log is the first order of business.
If the server is overwhelmed you might not be able to open the error log from Management Studio. While you can usually open the log with Notepad, you might also consider using SQLCMD with the undocumented extended stored procedure xp_readerrorlog because of the filtering capabilities of the procedure.
By default, xp_readerrorlog returns the entire contents of the current error log. This can put an even heavier load on an already-stressed SQL server. But xp_readerrorlog has four undocumented optional parameters that can filter the output to return just the records of interest with much less impact on the server.
Exec xp_readerrorlog @lognumber, @LogType, @SearchTerm1, @SearchTerm2
The first optional parameter is the number of the log file to read. Zero is the default and designates the current error log. 1 is the next most recent log file, etc.
Parameter 2 accepts values of 1 or 2. 1, the default, causes it to read the SQL error log; 2 causes it to read the SQL Agent log.
Parameter 3 is the first filter parameter. It accepts a string value and will only return lines from the log containing that value.
Parameter 4 is similar to Parameter 3. It allows you to specify another search term. The two search parameters are joined in a logical AND condition so the rows returned must contain BOTH search terms.
Here is an example of a Windows command line using SQLCMD that opens an admin connection and returns the entire contents of the current SQL error log:
SQLCMD –A –SmyServer –q”Exec xp_readerrorlog” –o”C:\logout.txt”
I have also included an output file parameter in this command because it is difficult to read a large amount of text in the command window. This command will create the specified file in the specified directory (the directory must already exist). It will also overwrite any file of the same name in the directory.
Here is an example of a filtered query. It will find records in the current errorlog that might indicate a disk problem.
SQLCMD –A –SmyServer –q”Exec xp_readerrorlog 0,1,’error’,’323’” –o”C:\logout.txt”
Error number 323 occurs on certain types of i/o failures and usually indicates a disk hardware problem. Using a little imagination, you can craft a search filters to suit your own situation.
Case 4: A database stays in a SUSPECT or RECOVERY_PENDING State.
A database in SUSPECT or RECOVERY_PENDING state cannot be opened by normal means. In earlier editions of SQL Server, this was the end of the line for a corrupt database. If you could not open it, you could not run CheckDB with the REPAIR_ALLOW_DATA_LOSS option.
Your database has to be restored from the most recent known-good backup. In cases of gradually accumulating corruption, the last good backup might be weeks or months old.
Beginning with SQL 2005, the Alter Database command has a SET EMERGENCY option that will allow you to open a suspect database in order to have one last chance at recovering it. The only command you can run when in emergency mode is DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS
The most important point to keep in mind about Emergency Mode Repair is that this is the very last chance you will have to recover your database, so make sure you have tried everything else possible before trying Emergency Mode.
The basic steps are relatively simple. You put the database in emergency mode with the command “ALTER DATABASE [dbname] SET EMERGENCY”, then you run “DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS”.
Before pulling the trigger on this, consider long and deeply whether you have exhausted all your other options. Emergency mode recovery can result in the loss of serious amounts of data up to and including losing the entire database . On the optimistic side, it often results in little data loss and everyone is your friend again.
- Learn what you need to know to deal with situations when a SQL Server or a database is unresponsive. And learn it before you need to know it.
- Practice opening a dedicated admin connection through SQLCMD and running a few commands.
- Consider packaging your favorite diagnostic scripts for easy access via SQLCMD.
- Read Paul Randal’s several blog posts about Emergency Mode Repair.