Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

DAC - What to Execute when Connected?

By Rudy Panigas,

If your SQL server is in trouble and you cannot connect then the next option is to connect with the Dedicated Administators Connection (DAC). Once connected, you can collect some information on what is happening. Since you are under pressure, it would be nice to have a stored procedure to execute that displays information? Below is my script that does just that.

You want to execute the code below in the MASTER database to create the sp_dba_DAC stored procedure. We do this in MASTER because you generally have access to this database when the server is extremely busy. Now open a CMD/DOS window and execute the following.

sqlcmd -A -S<server/instance name> -dmaster -E -Q"sp_dba_DAC" -oc:\dac-results.txt

Replace the <server/instance name> with your server or instance name.

Once you execute this command, a connection is made to the server/instance and the stored procedure "sp_dba_DAC" located in the MASTER dataabse is executed. A file called "dac-results.txt" is created in the root of C: and the connection is terminated.

Open the "dac-results.txt" file with notepad (or any other editor) and you can quickly see the information.

Below is what is collected

  • Shows SQL Servers information
  • Shows top 5 high cpu used statemants
  • Shows who so logged in
  • Shows long running cursors
  • Shows idle sessions that have open transactions
  • Shows free space in tempdb database
  • Shows total disk allocated to tempdb database
  • Show active jobs
  • Shows clients connected
  • Shows running batch
  • Shows currently blocked requests
  • Shows last backup dates
  • Shows jobs that are still executing
  • Shows failed MS SQL jobs report
  • Shows disabled jobs
  • Shows avail free DB space
  • Shows total DB size (.MDF+.LDF)
  • Show hard drive space available

Remember to create this stored procedure on your servers before you need to connect with DAC. Add additional stored procedure(s) or T-SQL code to provide even more information.

---------------------------------------------------------------------------------------------------

USE master
GO -- 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 database CREATE PROC sp_dba_DAC
AS SELECT '*** 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 COLLATION 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; 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 s
WHERE 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_files SELECT '-- Show active jobs'
SELECT DB_NAME(database_id) AS [Database], COUNT(*) AS [Active Async Jobs]
FROM sys.dm_exec_background_job_queue
WHERE in_progress = 1
GROUP BY database_id; SELECT '--Shows clients connected'
SELECT session_id, client_net_address, client_tcp_port
FROM 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.name SELECT '--Shows jobs that are still executing' as ' '
exec msdb.dbo.sp_get_composite_job_info NULL, NULL, NULL, NULL, NULL, NULL, 1, NULL, NULL SELECT '--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 = 0 SELECT '--Shows disabled jobs ' as ' '
SELECT name FROM msdb.dbo.sysjobs WHERE enabled = 0 ORDER BY name 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' 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 @name while @@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 c1 select 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 #databases SELECT '--Show hard drive space available ' as ' '
EXEC master..xp_fixeddrives SELECT '*** End of Report **** ' GO
Total article views: 5784 | Views in the last 30 days: 3
 
Related Articles
FORUM

@@servername and serverproperty('servername') gives different values

select serverproperty('servername') and select @@servername shows different names

BLOG

DMV-11 : T-Log space used by transaction……..sys.dm_tran_database_transactions

sys.dm_tran_database_transactions DMV (Dynamic Management View), described by BOL as follows: http:/...

FORUM

Microsoft bug? sys.dm_db_index_physical_stats breaks when any database is off-line

sys.dm_db_index_physical_stats breaks when any database is off-line

BLOG

Time Trial sys.dm_exec_query_plan vs sys.dm_exec_sql_text

I’ve been working on a way to test performance usage on a server by database. I started out using sy...

FORUM

Auto-Select Database Name

Auto-Select Database Name

Tags
dac    
monitoring    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones