March 4, 2012 at 11:37 pm
Hi experts,
Is there any way to find out the database growth for the last month even if i dont have any tracking method in place?
TIA
March 5, 2012 at 1:28 am
You can use the default trace to find out about file growth. Bellow there is a code, that I've once found on the net (sorry, I just don't remember where I got it and who wrote it, so I can't give credit) and I've been using since:
--Find out details about AutoGrow in your DB
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,
CONVERT(INT, EventClass) AS EventClass,
DatabaseName,
Filename,
( Duration / 1000 ) AS Duration,
StartTime,
EndTime,
( IntegerData * 8.0 / 1024 ) AS ChangeInSize
FROM ::fn_trace_gettable(@base_tracefilename, DEFAULT)
WHERE EventClass >= 92
AND EventClass <= 95
AND ServerName = @@SERVERNAME
AND DatabaseName = db_name()
AND Filename = 'WriteFileNameHere' --Modify this line to write the real file name
ORDER BY StartTime DESC
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 5, 2012 at 5:03 am
Ratheesh.K.Nair (3/4/2012)
Hi experts,Is there any way to find out the database growth for the last month even if i dont have any tracking method in place?
TIA
Backup history is stored in MSDB database. Check the backup_size column in BackupSet table for the database you are looking for. Compare today's backup size to 30 days old backup size.
March 5, 2012 at 8:56 am
.
March 6, 2012 at 10:13 am
Divine Flame (3/5/2012)
Backup history is stored in MSDB database. Check the backup_size column in BackupSet table for the database you are looking for. Compare today's backup size to 30 days old backup size.
That's what I do. There are some scripts you can find for this.
March 7, 2012 at 12:55 am
I posted this a long time ago and still use it quite often!
http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30901/
March 8, 2012 at 6:47 am
Here's my script that uses the backup sizes. Checks every two weeks (1st, 8th, 15th, and 28th). Does pct change from previous backup size, and overall avg pct change for whole list.
/* =========================================================================================== */
/* Get Size growth of database by tracking the backup size */
/* =========================================================================================== */
DECLARE @cmd varchar(4000)
DECLARE @recid int
DECLARE @busizedecimal(10,2)
DECLARE @prevbusizedecimal(10,2)
DECLARE @pctchange decimal(10,2)
DECLARE @dbname varchar(128)
SET @dbname = 'mydatabase'-- SET THE DATABASE NAME HERE
CREATE TABLE #dummybackupsizes
(
RecIDintIDENTITY,
DatabaseNamevarchar(128),
StartDatedatetime,
FinishDatedatetime,
BackupSizeMBdecimal(10,2),
PctChangeFromPrevdecimal(10,2)
)
INSERT INTO #dummybackupsizes
SELECT CONVERT(varchar(128),S.database_name) AS DatabaseName, S.backup_start_date AS StartDate, S.backup_finish_date AS FinishDate, CONVERT(decimal(10,2),S.backup_size/1024.0/1024.0) AS BackupSizeMB, CONVERT(decimal(10,2),0.0) AS PctChangeFromPrev
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = @dbname AND S.type = 'D' AND (DAY(S.backup_start_date) = 1 OR DAY(S.backup_start_date) = 8 OR DAY(S.backup_start_date) = 15 OR DAY(S.backup_start_date) = 28)
ORDER by backup_start_date DESC
DECLARE myCursorVariable CURSOR FOR
SELECT RecID, BackupSizeMB FROM #dummybackupsizes
OPEN myCursorVariable
-- Loop through all the files for the database
FETCH NEXT FROM myCursorVariable INTO @recid, @busize
WHILE @@FETCH_STATUS = 0
BEGIN
SET @prevbusize = (SELECT TOP 1 BackupSizeMB FROM #dummybackupsizes WHERE RecID > @recid ORDER BY RecID)
if @prevbusize = 0.0 OR @prevbusize IS NULL
SET @pctchange = 0.0
ELSE
SET @pctchange = ((@busize - @prevbusize)/@busize) * 100-- 1.45 = 1.45% change
UPDATE #dummybackupsizes SET PctChangeFromPrev = @pctchange WHERE RecID = @recid
FETCH NEXT FROM myCursorVariable INTO @recid, @busize
END
CLOSE myCursorVariable
DEALLOCATE myCursorVariable
SELECT * FROM #dummybackupsizes ORDER BY RecID
--Do not include the last record, since there is no "Previous" record
SELECT AVG(PctChangeFromPrev) AS AvgPctChange FROM #dummybackupsizes WHERE RecID <> (SELECT MAX(RecID) FROM #dummybackupsizes)
DROP TABLE #dummybackupsizes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply