Query to Display Recent Data and TLog File Autogrowth and Autoshrink Events   2 comments

Here is a query to read the default trace on your SQL instance and produce a report of recent Data and TLog file autogrowth and autoshrink events that have occurred for that SQL instance. The query works for SQL 2005 and above, and there are no edits required.

The query is a modified version of the one used by the SQL 2008 R2 Management Studio’s “Standard Report” named “Disk Usage,” which has a section that displays this data (Object Explorer –> right click on database –> Reports –> Standard Reports –> “Disk Usage”).

Just open a query window connected to the SQL instance you wish to report on and run the query. This version displays data for all databases, including the database name, the date of the autogrowth event, the number of milliseconds it took, and the size of the growth in MB.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--NE - 6/17/2011

--Query to display recent Data and TLog file autogrowth and autoshrink events
--for all databases on the instance. Based on query used by the SSMS Standard
--Report named "Disk Usage" which has a section that displays this data. Data
--is retrieved from the default trace.

--Works for SQL 2005 and above.

USE [master]
GO

BEGIN TRY
	IF (SELECT CONVERT(INT,value_in_use) FROM sys.configurations WHERE NAME = 'default trace enabled') = 1
	BEGIN 
		DECLARE @curr_tracefilename VARCHAR(500);
		DECLARE @base_tracefilename VARCHAR(500);
		DECLARE @indx INT;

		SELECT @curr_tracefilename = path FROM sys.traces WHERE is_default = 1;
		SET @curr_tracefilename = REVERSE(@curr_tracefilename);
		SELECT @indx  = PATINDEX('%\%', @curr_tracefilename) ;
		SET @curr_tracefilename = REVERSE(@curr_tracefilename) ;
		SET @base_tracefilename = LEFT( @curr_tracefilename,LEN(@curr_tracefilename) - @indx) + '\log.trc'; 
		SELECT
			--(DENSE_RANK() OVER (ORDER BY StartTime DESC))%2 AS l1,
			ServerName AS [SQL_Instance],
			--CONVERT(INT, EventClass) AS EventClass,
			DatabaseName AS [Database_Name],
			Filename AS [Logical_File_Name],
			(Duration/1000) AS [Duration_MS],
			CONVERT(VARCHAR(50),StartTime, 100) AS [Start_Time],
			--EndTime,
			CAST((IntegerData*8.0/1024) AS DECIMAL(19,2)) AS [Change_In_Size_MB]
		FROM ::fn_trace_gettable(@base_tracefilename, default)
		WHERE 
			EventClass >=  92
			AND EventClass <=  95
			--AND ServerName = @@SERVERNAME
			--AND DatabaseName = 'myDBName'  
		ORDER BY DatabaseName, StartTime DESC;  
	END     
	ELSE    
		SELECT -1 AS l1,
		0 AS EventClass,
		0 DatabaseName,
		0 AS Filename,
		0 AS Duration,
		0 AS StartTime,
		0 AS EndTime,
		0 AS ChangeInSize 
END TRY 
BEGIN CATCH 
	SELECT -100 AS l1,
	ERROR_NUMBER() AS EventClass,
	ERROR_SEVERITY() DatabaseName,
	ERROR_STATE() AS Filename,
	ERROR_MESSAGE() AS Duration,
	1 AS StartTime, 
	1 AS EndTime,
	1 AS ChangeInSize 
END CATCH

Posted June 21, 2011 by Norm Enger in Microsoft SQL Server

Posted June 21, 2011 by Norm Enger in Microsoft SQL Server

Publishing Source Code to WordPress Using Windows Live Writer 2011   2 comments

My previous blog post “Publishing Code Snippets on My WordPress Blog” explained how I used a read-only textarea to publish source code. Alas, WordPress has started stripping out <textarea> tags when you publish to their site or edit existing posts. They explain that this is for security reasons at http://en.support.wordpress.com/code/.

The above WordPress article also references another article at http://en.support.wordpress.com/code/posting-source-code/ which explains that they have a “sourcecode” “short code” available which can help with publishing source code blocks or code snippets. Unfortunately, that article is muddled in how it presents how to insert the short codes, and also it does not offer any help for Windows Live Writer 2011 users.

After experimenting a bit, I have discovered how to easily use the short codes from within Live Writer, in combination with the <PRE> tag, and what to expect when using it in this editor.

