Archive for category SQL

HTML Application – SQL Differential Backup Size Prediction

You can find the full post here: http://dougzuck.com/hta

diffBackupPredictionHTA

, ,

No Comments

SQL – Differential Backup Size Prediction / Estimation

Darwin Hatheway wrote a really nice article explaining how the differential backup size can be estimated or predicted.  He gets all the credit for teaching me how to do this.  However, since he didn’t provide a complete solution, I had to do it myself. There are two implementations below.  One is a straight T-SQL script, and the other is a HTML Application (HTA) that utilizes the T-SQL script with a bit of vbscript.

T-SQL Version: SQL Differential Backup Size Prediction
HTML Application: http://dougzuck.com/hta

diffPredictionResults

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
/*SQL Differential Backup Size Prediction / Estimation - Doug Zuckerman, 2009 - http://dougzuck.com */
 
IF isNULL(object_id('tempdb.dbo.##showFileStats'), 1) <> 1
	DROP TABLE ##showFileStats
 
CREATE TABLE ##showFileStats (
	fileID INT,
	fileGroup INT,
	totalExtents BIGINT,
	usedExtents BIGINT,
	logicalFileName VARCHAR (500),	
	filePath VARCHAR (1000)
)
 
IF isNULL(object_id('tempdb.dbo.##DCM'), 1) <> 1
	DROP TABLE ##DCM
 
CREATE TABLE ##DCM (
	parentObject VARCHAR(5000),
	[object] VARCHAR(5000),
	FIELD VARCHAR (5000),
	VALUE VARCHAR (5000)
)
 
/*we need to get a list of all the files in the database.  each file needs to be looked at*/	
INSERT INTO ##showFileStats EXEC('DBCC SHOWFILESTATS with tableresults')
 
DECLARE @currentFileID INT,
	@totalExtentsOfFile BIGINT,
	@dbname VARCHAR(100),
	@SQL VARCHAR(200),
	@currentDCM BIGINT,
	@step INT
 
SET @dbname = db_name()
SET @step = 511232
 
DECLARE myCursor SCROLL CURSOR FOR
SELECT fileID, totalExtents 
FROM ##showFileStats
 
OPEN myCursor
FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
 
/*look at each differential change map page in each data file of the database and put the output into ##DCM*/
WHILE @@FETCH_STATUS = 0 
BEGIN
 
	SET @currentDCM = 6
	WHILE @currentDCM <= @totalExtentsOfFile*8
	BEGIN	
		SET @SQL = 'dbcc page('+ @dbname + ', ' + CAST(@currentFileID AS VARCHAR) + ', ' + CAST(@currentDCM AS VARCHAR) + ', 3) WITH TABLERESULTS'
		INSERT INTO ##DCM EXEC (@SQL)
		SET @currentDCM = @currentDCM + @step
	END
 
	FETCH NEXT FROM myCursor INTO @currentFileID, @totalExtentsOfFile
END
CLOSE myCursor
DEALLOCATE myCursor
 
/*remove all unneeded rows from our results table*/
DELETE FROM ##DCM WHERE VALUE = 'NOT CHANGED' OR parentObject NOT LIKE 'DIFF_MAP%'
--SELECT * FROM ##DCM
 
/*sum the extentTally column*/
SELECT SUM (extentTally) AS totalChangedExtents, SUM(extentTally)/16 AS 'diffPrediction(MB)', SUM(extentTally)/16/1024 AS 'diffPrediction(GB)' 
FROM
	/*create extentTally column*/
	(SELECT extentTally =
	CASE
		WHEN secondChangedExtent > 0 THEN CAST(secondChangedExtent AS BIGINT) - CAST(firstChangedExtent AS BIGINT) + 1
		ELSE 1
	END
	FROM
		/*parse the 'field' column to give us the first and last extents of the range*/
		(SELECT (SUBSTRING(FIELD,(SELECT CHARINDEX(':', FIELD, 0))+1,(CHARINDEX(')', FIELD, 0))-(CHARINDEX(':', FIELD, 0))-1))/8 AS firstChangedExtent,
		secondChangedExtent = 
		CASE	
			WHEN CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1) > 0 THEN (SUBSTRING(FIELD,(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1)+1),(CHARINDEX(')', FIELD,CHARINDEX(')', FIELD, 0)+1))-(CHARINDEX(':', FIELD, CHARINDEX(':', FIELD, 0)+1))-1))/8
			ELSE ''
		END 
		FROM ##DCM)parsedFieldColumn)extentTallyColumn

9 Comments

HTML Application – Retrieve SP_WHO2 and the Input Buffer from a SQL Server

You can find the full post here: http://www.dougzuck.com/hta

get_sql_spwho2_and_inputbuffer

, , ,

No Comments

HTML Application – Retrieve SQL Backup History

You can find the full post here: http://www.dougzuck.com/hta

get_sql_backup_history

, ,

No Comments

HTML Application – Retrieving a graphical representation of all SQL databases and the breakdown of space used by each

You can find the full post here: http://www.dougzuck.com/hta

get_sql_db_sizes

, ,

No Comments

SQL Backup History – How to retrieve a list of the most recent database backups

The MSDB database maintains a record of all SQL backup jobs that have been executed.  Specifically you can find this information if you query MSDB..backupset.  This script joins sys.databases, so that if a database exists but has not been backed up yet, it will still be displayed in the results.  If you were to query the backupset table alone without the JOIN statement, you wouldn’t see any entries for databases on the server that have not been backed up.  Also note that this particular script assumes that you’re only interested in Full and Differential backups.  You could easily modify it to include information about Log backups.

Download the script here: SQL Backup History

I’ve also posted a HTML Application (HTA) version of this script as well, which you can see here: http://dougzuck.com/hta

OK, let’s take a look at the code:

SELECT @@servername AS server_name, s.name AS database_name, backup_type =
CASE
    WHEN b.type = 'D' THEN 'Full'
    WHEN b.type = 'I' THEN 'Diff'
END,
MAX(b.backup_start_date) AS last_backup_start_time,
DATEDIFF(d, MAX(b.backup_start_date), getdate()) AS days_since_last_backup, STATUS =
CASE
    WHEN b.type = 'D' AND DATEDIFF(d, MAX(b.backup_start_date), getdate())  2 THEN 'Possible Problem' /*if the most recent diff backup was more than 3 days ago, Possible Problem*/
    WHEN MAX(b.backup_start_date) IS NULL THEN 'No Backup Exists'
END
FROM sys.databases s LEFT JOIN msdb..backupset b
ON s.name = b.database_name
WHERE s.name <> 'master' AND s.name <> 'model' AND s.name <> 'msdb' AND s.name <> 'tempdb'
GROUP BY s.name, b.type
ORDER BY s.name

You can see in the results screenshot below that I haven’t done backups of my local databases in a long time, which is why the status column is indicating there’s a possible problem.
backup_history_query_results

No Comments