How to extract historical deadlock from extended events

DECLARE@ringAS[xml];

 

SET@ring= (SELECT     cast(xet.[target_data]AS[xml])

                        FROM       sys.dm_xe_session_targetsxet

                        INNERJOINsys.dm_xe_sessionsxe

                                        ONxe.[address]=xet.[event_session_address]

                        WHERE      xe.[name]=’system_health’);

 

SELECT                                                                                                                AS’DEADLOCKS’,

                        row_number()

                                OVER (

                                        ORDERBYsyshealth.xevent.value(‘(@timestamp)’,’DATETIME’))           AS’Sequence’,

                        syshealth.xevent.value(‘(@timestamp)’,

                                                                   ‘DATETIME’)                                    AS’Deadlock time’,

                        –SysHealth.XEvent.query(‘.’) AS [DeadlockEvent],

 

                        cast(syshealth.xevent.value(‘data[1]’,

                                                                                ‘NVARCHAR(MAX)’)ASxml)         AS’Deadlock graph’

–SysHealth.XEvent.value(‘data[1]’,’NVARCHAR(MAX)’) AS DeadlockGraph

FROM        (SELECT@ringASring)ASbuffer

CROSSapplyring.nodes (‘//RingBufferTarget/event’)ASsyshealth (xevent)

WHERE       syshealth.xevent.value(‘(@name)[1]’,

                                                                   ‘varchar (100)’)=’xml_deadlock_report’

ORDER       BY[deadlock time]DESC;

Advertisements

When was the last time DBCC CHECKDB executed on my databases

CREATE TABLE #Temp

(
Id INT IDENTITY(1,1),
ParentObject VARCHAR(255),
[Object] VARCHAR(255),
Field VARCHAR(255),
[Value] VARCHAR(255)
)
INSERT INTO #temp
EXECUTE SP_MSFOREACHDB’DBCC DBINFO ( ”?”) WITH TABLERESULTS’;
;WITH CHECKDB1 AS
(
SELECT [Value],ROW_NUMBER() OVER (ORDER BY ID) AS rn1 FROM #temp WHERE Field IN (‘dbi_dbname’))
,CHECKDB2 AS ( SELECT [Value], ROW_NUMBER() OVER (ORDER BY ID) AS rn2 FROM #temp WHERE Field IN (‘dbi_dbccLastKnownGood’)
)    
SELECT CHECKDB1.Value AS DatabaseName, replace(CHECKDB2.Value,’1900-01-01 00:00:00.000′,’Never Run’) AS LastRanDBCCCHECKDB
FROM CHECKDB1 JOIN CHECKDB2
ON rn1 =rn2

DROP TABLE #temp

Script to get detail all detail of SQL Instance Health in Last 7 Days

SET NOCOUNT ON

use master

go

 

print ‘***************************************************************’

print ‘  MANUAL ACTIVITIES  ‘

print ‘    ‘

print ‘ A. See database startup parameters  ‘

print ‘ B. See SQL Server Error Log and NT Event Viewer ‘

print ‘ C. See authentication mode ( NATIVE or MIXED ) ‘

print ‘ D. See SQL Server and SQL Agent services account startup ‘

print ‘ E. See SQL Mail configuration                  ‘

print ‘ F. See backup politic ( full and transaction ) ‘

print ‘***************************************************************’

 

print ”

print ‘1. General Info’

print ‘*********************’

print ”

 

print ‘Server Name……………: ‘ + convert(varchar(30),@@SERVERNAME)

print ‘Instance………………: ‘ + convert(varchar(30),@@SERVICENAME)

print ‘Current Date Time………: ‘ + convert(varchar(30),getdate(),113)

print ‘User………………….: ‘ + USER_NAME()

go

 

print ”

print ‘1.1 Database and Operational System versions.’

print ‘———————————————-‘

print ”

 

select @@version

go

 

exec master..xp_msver

go

 

print ”

print ‘1.2 Miscelaneous’

print ‘—————————‘

print ”

 

select convert(varchar(30),login_time,109) as ‘Servidor inicializado em ‘ from master..sysprocesses where spid = 1

 

print ‘Number of connections..: ‘ + convert(varchar(30),@@connections)

print ‘Language……………: ‘ + convert(varchar(30),@@language)

print ‘Language Id…………: ‘ + convert(varchar(30),@@langid)

print ‘Lock Timeout………..: ‘ + convert(varchar(30),@@LOCK_TIMEOUT)

print ‘Maximum of connections.: ‘ + convert(varchar(30),@@MAX_CONNECTIONS)

print ‘Server Name…………: ‘ + convert(varchar(30),@@SERVERNAME)

print ‘Instance……………: ‘ + convert(varchar(30),@@SERVICENAME)

print ”

print ‘CPU Busy………..: ‘ + convert(varchar(30),@@CPU_BUSY/1000)

print ‘CPU Idle………..: ‘ + convert(varchar(30),@@IDLE/1000)

print ‘IO Busy…………: ‘ + convert(varchar(30),@@IO_BUSY/1000)

print ‘Packets received…: ‘ + convert(varchar(30),@@PACK_RECEIVED)

print ‘Packets sent…….: ‘ + convert(varchar(30),@@PACK_SENT)

print ‘Packets w errors…: ‘ + convert(varchar(30),@@PACKET_ERRORS)

print ‘TimeTicks……….: ‘ + convert(varchar(30),@@TIMETICKS)

print ‘IO Errors……….: ‘ + convert(varchar(30),@@TOTAL_ERRORS)

print ‘Total Read………: ‘ + convert(varchar(30),@@TOTAL_READ)

print ‘Total Write………: ‘ + convert(varchar(30),@@TOTAL_WRITE)

go

 

———————————————————————————————————-

print ”

print ‘2. Server Parameters’

print ‘*************************’

print ”

 

–exec sp_configure ‘show advanced options’,1

exec sp_configure

go

———————————————————————————————————-

print ”

print ‘3. Databases parameters’

print ‘***************************’

print ”

 

exec sp_helpdb

go

 

SELECT LEFT(name,30) AS DB,

 SUBSTRING(CASE status & 1 WHEN 0 THEN ” ELSE ‘,autoclose’ END +

 CASE status & 4 WHEN 0 THEN ” ELSE ‘,select into/bulk copy’ END +

 CASE status & 8 WHEN 0 THEN ” ELSE ‘,trunc. log on chkpt’ END +

 CASE status & 16 WHEN 0 THEN ” ELSE ‘,torn page detection’ END +

 CASE status & 32 WHEN 0 THEN ” ELSE ‘,loading’ END +

 CASE status & 64 WHEN 0 THEN ” ELSE ‘,pre-recovery’ END +

 CASE status & 128 WHEN 0 THEN ” ELSE ‘,recovering’ END +

 CASE status & 256 WHEN 0 THEN ” ELSE ‘,not recovered’ END +

 CASE status & 512 WHEN 0 THEN ” ELSE ‘,offline’ END +

 CASE status & 1024 WHEN 0 THEN ” ELSE ‘,read only’ END +

 CASE status & 2048 WHEN 0 THEN ” ELSE ‘,dbo USE only’ END +

 CASE status & 4096 WHEN 0 THEN ” ELSE ‘,single user’ END +

 CASE status & 32768 WHEN 0 THEN ” ELSE ‘,emergency mode’ END +

 CASE status & 4194304 WHEN 0 THEN ” ELSE ‘,autoshrink’ END +

 CASE status & 1073741824 WHEN 0 THEN ” ELSE ‘,cleanly shutdown’ END +

 CASE status2 & 16384 WHEN 0 THEN ” ELSE ‘,ANSI NULL default’ END +

 CASE status2 & 65536 WHEN 0 THEN ” ELSE ‘,concat NULL yields NULL’ END +

 CASE status2 & 131072 WHEN 0 THEN ” ELSE ‘,recursive triggers’ END +

 CASE status2 & 1048576 WHEN 0 THEN ” ELSE ‘,default TO local cursor’ END +

 CASE status2 & 8388608 WHEN 0 THEN ” ELSE ‘,quoted identifier’ END +

 CASE status2 & 33554432 WHEN 0 THEN ” ELSE ‘,cursor CLOSE on commit’ END +

 CASE status2 & 67108864 WHEN 0 THEN ” ELSE ‘,ANSI NULLs’ END +

 CASE status2 & 268435456 WHEN 0 THEN ” ELSE ‘,ANSI warnings’ END +

 CASE status2 & 536870912 WHEN 0 THEN ” ELSE ‘,full text enabled’ END,

2,8000) AS Descr

FROM master..sysdatabases

go

———————————————————————————————————-

print ”

print ‘4. LOG utilization’

print ‘****************************’

print ”

 

dbcc sqlperf(logspace)

go

———————————————————————————————————-

print ”

print ‘5. Datafiles list’

print ‘***********************’

print ”

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#TempForFileStats ‘))

DROP TABLE #TempForFileStats

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#TempForDataFile’))

DROP TABLE #TempForDataFile

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#TempForLogFile’))

DROP TABLE #TempForLogFile

 

DECLARE @DBName nvarchar(100)

DECLARE @SQLString nvarchar (4000)

DECLARE c_db CURSOR FOR

 SELECT name

 FROM master.dbo.sysdatabases

 WHERE status&512 = 0

 

CREATE TABLE #TempForFileStats([Server Name] nvarchar(40),

  [Database Name] nvarchar(100),

  [File Name] nvarchar(128),

  [Usage Type] varchar (6),

  [Size (MB)] real,

  [Space Used (MB)] real,

  [MaxSize (MB)] real,

  [Next Allocation (MB)] real,

  [Growth Type] varchar (12),

  [File Id] smallint,

  [Group Id] smallint,

  [Physical File] nvarchar (260),

  [Date Checked] datetime)

 

CREATE TABLE #TempForDataFile ([File Id] smallint,

  [Group Id] smallint,

  [Total Extents] int,

  [Used Extents] int,

  [File Name] nvarchar(128),

  [Physical File] nvarchar(260))

 

CREATE TABLE #TempForLogFile ([File Id] int,

  [Size (Bytes)] real,

  [Start Offset] varchar(30),

  [FSeqNo] int,

  [Status] int,

  [Parity] smallint,

  [CreateTime] varchar(30))

 

OPEN c_db

FETCH NEXT FROM c_db INTO @DBName

WHILE @@FETCH_STATUS = 0

 BEGIN

 SET @SQLString = ‘SELECT @@SERVERNAME  as ”ServerName”, ‘ +  — changed by seraj : *8 to *8.000000000

  ”” + @DBName + ”” + ‘ as ”Database”, ‘ +

  ‘ f.name, ‘    +

  ‘ CASE ‘    +

  ‘ WHEN (64 & f.status) = 64 THEN ”Log” ‘  +

  ‘ ELSE ”Data” ‘   +

  ‘ END  as ”Usage Type”, ‘ +

  ‘ f.size*8.00000000/1024.00 as ”Size (MB)”, ‘ +

  ‘ NULL  as ”Space Used (MB)”, ‘ +

  ‘ CASE f.maxsize ‘   +

  ‘ WHEN -1 THEN -1 ‘   +

  ‘ WHEN 0 THEN f.size*8.00000000/1024.00 ‘  +

  ‘ ELSE f.maxsize*8.00000000/1024.00 ‘  +

  ‘ END  as ”Max Size (MB)”, ‘ +

  ‘ CASE ‘    +

  ‘ WHEN (1048576&f.status) = 1048576 THEN (growth/100.00)*(f.size*8.00000000/1024.00) ‘ +

  ‘ WHEN f.growth =0  THEN 0 ‘ +

  ‘ ELSE   f.growth*8.00000000/1024.00 ‘ +

  ‘ END  as ”Next Allocation (MB)”, ‘ +

  ‘ CASE ‘    +

  ‘ WHEN (1048576&f.status) = 1048576 THEN ”Percentage” ‘ +

  ‘ ELSE ”Pages” ‘   +

  ‘ END  as ”Usage Type”, ‘ +

  ‘ f.fileid, ‘    +

  ‘ f.groupid, ‘    +

  ‘ filename, ‘    +

  ‘ getdate() ‘    +

  ‘ FROM [‘ + @DBName + ‘].dbo.sysfiles f’ — Seraj Alam added []

 INSERT #TempForFileStats

 EXECUTE(@SQLString)

 

 ————————————————————————

 SET @SQLString = ‘USE [‘ + @DBName + ‘] DBCC SHOWFILESTATS’ — seraj alam added []

 INSERT #TempForDataFile

 EXECUTE(@SQLString)

 —

 UPDATE #TempForFileStats

 SET [Space Used (MB)] = s.[Used Extents]*64/1024.00

 FROM #TempForFileStats f,

 #TempForDataFile s

 WHERE f.[File Id] = s.[File Id]

 AND f.[Group Id] = s.[Group Id]

 AND f.[Database Name] = @DBName

 —

 TRUNCATE TABLE #TempForDataFile

 ————————————————————————-

 

 SET @SQLString = ‘USE [‘ + @DBName + ‘] DBCC LOGINFO’ — seraj alam added []

 INSERT #TempForLogFile

 EXECUTE(@SQLString)

 —

 UPDATE #TempForFileStats

 SET [Space Used (MB)] = (SELECT (MIN(l.[Start Offset]) +

   SUM(CASE

   WHEN l.Status <> 0 THEN l.[Size (Bytes)]

   ELSE 0

   END))/1048576.00

  FROM #TempForLogFile l

  WHERE l.[File Id] = f.[File Id])

 FROM #TempForFileStats f

 WHERE f.[Database Name] = @DBName

 AND f.[Usage Type] = ‘Log’

 —

 TRUNCATE TABLE #TempForLogFile

 ————————————————————————-

 FETCH NEXT FROM c_db INTO @DBName

 END

DEALLOCATE c_db

 

SELECT * FROM #TempForFileStats

————

DROP TABLE #TempForFileStats

DROP TABLE #TempForDataFile

DROP TABLE #TempForLogFile

go

———————————————————————————————————-

print ”

print ‘6. IO per datafile’

print ‘******************’

print ”

use tempdb

go

 

if exists (select [id] from sysobjects where [id] = OBJECT_ID (‘#TBL_DATABASEFILES’))

 DROP TABLE #TBL_DATABASEFILES

 

 

if exists (select [id] from sysobjects where [id] = OBJECT_ID (‘#TBL_FILESTATISTICS’))

 DROP TABLE #TBL_FILESTATISTICS

 

 

DECLARE @INT_LOOPCOUNTER INTEGER

DECLARE @INT_MAXCOUNTER INTEGER

DECLARE @INT_DBID INTEGER

DECLARE @INT_FILEID INTEGER

DECLARE @SNM_DATABASENAME SYSNAME

DECLARE @SNM_FILENAME SYSNAME

DECLARE @NVC_EXECUTESTRING NVARCHAR(4000)

 

DECLARE @MTB_DATABASES TABLE (

ID INT IDENTITY,

DBID INT,

DBNAME SYSNAME )

 

CREATE TABLE #TBL_DATABASEFILES (

ID INT IDENTITY,

DBID INT,

FILEID INT,

FILENAME SYSNAME,

FILENAME1 varchar(600),

DATABASENAME SYSNAME)

 

INSERT INTO @MTB_DATABASES (DBID,DBNAME) SELECT DBID,NAME FROM MASTER.DBO.SYSDATABASES ORDER BY DBID

SET @INT_LOOPCOUNTER = 1

SELECT @INT_MAXCOUNTER=MAX(ID) FROM @MTB_DATABASES

WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER

BEGIN

 SELECT @INT_DBID = DBID,@SNM_DATABASENAME=DBNAME FROM @MTB_DATABASES WHERE ID = @INT_LOOPCOUNTER

 SET @NVC_EXECUTESTRING = ‘INSERT INTO #TBL_DATABASEFILES(DBID,FILEID,FILENAME, FILENAME1,DATABASENAME) SELECT ‘+STR(@INT_DBID)+’ AS DBID,FILEID,NAME AS FILENAME, FILENAME AS FILENAME1,”’+@SNM_DATABASENAME+”’ AS DATABASENAME FROM [‘+@SNM_DATABASENAME+’].DBO.SYSFILES’

 EXEC SP_EXECUTESQL @NVC_EXECUTESTRING

 SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1

END

–‘OK WE NOW HAVE ALL THE DATABASES AND FILENAMES ETC….

 

CREATE TABLE #TBL_FILESTATISTICS (

ID INT IDENTITY,

DBID INT,

FILEID INT,

DATABASENAME SYSNAME,

FILENAME SYSNAME,

SAMPLETIME DATETIME,

NUMBERREADS BIGINT,

NUMBERWRITES BIGINT,

BYTESREAD BIGINT,

BYTESWRITTEN BIGINT,

IOSTALLMS BIGINT)

 

SELECT @INT_MAXCOUNTER=MAX(ID) FROM #TBL_DATABASEFILES

SET @INT_LOOPCOUNTER = 1

WHILE @INT_LOOPCOUNTER <= @INT_MAXCOUNTER

BEGIN

 SELECT @INT_DBID = DBID,@INT_FILEID=FILEID,@SNM_DATABASENAME=DATABASENAME,@SNM_FILENAME=FILENAME FROM #TBL_DATABASEFILES WHERE ID = @INT_LOOPCOUNTER

 INSERT INTO #TBL_FILESTATISTICS(DBID,FILEID,SAMPLETIME,NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,DATABASENAME,FILENAME)

 SELECT DBID,FILEID,GETDATE(),NUMBERREADS,NUMBERWRITES,BYTESREAD,BYTESWRITTEN,IOSTALLMS,@SNM_DATABASENAME AS DATABASENAME,@SNM_FILENAME AS FILENAME FROM :: FN_VIRTUALFILESTATS(@INT_DBID,@INT_FILEID)

 SET @INT_LOOPCOUNTER = @INT_LOOPCOUNTER + 1

END

select * from #TBL_FILESTATISTICS

 

drop table #TBL_DATABASEFILES

drop table #TBL_FILESTATISTICS

go

—————————————————————————————

print ”

print ‘7. List of last backup full”s’

print ‘*************************************’

print ”

 

select      SUBSTRING(s.name,1,40)              AS    ‘Database’,

      CAST(b.backup_start_date AS char(11))     AS    ‘Backup Date ‘,

      CASE WHEN b.backup_start_date > DATEADD(dd,-1,getdate())

            THEN ‘Backup is current within a day’

       WHEN b.backup_start_date > DATEADD(dd,-7,getdate())

            THEN ‘Backup is current within a week’

       ELSE ‘*****CHECK BACKUP!!!*****’

            END

                                    AS ‘Comment’

 

from master..sysdatabases    s

LEFT OUTER JOIN   msdb..backupset b

      ON s.name = b.database_name

      AND b.backup_start_date = (SELECT MAX(backup_start_date)

                              FROM msdb..backupset

                              WHERE database_name = b.database_name

                                    AND type = ‘D’)         — full database backups only, not log backups

WHERE s.name <> ‘tempdb’

 

ORDER BY    s.name

go

———————————————————————————————————-

print ”

print ‘8. List of logins’

print ‘********************’

print ”

 

exec sp_helplogins

go

———————————————————————————————————-

print ”

print ‘9. List of users per role’

print ‘*******************************’

print ”

 

exec sp_helpsrvrolemember

go

———————————————————————————————————-

print ”

print ’10.List of special users per database’

print ‘*************************************’

print ”

 

 

declare @name sysname,

      @SQL nvarchar(600)

 

if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID (‘tempdb..#tmpTable’))

      drop table #tmpTable

CREATE TABLE #tmpTable (

      [DATABASE_NAME] sysname NOT NULL ,

      [USER_NAME] sysname NOT NULL,

      [ROLE_NAME] sysname NOT NULL)

 

declare c1 cursor for

      select name from master.dbo.sysdatabases

open c1

fetch c1 into @name

while @@fetch_status >= 0

begin

      select @SQL =

            ‘insert into #tmpTable

             select N”’+ @name + ”’, a.name, c.name

            from ‘ + QuoteName(@name) + ‘.dbo.sysusers a

            join ‘ + QuoteName(@name) + ‘.dbo.sysmembers b on b.memberuid = a.uid

            join ‘ + QuoteName(@name) + ‘.dbo.sysusers c on c.uid = b.groupuid

            where a.name != ”dbo”’

 

            /*    Insert row for each database */

            execute (@SQL)

      fetch c1 into @name

end

close c1

deallocate c1

select * from #tmpTable

 

drop table #tmpTable

go

———————————————————————————————————-

print ”

print ’11. Information about remote servers ‘

print ‘*****************************************’

print ”

 

Print ‘Linked Servers’

print ”

 

exec sp_linkedserver

 

print ‘linked Server login mappings’

print ”

 

exec sp_helplinkedsrvlogin

 

print ‘Remote Logins’

print ”

 

exec sp_helpremotelogin

 

go

———————————————————————————————————-

print ”

print ’12. List of jobs ‘

print ‘*******************’

print ”

 

exec msdb..sp_help_job

go

———————————————————————————————————-

 

print ”

print ’13. Cache Hit Ratio ‘

print ‘*******************’

print ”

 

select      distinct counter_name,

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio%’

      and   A.counter_name = B.counter_name) as CurrHit,

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio base%’

      and   lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ‘ base’)) as CurrBase,

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio%’

      and   A.counter_name = B.counter_name) /

      (select isnull(sum(convert(dec(15,0),B.cntr_value)),0)

      from master..sysperfinfo as B (nolock)

      where       Lower(B.counter_name) like ‘%hit ratio base%’

      and   lower(B.counter_name) = (lower(ltrim(rtrim(A.counter_name))) + ‘ base’)) as HitRatio

from master..sysperfinfo as A (nolock)

where       Lower(A.counter_name) like ‘%hit ratio%’

and   Lower(A.counter_name) not like ‘%hit ratio base%’

 

— Audit list as a double verification

 

select counter_name,isnull(sum(convert(dec(15,0),cntr_value)),0) as Value

from master..sysperfinfo (nolock)

where       Lower(counter_name) like ‘%hit ratio%’

or    Lower(counter_name) like ‘%hit ratio base%’

group by counter_name

 

go

———————————————————————————————————-

 

print ”

print ’14. SP_WHO ‘

print ‘***********’

print ”

exec sp_who

exec sp_who2

go

 

———————————————————————————————————-

 

print ”

print ’14. SP_LOCKS ‘

print ‘***********’

print ”

exec sp_lock

 

go

 

 

set nocount on

use msdb

go

Print ”

Print ‘Linked Servers’

print ‘***********’

print ”

 

exec sp_linkedservers

 

Print ”

print ’15. Maintenance Plans’

print ‘***********’

print ”

go

 

select @@servername “ServerName”, smp.plan_id, plan_name, owner, smpd.database_name, smpj.job_id, sj.name, sjs.name “Job Part Name”, sjs.enabled “Job Enabled”, sjs.freq_type “Frequency”, sjs.active_start_time “Job Start Time”

