DAC in SQL Server 2005

SQL SERVER - DAC in SQL Server 2005

Hi consider there was situation where you went out of resources and corruption stopping the user trying to connect SQL Server.

Simply, restarting the SQL Server restart sometimes results in database shutdown or some times make the database to shutdown or during the startup the database takes longer time to roll back process.

What is DAC?

In SQL Server 2005, Microsoft offered a new feature called DAC connection. DAC stands for dedicated administrator connection.  This configuration is intended to serve as a last means of defense to troubleshoot and kill the offending SQL Server processes as opposed to rebooting the server possibly causing database corruption and/or access violations. The DAC allows you to connect to the server and issue T-SQL commands to troubleshoot and correct the problem.

Solution:

Plan a) To use the DAC you can either access it using SQLCMD from a command prompt

• sqlcmd -A -d master (the -A uses the DAC and the -d connects to the master database)
• sqlcmd -A -d master -E -S Ganapathi (the -E uses Windows authentication and the -S connects the server and instance)
• I had used the default instance named Ganapathi which is also my servername

Now, What is sqlcmd utility?

sqlcmd utility, a Microsoft Win32 command prompt utility, to run ad hoc Transact-SQL statements and scripts.

For more information read it here: SQLCMD

• sqlcmd -A -d master (the -A uses the DAC and the -d connects to the master database)
• sqlcmd -A -d master -E -S Ganapathi (the -E uses Windows authentication and the -S connects the server and instance)

by using SQL Server Management Studio with the ADMIN: option when connecting to the server.  To use this option:

• launch SQL Server Management Studio (don't connect to an instance of SQL Server yet)
• select Database Engine Query (icon right next to "New Query")
• put "ADMIN:" in front of the server\instance name
• use your regular Authentication procedures to connect
• and select Connect

Appreciate your suggestions. Please comment your feedback and reach me out at email

Regards,

Ganapathi varma

Reference: (http://mssqllover.blogspot.in)

Linkedin