Redgate DMV Starter pack

If you haven’t already done so then please read the following http://tenbulls.co.uk/enlightenment/scripts/

Redgate is a fantastic little software company that is based in Cambridge in the United Kingdom. They are the home of many fantastic Tools and Solutions such as the famous SQL Compare and frequently win awards for being one of the best Companies to work for in the UK.

The DMV Starter pack was written by Glenn Berry (blog|twitter), Louis Davidson (blog|twitter) and Tim Ford (blog|twitter), comes with an excellent EBook of the same title and both can be downloaded from here.

-- Script 1
-- Get a count of SQL connections by IP address
SELECT  ec.client_net_address ,
        es.[program_name] ,
        es.[host_name] ,
        es.login_name ,
        COUNT(ec.session_id) AS [connection count]
FROM    sys.dm_exec_sessions AS es
        INNER JOIN sys.dm_exec_connections AS ec
                                   ON es.session_id = ec.session_id
GROUP BY ec.client_net_address ,
        es.[program_name] ,
        es.[host_name] ,
        es.login_name
ORDER BY ec.client_net_address ,
        es.[program_name] ;

-- Script 2
--  Get SQL users that are connected and how many sessions they have
SELECT  login_name ,
        COUNT(session_id) AS [session_count]
FROM    sys.dm_exec_sessions
GROUP BY login_name
ORDER BY COUNT(session_id) DESC ;

-- Script 3
-- Look at current expensive or blocked requests
SELECT  r.session_id ,
        r.[status] ,
        r.wait_type ,
        r.scheduler_id ,
        SUBSTRING(qt.[text], r.statement_start_offset / 2,
            ( CASE WHEN r.statement_end_offset = -1
                   THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
                   ELSE r.statement_end_offset
              END - r.statement_start_offset ) / 2) AS [statement_executing] ,
        DB_NAME(qt.[dbid]) AS [DatabaseName] ,
        OBJECT_NAME(qt.objectid) AS [ObjectName] ,
        r.cpu_time ,
        r.total_elapsed_time ,
        r.reads ,
        r.writes ,
        r.logical_reads ,
        r.plan_handle
FROM    sys.dm_exec_requests AS r
        CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS qt
WHERE   r.session_id > 50
ORDER BY r.scheduler_id ,
        r.[status] ,
        r.session_id ;

-- Script 4
-- Top 3 CPU-sapping queries for which plans exist in the cache
SELECT TOP 3
        total_worker_time ,
        execution_count ,
        total_worker_time / execution_count AS [Avg CPU Time] ,
        CASE WHEN deqs.statement_start_offset = 0
                  AND deqs.statement_end_offset = -1
             THEN '-- see objectText column--'
             ELSE '-- query --' + CHAR(13) + CHAR(10)
                  + SUBSTRING(execText.text, deqs.statement_start_offset / 2,
                              ( ( CASE WHEN deqs.statement_end_offset = -1
                                       THEN DATALENGTH(execText.text)
                                       ELSE deqs.statement_end_offset
                                  END ) - deqs.statement_start_offset ) / 2)
        END AS queryText
FROM    sys.dm_exec_query_stats deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) AS execText
ORDER BY deqs.total_worker_time DESC ;

-- Script 5
-- Use Counts and # of plans for compiled plans
SELECT  objtype ,
        usecounts ,
        COUNT(*) AS [no_of_plans]
FROM    sys.dm_exec_cached_plans
WHERE   cacheobjtype = 'Compiled Plan'
GROUP BY objtype ,
        usecounts
ORDER BY objtype ,
        usecounts ;

-- Script 6

-- Script 7
-- Top Cached SPs By Total Logical Reads (SQL 2008 only).
-- Logical reads relate to memory pressure
SELECT TOP ( 25 )
        p.name AS [SP Name] ,
        qs.total_logical_reads AS [TotalLogicalReads] ,
        qs.total_logical_reads / qs.execution_count AS [AvgLogicalReads] ,
        qs.execution_count ,
        ISNULL(qs.execution_count /
                 DATEDIFF(Second, qs.cached_time, GETDATE()),
               0) AS [Calls/Second] ,
        qs.total_elapsed_time ,
        qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,
        qs.cached_time
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS qs
                              ON p.[object_id] = qs.[object_id]
WHERE   qs.database_id = DB_ID()
ORDER BY qs.total_logical_reads DESC ;

-- Script 8
-- Top Cached SPs By Total Physical Reads (SQL 2008 only)
-- Physical reads relate to disk I/O pressure
SELECT TOP ( 25 )
        p.name AS [SP Name] ,
        qs.total_physical_reads AS [TotalPhysicalReads] ,
        qs.total_physical_reads / qs.execution_count AS [AvgPhysicalReads] ,
        qs.execution_count ,
        ISNULL(qs.execution_count /
                 DATEDIFF(Second, qs.cached_time, GETDATE()),
               0) AS [Calls/Second] ,
        qs.total_elapsed_time ,
        qs.total_elapsed_time / qs.execution_count AS [avg_elapsed_time] ,
        qs.cached_time
FROM    sys.procedures AS p
        INNER JOIN sys.dm_exec_procedure_stats AS qs
                              ON p.[object_id] = qs.[object_id]
WHERE   qs.database_id = DB_ID()
ORDER BY qs.total_physical_reads DESC ;

-- Script 9
-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2008 version
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.ideal_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231')
ORDER BY mg.requested_memory_kb DESC ;

-- Shows the memory required by both running (non-null grant_time)
-- and waiting queries (null grant_time)
-- SQL Server 2005 version
SELECT  DB_NAME(st.dbid) AS [DatabaseName] ,
        mg.requested_memory_kb ,
        mg.request_time ,
        mg.grant_time ,
        mg.query_cost ,
        mg.dop ,
        st.[text]
FROM    sys.dm_exec_query_memory_grants AS mg
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE   mg.request_time < COALESCE(grant_time, '99991231') ORDER BY mg.requested_memory_kb DESC ; -- Script 10 -- Monitoring transaction activity SELECT  st.session_id ,         DB_NAME(dt.database_id) AS database_name ,         CASE WHEN dt.database_transaction_begin_time IS NULL THEN 'read-only'              ELSE 'read-write'         END AS transaction_state ,         dt.database_transaction_begin_time AS read_write_start_time ,         dt.database_transaction_log_record_count ,         dt.database_transaction_log_bytes_used FROM    sys.dm_tran_session_transactions AS st         INNER JOIN sys.dm_tran_database_transactions AS dt             ON st.transaction_id = dt.transaction_id ORDER BY st.session_id ,         database_name -- Script 11 -- Look at active Lock Manager resources for current database SELECT  request_session_id ,         DB_NAME(resource_database_id) AS [Database] ,         resource_type ,         resource_subtype ,         request_type ,         request_mode ,         resource_description ,         request_mode ,         request_owner_type FROM    sys.dm_tran_locks WHERE   request_session_id > 50
        AND resource_database_id = DB_ID()
        AND request_session_id <> @@SPID
ORDER BY request_session_id ;

-- Look for blocking
SELECT  tl.resource_type ,
        tl.resource_database_id ,
        tl.resource_associated_entity_id ,
        tl.request_mode ,
        tl.request_session_id ,
        wt.blocking_session_id ,
        wt.wait_type ,
        wt.wait_duration_ms
FROM    sys.dm_tran_locks AS tl
        INNER JOIN sys.dm_os_waiting_tasks AS wt
           ON tl.lock_owner_address = wt.resource_address
ORDER BY wait_duration_ms DESC ;

-- Script 12
-- Missing Indexes in current database by Index Advantage
SELECT  user_seeks * avg_total_user_cost * ( avg_user_impact * 0.01 )
                                                       AS [index_advantage] ,
        migs.last_user_seek ,
        mid.[statement] AS [Database.Schema.Table] ,
        mid.equality_columns ,
        mid.inequality_columns ,
        mid.included_columns ,
        migs.unique_compiles ,
        migs.user_seeks ,
        migs.avg_total_user_cost ,
        migs.avg_user_impact
FROM    sys.dm_db_missing_index_group_stats AS migs WITH ( NOLOCK )
        INNER JOIN sys.dm_db_missing_index_groups AS mig WITH ( NOLOCK )
           ON migs.group_handle = mig.index_group_handle
        INNER JOIN sys.dm_db_missing_index_details AS mid WITH ( NOLOCK )
           ON mig.index_handle = mid.index_handle
WHERE   mid.database_id = DB_ID()
ORDER BY index_advantage DESC ;

-- Script 13
--- Index Read/Write stats (all tables in current DB)
SELECT  OBJECT_NAME(s.[object_id]) AS [ObjectName] ,
        i.name AS [IndexName] ,
        i.index_id ,
        user_seeks + user_scans + user_lookups AS [Reads] ,
        user_updates AS [Writes] ,
        i.type_desc AS [IndexType] ,
        i.fill_factor AS [FillFactor]
FROM    sys.dm_db_index_usage_stats AS s
        INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
        AND i.index_id = s.index_id
        AND s.database_id = DB_ID()
