Script to Connect Sqlserver 2005 using DAC if Sqlserver is experiencing login problem.

Posted: December 19, 2008 in Sqlserver

If SQL server is in trouble and you cannot connect to it then the next option is to connect using with the Dedicated Administrators Connection (DAC). Once you get connected, you can collect some information on what is happening. It would be nice to have a stored procedure to execute that displays information and can even generate a text file for future review.

Please use the below script and run on any DEV or Production server once. This script is a stored procedure you need to create under MASTER database. Reason behind this is when you connect you have access to MASTER database only when the server is extremely busy. Now open a CMD/DOS window and execute the following script

Link to enable DAC in sqlserver's http://www.mssqltips.com/tip.asp?tip=1005

USE master

GO

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

—– Once you are done with the execution of the above script. Please open command prompt use below query to test it.


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


Thanks & Regards
Md.S.Hassan

Advertisements

Comments are closed.