SQLServerCentral / Discuss content posted by Rudy Panigas / Article Discussions / Article Discussions by Author / DAC - What to Execute when Connected? / Latest Posts

RE: DAC - What to Execute when Connected?

Rudy Panigas — Wed, 24 Dec 2008 14:59:41 GMT

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

RE: DAC - What to Execute when Connected?

Jonathan Kehayias — Mon, 22 Dec 2008 15:06:16 GMT

[quote][b]Steve Jones - Editor (12/21/2008)[/b][hr]Have you enabled the DAC? It's disabled by default. Use the Surface Area tool to turn it on.[/quote]Steve,Isn't the DAC enabled by default? You only need to change the surface area to enable the Remote DAC.

RE: DAC - What to Execute when Connected?

billburke — Mon, 22 Dec 2008 14:08:59 GMT

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.

RE: DAC - What to Execute when Connected?

billburke — Mon, 22 Dec 2008 14:01:13 GMT

The CROSS APPLY line below returns this errorMsg 102, Level 15, State 1, Line 43Incorrect syntax near '.'.This is true for the original code from the article and the formatted code in this discussion. What am I missing?Thanks,BillSELECT '-- 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_textFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_worker_time/execution_count DESC;

RE: DAC - What to Execute when Connected?

Rudy Panigas — Mon, 22 Dec 2008 13:26:53 GMT

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

RE: DAC - What to Execute when Connected?

Leendert van Staalduinen — Sun, 21 Dec 2008 12:00:23 GMT

Steve,Bingo. Great thanks.We learn every day.Leendert.

RE: DAC - What to Execute when Connected?

Steve Jones - SSC Editor — Sun, 21 Dec 2008 09:20:01 GMT

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

RE: DAC - What to Execute when Connected?

Leendert van Staalduinen — Sun, 21 Dec 2008 08:44:27 GMT

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.

RE: DAC - What to Execute when Connected?

ps. — Sun, 21 Dec 2008 05:31:43 GMT

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:[i]HResult 0xFFFFFFFF, Level 16, State 1SQL 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]I found that SQL Server Browser was disabled, i enabled it and started it and again fired it. Still same error.

RE: DAC - What to Execute when Connected?

Anipaul — Sun, 21 Dec 2008 01:23:30 GMT

It is a wonderful article...:)

RE: DAC - What to Execute when Connected?

David O — Sat, 20 Dec 2008 09:19:57 GMT

Good stuff guys, I'm going to make this part of our standard server build.Probably add an exec sp_readerrorlog after the fixed driver proc.

RE: DAC - What to Execute when Connected?

Leendert van Staalduinen — Fri, 19 Dec 2008 22:56:21 GMT

I found what was wrong with:"CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st":I worked in SSMS / SQL Server 2005 connected to a database having "Compatibility level: SQL Server 2000 (80)".Either connecting to the master database or changing to "Compatibility level: SQL Server 2005 (90)" did the job.Thanks, Leendert.

RE: DAC - What to Execute when Connected?

sammesel — Fri, 19 Dec 2008 13:19:25 GMT

I did, and fixed a minor problem: If your SQL Server is CASE SENSITIVE the original code won't work due some names spelled in CAPITALs. The attached .txt file fixes these little problems.Great article.Thanks a lot

RE: DAC - What to Execute when Connected?

Steve Jones - SSC Editor — Fri, 19 Dec 2008 13:04:20 GMT

The code has been edited to be one long file.

RE: DAC - What to Execute when Connected?

Rudy Panigas — Fri, 19 Dec 2008 12:15:54 GMT

Would you be willing to post an updated version of the complete code to correct the issues? As mentioned I don't have much time right now but will look at all the comments and update the code sometime in Feb 2009.Thanks,Rudy

RE: DAC - What to Execute when Connected?

Jonathan Kehayias — Fri, 19 Dec 2008 12:11:56 GMT

As my coworker jbwillia pointed out, it doesn't support case insensitive collation databases like Adventure works, and it also fails for databases like the Sharepoint Admin databases which are installed by default with a GUID in the database name so bracketing the ? as [?] is necessary for those databases. I also don't see that it is dumping some significant items like the memory clerks which could help explain why the system is not responding properly. Just some small feedback items, and certainly not detracting from the article. We didn't have a set of code for this kind of scenario in place prior to today, but we will after the article since it isn't difficult to put into place. I tend to be motivated out of necessity for creating scripts like this, but this article points out the need.