from sysdbmaintplans smp, sysdbmaintplan_databases smpd, sysdbmaintplan_jobs smpj, sysjobs sj, sysjobschedules sjs

where smp.plan_id = smpd.plan_id and smp.plan_id = smpj.plan_id and sj.job_id = smpj.job_id and sj.job_id = sjs.job_id

go

Print ”

print ‘Maintenance Plan 2005′

SELECT sv.name AS [Name], sv.job_id AS [JobID],sv.originating_server AS [OriginatingServer],

CAST(sv.enabled AS bit) AS [IsEnabled],

ISNULL(sv.description,N”) AS [Description],sv.start_step_id AS [StartStepID],ISNULL(suser_sname(sv.owner_sid), N”) AS [OwnerLoginName],

sv.notify_level_eventlog AS [EventLogLevel],sv.notify_level_email AS [EmailLevel],sv.notify_level_netsend AS [NetSendLevel],

sv.notify_level_page AS [PageLevel],sv.delete_level AS [DeleteLevel],sv.date_created AS [DateCreated],sv.date_modified AS [DateLastModified],

sv.version_number AS [VersionNumber]FROM msdb.dbo.sysjobs_view AS sv

go

 

 

set dateformat dmy

 

use msdb

go

 

select y.Server_name, y.database_name, y.database_creation_date, y.backup_start_Date, y.backup_finish_Date , y.backup_file, y.backup_size

from (

select c.Server_name, c.database_name, c.database_creation_date, c.backup_start_Date, c.backup_finish_Date , a.physical_device_name as backup_file, c.backup_size

from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id

inner join backupfile b on

c.backup_set_id=b.backup_set_id and b.file_type=’D’

) y

inner join

(select distinct c.database_name, c.backup_finish_Date as backup_finish_date from Backupmediafamily a inner join backupset c on a.media_set_id=c.media_set_id

inner join backupfile b on

c.backup_set_id=b.backup_set_id and b.file_type=’D’ where c.backup_finish_date > getdate()-10

) z

on y.database_name=z.database_name and y.backup_finish_Date=z.backup_finish_date

 

 

 

 

 

Print ”

Print ’16. OTHER’

PRINT ‘DTS Packages’

print ‘***********’

print ”

 

go

select @@servername “ServerName”, sd.name, sd.id, sd.categoryid, sdc.name “Category”, sd.description, owner, createdate

from sysdtspackages sd, sysdtscategories sdc

where sd.categoryid = sdc.id

go

exec sp_MSgetalertinfo

 

print ”

print ‘Alerts settings’

print ‘***********’

print ”

 

exec msdb..sp_help_alert

 

Print ”

Print ‘Operators’

print ‘***********’

print ”

 

EXECUTE sp_help_operator

 

print ”

print ‘SQL Mails’

EXECUTE master.dbo.xp_sqlagent_notify N’M’,null,null,null,N’E’

 

print ”

Print ‘SQL Agent Propertes’

EXECUTE msdb.dbo.sp_get_sqlagent_properties

 

print ”

print ‘Startup Parameters 01′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg0′

 

print ”

print ‘Startup Parameters 02′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg1′

 

print ”

print ‘Startup Parameters 03′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg2′

 

print ”

print ‘Startup Parameters 04′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg3′

 

— Though there is very little chance of more than 4 parameters, still it is good to check.

 

print ”

print ‘Startup Parameters 05′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg4′

 

print ”

print ‘Startup Parameters 06′

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’,

N’SOFTWARE\Microsoft\Microsoft SQL Server\MSSQLServer\Parameters’, N’SQLarg5′

 

print ”

 

–exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\Setup’, N’SQLPath’

 

 

print ”

print ‘Login Mode’

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, ‘LoginMode’

go

 

print ”

Print ‘Audit Level’

 

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, N’AuditLevel’

 

Print ”

Print ‘Clustered?’

select convert(int, serverproperty(N’isclustered’))

go

Print ”

Print ‘Is Mapi set?’

DECLARE @retval varchar(255) EXECUTE master.dbo.xp_instance_regread ‘HKEY_LOCAL_MACHINE’,’SOFTWARE\Microsoft\Windows Messaging Subsystem’,’MAPIX’,@retval OUTPUT SELECT @retval

go

 

Print ”

Print ‘Mail Account’

exec master..xp_instance_regread N’HKEY_LOCAL_MACHINE’, N’SOFTWARE\Microsoft\MSSQLServer\MSSQLServer’, N’MailAccountName’

Print ”

 

set nocount off

 

 

print ‘******************************************************************’

print ‘  FIM   ‘

print ‘******************************************************************’

———————————————————————————————————-

set nocount off

 

How to capture DBCC out put in a table

 

DROP TABLE #TempForLogSpace

go

CREATE TABLE #TempForLogSpace

(

 DBName varchar(32),

 LogSize real,

 LogSpaceUsed real,

 Status int

)

Declare @sql_command varchar(1000)

 

SELECT @sql_command = ‘dbcc sqlperf (logspace)’

 

INSERT INTO #TempForLogSpace EXEC (@sql_command)

 

select * from #TempForLogSpace

 

GO

______________________________________________________________________________________________

How to capture DBCC out put in a table

 

DROP TABLE #TempForLogSpace

go

CREATE TABLE #TempForLogSpace

(

 DBName varchar(32),

 LogSize real,

 LogSpaceUsed real,

 Status int

)

Declare @sql_command varchar(1000)

 

SELECT @sql_command = ‘dbcc sqlperf (logspace)’

 

INSERT INTO #TempForLogSpace EXEC (@sql_command)

 

select * from #TempForLogSpace

 

GO

 ___________________________________________________________________________________________________

DROP TABLE #inputbuffer

go

create table #inputbuffer(c1 varchar(15), c2 int, c3 varchar(255))

insert into #inputbufferEXEC(‘dbcc inputbuffer(7)’)

select * from #inputbuffer 

 

 

Capture lead blocker\blocking details in SQL 2008 along with SQL Commands exceeding 60 seconds

It is very hard to find good and bad blocking, in case you need to capture blocking in your database which is exceeding a specific timeframe than below mentioned script can be used.

Step 1 Create Table to store blocking information

SET ANSI_NULLS ON

GO

 

SET QUOTED_IDENTIFIER ON

GO

 

SET ANSI_PADDING ON

GO

 

