Category Archives: Reports

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

Verify Synonyms

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

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

 

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

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

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

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

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

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

Find If Mix Backup Types are Present

There are mainly two types of backups:

  • Native SQL backups (either stored to local disk or a network location)
  • API backups, where software such as TSM/TDP or NetBackup, or CommVault, SQLLite, etc

There could be problems recovering a database if a server has different types of backups configured, unbeknown to the DBA. Per example, if TDP is used, and another DBA takes a local disk backup (out of band backup), it could pose a problem when we are restoring Tlog backups. This script identifies this situation, and lists those databases where different types of backups have been performed. If none are found, the result is blank

 


/*
DESCRIPTION
Queries the location of the last X days of databases backups
and reviews its location: API, DiskBackup or NetworkLocation

If backup location has changed in the last X days,
it returns those databases
If no change in location n the last X days,
it returns empty.

CONFIGURATION Set @days for the number of days to search for backups
Set @dbname for the specific database; leave NULL for all
 
Compatibility list:
MSSQL2012 MSSQL2005 MSSQL2008 MSSQL2000  
*/

 
DECLARE @days INT
DECLARE @dbname sysname
 
SET @days = 7			-- Number of days. Limited by the history stored on MSDB
SET @dbname = NULL		-- Specify database like 'database1' . If Null it will do all
 
 
 
 ---- End of configuration
 
declare @BackupLocation table
(i int identity(1,1) ,db varchar(1000) , bk_type varchar(10), bk_Loc varchar(3))

insert into @BackupLocation 

SELECT
 bs.database_name
, CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'TLog'
END AS backup_type
, left(bmf.physical_device_name,3)
 
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE 1=1
and bmf.family_sequence_number = '1'
and (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() - @days)
--and bs.type = 'D'
and bs.database_name like coalesce(@dbname,'%')


ORDER BY
bs.database_name

--------------------------
declare @summary table

(i int identity(1,1) ,db varchar(1000), db2 varchar(1000) )

insert into @summary

select db, db+'_'+bk_type
 from @BackupLocation 

group by bk_loc, db, bk_type
order by db



 select 
 case
 when count(db2) > 1 then 'Mix Backups Found'
 else 'Only one Type of Backups'
 end as 'backups'
 , db2 from @Summary 
 
 group by db2
 having count(db2)>1 


select db,  bk_type,
case substring(bk_loc,2,1)
WHEN ':' THEN 'LOCAL DISK'
WHEN '\' THEN 'NETWORK LOCATION'  --'
ELSE 'API BACKUPS'
end as 'Type of Backups'

 from @BackupLocation 

where db in
(select db from @Summary 
 group by db2, db
 having count(db2)>1 
 )
 
group by bk_loc, db, bk_type
order by db


Thank you Andrey for the idea to create this script

Location of Data files and Tlog

.


/*

-- SQL Server 2008 and R2 Diagnostic Information Queries
-- Glenn Berry
-- June 2012
-- Last Modified: June 19, 2012
-- http://sqlserverperformance.wordpress.com/
-- http://sqlskills.com/blogs/glenn/
-- Twitter: GlennAlanBerry


DESCRIPTION:  List location of all files for all databases

Look for tempDB and  Tlog sharing the same drives as a source of I/O contention issues


 
Compatibility list:
MSSQL2005
MSSQL2008
MSSQL2008R2
MSSQL2012

DOES NOT WORK
MSSQL2000

Update Log:

*/

 

 

SELECT

DB_NAME([database_id])AS [Database Name]

,   [file_id]

, name

, physical_name

, type_desc

, state_desc

,  CONVERT( bigint, size/128.0) AS [Total Size in MB]
FROM sys.master_files WITH (NOLOCK)
WHERE [database_id] > 4
AND [database_id] <> 32767
OR [database_id] = 2
ORDER BY DB_NAME([database_id]) OPTION (RECOMPILE);

.