DAC 连接数据库需要做些什么

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

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