ORDER BY OBJECT_NAME(s.[object_id]) ,
        writes DESC ,
        reads DESC ;

-- Script 14
-- List unused indexes
SELECT  OBJECT_NAME(i.[object_id]) AS [Table Name] ,
        i.name
FROM    sys.indexes AS i
        INNER JOIN sys.objects AS o ON i.[object_id] = o.[object_id]
WHERE   i.index_id NOT IN ( SELECT  s.index_id
                            FROM    sys.dm_db_index_usage_stats AS s
                            WHERE   s.[object_id] = i.[object_id]
                                    AND i.index_id = s.index_id
                                    AND database_id = DB_ID() )
        AND o.[type] = 'U'
ORDER BY OBJECT_NAME(i.[object_id]) ASC ;

-- Script 15
-- Possible Bad NC Indexes (writes > reads)
SELECT  OBJECT_NAME(s.[object_id]) AS [Table Name] ,
        i.name AS [Index Name] ,
        i.index_id ,
        user_updates AS [Total Writes] ,
        user_seeks + user_scans + user_lookups AS [Total Reads] ,
        user_updates - ( user_seeks + user_scans + user_lookups )
            AS [Difference]
FROM    sys.dm_db_index_usage_stats AS s WITH ( NOLOCK )
        INNER JOIN sys.indexes AS i WITH ( NOLOCK )
            ON s.[object_id] = i.[object_id]
            AND i.index_id = s.index_id
WHERE   OBJECTPROPERTY(s.[object_id], 'IsUserTable') = 1
        AND s.database_id = DB_ID()
        AND user_updates > ( user_seeks + user_scans + user_lookups )
        AND i.index_id > 1
ORDER BY [Difference] DESC ,
        [Total Writes] DESC ,
        [Total Reads] ASC ;

-- Script 16
-- Table and row count information
SELECT  OBJECT_NAME(ps.[object_id]) AS [TableName] ,
        i.name AS [IndexName] ,
        SUM(ps.row_count) AS [RowCount]
FROM    sys.dm_db_partition_stats AS ps
        INNER JOIN sys.indexes AS i ON i.[object_id] = ps.[object_id]
                                       AND i.index_id = ps.index_id
WHERE   i.type_desc IN ( 'CLUSTERED', 'HEAP' )
        AND i.[object_id] > 100
        AND OBJECT_SCHEMA_NAME(ps.[object_id]) <> 'sys'
GROUP BY ps.[object_id] ,
        i.name
ORDER BY SUM(ps.row_count) DESC ;

-- Script 17
-- Get Free Space in TempDB
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 ;

-- Quick TempDB Summary
SELECT SUM(user_object_reserved_page_count) * 8.192 AS [UserObjectsKB] ,
      SUM(internal_object_reserved_page_count) * 8.192 AS [InternalObjectsKB] ,
      SUM(version_store_reserved_page_count) * 8.192 AS [VersonStoreKB] ,
      SUM(unallocated_extent_page_count) * 8.192 AS [FreeSpaceKB]
FROM    sys.dm_db_file_space_usage ;

-- Script 18
-- Calculates average stalls per read, per write, and per total input/output
-- for each database file.
SELECT  DB_NAME(database_id) AS [Database Name] ,
        file_id ,
        io_stall_read_ms ,
        num_of_reads ,
        CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1))
            AS [avg_read_stall_ms] ,
        io_stall_write_ms ,
        num_of_writes ,
        CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1))
            AS [avg_write_stall_ms] ,
        io_stall_read_ms + io_stall_write_ms AS [io_stalls] ,
        num_of_reads + num_of_writes AS [total_io] ,
        CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads
                                                          + num_of_writes)
           AS NUMERIC(10,1)) AS [avg_io_stall_ms]
FROM    sys.dm_io_virtual_file_stats(NULL, NULL)
ORDER BY avg_io_stall_ms DESC ;

-- Script 19
-- Look at pending I/O requests by file
SELECT  DB_NAME(mf.database_id) AS [Database] ,
        mf.physical_name ,
        r.io_pending ,
        r.io_pending_ms_ticks ,
        r.io_type ,
        fs.num_of_reads ,
        fs.num_of_writes
FROM    sys.dm_io_pending_io_requests AS r
        INNER JOIN sys.dm_io_virtual_file_stats(NULL, NULL) AS fs
                                          ON r.io_handle = fs.file_handle
        INNER JOIN sys.master_files AS mf ON fs.database_id = mf.database_id
                                             AND fs.file_id = mf.file_id
ORDER BY r.io_pending ,
        r.io_pending_ms_ticks DESC ;