The basic way to include a code block is as follows. Note that the short code tags are enclosed in square brackets rather than angle brackets. Also you will have to type this code in HTML view.

image

Please note that when you go back to the Edit view in Live Writer, the above “sourcecode” lines will appear as text. Do not edit these out. When you publish, the WordPress site will accept these and will not display them, but will interpret them correctly and display your code with the fancy highlighting.

A second important note: when in the Edit view in Live Writer, you will see your snippits appearing as plain text, without the syntax coloring. Don’t try to correct this. Once you publish the post to WordPress, it will appear as expected, with the syntax highlighting, toolbar, etc.

Please note that you must also include the <PRE> tag because if you do not, WordPress will remove the line breaks and tabs from your snippets when you publish.

In my example, the language=”sql” parameter specifies which language I want syntax highlighting for, in my case, SQL. The wraplines=”false” parameter ensures that a horizontal scroll bar will appear if I have longer lines of code. This particular option was needed with my WordPress theme, but you may need to experiment with this. The gutter=”false” parameter prevents line numbering in the code block. The full explanation of these and other parameters is explained in greater detail at http://en.support.wordpress.com/code/posting-source-code/.

Happy blogging!

Posted June 20, 2011 by Norm Enger in Microsoft SQL Server

Display Current Backup/Restore Progress Using DMVs   Leave a comment

Here is a script gleaned from an article at http://www.mssqltips.com/tip.asp?tip=2343, with some minor modifications of mine, which very nicely allows you to monitor SQL backup or restore progress, including estimated completion times, based on data from system DMVs. This script works for SQL 2005 and above.

What is nice also is that it not only reports on native SQL backups and restores, but also third-party backups and restores performed by tools such as Quest SQL LiteSpeed or the Simpana CommVault SQL backup agent.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--Display current backup/restore progress using DMVs.
--SQL 2005 and above
--From http://www.mssqltips.com/tip.asp?tip=2343

USE master

SELECT
	session_id as SPID,
	CONVERT(VARCHAR(50),start_time,100) AS start_time,
	percent_complete,
	CONVERT(VARCHAR(50),dateadd(second,estimated_completion_time/1000, getdate()),100) as estimated_completion_time,
	command, a.text AS Query
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%' OR r.command LIKE 'RESTORE%'

Posted June 20, 2011 by Norm Enger in Microsoft SQL Server

Posted June 20, 2011 by Norm Enger in Microsoft SQL Server

Using Excel 2007 and Excel 2010 to Analyze Perfmon Captures in the Form of .csv Comma-Delimited Counter Logs   7 comments

Here is an explanation of how to easily use Excel 2007 and Excel 2010 to analyze performance captures saved to counter logs via Windows’ built-in perfmon utility. This article assumes that you have already collected whatever SQL or Windows or other performance counters you wish to analyze, and that you have captured them to counter logs in the form of one or more comma-delimited .csv files.

We will look at how to easily open a .csv file, apply a few formatting changes, and use the very handy Pivot Chart functionality built into Excel to graph your counters. Finally, we will demonstrate how to use date filters to drill deeper into specific time ranges, in order to view the graph data in greater detail.

The screen shots and steps below are for Excel 2007, but they should also work for Excel 2010.

Note that in the process below, Excel may not by default select the line chart type you prefer, for example one with data points displayed. If this is the case, right-click on an open area of the Pivot Table chart and select “Change Chart Type.” Select the left-most line chart type (the most basic line chart), and click the option at the bottom to make that your default type. See Excel help for more info.

Let’s get started.

1. Double-click on the .csv counter log file. The .csv extension should already be associated, so the file will automatically open in Excel.

image

2. Make some formatting changes.

