Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

DAC - What to Execute when Connected? Expand / Collapse
Author
Message
Posted Sunday, December 21, 2008 5:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, July 6, 2014 3:07 PM
Points: 2,242, Visits: 3,643
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
Post #623568
Posted Sunday, December 21, 2008 8:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 17, 2013 1:19 PM
Points: 29, 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.


Post #623598
Posted Sunday, December 21, 2008 9:20 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:30 PM
Points: 33,055, Visits: 15,167
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
Post #623608
Posted Sunday, December 21, 2008 12:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, November 17, 2013 1:19 PM
Points: 29, Visits: 397
Steve,

Bingo. Great thanks.
We learn every day.
Leendert.
Post #623632
Posted Monday, December 22, 2008 1:26 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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



Post #624211
Posted Monday, December 22, 2008 2:01 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 26, 2012 4:51 PM
Points: 160, 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;



Post #624247
Posted Monday, December 22, 2008 2:08 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 26, 2012 4:51 PM
Points: 160, 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.



Post #624254
Posted Monday, December 22, 2008 3:06 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:55 AM
Points: 1,708, Visits: 1,792
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
Post #624287
Posted Wednesday, December 24, 2008 2:59 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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



Post #625583
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse