How to connect to the Dedicated Administrator Connection [DAC] in SQL Server

1.     To Enable the Remote Admin Connections :-

Start the SSMS, Connect to the Database Instance, Open a new Query editor window,
Issue the command sp_configure to see if the config_value and run_value for 'remote admin connections'  are set to 1.

if you are unable to see this option under sp_configure, Try running the command below :-

Sp_configure 'show advanced options' , '1'
reconfigure
go

then run sp_configure again to see the advanced options,

If the above said values for 'remote admin connections'  are not set to 1, use the below command to enable it.

sp_configure 'remote admin connections', '1'
reconfigure
go

Run sp_configure to verify the same.

Enable the DAC [Dedicated Administrator Connection]:-

Go to, SQL Server Surface area configuration to enable the remote DAC,

Click Start , Choose All Programs, Locate Microsoft SQL Server 2005, Point to Configuration Tools, Click on Surface area configuration  to get to this window.

Choose Surface Area Configuration for Features option, Expand the instance, expand the Database Engine, Click on DAC and on the right panel check the box against Enable remote DAC.

This will prompt you to restart the SQL Server Service for the changes to take effect. go ahead and restart the

2.     Enable the Remote admin connections with appropriate protocols :-


Click Start , Choose All Programs, Locate Microsoft SQL Server 2005, Point to Configuration Tools, Click on Surface area configuration
In the Surface area configuration window Click on Surface area Configuration for Services and Connections
Then expand the instance, expand the database engine , Click on Remote Connections
Choose Local and Remote Connections and with the appropriate protocols ( as shown in the pic ).

3.     Make sure to have the Browser Service running :-

Click Start , Choose All Programs, Locate Microsoft SQL Server 2005, Point to Configuration Tools, Click on Surface area configuration
In the Surface area configuration window Click on Surface area Configuration for Services and Connections, Expand the SQL Server Browser on the left pane below the instance name and  click on Service to change the SQL Server Browser settings accordingly.

4.     Add port in the Windows Firewall Exceptions list :-

Click Start > Choose Control Panel > Double Click on Windows Firewall > Choose the Exceptions tab and Click on Add Exceptions to add SQL Server into the list.
Sqlserver.exe will be generally located at :-
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe
Also, you need to add the SQL Browser service to have the DAC connected. Browser service will be located at :-
C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe

After adding the above two services, Please add the DAC port to the exception list to complete the process.

you can check in the error logs from which port the DAC is listening :-
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.

Once you get the port number, go ahead and add it in the exception list.

5.     Connecting to the DAC using the command prompt :-
Start > Run > cmd
run the below command in the command prompt :-

Sqlcmd -Sadmin:<servername>/<instancename>

You should get a > sign below the command, This indicates that you have successfully connected to the DAC, you can also make sure by running the below command

Select  *  from sys.dm_exec_connections  A  JOIN sys.endpoints B on (A.endpoint_id = B.endpoint_id) where B.name='Dedicated Admin Connection'  

You will get to see your connection.

Else, If you get the below error while connecting to the DAC,

HResult 0xFFFFFFFF, Level 16, State 1
SQL Network Interfaces: An error occurred while obtaining the dedicated administrator connection (DAC) port.
Make sure that SQL Browser is running, or check the error log for the port number [xFFFFFFFF].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

Check whether you have enabled the appropriate port, also, you need to check if the browser service is running and the sqlserver.exe service added in the exception list is of the same instance you are intending to use.

NOTE:- if you try connecting the Instance using the SSMS like this :-

admin:<servername>/<instancename>
you will get an error message:-

Dedicated administrator connections are not supported(object explorer)

This is because DAC is a privileged connection and wouldn't want to be allowed on the Object explorer , Microsoft states in one of its article as follows :-
 
DAC connections are only allowed for the query window because they are special highly-privileged connections used when the server would normally reject the connection. The idea is to run sp_who in the query editor and kill any rouge process. Using the DAC connection for the Object Explorer could make the problem worse.

You can connect to the instance normally, so, open a new query and then change the connection to admin:<servername>\<instancename>. This works., Then, you can verify the DAC connection either from the logs or by running the above query for DAC.

Happy Connecting.

~Cheers,
Deepak.P.

Resolving the 'KILLED/ROLLBACK' sessions in SQL Server

Hi Folks,

One of the not so frequent issues encountered off lately which doesn't has any other solution than to restart the SQL Service ( Recycling ) is when you have the Command status as 'KILLED/ROLLBACK' for any of the sessions in sysprocesses.

The Command of the sysprocesses session 'KILLED/ROLLBACK'  happens when there's any DML ( INSERT , UPDATE, DELETE ) activity happening against any database and it is being interrupted or killed before the process is complete.

Some of the scenarios where u might encounter this situation :-

1.    Database backup using any third party software is taking longer than expected and the process is being killed. This session will lead to 'Killed/Rollback' status.
2.    Any DML statement running via linked server connecting from another server and the process is being killed from either end will lead to the 'Killed/Rollback' Status.
3.    Any DMLs being killed from the application end before the process completes, will lead the process to 'Killed/Rollback' State.

To determine which process is hung or in the state of 'Killed/Rollback' , You can use the following commands :-

SP_Who2

OR

select spid, kpid, login_time, last_batch, status, hostname, nt_username, loginame  from sys.sysprocesses where cmd = 'KILLED/ROLLBACK'
After figuring out the spid and kpid, run the DBCC INPUTBUFFER (<SPID>) to know what is the batch running under this session.

There are a few blogs recommending to kill the kpid session from the task manager.
** Please be careful about what you are doing. This is not recommended, especially,If there's any third party tool involved in the process. You may hang the third party tool's service as well, while trying to kill this session from the task manager**.