a. Change the text in the A1 cell from “(PDH-CSV 4.0…” to simply “DateTime.”

image

image

b. Remove row 2 by right clicking on the row number –> “Delete.” This row, containing the first row of data, is typically a junk row.

image

c.  Highlight the entire “A” column by clicking on the column header, then right-click anywhere in the column –> Format Cells…

image

d. On the Number tab, select the “Date” category and the “3/14/01 1:30 PM” type, and click OK.

image

e. Press Ctrl + Home to get the focus back on the A1 cell.

image

f. These steps are repetitive for every .csv file you will analyze, so I recommend that you automate steps a. through e. using an Excel macro, by having Excel record your actions, and then you will assign a keyboard shortcut to your macro. Please refer to the Excel help for how to do this.

Note that I have found using the Excel macro functionality for steps beyond this step unworkable.

3. Create your Pivot Chart

a. Switch to the “Insert” ribbon, click on the down-arrow on the “Pivot Table” button, and select “Pivot Chart.”

image

b. The Pivot Chart dialog “auto-magically” selects the correct Table/Range of data to analyze. Very nice functionality! Click OK.

image

c. In the PivotTable Field List box, click and drag “DateTime” down to the “Axis Fields (Categories)” pane below.

image

d. In this example, we are going to first analyze the total processor time. In the PivotTable Field List box, scroll down and find the “Processor(_Total)\% Processor Time” counter, and click and drag it to the “Values” pane below.

image

e. Notice that in this instance, Excel incorrectly guessed that I want to summarize the data by “Count of….” What I actually want is for Excel to summarize the data by “Sum of….” Sometimes Excel correctly guesses this, sometimes not.

To correct this, right-click on the the Excel header cell labeled “Count of \\mySQLInstance\Processor(_Total)\% Processor Time” –> “Summarize Data By” –> and select “Sum.”

You may find this step necessary for other counters that you want to graph later as well.

image    

4. Format and size the graph to your liking.

a. You will now have a basic graph that looks something like this.

image

b. For this chart, I will change the Max value shown on the left axis from “120” to “100.” Right-click on the left axis area –> Format Axis. Change the “Maximum” option under “Axis Options” from “Auto” to “Fixed” and type in “100.” Then click Close.

image

image

c. Edit the “Title” area text, which currently reads “Total,” to your liking. I will insert the text “\\mySQLInstance\Processor(_Total)\% Processor Time”

d. I will also click on the legend box on the right and delete it.

e. Here is what my final graph will look like.

image

f. You can click on any open space within the chart to select the whole chart, then press Ctrl + C to copy, then paste the chart image into an email or Paint or your favorite editor.

g. Once you have saved the image of the chart, in the PivotTable Field List box, you can uncheck the “Processor(_Total)\% Processor Time” counter, then drag a different counter down into the Values pane.

h. You may repeat the process for as many different counters as you may wish to graph and analyze, reusing the same chart area.

5. Drilling deeper into specific time ranges.

a. Looking at the “Processor(_Total)\% Processor Time” chart I already created, I notice there was a CPU spike around the hour of 5:30 a.m. to 6:30 a.m. I want to investigate that time period more closely, and “zoom” the graph in, to that time period, so I can see in more detail what was happening during that hour.

b. In the PivotChart Filter Pane, click on the down-arrow next to “DateTime.”

image

c. Go to “Date Filters” –> and select “Between….”

image

d. Type in the beginning and ending dates/times in the format “m/d/yy h:mm am/pm” and click OK.

image

e. Your chart will now be “zoomed in” to the hour of 5:30 a.m. to 6:30 a.m. and will look something like this.

image

There are other features you may want to experiment with, such as adding trend lines, and making other changes to the display of the left axis area.

Happy charting!

Posted May 30, 2011 by Norm Enger in Excel, Microsoft SQL Server, Performance Monitoring

A PowerShell Script to Help You in Cleaning Up Old or Unused Files in Your SQL Data File Directories   Leave a comment

Here is a PowerShell script to list non-locked files in SQL Data directories as possible candidates to delete, in order to free up space. This can be useful for identifying old files that, for whatever reason, were not deleted and may be wasting disk space.

Note: before deleting any file, make sure it is not needed. For example, if a database is offline, its data files will be closed (not locked) and thus show up on this report, but you may not want to delete the files in case someone should need to bring the database back online again.

There are two modifiable sections in the script that allow you to prevent files from being evaluated based on either the file extension or the file name.

The script works by simply looping through files in the directory that you pass in as a parameter, and attempts to open the first line of each file. If a file is locked (as in a live SQL data or transaction log file), the attempt to open it and read the first line will fail, and the file will not be reported on. If the attempt to open and read the first line is successful, then the file will be reported on as a candidate for deletion.

Being the paranoid, DBA-type, I have written the script so that it will not perform the actual deletion of any files. Again I recommend that a human being look at each listed file and make the final decision of whether to delete.

I like to run the script from a PowerShell 2.0 ISE window. The example execution in the script assumes you save this script to your local system as “C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1.” Please follow the directions within the comments of the script for a sample execution.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

param([string]$theDir="")

#Norm Enger - 4/30/2011 - PowerShell script to list non-locked files in SQL Data directories
#as possible candidates to delete, in order to free up space. Note: before deleting any
#file, make sure it is not needed. For example, if a database is offline, its data file
#will be closed (not locked) and thus show up on this report, but you would not want to delete
#the file in case someone should need to bring the database back online again.

#To run, open a Powershell window and run a command like the following (starting with
#the amperstand "&" character)...
#& "C:\PowerShell Scripts\UnusedFiles\UnusedFiles.PS1" -theDir "C:\mySQLDirectory\Data"

#Output will be similar to the following:
#C:\mySQLDirectory\Data\New Text Document.txt (10/8/2010 4:46:33 PM)

#Array of extensions to exclude from report (change as needed)...
$arrExcludeExtensions = ".preg",".old",".cer"

#Array of file names to exclude from report (change as needed)...
$arrExcludeFileNames = "distmdl.mdf","distmdl.ldf", `
	"mssqlsystemresource.mdf","mssqlsystemresource.ldf"

#Sets the folder location
Set-Location $theDir

foreach($file in get-childitem | Sort-Object $_.Name `
	| Where-Object {$arrExcludeExtensions -notcontains $_.Extension `
	-and $arrExcludeFileNames -notcontains $_.Name})
{
#Get-Content returns error if the file is in use/locked
#We limit lines to try to read at 1 using TotalCount...
$content = Get-Content $file -TotalCount 1 -ErrorAction silentlycontinue
	if($?){
		$file.FullName.ToString() + `
		" (" + $file.LastWriteTime.ToString() + ")"
	}
	else{} #write out nothing
}

Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell

