Category Archives: Reports

Reporting… like how old backups are, baselines, anything that gathers info about a Server

Location of very last Backups — All Databases

Backups, Reports, restore

One script with some CTE, but two ways to show the info.

A narrow view: 

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
--ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
--	top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
--ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
--top 10
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
--    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
--ORDER BY  s.backup_finish_date desc
)
, final as
(
select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff' 'bk_type', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1

UNION ALL

select c.database_name, c.recovery_model, 'tlog' 'bk_type', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1 and coalesce(b.number,1) = 1
)
select * from final
order by 1,3

 

 

A wide view 


 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @LastXdays nvarchar(2)
set @LastXdays = '30'

 
;with fullbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
)
, Diffbk as (  
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
)
, TlogBK as (
SELECT
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE 1=1
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
)

select a.database_name, a.recovery_model
	, a.bk_type, a.physical_device_name,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
	, 'DIFF' 'DIFF_BK', b.physical_device_name,b.Seconds_TimeTaken,b.age_in_days, b.backup_start_date
	,  'TLOG' 'TLOG_BK', c.physical_device_name,c.Seconds_TimeTaken,c.age_in_days, c.backup_start_date
from fullbk a
left join Diffbk b on a.dbid = b.dbid
left join TlogBK c on a.dbid = c.dbid

where 1=1
and a.number  = 1 
and coalesce(b.number,1)=1
and coalesce(c.number,1)=1

union all
select Distinct
    convert(varchar(30),name) as DBname
    ,DATABASEPROPERTYEX(name,'recovery') 'RecoveryModel'
    ,'NO BACKUPS RECORDED ON MSDB',NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
    ,NULL,NULL,NULL,NULL,NULL
 
from master..sysdatabases as record
where
    name not in (select distinct database_name from msdb..backupset)
    and name not in ('tempdb','pubs','northwind','model')
order by 1,2

Location of very last Backups

Backups, Reports, restore

This script will list the location of the last Full, Differential and Tlog (if exists)

I use three CTE and at the end I use a UNION all

the last two select statements are joins so it displays NULL if either Diff or Tlog do not exist

  

 /*
DESCRIPTION    List location of  all backup for a single database.
				Includes if a Tlog or Dif exists or not
CONFIGURATION    

                          Set @LastXday to limit the number of days to search for

                          Set @dbname for the database

 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012
MSSQL2014

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = db_name()
--SET @dbname = 'ELMO'
set @LastXdays = '30'
 
;with fullbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'full' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'D'
ORDER BY  s.backup_finish_date desc
)
, Diffbk as (  
SELECT
	top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
    ,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Diff' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'I'
ORDER BY  s.backup_finish_date desc
)
, TlogBK as (
SELECT
top 1
	 s.database_name
	, db_id(s.database_name) 'dbid'
	,s.recovery_model
    ,m.physical_device_name
	,cast(s.backup_size/1000000 as varchar(24))+' '+'MB' as bkSize
    ,DATEDIFF(second,s.backup_start_date , s.backup_finish_date) 'Seconds_TimeTaken'
    ,DATEDIFF(dd,(s.[backup_finish_date]),GETDATE())  AS [age_in_days]
    ,s.backup_start_date
    ,'Tlog' 'bk_type'
	,ROW_NUMBER() over (partition by s.database_name order by s.database_name, s.backup_start_date desc) 'number'
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE
    s.database_name like @dbname
    and DATEDIFF(dd,(s.[backup_finish_date]),GETDATE()) < @LastXdays
	and s.[type] = 'L'
ORDER BY  s.backup_finish_date desc
)

select a.database_name, a.recovery_model, a.bk_type, a.physical_device_name,a.bk_type,a.Seconds_TimeTaken,a.age_in_days, a.backup_start_date
from fullbk a
where 1=1
and a.number  = 1
UNION ALL
select c.database_name, c.recovery_model, 'Diff', 
coalesce(b.physical_device_name,'') as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Diffbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1		 

UNION ALL
select c.database_name, c.recovery_model, 'tlog', 
case c.recovery_model
when 'simple' then ''
else b.physical_device_name end as 'physical_device_name',
b.bk_type,
b.seconds_TimeTaken,
b.age_in_days,
b.backup_start_date

from Tlogbk b right join fullbk c on b.dbid = c.dbid
where 1=1
and c.number  = 1

 

 

 

Verify Synonyms

misc, Reports

The following script gets a list of the synonyms, parses them, and then the checks if the existence of the object on the sys.objects table.

Any errors encountered are assumed as "not a valid synonym" 

/*
Author: Miguel Quintana

Description:
Reads all SYNONYMS on the database and test if they are valid.
It checks their EXISTENCE on the SYSOBJECTS table
and uses the Link Server if available

No configuration needed. Just run it under the DB that you are testing Synonyms

*/