You can use this technique of killing the kpid to release the session, If there's any stored procedure or a batch running against the database directly and due to some reasons it was killed and the session moved to 'Killed/Rollback' status, that should work., However, that's never going to be the case.

So, we can try using the below command to see the status of the session.

KILL  <SPID>  with STATUSONLY

If you would get the output message as follows :-
   
SPID <spid> : transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

Only if you find the percentage complete is any number other than 0% or 100% and the time remaining is other than 0 or 554 seconds, only then, you still have a hope of getting this command completed by the time displayed, else, this process can go on hung until you restart the SQL Server service.

If there's a linked server or replication process involved in this session, here's what we can do :-

Try restarting the DTC ( Distributed Transaction Coordinator) service, which could release the session from 'Killed/Rollback' Status.

This could work if there's any Linked Server or Replication service involved in the process, OR, if there's any application triggered stored procedure which was closed from the front-end before the transaction committed.

Considering this issue occurring on the Production server where you would not have the leverage of restarting the SQL Server Service frequently.

There's one more option which you could try before giving up or concluding to restarting the SQL Server service :-

KILLING THE PROCESS USING THE Dedicated Administrator's Connection (DAC).

You need to have the DAC enabled / activated before your can think of using this option.

To know more about how to connect using DAC and how to check if the DAC is enabled, Please check the post on DAC ;-
http://sqlskillport.blogspot.in/2012/03/how-to-connect-to-dedicated.html

So, if we have the DAC enabled and if you are able to connect to the DAC session, Please go ahead and run the appropriate queries to determine the spids associated with the command 'KILLED/ROLLBACK' and kill them. This could resolve the issue.

If the above said scenarios or options didn't work for you, please don't think anything else, go ahead and have the SQL Service restarted to release these sessions.

Hope this helps

~Cheers,
Deepak.P.

How To Shrink The TEMPDB Database in SQL SERVER ?

Occasionally, Every DBA will bump across this issue, Tempdb database is getting full, Tempdb Drive has exceeded the disk threshold and need to take action to reduce the file size.

DBAs having little experience would think of shrinking the database using the GUI, Right Click on the Database > Tasks > Shrink > Database / Files and then shrink the database accordingly.

DBAs with some experience would think of running the command DBCC Shrinkfile ('Filename', target_in_MB)

However, with both the Solutions / Steps,  files / database doesn't shrink  and you can see that the file size is not having any impact.

So, here  is a couple of solutions you can try to shrink the Tempdb database and reduce the size on the drive accordingly.

1.  Try running the following command :-

USE TEMPDB
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO

and then run the command.

DBCC SHRINKFILE ('FILENAME', TARGET_SIZE_IN_MB)
GO

If there are more than one Tempdb data ( Tempdev )  file , you can use those files seperately to run the DBCC Shrinkfile command to shrink the file size after clearing the DBCC System , Session and Proc Caches

These are the DBCC commands to free up some of the SQL SERVER Memory Caches.

DBCC FREESYSTEMCACHE ('ALL')
Clears all unused cache entries from all the caches.

DBCC FREESESSIONCACHE
Flushes all the distributed query connections' cache that are used against the instance of Microsoft SQL Server.

DBCC FREEPROCCACHE
Clears all elements from the procedure cache.
Freeing the procedure cache would cause, an ad-hoc SQL statement to be recompiled rather than reused from the cache.


2.  Here's another solution which can help to shrink the tempdb database.

Add a few MB to the data file and then try shrinking the file., it will release some space to the OS.

For Example:- when you find the tempdb is growing and has a couple of hundreds of MBs to reach the threshold. You can try shrinking the database normally. If there's any bulk transaction running against the instance or If there's a replication set up on the database.
Then, you can increase the tempdb data file to a few MBs and then try shrinking the file using the DBCC command to claim more space.

-Cheers

Default Location for Backup & Restore database in SQL Server

"Hello World, "
Off course, even if we start writing or printing a program, the first output program we write is printf "Hello World".
So, here i go with the same trend.
This is my first blog publish .. I have been answering questions and doubts in many forums and blogs.., i thought, its time i start writing blogs and publish articles at my own blogspot. so, here we go..

Off lately, I have been interviewing candidates for a DBA position in our company. What i could infer from the answers is that, they fail to understand the basics of how SQL Server engine works.
First and the foremost issue i would like to discuss here is that, 
If there's a backup database command issued in the management studio / Query Analyzer without providing the appropriate backup location. what is the output ?

For Ex :- 
Backup database <database name> to disk = 'databasename_date_time.bak' 
what happens ??
Obviously, we have a common answer that the query returns error asking to provide a proper location. 
WRONG !!

This query works absolutely fine and the database will be backed up accordingly. 
Now, where will the database backup be residing ? how will it be backed up ? how can we check about the backup location ?

There's a default location mentioned for every database while installing. As, the other folders are created like the Data, log and Binn, SQL Server creates a folder called Backup in the root directory.


we can check the root directory location here :- 
Right click on the Database Instance > Properties. Under the General properties you have the Root Directory.

If we check in this location, we can find a folder called Backup to which the database file will be backed up.

Even while restoring the database, if we issue a command without providing the appropriate location. what does the SQL Server do ?

It restores the database successfully. Again, when the restore command is issued.. First, it checks if there's any location provided in the query, if not, SQL Server tries to find the backup file in the default location 


"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup"  to restore the database accordingly.


You must have noticed. if you use the GUI to restore any database and when you click on the browse button to navigate to the backup file path. First, it takes you to the default location and then lets you choose the proper location.

There's a lot more to come along the way.


-Cheers