Posted April 30, 2011 by Norm Enger in Microsoft SQL Server, Powershell

An Exceptions-Based Report for SQL Backups – Part 2   Leave a comment

As promised, here is part 2 of of the blog “An Exceptions-Based Report for SQL Backups” (click HERE for the first blog).

Using the attached “Backups Exception Report.rdl” file as an example (click HERE to download), you can create a nifty report based on the stored procedure in the first post. Click HERE to see an example in .pdf format of what the report looks like when rendered. The report .RDL file is in SSRS 2005 format, so you can easily use it with SSRS 2005 and above.

One of the most useful – well fantastic, really! – features of this report and the accompanying stored procedure is that records are shown only for SQL backups that are missing. Potential exceptions are denoted in red and bolded. If there are no missing backups for the SQL instance, a message is displayed stating, “No exceptions were noted for this server.” In this way you have a quick and reliable way to identify when your backups are being missed, without having to wade through the vast majority of backups that are not missing.

You will also notice that the report displays records for databases that are in a status other than “ONLINE” (e.g. “OFFLINE” status), with the last backups of each type displayed (assuming one of the last backups ages over the number of prescribed days). This will be a handy visual reminder of any databases in an unusual status.

To use the example .RDL file, load it into an SSRS project in BIDS and open it. When it first opens, there may be an error displayed. Click to ignore it as it is just trying to connect the data source to a dummy SQL server name which I have embedded in the report. Also make sure you deploy the stored procedure from the first post to the master database of the instance you will be running this against.

Once the report is open in BIDS, go to the data sources tab and click to edit the data set. Within that dialog box, click to edit the data source and change the name of it, and the connection properties, to reflect the name of your SQL instance, then click OK. Then make sure the query type is set to Stored Procedure and in the query text field, paste the name of the procedure, rpt_SQL_Backups_Report. Click on the parameters tab and make sure there is a parameter named “@type” (don’t include the double quotes), and in the value column for that, make sure it says:

=”all_exceptions”

…(include the double quotes)

When you click OK, another error may be generated stating it cannot connect to a #TEMP table. This is expected. Click OK, then click the “Refresh Fields” button on the toolbar, and when it prompts you to enter a value for “@type,” enter the string “all_exceptions” (DON’T include the double quotes).

If you made a change to the name of the data set, you will need to point the table on the layout tab to use the new data set name. That is all! You are ready to start using this report.

Another enhancement, which I have not demonstrated here, is to include data sets for multiple SQL instances on the same report, and wire them up to separate tables on the layout tab (copying and pasting from the first table to easily create clones).

Happy reporting!

Posted March 25, 2011 by Norm Enger in Microsoft SQL Server