CREATE TABLE [dbo].[CatchBlocking](

      [CaptureDate] [datetime] NULL,

      [session id] [smallint] NULL,

      [login] [nvarchar](256) NULL,

      [database] [nvarchar](256) NULL,

      [task state] [nvarchar](120) NULL,

      [command] [nvarchar](32) NULL,

      [application] [nvarchar](256) NULL,

      [head blocker] [varchar](1) NULL,

      [login time] [datetime] NULL,

      [last request start time] [datetime] NULL,

      [monitoring time] [datetime] NULL,

      [blocked minutes] [nvarchar](256) NULL,

      [host name] [nvarchar](256) NULL,

      [text] [nvarchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

GO

 

SET ANSI_PADDING OFF

GO

 

 

 

Step 2 Insert the below code in a job step which will execute after every minute

 

 

DECLARE @loginname         sysname,

            @databasename      sysname,

            @hostname          sysname,

            @userprocessesonly char(1),

            @headblocker       nvarchar(1),

            @minutesofblocking int,

            @monitortime       datetime

 

— Filter your monitoring here!

—————————————–

SET @loginname = ”

SET @databasename = ”

SET @hostname = ”

SET @userprocessesonly = ‘Y’ –Y

SET @headblocker = ‘1’ — 1

SET @minutesofblocking = ’60’ — 1

–Blocking can be captured in terms of minutes and seconds, below in the script use ss\minutes accordingly to

–capture blocking in minutes\seconds

—————————————–

 

SET @monitortime = getdate()

INSERT INTO dbo.CatchBlocking

SELECT         GETDATE(),

                         [session id] = s.session_id,

                        —  [User Process]  = CONVERT(CHAR(1), s.is_user_process),

                        [login] = s.login_name,

                        [database] = isnull(db_name(p.dbid),

                                                      N”),

                        [task state] = isnull(t.task_state,

                                                        N”),

                        [command] = isnull(r.command,

                                                   N”),

                        [application] = isnull(s.program_name,

                                                         N”),

                        [head blocker] = CASE

                                                       — session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

                                                       WHEN r2.session_id IS NOT NULL AND

                                                              (r.blocking_session_id = 0  OR

                                                               r.session_id IS NULL) THEN ‘1’

                                                       — session is either not blocking someone, or is blocking someone but is blocked by another party

                                                       ELSE ”

                                                 END,

                        [login time] = s.login_time,

                        [last request start time] = s.last_request_start_time,

                        [monitoring time] = @monitortime,

                        [blocked minutes] = cast(datediff(ss,s.last_request_start_time, @monitortime) AS int),

                        [host name] = isnull(s.host_name,

                                                       N”),

                        st.text AS ‘Text’

FROM            sys.dm_exec_sessions s

LEFT OUTER JOIN sys.dm_exec_connections c

                   ON (s.session_id = c.session_id)

LEFT OUTER JOIN sys.dm_exec_requests r

                   ON (s.session_id = r.session_id)

LEFT OUTER JOIN sys.dm_os_tasks t

                   ON (r.session_id = t.session_id AND

                         r.request_id = t.request_id)

LEFT OUTER JOIN (

                        — In some cases (e.g. parallel queries, also waiting for a worker), one thread can be flagged as

                        — waiting for several different threads.  This will cause that thread to show up in multiple rows

                        — in our grid, which we don’t want.  Use ROW_NUMBER to select the longest wait for each thread,

                        — and use it as representative of the other wait relationships this thread is involved in.

                        SELECT *,

                                 row_number()

                                       OVER (

                                             partition BY waiting_task_address

                                             ORDER BY wait_duration_ms DESC) AS row_num

                         FROM   sys.dm_os_waiting_tasks) w

                   ON (t.task_address = w.waiting_task_address) AND

                        w.row_num = 1

LEFT OUTER JOIN sys.dm_exec_requests r2

                   ON (s.session_id = r2.blocking_session_id)

LEFT OUTER JOIN sys.dm_resource_governor_workload_groups g

                   ON (g.group_id = s.group_id)

LEFT OUTER JOIN sys.sysprocesses p

                   ON (s.session_id = p.spid)

CROSS apply     sys.dm_exec_sql_text(r.sql_handle) AS st

WHERE          

 

P.dbid in (select dbid from  sys.sysdatabases where name like ‘%IDEA%’)    

AND

 

                              (@loginname = ”  OR

                         s.login_name = @loginname) AND

                        (@databasename = ”  OR

                         isnull(db_name(p.dbid),

                                    N”) = @databasename) AND

                        (@hostname = ”  OR

                         @hostname = isnull(s.host_name,

                                                      N”)) AND

                        (s.is_user_process = CASE

                                                             WHEN upper(@userprocessesonly) = ‘Y’ THEN 1

                                                             ELSE s.is_user_process

                                                       END) AND

                        (@headblocker = ”  OR

                         @headblocker = CASE

                                                      — session has an active request, is blocked, but is blocking others or session is idle but has an open tran and is blocking others

                                                      WHEN r2.session_id IS NOT NULL AND

                                                             (r.blocking_session_id = 0  OR

                                                              r.session_id IS NULL) THEN ‘1’

                                                      — session is either not blocking someone, or is blocking someone but is blocked by another party

                                                      ELSE ”

                                                END) AND

                        datediff(ss,s.last_request_start_time,@monitortime) >= @minutesofblocking AND

                        (st.text NOT LIKE ‘%BACKUP%’ AND st.text NOT LIKE ‘%RESTORE%’)

ORDER           BY s.session_id

 

— If there is an unreported head blocker session let’s send a new mail.

IF EXISTS (SELECT hbs1.[session id]

               FROM   dbo.CatchBlocking hbs1

               WHERE  cast(hbs1.[monitoring time] AS nvarchar(256)) = cast(@monitortime AS nvarchar(256)) AND

                          hbs1.[session id] NOT IN (SELECT hbs2.[session id]

                                                                  FROM   dbo.CatchBlocking hbs2

                                                                  WHERE  cast(hbs2.[monitoring time] AS nvarchar(256)) < cast(@monitortime AS nvarchar(256)) AND

                                                                           hbs2.[session id] = hbs1.[session id] AND

                                                                           hbs2.[last request start time] = hbs1.[last request start time] AND

                                                                           hbs2.text = hbs1.text))

 

 

select * from dbo.CatchBlocking

GO

Capture blocking details in SQL 2005 along with SQL Commands for both SPID and Blocked

If you want to store all blocking details in your databases in SQL Server 2005 then you can use the below method.

Step 1 Run the below script to create a table

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGOFF

GO

CREATETABLE [dbo].[MyBlockingInfo](

[CaptureDateTime] [datetime] NOTNULL,

[spid] [smallint] NOTNULL,

[kpid] [smallint] NOTNULL,

[blocked] [smallint] NOTNULL,

[waittype] [binary](2)NOTNULL,

[waittime] [bigint] NOTNULL,

[lastwaittype] [nchar](32)NOTNULL,

[waitresource] [nchar](256)NOTNULL,

[dbid] [smallint] NOTNULL,

[uid] [smallint] NULL,

[cpu] [int] NOTNULL,

[physical_io] [bigint] NOTNULL,

[memusage] [int] NOTNULL,

[login_time] [datetime] NOTNULL,

[last_batch] [datetime] NOTNULL,

[ecid] [smallint] NOTNULL,

[open_tran] [smallint] NOTNULL,

[status] [nchar](30)NOTNULL,

[sid] [binary](86)NOTNULL,

[hostname] [nchar](128)NOTNULL,

[program_name] [nchar](128)NOTNULL,

[hostprocess] [nchar](10)NOTNULL,

[cmd] [nchar](16)NOTNULL,

[nt_domain] [nchar](128)NOTNULL,

[nt_username] [nchar](128)NOTNULL,

[net_address] [nchar](12)NOTNULL,

[net_library] [nchar](12)NOTNULL,

[loginame] [nchar](128)NOTNULL,

[context_info] [binary](128)NOTNULL,

[sql_handle] [binary](20)NOTNULL,

[stmt_start] [int] NOTNULL,

[stmt_end] [int] NOTNULL,

[request_id] [int] NOTNULL

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

Step 2 Run the below script to create a table to capture SQL Command

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETABLE [dbo].[CAPTUREINPUTBUFFERVALUE](

[CPDATETIME] [datetime] NULL,

[SPIDTXT] [nvarchar](4000)NULL,

[BKSPIDTXT] [nvarchar](4000)NULL

)ON [PRIMARY]

Step 3 Run the below script to create trigger on table MyBlocking

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATETRIGGER [dbo].[CAPTUREINPUTBUFFER]

ON [dbo].[MyBlocking]

FORINSERT

AS

begin

DECLARE @SQLCOMMAND VARCHAR(100)

DECLARE @SQLCOMMANDbk VARCHAR(100)

DECLARE @CPDATE DATETIME

DECLARE @SPID INT

DECLARE @BKSPID INT

DECLARE @SPIDTXT  NVARCHAR(4000)

DECLARE @BKSPIDTXT NVARCHAR(4000)

DECLARE @SQLHandle BINARY(20)

DECLARE @SQLHandleBK BINARY(20)

set @CPDATE=(select CAPTUREDATETIME from INSERTED wheredbid=13)

set @SPID=(select SPID from INSERTED wheredbid=13)

set @BKSPID=(select BLOCKED from INSERTED wheredbid=13)

— this will give you the SQLHandle for the culprit SPID

SELECT @SQLHandle =SQL_HANDLEFROM MASTER..SYSPROCESSES WHERE SPID = @SPID

SELECT @SQLHandleBK =SQL_HANDLEFROM MASTER..SYSPROCESSES WHERE SPID = @BKSPID

— this statement will give you the SQL Statement for culprit SPID

SET @SPIDTXT=(SELECT [TEXT] FROM::FN_GET_SQL(@SQLHandle))

SET @BKSPIDTXT=(SELECT [TEXT] FROM::FN_GET_SQL(@SQLHandleBK))

IF @SPID ISNOTNULL

INSERTINTO CAPTUREINPUTBUFFERVALUE VALUES(@CPDATE,@SPIDTXT,@BKSPIDTXT)

END

Step 4 Run the below script to create a table

Create 2 jobs with an interval of 30 seconds inserting sysprocesses information in MyBlocking table.

@command=N’Insert into dbo.MyBlocking

select getdate(),* from sys.sysprocesses where blocked <>0′,

When the above jobs execute they will insert the blocking information in your blocking table MyBlocking and then the trigger will capture the details of running process and the blocking process in another table for future references and output will be shown as below

deadlock