locked
SQL Server Monitoring Script RRS feed

  • Question

  • Hi,

    I am looking for daily database server monitoring scripts. I want to run script from one server and generate Excel sheet that contains data for all other database servers:

    1. Disk space used/free

    2. Jobs status

    3. Sql Services

    Thanks

    Friday, February 1, 2013 8:25 PM

Answers

  • I use this for daily storage usage and backup info, I hope it is a good starting point for you:

    SET NOCOUNT ON
    USE MASTER
    DECLARE @dbs TABLE(name varchar(100))
    DECLARE @currentdb varchar(100)
    DECLARE @sql varchar(1000)
    
    IF object_id('tempdb..#DBInfo') IS NOT NULL
    BEGIN
       DROP TABLE #DBInfo
    END	
    CREATE TABLE #DBInfo(name varchar(100), TotalMb float, UsedMb bigint, UsedPc float, LogTotalMb BIGINT, LogUsedPc decimal(10,2), recoverymodel varchar(50), lastbackupcompletion datetime, lastbackupduration int, backup_size bigint , compressed_backup_size bigint,  owner varchar(100))		
    
    IF object_id('tempdb..#DBSizes') IS NOT NULL
    BEGIN
       DROP TABLE #DBSizes
    END	
    CREATE TABLE #DBSizes(name varchar(100), TotalSizeMB bigint, UsedMb bigint, AvailableMB bigint, UsedPc decimal(10,2))
    
    DECLARE @Logdata TABLE(dbname varchar(100), logsize float, logpcused float, status int)
    INSERT @Logdata exec('DBCC SQLPERF(logspace) WITH NO_INFOMSGS')
    
    declare @backup_finishes TABLE(database_name varchar(200), last_backup_completion datetime)
    insert @backup_finishes(database_name, last_backup_completion)
    select database_name
    , max(backup_finish_date) as last_backup_completion --, *
    from msdb.dbo.backupset
    where [Type] = 'D'
    group by database_name
    
    declare @backup_finishes_allinfo TABLE(database_name varchar(200), backup_finish_date datetime, backup_duration_mins int, backup_size bigint, compressed_backup_size bigint)
    insert @backup_finishes_allinfo(database_name, backup_finish_date, backup_duration_mins, backup_size, compressed_backup_size)
    select bus.database_name
    , backup_finish_date
    , datediff(minute,backup_start_date, backup_finish_date) as backup_duration_mins
    , backup_size
    , null
    from msdb.dbo.backupset bus
    inner join @backup_finishes bf on bus.database_name = bf.database_name
    	and bus.backup_finish_date = bf.last_backup_completion
    where [Type] = 'D'
    
    insert #DBInfo(NAME, recoverymodel, LogTotalMb, LogUsedPc, owner, lastbackupcompletion, lastbackupduration, backup_size, compressed_backup_size)
    SELECT NAME, recovery_model_desc, logsize, logpcused, SUSER_SNAME(owner_sid) as owner, bfa.backup_finish_date, bfa.backup_duration_mins, bfa.backup_size, bfa.compressed_backup_size
    FROM SYS.DATABASES DB
    left JOIN @Logdata LD ON DB.NAME = LD.[dbname]	
    left JOIN @backup_finishes_allinfo bfa ON db.name = bfa.database_name
    WHERE dATABASE_id > 4
    
    EXEC sp_msforeachdb
    ' USE ?
    INSERT #DBSizes (name) VALUES(''?'') '
    
    EXEC sp_msforeachdb
    ' USE ?
    UPDATE #DBSizes
      SET TotalSizeMB = x.TotalSizeMB
    , UsedMb = x.UsedMb
    , AvailableMB = x.AvailableMB
    , UsedPc = x.UsedPc
    FROM (
    	select SUM(size/128) as TotalSizeMB
    	, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128) as UsedMb
    	, SUM(size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128) as AvailableMB
    	, CONVERT(decimal(10,2) ,(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128) / SUM(size/128.0)) * 100) as UsedPc
    	from	sys.database_files df
    ) x	
    where name = ''?''	'
    
    update #DBInfo
    SET totalMb = DBS.TotalSizeMB
    , usedMb = DBS.usedMb
    , usedpc = DBS.UsedPc
    FROM #DBInfo DBI 
    	INNER JOIN #DBSizes DBS 
    		ON DBI.NAME = DBS.name
    
    select * from #DBInfo


    www.minidba.com | Sql Server Performance Tuning & Monitoring Software | Free Lite Edition

    • Proposed as answer by Ramesh Babu Vavilla Tuesday, February 5, 2013 5:25 AM
    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 9:48 AM
    Tuesday, February 5, 2013 12:17 AM