Posted March 25, 2011 by Norm Enger in Microsoft SQL Server

An Exceptions-Based Report for SQL Backups   Leave a comment

UPDATE 3/26/2011: If you used an earlier version of the stored procedure, I have made a couple of minor corrections to the procedure below, including adding logic for including, in the all_exceptions implementation, dbs in the BULK_LOGGED recovery model, which were omitted before.

Here is a stored procedure that reports on SQL backups that are missing, based on the number of days since the last backups of each type, for each SQL database on an instance. This stored procedure works with all versions of SQL Server, from SQL 2000 and above.

In particular, calling the procedure with the “all_exceptions” parameter will show records for any databases in the SQL instance that have not had a Transaction Log backup in over 1 day (if in the FULL or BULK_LOGGED recovery models), or have not had a Full backup in over 7 days, or have not had a Differential backup in over 1 day. If there has never been a backup, this is indicated in the results by returning “999″ in the  “Days_Since_Last_Full,” “Days_Since_Last_TLog,” or “Days_Since_Last_Diff” columns. Calling with the “all_exceptions” parameter returns results sorted by recovery model, then database name.

In a follow-up post (click HERE) I will include a SQL Server Reporting Services report example which integrates nicely with this stored procedure. The “999″ result is used in some of the formatting expressions in the SSRS report, so if you intend to use this report, you should not modify those parts of the procedure.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

USE [master]
GO

CREATE PROC [dbo].[rpt_SQL_Backups_Report]

	@type VARCHAR(50) = 'all_exceptions'

AS

DECLARE @srvrName VARCHAR(500)
SELECT @srvrName = @@SERVERNAME

/*

	Created by Norm Enger 2/28/2011, based in part on a script at:

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=715

	Shows status, recovery models, last full backup dates, last tlog backup dates,
	last diff backup dates, and days lapsed since each of those, for all dbs on a server.
	Works for SQL 2000 and above.

	The "all_alpha" sorts by db name.

	The "all_recovery_alpha" sorts by recovery model, then db name.

	The "all_exceptions" shows only ones that have TLog backups aged over 1 day,
	Full backups aged over 7 days, or Diff backups aged over 1 day, or any type
	that has never have had a backup (indicated by returning "999" in the
	"Days_Since_Last_Full, "Days_Since_Last_TLog", or "Days_Since_Last_Diff"
	results; and sorts by recovery model, then db name.

	Sample executions:
	EXEC rpt_SQL_Backups_Report 'all_exceptions'
	EXEC rpt_SQL_Backups_Report 'all_recovery_alpha'
	EXEC rpt_SQL_Backups_Report 'all_alpha'

*/

SET NOCOUNT ON

CREATE TABLE #TEMP
(
	DBName VARCHAR(500),
	[Recovery] VARCHAR(500),
	[DBStatus] VARCHAR(500)
)

DECLARE
	@dbname VARCHAR(500),
	@STR VARCHAR (8000)

DECLARE DBRECOVERYMODELGENERATOR_CURSOR CURSOR FOR
	SELECT name FROM sysdatabases
	WHERE category IN ('0', '1','16')
	ORDER BY name

OPEN DBRECOVERYMODELGENERATOR_CURSOR

FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname WHILE (@@FETCH_STATUS <> -1)

BEGIN
	IF (@@FETCH_STATUS <> -2)
	BEGIN
		SELECT @STR = 'SELECT ''' + @dbname + ''' AS DBName, CAST(DATABASEPROPERTYEX ('''+ @dbname + ''', ''recovery'') AS VARCHAR(500)) AS Recovery, CAST(DATABASEPROPERTYEX (''' + @dbname + ''', ''STATUS'') AS VARCHAR(500)) + CAST(CASE WHEN DATABASEPROPERTYEX(''' + @dbname + ''', ''IsInStandBy'') = 0 THEN '''' ELSE '' (Standby / Read-Only)'' END AS VARCHAR(500)) AS DBStatus'
		INSERT INTO #TEMP
		EXEC (@STR)
	END 

	FETCH NEXT FROM DBRECOVERYMODELGENERATOR_CURSOR INTO @dbname
END

CLOSE DBRECOVERYMODELGENERATOR_CURSOR

DEALLOCATE DBRECOVERYMODELGENERATOR_CURSOR