RE: DAC - What to Execute when Connected?

Rudy Panigas — Fri, 19 Dec 2008 12:07:59 GMT

WOW! Love all the comments and code changes everyone is posting! Now Mr Kehayias ask a good question"If this is intended for SQL 2005/2008 why are you using legacy views like sysdatabases and sysfiles in the code. You also are not using the schema qualifiers in the code."The code was written to quickly get some information. I have a collection of code from other projects, copy/paste some and wrote some to make this new one. So yes, there is older type code but it works so why not use it (for now). I didn't have a lot of time to write the code as we has a production issue to address. If anyone would like to update the code and post it here, that would be great. But the code "as is" worked just fine and help me to correct the production issue.Rudy

RE: DAC - What to Execute when Connected?

gagandeshpande — Fri, 19 Dec 2008 11:58:27 GMT

Got it, Thank you !!

RE: DAC - What to Execute when Connected?

gagandeshpande — Fri, 19 Dec 2008 11:55:01 GMT

The output for sp_help sp_MSForEachDB is the following.Msg 15009, Level 16, State 1, Procedure sp_help, Line 66The object 'sp_MSForEachDB' does not exist in database 'master' or is invalid for this operation.

RE: DAC - What to Execute when Connected?

jbwillia — Fri, 19 Dec 2008 11:53:18 GMT

That was definitley a nice article and the code works. I only had to do minor changes like:exec sp_MSForEachDB 'Use [?] SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' ) AS float)/128.0 AS ''Available Space In MB'' FROM sysfiles'They way you had it wriiten does not support case sensitive collation.

RE: DAC - What to Execute when Connected?

gagandeshpande — Fri, 19 Dec 2008 11:50:59 GMT

I tried thisSELECT '--Shows avail free DB space ' as ' ' exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' ) AS int)/128.0 AS ''Available Space In MB'' FROM sysfiles'Gave me the following output(1 row(s) affected)Msg 2812, Level 16, State 62, Line 2Could not find stored procedure 'sp_MSForEachDB'.Results was only--Shows avail free DB spaceIs the installation missing the procedure ?

RE: DAC - What to Execute when Connected?

Jonathan Kehayias — Fri, 19 Dec 2008 11:47:42 GMT

Rudy,If this is intended for SQL 2005/2008 why are you using legacy views like sysdatabases and sysfiles in the code. You also are not using the schema qualifiers in the code.

RE: DAC - What to Execute when Connected?

Jerry Hung — Fri, 19 Dec 2008 11:41:43 GMT

It worked fine for me on SQL 2008You have to remove the period and lower case 'sysfiles' (SYSFILES won't work for me)[code]exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' ) AS int)/128.0 AS ''Available Space In MB'' FROM [b]sysfiles[/b]' --select * from sysfiles[/code]

RE: DAC - What to Execute when Connected?

gagandeshpande — Fri, 19 Dec 2008 11:33:17 GMT

Need some help...The following gives an error.SELECT '--Shows avail free DB space ' as ' ' exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' ) AS int)/128.0 AS ''Available Space In MB'' FROM .SYSFILES'I also got this when I tried to execute - EXEC sp_msForEachDB 'PRINT ''?'''Could not find stored procedure 'sp_msForEachDB'.We are on SQL Server Enterprise 2005Any ideas ?RegardsGagan

RE: DAC - What to Execute when Connected?

Leendert van Staalduinen — Fri, 19 Dec 2008 09:58:36 GMT

Rudy, thanks.I am using SQL Server 2005, Leendert.

RE: DAC - What to Execute when Connected?

Rudy Panigas — Fri, 19 Dec 2008 09:27:26 GMT

Are you executing the code on a SQL 2005? Does not work on SQL 2000.Here is the link for more information on CROSS APPLYhttp://msdn.microsoft.com/en-us/library/ms177634.aspxRudy

RE: DAC - What to Execute when Connected?

Leendert van Staalduinen — Fri, 19 Dec 2008 08:35:47 GMT

We learn every day, but my SSMS complains about the line:"CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st".I do not know what is going wrong.Leendert.

RE: DAC - What to Execute when Connected?

dparmender — Fri, 19 Dec 2008 08:26:55 GMT

Thanks for the updated code. this code will be a great asset for all dba's.