All replies

    • Edited by Kalman Toth Saturday, February 2, 2013 4:31 PM
    Saturday, February 2, 2013 4:28 PM
  • I use this for daily storage usage and backup info, I hope it is a good starting point for you:

    SET NOCOUNT ON
    USE MASTER
    DECLARE @dbs TABLE(name varchar(100))
    DECLARE @currentdb varchar(100)
    DECLARE @sql varchar(1000)
    
    IF object_id('tempdb..#DBInfo') IS NOT NULL
    BEGIN
       DROP TABLE #DBInfo
    END	
    CREATE TABLE #DBInfo(name varchar(100), TotalMb float, UsedMb bigint, UsedPc float, LogTotalMb BIGINT, LogUsedPc decimal(10,2), recoverymodel varchar(50), lastbackupcompletion datetime, lastbackupduration int, backup_size bigint , compressed_backup_size bigint,  owner varchar(100))		
    
    IF object_id('tempdb..#DBSizes') IS NOT NULL
    BEGIN
       DROP TABLE #DBSizes
    END	
    CREATE TABLE #DBSizes(name varchar(100), TotalSizeMB bigint, UsedMb bigint, AvailableMB bigint, UsedPc decimal(10,2))
    
    DECLARE @Logdata TABLE(dbname varchar(100), logsize float, logpcused float, status int)
    INSERT @Logdata exec('DBCC SQLPERF(logspace) WITH NO_INFOMSGS')
    
    declare @backup_finishes TABLE(database_name varchar(200), last_backup_completion datetime)
    insert @backup_finishes(database_name, last_backup_completion)
    select database_name
    , max(backup_finish_date) as last_backup_completion --, *
    from msdb.dbo.backupset
    where [Type] = 'D'
    group by database_name
    
    declare @backup_finishes_allinfo TABLE(database_name varchar(200), backup_finish_date datetime, backup_duration_mins int, backup_size bigint, compressed_backup_size bigint)
    insert @backup_finishes_allinfo(database_name, backup_finish_date, backup_duration_mins, backup_size, compressed_backup_size)
    select bus.database_name
    , backup_finish_date
    , datediff(minute,backup_start_date, backup_finish_date) as backup_duration_mins
    , backup_size
    , null
    from msdb.dbo.backupset bus
    inner join @backup_finishes bf on bus.database_name = bf.database_name
    	and bus.backup_finish_date = bf.last_backup_completion
    where [Type] = 'D'
    
    insert #DBInfo(NAME, recoverymodel, LogTotalMb, LogUsedPc, owner, lastbackupcompletion, lastbackupduration, backup_size, compressed_backup_size)
    SELECT NAME, recovery_model_desc, logsize, logpcused, SUSER_SNAME(owner_sid) as owner, bfa.backup_finish_date, bfa.backup_duration_mins, bfa.backup_size, bfa.compressed_backup_size
    FROM SYS.DATABASES DB
    left JOIN @Logdata LD ON DB.NAME = LD.[dbname]	
    left JOIN @backup_finishes_allinfo bfa ON db.name = bfa.database_name
    WHERE dATABASE_id > 4
    
    EXEC sp_msforeachdb
    ' USE ?
    INSERT #DBSizes (name) VALUES(''?'') '
    
    EXEC sp_msforeachdb
    ' USE ?
    UPDATE #DBSizes
      SET TotalSizeMB = x.TotalSizeMB
    , UsedMb = x.UsedMb
    , AvailableMB = x.AvailableMB
    , UsedPc = x.UsedPc
    FROM (
    	select SUM(size/128) as TotalSizeMB
    	, sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128) as UsedMb
    	, SUM(size/128 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128) as AvailableMB
    	, CONVERT(decimal(10,2) ,(sum(CAST(FILEPROPERTY(name, ''SpaceUsed'') AS int)/128) / SUM(size/128.0)) * 100) as UsedPc
    	from	sys.database_files df
    ) x	
    where name = ''?''	'
    
    update #DBInfo
    SET totalMb = DBS.TotalSizeMB
    , usedMb = DBS.usedMb
    , usedpc = DBS.UsedPc
    FROM #DBInfo DBI 
    	INNER JOIN #DBSizes DBS 
    		ON DBI.NAME = DBS.name
    
    select * from #DBInfo


    www.minidba.com | Sql Server Performance Tuning & Monitoring Software | Free Lite Edition

    • Proposed as answer by Ramesh Babu Vavilla Tuesday, February 5, 2013 5:25 AM
    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 9:48 AM
    Tuesday, February 5, 2013 12:17 AM
  • Hi

    Definetely for checking in all the servers you need to create linked server or use SSIS package.

    for Job Status : exec msdb..sp_help_job @execution_status = 1

    for disk space used free : xp_fixeddrives

    Thanks

    Saurabh Sinha

    • Proposed as answer by Saurabh Sinha DBA Sunday, February 10, 2013 4:42 PM
    • Marked as answer by Maggie Luo Wednesday, February 20, 2013 9:48 AM
    • Unmarked as answer by Maggie Luo Wednesday, February 20, 2013 9:48 AM
    Tuesday, February 5, 2013 1:53 AM