-- this variables are for the cursor
DECLARE @NAME NVARCHAR(100)
  ,@SERVERNAME NVARCHAR(20)
  ,@DBName NVARCHAR(100)
  ,@SCHEMA NVARCHAR(100)
  ,@OBJECTNAME NVARCHAR(1000)
  ,@BaseObjectName NVARCHAR(1000)
-- This are to process the data
DECLARE @CMD NVARCHAR(MAX)
DECLARE @RESULTS TABLE (
  VALUE NVARCHAR(100)
  ,SYNONYM NVARCHAR(100)
  ,BaseObjectName NVARCHAR(100)
  ,Link_SERVER NVARCHAR(100)
  )
DECLARE CUR CURSOR
FOR
SELECT NAME
  ,PARSENAME(base_object_name, 4) ServerName
  ,COALESCE(PARSENAME(base_object_name, 3), DB_NAME(DB_ID())) AS dbName
  ,COALESCE(PARSENAME(base_object_name, 2), SCHEMA_NAME(SCHEMA_ID())) AS schemaName
  ,PARSENAME(base_object_name, 1) AS objectName
  ,BASE_OBJECT_NAME
FROM sys.synonyms
order by ServerName DESC, NAME



OPEN CUR
FETCH NEXT FROM CUR INTO 
   @name
  ,@SERVERNAME
  ,@DBNAME
  ,@Schema
  ,@ObjectName
  ,@BaseObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Test SYNONYMS that use Link Servers
  IF @SERVERNAME IS NOT NULL
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + '''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @SERVERNAME + ''''''') AT [' + @SERVERNAME + ']'
    print @cmd
	BEGIN TRY
      INSERT INTO @RESULTS
	  EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@SERVERNAME
        )
    END CATCH
  END
  -- Test SYNONYMS that do not use Link Servers
  ELSE
  BEGIN
    SET @cmd = 'EXEC (''IF EXISTS(select name from ' + @DBName + '.[sys].[objects] where name = ''''' + @OBJECTNAME + ''''')
    SELECT ''''VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''','''' ''''
    ELSE
    SELECT ''''NOT A VALID SYNONYM'''',''''' + @NAME + ''''',''''' + @BaseObjectName + ''''',''''' + @@SERVERNAME + ''''''')'
    print @cmd    
	
	BEGIN TRY
      INSERT INTO @RESULTS
      EXEC (@cmd)
    END TRY
    -- IF the exec fails for any reason, the SYSNONYM is not valid
    BEGIN CATCH
      INSERT INTO @RESULTS
      VALUES (
        'NOT A VALID SYNONYM'
        ,@NAME
        ,@BaseObjectName
        ,@@SERVERNAME
        )
    END CATCH
  END
  FETCH NEXT FROM CUR INTO
     @name
    ,@SERVERNAME
    ,@DBNAME
    ,@Schema
    ,@ObjectName
    ,@BaseObjectName
END
CLOSE CUR
DEALLOCATE CUR

SELECT Value
  ,[SYNONYM]
  ,UPPER(Link_SERVER) 'Link Server'
  ,BaseObjectName
  ,COALESCE(ss.data_source,'') 'Data Source'
FROM @RESULTS r
LEFT JOIN master.sys.servers ss on r.Link_SERVER = ss.name


ORDER BY VALUE

Link Servers are supported.

 

Sometimes, it will report as not valid SYNONYM even though you are able to use them (select top 1 * from <sn_syonym_name>). but users without sysadmin cannot.

There are many reasons why that would happen, usually drop & recreate fixes the issue or even running the following:

  


EXEC sp_serveroption @server = '',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;

 

Duration of SQL Jobs

Job Information, Reports

List SQL Job duration

If you need step details, see http://www.cookingsql.com/2017/02/duration-of-sql-jobs-steps/ 

/*
DESCRIPTION	List duration of jobs 
CONFIGURATION none
some filtering might be used

Compatibility List
SQL 2008 and up

*/
select 
 j.name as 'JobName',
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
 ((run_duration/10000*3600 + (run_duration/100)%100*60 + run_duration%100 + 31 ) / 60) 
         as 'RunDurationMinutes'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h  ON j.job_id = h.job_id 
where 1=1
and j.enabled = 1   --Only Enabled Jobs
and h.step_id = 0   --Only show the total duration
and j.name like '%backup%'           --Uncomment to search for a single job
/*
and msdb.dbo.agent_datetime(run_date, run_time) 
BETWEEN '01/01/2017' and '02/01/2017'  --Uncomment for date range queries
*/
order by JobName, RunDateTime desc

 

 

Link Server Information

misc, Reports

 

A very simply script to list all link servers available on the SQL Server 


SELECT 
 ss.name 
,ss.product 
,ss.provider
,ss.data_source 
,case sl.uses_self_credential 
	when 1 then 'Uses Self Credentials' 
    else coalesce(ssp.name,'') 
	end 'Local login'
, coalesce(sl.remote_name,'') 'Remote Login Name'
,case ss.is_rpc_out_enabled
	when 1 then 'True'
	else 'False'
	end 'RPC Out Enabled'
 ,case ss.is_data_access_enabled 
   when 1 then 'True'
   else 'False'
   end 'Data Access Enabled'
,ss.modify_date 
FROM sys.Servers ss 
 LEFT JOIN sys.linked_logins sl ON ss.server_id = sl.server_id 
 LEFT JOIN sys.server_principals ssp ON ssp.principal_id = sl.local_principal_id
 where 1=1
Order by ss.name

 

Growth of Database

InsideDatabases, Reports

The following script provides an educated guess regarding database growth

It does this by examining the size of backups. It queries MSDB for backup sizes, calculated the difference, divided by the number of days in between, and finally averages the difference. 

/*
DESCRIPTION    Estimate Database growth based on backups.

Assuming backup size grows at the same rate as the Database size.  
First calculate difference in size between backups,
and then divides it by the number of days the backup took place
Finaly, averages the result.
CONFIGURATION    
    Set @LastXday to limit the number of days to search for
    Set @dbname for the database
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2012

 */ 
declare @dbname varchar(500)
declare @LastXdays nvarchar(2)
set @dbname = 'AdventureWorks'
set @LastXdays = '150'

;WITH BK_Growth (BKsize,BKDate,rownumber) as(  
	SELECT
		 backup_size
		,backup_start_date
		,row_number() OVER 
				(ORDER BY database_name,
				[type], backup_start_date DESC)
				AS rownumber
	FROM msdb.dbo.backupset
	WHERE
		database_name like @dbname
		and DATEDIFF(dd,([backup_finish_date]),GETDATE()) < @LastXdays
		and type = 'D'
)
select 
@dbname 'dbname'
,CAST( CAST(avg(100*((
(prev.BKSize - cur.BKSize)/cur.BKSize)/(DATEDIFF(dd,cur.BKDate,Prev.BKDate))
)) as NUMERIC(5,3))
as VARCHAR(5))+' %' 'Average Daily Growth'

,     (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB
,     (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
 
from BK_Growth cur
inner join BK_Growth prev on CUR.rownumber = (PREV.rownumber+1)

cross apply sys.databases db
    LEFT JOIN (
    SELECT database_id, SUM(size) RowSize 
    FROM sys.master_files 
    WHERE type = 0 and database_id = DB_ID(@DBName)
    GROUP BY database_id, type
        ) mfrows ON mfrows.database_id = db.database_id
    
    LEFT JOIN (
    SELECT database_id, SUM(size) LogSize 
    FROM sys.master_files 
    WHERE type = 1  and database_id = DB_ID(@DBName)
    GROUP BY database_id, type
        ) mflog ON mflog.database_id = db.database_id

WHERE mfrows.RowSize is not null
GROUP BY mfrows.RowSize, mflog.LogSize

 

Historical Backup Report

Backups, Reports

The Greatest Backup Report

In some organization an important Key Point Indicator (KPI) has to do with backup success rates. This KPI attempts to measure what percentage of backups have taken place. However, many times this KPI has not been clearly defined.

I always ask "what constitute a backup failure"?  If a backup fails to take place but I fix it the next morning, is that still a fail backup?  or if a Full Backup fails but the Tlogs backups still take place, so if a failure occurs I'm still able to recover with no data loss, is that still a failed backup? This is the equivalent of if a tree fails in the forest, but I repair it without anyone knowing, did it still fall?

If we are reporting against SQL Jobs success rates, then we can run into a situation where if the job is misconfigured and it fails to account for newly created databases, we could run into a situation where we have 100% success rate but have databases with no backups.

Some might argue that, using the true definition, "a successful backup is one that you could restore".  This is fine and dandy, but how often are we able to restore all our database backups in a daily manner?

As you can see, there areextremes, but we still need to provide an honest measure for the KPI.

A good compromise would be to report against the actual backup as recorded in MSDB versus the expetected schedule of said backup

In this sense, we are not interested just on the last backup, but we are interested to know if all backups are running at regular intervals, and what that interval is.

This is a first iteration of the script where for a single DB might look like this:

1-Bk_Report_history

Based on the results, we can assume that the Backup Schedule probably is as follow:
Full 7 days.
Diff 1 day.
Tlog: Hourly.

We can also assume that the first value for Full is 5.  Since 5 is less than 7, not an issue,  still within schedule.
The last  value for Diff is 2.  Since 2 is more than 1, possible and issue. However, it probably means that a full backup was taken instead.
There is a value Tlog that is 2. Since 2 more than 1, there was a missed backup in that time period.

 

Another example:

2-Bk_Report_history

We can tell that FULL failed, since the first value is 12, and 12 is more than 7, therefore a Full Backup is missing.
We can also tell that DIFF failed, since the first value is   2, and   2 is more than 1, therefore a Diff Backup is missing.

Last Example:

3-Bk_Report_history

In here, we can see that someone was taking backups outside our normal backup schedule, sine most backups are going to TDP and we have a few backups going to local disk.

 

Finally, the script itself:

The Script is fully customizable to report for a certain number of days and either all databases or just one:

  

/*
DESCRIPTION	
List Database Backup Age, per database, and the intervale between backups

CONFIGURATION	top
SET @LastXDays for how far back you query MSDB
Set @DiffDays if you want to limit Diff.
Set @TlogDays if you want to limit the Tlog 

Set @dbname to NULL for all DBs, or to a single DB

Author		 mquinta@us.ibm.com

Compatibility list: 
MSSQL2005
MSSQL2008
MSSQL2012


*/

DECLARE @dbname varchar(500)
DECLARE @LastXdays nvarchar(2)
DECLARE @TlogDays int
DECLARE @DiffDays int

-- Configuration
--  how much history we query
SET @LastXdays = 28

-- limt the results for Differential backups
SET @DiffDays = 7
-- Limit results for Tlogs. Recommended 1 or 2 days
SET @TlogDays = 1  

-- SET database. If Null, it'll do all databases
SET @dbname = null  
-- SET @dbname = 'adventureworks'
----  end configuration


;WITH BackupAgeTable AS
(  
-- query info for backups, and place it in a 
-- Common Table Expression, adding a row_number row.
SELECT
	 s.database_name
	,s.backup_size
	,s.backup_start_date
	,s.type
	,m.physical_device_name
	,m.device_type
-- Ordering will group by datbase, then type of BK and then sort by date
	,row_number() OVER (ORDER BY s.database_name,
								 s.[type],
								 s.backup_start_date DESC)
						AS rownumber
FROM SYS.DATABASES d   
INNER JOIN msdb.dbo.backupSET s ON d.database_id = db_id(s.database_name)
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_SET_id = m.media_SET_id
WHERE
	1=1
	and DATEDIFF(dd,(s.backup_start_date),GETDATE()) < = @LastXdays
	and s.database_name like coalesce (@dbname,'%')
		
)

SELECT 
	cur.database_name
	, CASE cur.type
		WHEN 'D' THEN 'Full'
		WHEN 'I' THEN 'Diff'
		WHEN 'L' THEN 'TLog'
		else 'other'
	END AS backup_type
	, cast(cur.backup_size/1024/1024 as numeric (10,1)) 'SizeMB'
	, CASE cur.device_type
		when 2 then 'Disk'
		when 7 then 'Virtual' -- Like TDP
	end as 'DeviceType'
	, cur.physical_device_name
	, cur.backup_start_date
-- Using COALESCE because first row will be NULL and we need backup age instead
	, case cur.type
		when 'D' then 
		COALESCE (DATEDIFF(dd,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(dd,CUR.backup_start_date,GETDATE()))
		when 'I' then
		COALESCE (DATEDIFF(dd,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(dd,CUR.backup_start_date,GETDATE()))
		when 'L' then
		COALESCE (DATEDIFF(hh,CUR.backup_start_date,PREV.backup_start_date),
				  DATEDIFF(hh,CUR.backup_start_date,GETDATE()))
		else 'other'			
	END as 'interval of Backups'
-- Using CASE statement because we want HOURs for Tlogs Backups and DAYS for the rest	
	, case cur.type
		when 'D' then 'days'
		when 'I' then 'days'
		when 'L' then 'hours'
		else 'other'
	END as 'units (days/hours)'

FROM BackupAgeTable CUR
-- Left join over itsef but with a offSET of one row
-- to be able to make a DIFF between dates of two consecutive rows
LEFT join BackupAgeTable PREV 
	on CUR.rownumber = (PREV.rownumber+1)
	   and cur.database_name=prev.database_name
	   and cur.type = prev.type 
where 1=1
-- Clever filtering to limit resutls
      and(   (cur.type = 'D')-- and datediff(dd,cur.backup_start_date,getdate())< @LastXdays)
	  or (cur.type = 'I' and datediff(dd,cur.backup_start_date,getdate())< @DiffDays )
	  or (cur.type = 'L' and datediff(dd,cur.backup_start_date,getdate())< @TlogDays )
	 )

-- The UNION ALL checks for DBs with no backups
UNION ALL
SELECT DISTINCT
	  SD.name 'database'
	, 'NO BACKUPS' 'TYPE'
	, NULL 'SizeMB'
	, NULL 'DeviceType'
	, NULL 'PhysicalDevice'
	, NULL 'BackupStartDate'
	, NULL 'Interval'
	, 'NO BACKUPS'	'Units'
FROM 
	 master..sysdatabases as SD

LEFT JOIN msdb..backupSET bp on SD.name = bp.database_name 
		 AND DATEDIFF(dd,(bp.backup_start_date),GETDATE()) <= @LastXdays
WHERE 1=1
and bp.database_name is NULL
and sd.name not in ('tempDB','model')


ORDER BY 8 DESC ,1,2, 6
-- First order by column 8 so databases with no backups
-- are listed on top

 

Failed Job History

Job Information, Reports

List all jobs that have failed in the last week

  

/*
DESCRIPTION    List Jobs that have failed in the last 7 days.
CONFIGURATION   none
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2000
 */

SELECT 
DISTINCT msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime',
t2.name 'Job Name',
T1.step_id AS 'Step_id', 
T1.step_name AS 'Step Name',
 
case
when run_status=0 then 'Failed'
when run_status=1 then 'Succeeded'
when run_status=2 then 'Retry'
when run_status=3 then 'Canceled'
else ''
end as Status,
LEFT(T1.[message],500) AS 'Error Message'

  
 FROM msdb..sysjobhistory T1 
 JOIN msdb..sysjobs T2 ON T1.job_id = T2.job_id
 
  WHERE 
  T1.run_status NOT IN (1,4) 
  AND T1.step_id != 0 
  and msdb.dbo.agent_datetime(run_date, run_time) > DateAdd(day,-7,GetDate())

SysAdmins on SQL

misc, Reports

The following query well known query will list all logins with SYSADMIN access to a sql Server: 

    SELECT  p.name 'LoginName',
	    p.type_desc

    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
		p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
		-- Logins that are not process logins
        AND p.name NOT LIKE '##%'
		-- exclude disabled
		and p.is_disabled = 0
		-- Logins that are sysadmins
		AND s.sysadmin = 1
ORDER BY Type

The problem is that the query may return Windows Groups, and we might want to know membership to those groups.

Which is why we rather run the following query instaead: 


/*
DESCRIPTION:  List users with SYSADMIN access 
If a Windows group has sysadmin access
it will list its members

Lists only enabled users at SQL level
cannot check if valid AD account

CONFIGURATION: None
 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012
DOES NOT WORK

*/

DECLARE @logininfo table
( [LoginName] sysname
, [type] char(8), [priv] char(9), [mpln] sysname
, [GroupMembership] sysname
)

DECLARE @GroupName sysname

DECLARE cur CURSOR FOR

	SELECT
			p.name
    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
			p.type_desc IN ('WINDOWS_GROUP')
			-- exclude disabled
			and p.is_disabled = 0
			-- Logins that are sysadmins
			AND s.sysadmin = 1

OPEN cur
FETCH NEXT FROM cur INTO @GroupName
WHILE @@FETCH_STATUS = 0
BEGIN

	INSERT INTO @logininfo
	EXEC master..xp_logininfo 
	@acctname = @GroupName,
	@option = 'members'

FETCH NEXT FROM cur INTO @GroupName
END
CLOSE cur
DEALLOCATE cur

SELECT 
	LoginName,
	'WINDOWS_GROUP' 'Type_Desc',
	GroupMembership 'GroupMembership'
FROM @logininfo  
UNION ALL
SELECT  p.name 'LoginName',
		p.type_desc,
		'' 'GroupMembership'
    FROM    sys.server_principals p
    JOIN	sys.syslogins s ON p.sid = s.sid
	WHERE   
		p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN')
		-- Logins that are not process logins
        AND p.name NOT LIKE '##%'
		-- exclude disabled
		and p.is_disabled = 0
		-- Logins that are sysadmins
		AND s.sysadmin = 1
ORDER BY GroupMembership, Type_Desc

 

Database Size Totals

All_The_Basics, Reports, Space-Issues

Very simple script that list the total size of a database and its transaction log.

If you need for SQL 2000 click here

  



/*
DESCRIPTION    List space used on databases
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2012
MSSQL2008
MSSQL2005

DOES NOT WORK
MSSQL2000

 
*/


SELECT
    SERVERPROPERTY('SERVERNAME') ServerName,
    SERVERPROPERTY('IsClustered') IsCluster,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    case 
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.3 then 'Windows 2012R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

else RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
end,
    DB_NAME(db.database_id) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sys.databases db
    LEFT JOIN (
    SELECT database_id, SUM(size) RowSize 
    FROM sys.master_files 
    WHERE type = 0 
    GROUP BY database_id, type
				) mfrows ON mfrows.database_id = db.database_id
    
    LEFT JOIN (
    SELECT database_id, SUM(size) LogSize 
    FROM sys.master_files 
    WHERE type = 1 
    GROUP BY database_id, type
				) mflog ON mflog.database_id = db.database_id
WHERE
	db.database_id > 4

 

  For SQL 2000 


/*
DESCRIPTION    List space used on databases
 
CONFIGURATION    none

 
Compatibility list:
MSSQL2000

 
*/

SELECT
    SERVERPROPERTY('SERVERNAME') ServerName,
    SERVERPROPERTY('IsClustered') IsCluster,
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    case 
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.3 then 'Windows 2012R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.2 then 'Windows 2012'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.1 then 'Windows 2008R2'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 6.0 then 'Windows 2008'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.2 then 'Windows 2003'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.1 then 'Windows XP'
when RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)= 5.0 then 'Windows 2000'

else RIGHT(SUBSTRING(@@VERSION, CHARINDEX('Windows NT', @@VERSION), 14), 3)
end,
    DB_NAME(db.dbid) DatabaseName,
    (CAST(mfrows.RowSize AS FLOAT)*8)/1024 RowSizeMB,
    (CAST(mflog.LogSize AS FLOAT)*8)/1024 LogSizeMB
FROM sysdatabases db
    LEFT JOIN (
    SELECT dbid, SUM(size) RowSize 
    FROM master..sysaltfiles
    WHERE groupID = 1 
    GROUP BY dbid, groupID
				) mfrows ON mfrows.dbid = db.dbid
    
    LEFT JOIN (
    SELECT dbid, SUM(size) LogSize 
    FROM master..sysaltfiles
    WHERE groupID <> 1
    GROUP BY dbid, groupID
				) mflog ON mflog.dbid = db.dbid
WHERE
	db.dbid > 4