RE: DAC - What to Execute when Connected?

mtillman-921105 — Fri, 19 Dec 2008 08:22:59 GMT

Our abbreviations are beginning to overlap often. Since I like audo, I see "DAC" and I think "Digital to Analogue Converter."

RE: DAC - What to Execute when Connected?

Rudy Panigas — Fri, 19 Dec 2008 08:03:40 GMT

This works on SQL 2005/2008, x32bit and x64bit, stand alone or on a multi instance installation. DAC is not available in SQL 2000 or earlier, but I wish it did.Rudy

RE: DAC - What to Execute when Connected?

Patrick Draper — Fri, 19 Dec 2008 07:58:15 GMT

Does this stored procedure SQL 2K or is it for 2005/08 only. I have had a look but get a number of errors.

RE: DAC - What to Execute when Connected?

Ninja's_RGR'us — Fri, 19 Dec 2008 07:56:55 GMT

NP, it's easy to ask for something and wait for someone to have it done ;).

RE: DAC - What to Execute when Connected?

Rudy Panigas — Fri, 19 Dec 2008 07:52:26 GMT

Thanks to Ninja for providing the code in a copy/paste format. Not sure why the article's code is showing up that way.By the way, if you copy the complete article and paste it into MS Word, the code is now converted to a usable format.Rudy

RE: DAC - What to Execute when Connected?

nmoore — Fri, 19 Dec 2008 07:48:57 GMT

you can call [code]wmic volume get caption,capacity,freespace[/code]to get the freespace on all drives including mount points.Make this call from xp_cmdshell and place the results into a table if you want to processs them further.

RE: DAC - What to Execute when Connected?

Rudy Panigas — Fri, 19 Dec 2008 07:19:10 GMT

Thanks you for providing better code. I'm hoping that eventually we'll have a a nice stored procedure that you can execute when/if the SQL server gets into trouble.Is there any other information you think should be collected?Rudy

RE: DAC - What to Execute when Connected?

D.Oc — Fri, 19 Dec 2008 07:04:52 GMT

