SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DAC - What to Execute when Connected?


DAC - What to Execute when Connected?

Author
Message
ps.
ps.
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7029 Visits: 3668
The sp sp_dba_DAC created without any issues and is giving desired results when fired from within the SSMS.

However i'm unable to fire it from using sqlcmd. Getting following error:
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.

I found that SQL Server Browser was disabled, i enabled it and started it and again fired it. Still same error.



Pradeep Singh
Leendert van Staalduinen
Leendert van Staalduinen
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 397
I had the very same experience as Pradeep Singh:
- My SQL Server Browser is running.
- Remote connections are enabled (both TCP/IP and named pipes).
- DBCC TRACEON (7806, -1): done.

Running in SSMS: OK. Running from a command prompt: "An error occurred while obtaining the dedicated administrator connection (DAC) port ...".
Anyone knows what is missing ?
Thanks, Leendert.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)SSC Guru (143K reputation)

Group: Administrators
Points: 143522 Visits: 19424
Have you enabled the DAC? It's disabled by default. Use the Surface Area tool to turn it on.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Leendert van Staalduinen
Leendert van Staalduinen
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 397
Steve,

Bingo. Great thanks.
We learn every day.
Leendert.
Rudy Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2354 Visits: 1322
Thanks to everyone for help me out here and for the nice comments about the article. In the new year I'll have more time and will try to update the code and add more information.

Thanks,

Rudy



billburke
billburke
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 186
The CROSS APPLY line below returns this error

Msg 102, Level 15, State 1, Line 43
Incorrect syntax near '.'.

This is true for the original code from the article and the formatted code in this discussion. What am I missing?

Thanks,

Bill

SELECT '-- Shows top 5 high cpu used statemants'
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;



billburke
billburke
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 186
oops....
never mind. I didn't notice the additional pages of comments. my db was set to compatibility level 8.0.

Sorry for the repeated (and already answered) question.



Jonathan Kehayias
Jonathan Kehayias
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6343 Visits: 1818
Steve Jones - Editor (12/21/2008)
Have you enabled the DAC? It's disabled by default. Use the Surface Area tool to turn it on.


Steve,

Isn't the DAC enabled by default? You only need to change the surface area to enable the Remote DAC.

Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs
Rudy Panigas
Rudy Panigas
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2354 Visits: 1322
There are 2 things that you need to check before executing the code in the article.

1) Enable DAC. This is DISABLED by default as per Microsoft.

2) Database should be at level 9.0 or greater. If you have placed your database in 8.0 for backward compatibility then some of the data returned will not shown as correct.

I guess I should have mentioned this in the article.

Rudy



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search