-- Script 20
-- Total waits are wait_time_ms (high signal waits indicates CPU pressure)
SELECT  CAST(100.0 * SUM(signal_wait_time_ms) / SUM(wait_time_ms)
                              AS NUMERIC(20,2)) AS [%signal (cpu) waits] ,
        CAST(100.0 * SUM(wait_time_ms - signal_wait_time_ms)
        / SUM(wait_time_ms) AS NUMERIC(20, 2)) AS [%resource waits]
FROM    sys.dm_os_wait_stats ;

-- Script 21
-- Isolate top waits for server instance since last restart
-- or statistics clear
WITH    Waits
      AS ( SELECT   wait_type ,
                    wait_time_ms / 1000. AS wait_time_s ,
                    100. * wait_time_ms / SUM(wait_time_ms) OVER ( ) AS pct ,
                    ROW_NUMBER() OVER ( ORDER BY wait_time_ms DESC ) AS rn
           FROM     sys.dm_os_wait_stats
           WHERE    wait_type NOT IN ( 'CLR_SEMAPHORE', 'LAZYWRITER_SLEEP',
                                       'RESOURCE_QUEUE', 'SLEEP_TASK',
                                       'SLEEP_SYSTEMTASK',
                                       'SQLTRACE_BUFFER_FLUSH', 'WAITFOR',
                                       'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE',
                                       'REQUEST_FOR_DEADLOCK_SEARCH',
                                       'XE_TIMER_EVENT', 'BROKER_TO_FLUSH',
                                       'BROKER_TASK_STOP',
                                       'CLR_MANUAL_EVENT',
                                       'CLR_AUTO_EVENT',
                                       'DISPATCHER_QUEUE_SEMAPHORE',
                                       'FT_IFTS_SCHEDULER_IDLE_WAIT',
                                       'XE_DISPATCHER_WAIT',
                                       'XE_DISPATCHER_JOIN' )
         )
    SELECT  W1.wait_type ,
            CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s ,
            CAST(W1.pct AS DECIMAL(12, 2)) AS pct ,
            CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    FROM    Waits AS W1
            INNER JOIN Waits AS W2 ON W2.rn <= W1.rn
    GROUP BY W1.rn ,
            W1.wait_type ,
            W1.wait_time_s ,
            W1.pct
    HAVING  SUM(W2.pct) - W1.pct < 95 ; -- percentage threshold

-- Script 22
-- Recovery model, log reuse wait description, log file size,
-- log usage size and compatibility level for all databases on instance
SELECT  db.[name] AS [Database Name] ,
        db.recovery_model_desc AS [Recovery Model] ,
        db.log_reuse_wait_desc AS [Log Reuse Wait Description] ,
        ls.cntr_value AS [Log Size (KB)] ,
        lu.cntr_value AS [Log Used (KB)] ,
        CAST(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)
                    AS DECIMAL(18,2)) * 100 AS [Log Used %] ,
        db.[compatibility_level] AS [DB Compatibility Level] ,
        db.page_verify_option_desc AS [Page Verify Option]
FROM    sys.databases AS db
        INNER JOIN sys.dm_os_performance_counters AS lu
                    ON db.name = lu.instance_name
        INNER JOIN sys.dm_os_performance_counters AS ls
                    ON db.name = ls.instance_name
WHERE   lu.counter_name LIKE 'Log File(s) Used Size (KB)%'
        AND ls.counter_name LIKE 'Log File(s) Size (KB)%' ;

-- Script 23
-- Hardware information from SQL Server 2008
-- (Cannot distinguish between HT and multi-core)
SELECT  cpu_count AS [Logical CPU Count] ,
        hyperthread_ratio AS [Hyperthread Ratio] ,
        cpu_count / hyperthread_ratio AS [Physical CPU Count] ,
        physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)] ,
        sqlserver_start_time
FROM    sys.dm_os_sys_info ;
-- Hardware information from SQL Server 2005
-- (Cannot distinguish between HT and multi-core)
SELECT  cpu_count AS [Logical CPU Count] ,
        hyperthread_ratio AS [Hyperthread Ratio] ,
        cpu_count / hyperthread_ratio AS [Physical CPU Count] ,
        physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)]
FROM    sys.dm_os_sys_info ;

-- Script 24
-- Get CPU Utilization History for last 30 minutes (in one minute intervals)
-- This version works with SQL Server 2008 and SQL Server 2008 R2 only
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
	  SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
			AS [SystemIdle],
			record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
			'int')
			AS [SQLProcessUtilization], [timestamp]
	  FROM (
			SELECT [timestamp], CONVERT(xml, record) AS [record]
			FROM sys.dm_os_ring_buffers
			WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
			AND record LIKE N'%%') AS x
	  ) AS y
