Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
coolerbob
Aged Yak Warrior
841 Posts |
 Posted - 2007-06-19 : 05:30:56
|
| Am I imagining it or did I read somewhere once about an tool/command/method that allows the administrator/dba to access the server even if no-one else can - probably only in certain scenarios.I think it had a use for a situation where all the connections are used up and no further connection pooling can kick in for a while. This tool enabled the dba to still get to the server and I presume still be able to run stuff like sp_who and kill.Does this ring a bell with anyone? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-19 : 05:36:48
|
| Could be thishttp://www.microsoft.com/technet/prodtechnol/sql/2005/sqlydba.mspxor maybe emergency mode - which enables you to access a suspect database. Used to update the system tables to do it but now it's an alter database command.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
coolerbob
Aged Yak Warrior
841 Posts |
Posted - 2007-06-19 : 05:52:17
|
| Yeah, SQLCMD utility at the command prompt by using the -A switch , under "Dedicated Administrator Connection".Thanks mate.I'll play with that now.Wouldn't want to be trying that for the first time when it's all going Pete Tong.Update:In case anyone else reads this...You'll need to enable the DAC first, like this:http://www.sqlserver2005.de/ScreenCasts/Enable_DAC.htm |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-19 : 05:56:02
|
| You need to enable the access first I believe - another reason for not waiting until you need it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-19 : 12:18:37
|
quote:
Originally posted by spirit1
you can also run it from SSMShttp://weblogs.sqlteam.com/mladenp/archive/2007/02/19/60108.aspx_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp
You can also use if from SQL Server Management Studio Query Editor, according to SQL Server 2005 BOL:"The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). For more information about using sqlcmd, see Using sqlcmd with Scripting Variables. You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<instance_name>. You can also initiate a DAC from a SQL Server Management Studio Query Editor by connecting to admin:<instance_name>."To enable it:"SQL Server 2005 Books Onlineremote admin connections OptionMicrosoft SQL Server 2005 provides a dedicated administrator connection (DAC). The DAC lets an administrator access a running server in order to execute diagnostic functions or Transact-SQL statements, or troubleshoot problems on the server, even when the server is locked or running in an abnormal state and not responding to a Database Engine connection. By default, the DAC is only available from a client on the server. Use the remote admin connections option of sp_configure to allow client applications on remote computers to use the DAC.By default the DAC only listens on the loop-back IP address (127.0.0.1), port 1434.Possible values of the remote admin connections setting:0 - indicates only local connections are allowed using the DAC1 - indicates remote connections are allowed using the DACExample:The following code enables the DAC from a remote computer:sp_configure 'remote admin connections', 1;GORECONFIGURE;GO"CODO ERGO SUM |
|
|
|
|
|
|
Gain access to locked server