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.
Plan a) To use the DAC you can either
access it using SQLCMD from a command
prompt
by using SQL Server Management Studio with the ADMIN: option when connecting to the server. To use this option:
Solution:
- 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)
- 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