ORDER BY record_id DESC;

-- Script 25
-- Get Avg task count and Avg runnable task count
SELECT  AVG(current_tasks_count) AS [Avg Task Count] ,
        AVG(runnable_tasks_count) AS [Avg Runnable Task Count]
FROM    sys.dm_os_schedulers
WHERE   scheduler_id < 255
        AND [status] = 'VISIBLE ONLINE' ;

-- Script 26
-- Is NUMA enabled
SELECT  CASE COUNT(DISTINCT parent_node_id)
          WHEN 1 THEN 'NUMA disabled'
          ELSE 'NUMA enabled'
        END
FROM    sys.dm_os_schedulers
WHERE   parent_node_id <> 32 ;

-- Script 27
-- Good basic information about memory amounts and state
-- SQL Server 2008 and 2008 R2 only
SELECT  total_physical_memory_kb ,
        available_physical_memory_kb ,
        total_page_file_kb ,
        available_page_file_kb ,
        system_memory_state_desc
FROM    sys.dm_os_sys_memory ;

-- Script 28
-- SQL Server Process Address space info (SQL 2008 and 2008 R2 only)
--(shows whether locked pages is enabled, among other things)
SELECT  physical_memory_in_use_kb ,
        locked_page_allocations_kb ,
        page_fault_count ,
        memory_utilization_percentage ,
        available_commit_limit_kb ,
        process_physical_memory_low ,
        process_virtual_memory_low
FROM    sys.dm_os_process_memory ;

-- Script 29
-- Look at the number of items in different parts of the cache

SELECT  name ,
        [type] ,
        entries_count ,
        single_pages_kb ,
        single_pages_in_use_kb ,
        multi_pages_kb ,
        multi_pages_in_use_kb
FROM    sys.dm_os_memory_cache_counters
WHERE   [type] = 'CACHESTORE_SQLCP'
        OR [type] = 'CACHESTORE_OBJCP'
ORDER BY multi_pages_kb DESC ;

-- Script 30
-- Get total buffer usage by database
SELECT  DB_NAME(database_id) AS [Database Name] ,
        COUNT(*) * 8 / 1024.0 AS [Cached Size (MB)]
FROM    sys.dm_os_buffer_descriptors
WHERE   database_id > 4 -- exclude system databases
        AND database_id <> 32767 -- exclude ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC ;

-- Breaks down buffers by object (table, index) in the buffer pool
SELECT  OBJECT_NAME(p.[object_id]) AS [ObjectName] ,
        p.index_id ,
        COUNT(*) / 128 AS [Buffer size(MB)] ,
        COUNT(*) AS [Buffer_count]
FROM    sys.allocation_units AS a
        INNER JOIN sys.dm_os_buffer_descriptors
                 AS b ON a.allocation_unit_id = b.allocation_unit_id
        INNER JOIN sys.partitions AS p ON a.container_id = p.hobt_id
WHERE   b.database_id = DB_ID()
        AND p.[object_id] > 100
GROUP BY p.[object_id] ,
        p.index_id
ORDER BY buffer_count DESC ;

-- Script 31
-- Find long running SQL/CLR tasks
SELECT  os.task_address ,
        os.[state] ,
        os.last_wait_type ,
        clr.[state] ,
        clr.forced_yield_count
FROM    sys.dm_os_workers AS os
        INNER JOIN sys.dm_clr_tasks AS clr
                     ON ( os.task_address = clr.sos_task_address )
WHERE   clr.[type] = 'E_TYPE_USER' ;

-- Script 32
-- Get population status for all FT catalogs in the current database
SELECT  c.name ,
        c.[status] ,
        c.status_description ,
        OBJECT_NAME(p.table_id) AS [table_name] ,
        p.population_type_description ,
        p.is_clustered_index_scan ,
        p.status_description ,
        p.completion_type_description ,
        p.queued_population_type_description ,
        p.start_time ,
        p.range_count
FROM    sys.dm_fts_active_catalogs AS c
        INNER JOIN sys.dm_fts_index_population AS p
                       ON c.database_id = p.database_id
                        AND c.catalog_id = p.catalog_id
WHERE   c.database_id = DB_ID()
ORDER BY c.name ;

-- Script 33
-- Check auto page repair history (New in SQL 2008)
SELECT  DB_NAME(database_id) AS [database_name] ,
        database_id ,
        file_id ,
        page_id ,
        error_type ,
        page_status ,
        modification_time
FROM    sys.dm_db_mirroring_auto_page_repair ;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s