Database Growth

Question

Database Growth

VastSQL

SSC-Dedicated

Points: 36097
More actions
March 4, 2012 at 11:37 pm

#391720

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

Divine Flame SSCoach Points: 15943 More actions · Answer 2

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.

Sujeet Singh

matt.newman Hall of Fame Points: 3910 More actions · Answer 3

matt.newman

Hall of Fame

Points: 3910

March 5, 2012 at 8:56 am

#1454847

.

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

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.

henriettewhite Mr or Mrs. 500 Points: 521 More actions · Answer 5

I posted this a long time ago and still use it quite often!

http://www.sqlservercentral.com/scripts/Maintenance+and+Management/30901/

vikingDBA SSCertifiable Points: 5356 More actions · Answer 6

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