[quote][b]Ninja's_RGR'us (12/19/2008)[/b][hr]Nice article. Anyone care to spend 20 minutes doing copy/paste work to put all that code in a single file and test it so that this article can actually usable?[/quote]you mean something like this[code]USE masterGO-- This stored procedure will give you infomation on the SQL server in question.-- Connect with DAC and then execute this stored procedure located in the master databaseCREATE PROC sp_dba_DACASSELECT '*** Start of DAC Report ***'SELECT '-- Shows SQL Servers information'EXEC ('USE MASTER') SELECT CONVERT(char(20), SERVERPROPERTY('MachineName')) AS 'MACHINE NAME', CONVERT(char(20), SERVERPROPERTY('ServerName')) AS 'SQL SERVER NAME', (CASE WHEN CONVERT(char(20), SERVERPROPERTY('InstanceName')) IS NULL THEN 'Default Instance' ELSE CONVERT(char(20), SERVERPROPERTY('InstanceName')) END) AS 'INSTANCE NAME',CONVERT(char(20), SERVERPROPERTY('EDITION')) AS EDITION, CONVERT(char(20), SERVERPROPERTY('ProductVersion')) AS 'PRODUCT VERSION', CONVERT(char(20), SERVERPROPERTY('ProductLevel')) AS 'PRODUCT LEVL',(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 1 THEN 'Clustered' WHEN CONVERT(char(20), SERVERPROPERTY('ISClustered')) = 0 THEN 'NOT Clustered' ELSE 'INVALID INPUT/ERROR' END) AS 'FAILOVER CLUSTERED',(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 1 THEN 'Integrated Security ' WHEN CONVERT(char(20), SERVERPROPERTY('ISIntegratedSecurityOnly')) = 0 THEN 'SQL Server Security ' ELSE 'INVALID INPUT/ERROR' END) AS 'SECURITY',(CASE WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 1 THEN 'Single User' WHEN CONVERT(char(20), SERVERPROPERTY('ISSingleUser')) = 0 THEN 'Multi User' ELSE 'INVALID INPUT/ERROR' END) AS 'USER MODE',CONVERT(char(30), SERVERPROPERTY('COLLATION')) AS COLLATIONSELECT '-- 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_textFROM sys.dm_exec_query_stats AS qsCROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS stORDER BY total_worker_time/execution_count DESC;SELECT '-- Shows who so logged in'SELECT login_name ,COUNT(session_id) AS session_count FROM sys.dm_exec_sessions GROUP BY login_name;SELECT '-- Shows long running cursors'EXEC ('USE master')SELECT creation_time ,cursor_id ,name ,c.session_id ,login_name FROM sys.dm_exec_cursors(0) AS c JOIN sys.dm_exec_sessions AS s ON c.session_id = s.session_id WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;SELECT '-- Shows idle sessions that have open transactions'SELECT s.* FROM sys.dm_exec_sessions AS sWHERE EXISTS ( SELECT * FROM sys.dm_tran_session_transactions AS t WHERE t.session_id = s.session_id ) AND NOT EXISTS ( SELECT * FROM sys.dm_exec_requests AS r WHERE r.session_id = s.session_id );SELECT '-- Shows free space in tempdb database'SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;SELECT '-- Shows total disk allocated to tempdb database'SELECT SUM(size)*1.0/128 AS [size in MB]FROM tempdb.sys.database_filesSELECT '-- Show active jobs'SELECT DB_NAME(database_id) AS [Database], COUNT(*) AS [Active Async Jobs]FROM sys.dm_exec_background_job_queueWHERE in_progress = 1GROUP BY database_id;SELECT '--Shows clients connected'SELECT session_id, client_net_address, client_tcp_portFROM sys.dm_exec_connections;SELECT '--Shows running batch'SELECT * FROM sys.dm_exec_requests;SELECT '--Shows currently blocked requests'SELECT session_id ,status ,blocking_session_id ,wait_type ,wait_time ,wait_resource ,transaction_id FROM sys.dm_exec_requests WHERE status = N'suspended'SELECT '--Shows last backup dates ' as ' 'SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup, ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.nameSELECT '--Shows jobs that are still executing' as ' ' exec msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULLSELECT '--Shows failed MS SQL jobs report' as ' ' SELECT name FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobservers B WHERE A.job_id = B.job_id AND B.last_run_outcome = 0SELECT '--Shows disabled jobs ' as ' ' SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY nameSELECT '--Shows avail free DB space ' as ' ' exec sp_MSForEachDB 'Use ? SELECT name AS ''Name of File'', size/128.0 -CAST(FILEPROPERTY(name, ''SpaceUsed'' ) AS int)/128.0 AS ''Available Space In MB'' FROM .SYSFILES'SELECT '--Shows total DB size (.MDF+.LDF)' as ' ' set nocount on declare @name sysname declare @SQL nvarchar(600) -- Use temporary table to sum up database size w/o using group by create table #databases ( DATABASE_NAME sysname NOT NULL, size int NOT NULL) declare c1 cursor for select name from master.dbo.sysdatabases -- where has_dbaccess(name) = 1 -- Only look at databases to which we have access open c1 fetch c1 into @namewhile @@fetch_status >= 0 begin select @SQL = 'insert into #databases select N'''+ @name + ''', sum(size) from ' + QuoteName(@name) + '.dbo.sysfiles' -- Insert row for each database execute (@SQL) fetch c1 into @name end deallocate c1select DATABASE_NAME, DATABASE_SIZE_MB = size*8/1000 -- Convert from 8192 byte pages to K and then convert to MB from #databases order by 1 select SUM(size*8/1000)as '--Shows disk space used - ALL DBs - MB ' from #databases drop table #databasesSELECT '--Show hard drive space available ' as ' ' EXEC master..xp_fixeddrivesSELECT '*** End of Report **** 'GO[/code]

RE: DAC - What to Execute when Connected?

nmoore — Fri, 19 Dec 2008 06:41:55 GMT

Interesting article.Just a note - xp_fixeddrives doesn't return any space info on mount points in the filesystem below the drive letters. If you have database file in such locations thats an important point.

RE: DAC - What to Execute when Connected?

Ninja's_RGR'us — Fri, 19 Dec 2008 06:26:38 GMT

Nice article. Anyone care to spend 20 minutes doing copy/paste work to put all that code in a single file and test it so that this article can actually usable?

DAC - What to Execute when Connected?

Rudy Panigas — Thu, 18 Dec 2008 21:27:12 GMT

Comments posted to this topic are about the item [B]DAC - What to Execute when Connected?[/B]