IF @type = 'all_alpha'
BEGIN
	SELECT
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	T.[Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	U.[Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	V.[Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	ORDER BY DBName
END

IF @type = 'all_recovery_alpha'
BEGIN
	SELECT
	ServerName = @srvrName,
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	ORDER BY [Recovery], DBName
END

IF @type = 'all_exceptions'
BEGIN
	SELECT
	ServerName = @srvrName,
	a.[DBName],
	a.[Recovery],
	DBStatus =
	CASE
		WHEN a.[DBStatus] = 'ONLINE' THEN a.[DBStatus]
		ELSE '***' + a.[DBStatus] + '***'
	END,
	CAST(T.[Last_Full_Backup] AS varchar(50)) + ' to ' + T2.physical_device_name AS Last_Full_Backup,
	ISNULL(T.[Days_Since_Last_Full],999) AS [Days_Since_Last_Full],
	CAST(U.[Last__TLog_Backup] AS varchar(50)) + ' to ' + U2.physical_device_name AS Last__TLog_Backup,
	ISNULL(U.[Days_Since_Last_TLog],999) AS [Days_Since_Last_TLog],
	CAST(V.[Last__Diff_Backup] AS varchar(50)) + ' to ' + V2.physical_device_name AS Last__Diff_Backup,
	ISNULL(V.[Days_Since_Last_Diff],999) AS [Days_Since_Last_Diff]

	FROM #TEMP a

	LEFT JOIN 

	-- Backup of data
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Full_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Full
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'D'
	GROUP BY database_name
	--ORDER BY 3 DESC
	) T

	ON a.[DBName] = T.[database_name]

	LEFT JOIN

	--Get the device or filename the last Full backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) T2

	ON T.database_name = T2.database_name
	AND T.backup_finish_date = T2.backup_finish_date

	LEFT JOIN

	-- Find the backup of Transaction Log files
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__TLog_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_TLog
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'L'
	GROUP BY database_name
	) U

	ON a.[DBName] = U.[database_name]

	LEFT JOIN

	--Get the device or filename the last TLog backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) U2

	ON U.database_name = U2.database_name
	AND U.backup_finish_date = U2.backup_finish_date

	LEFT JOIN

	-- Find the differential backups
	(SELECT database_name, MAX(backup_finish_date) AS backup_finish_date,
		CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last__Diff_Backup,
		DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last_Diff
	FROM msdb.dbo.backupset WITH (NOLOCK)
	WHERE type = 'I'
	GROUP BY database_name
	) V

	ON a.[DBName] = V.[database_name]

	LEFT JOIN

	--Get the device or filename the last diff backup was made to
	(SELECT bs.database_name, bs.backup_finish_date, mf.physical_device_name
	FROM msdb.dbo.backupset bs WITH (NOLOCK)

	INNER JOIN [msdb].[dbo].[backupmediafamily] mf WITH (NOLOCK)
	ON bs.media_set_id = mf.media_set_id
	) V2

	ON V.database_name = V2.database_name
	AND V.backup_finish_date = V2.backup_finish_date

	WHERE
		(
			(
			T.[Days_Since_Last_Full] > 7
			)
			OR
			(
			U.[Days_Since_Last_TLog] > 1
			AND a.[Recovery] IN ('FULL','BULK_LOGGED')
			)
			OR
			(
			T.[Days_Since_Last_Full] IS NULL
			)
			OR
			(
			U.[Days_Since_Last_TLog] IS NULL
			AND a.[Recovery] IN ('FULL','BULK_LOGGED')
			)
			OR
			(
			a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind')
			AND
			a.[DBName] NOT LIKE 'adventurework%'
			AND
			V.[Days_Since_Last_Diff] > 1
			)
			OR
			(
			a.[DBName] NOT IN ('tempdb', 'master', 'model', 'msdb', 'pubs', 'northwind')
			AND
			a.[DBName] NOT LIKE 'adventurework%'
			AND
			V.[Days_Since_Last_Diff] IS NULL
			)
		)
		AND
		a.[DBName] NOT IN ('tempdb', 'model', 'pubs', 'northwind')
		AND
		a.[DBName] NOT LIKE 'adventurework%'

	ORDER BY [Recovery], DBName
END

DROP TABLE #TEMP

Posted February 28, 2011 by Norm Enger in Microsoft SQL Server

Posted February 28, 2011 by Norm Enger in Microsoft SQL Server

Follow

Get every new post delivered to your Inbox.