DAC - What to Execute when Connected?

Question

Post reply

DAC - What to Execute when Connected?

Viewing 9 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply

ps. One Orange Chip Points: 29422 More actions · Answer 1

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 SSC Eights! Points: 827 More actions · Answer 2

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 - SSC Editor SSC Guru Points: 734449 More actions · Answer 3

Have you enabled the DAC? It's disabled by default. Use the Surface Area tool to turn it on.

Leendert van Staalduinen SSC Eights! Points: 827 More actions · Answer 4

Leendert van Staalduinen

SSC Eights!

Points: 827

December 21, 2008 at 12:00 pm

#915884

Steve,

Bingo. Great thanks.

We learn every day.

Leendert.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 5

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 Say Hey Kid Points: 704 More actions · Answer 6

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 Say Hey Kid Points: 704 More actions · Answer 7

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 One Orange Chip Points: 26778 More actions · Answer 8

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.

Rudy Panigas SSChampion Points: 10702 More actions · Answer 9

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