Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Growth History


Database Growth History

Author
Message
SQLDCH
SQLDCH
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1845 Visits: 3384
Thanks for the script. Found a slight problem though: with database names that have funky characters in them, especially a period, this will throw an error (as wakanni found). I placed brackets around the @vcDatabaseName in the EXEC statement:

EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM ['+@vcDatabaseName+'].dbo.sysfiles'
)

No more errors for me.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
rituchaudhary.work
rituchaudhary.work
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
Hi,
I ran the script and it was giving desired results but in some of the servres it threw the below error:
Msg 207, Level 16, State 1, Line 89
Invalid column name 'size'.

Any idea how to get rid of this?
andegre
andegre
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 458
rituchaudhary.work (11/7/2012)
Hi,
I ran the script and it was giving desired results but in some of the servres it threw the below error:
Msg 207, Level 16, State 1, Line 89
Invalid column name 'size'.

Any idea how to get rid of this?



Without doing any testing, I'm assuming the fix for this would be to throw brackets around your column name, since "size" is probably a reserved word in SQL Server.
rituchaudhary.work
rituchaudhary.work
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
Hi,
thanks for the prompt reply but no good..I ran the query after adding the brackets but still got the same error..while the query is running fine on another server of the same version and edition. I am unable to make out where the fault is..
andegre
andegre
SSC Veteran
SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)SSC Veteran (289 reputation)

Group: General Forum Members
Points: 289 Visits: 458
rituchaudhary.work (11/8/2012)
Hi,
thanks for the prompt reply but no good..I ran the query after adding the brackets but still got the same error..while the query is running fine on another server of the same version and edition. I am unable to make out where the fault is..


What version of SQL Server are you running?
rituchaudhary.work
rituchaudhary.work
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
Hi Greg,

The instance on which it is failing has the version: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

But there is another instance with the version: Microsoft SQL Server 2005 - 9.00.4035.00 (X64) Nov 24 2008 16:17:31 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) and it is running successfully on this.
henrik staun poulsen
henrik staun poulsen
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1835 Visits: 1210
hi Nicholas,

I've found a problem when sum(Size) is too big to fit into an integer.
Here is the fix:

Best regards,
Henrik


SET NOCOUNT ON
/*
Author: Nicholas Williams
Date: 3rd February 2008
Desc: Calculates Growth Info for all databases on a server that are being backed up. Relies on the backup tables, and as a result will only contain as many
days history as do the backup tables(@iNoSamples). If a database is not being backup up the results will be NULL. (For example the Tempdb)
This is a rewrite of something I did a few years ago, as I dont know where I saved the other code. bummer.
Email: Nicholas.Williams@reagola.com
*/

CREATE TABLE ##tbl_DataSize
(
Size DECIMAL(20)
)

CREATE TABLE #tbl_GrowthData
(
DatabaseName VARCHAR(50)
,NoSampleDays DECIMAL(20,3)
,DataSizeMB DECIMAL(20,3)
,LogSizeMB DECIMAL(20,3)
,BackupSizeMB DECIMAL(20,3)
,TotalSpaceMB DECIMAL(20,3)
,DataGrowth DECIMAL(20,3)
,LogGrowth DECIMAL(20,3)
,GrowthPercentage DECIMAL(20,3)
)

DECLARE
@iNoSamples bigINT
,@nMaxBackupSize DECIMAL
,@nMinBackupSize DECIMAL
,@nMaxLogSize DECIMAL
,@nMinLogSize DECIMAL
,@nMaxDataSize DECIMAL
,@nMinDataSize DECIMAL
,@vcDatabaseName VARCHAR(50)
,@dtMaxBackupTime DATETIME
,@dtMinBackupTime DATETIME
,@iMinBackupID bigINT
,@iMaxBackupID bigINT

DECLARE file_cursor CURSOR FOR
SELECT [name] FROM master.dbo.sysdatabases
ORDER BY [name]
OPEN file_cursor

FETCH NEXT FROM file_cursor
INTO @vcDatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @dtMaxBackupTime = (SELECT MAX(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D')
SET @dtMinBackupTime = (SELECT MIN(backup_finish_date)FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D')
SET @iNoSamples =
DATEDIFF
(
dd
,@dtMinBackupTime
,@dtMaxBackupTime
)

SET @nMaxBackupSize = (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)
SET @nMinBackupSize = (SELECT backup_size FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)

SET @iMaxBackupID = (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMaxBackupTime)
SET @iMinBackupID = (SELECT MAX(backup_set_id) FROM msdb.dbo.backupset WHERE database_name = @vcDatabaseName AND [type] = 'D' AND backup_finish_date = @dtMinBackupTime)
SET @nMaxLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMaxBackupID AND file_type = 'L')
SET @nMinLogSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMinBackupID AND file_type = 'L')
SET @nMaxDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMaxBackupID AND file_type = 'D')
SET @nMinDataSize = (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfile WHERE backup_set_id = @iMinBackupID AND file_type = 'D')
EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(cast(size as bigint))) as DECIMAL(20,3)) FROM '+@vcDatabaseName+'.dbo.sysfiles'
)
INSERT INTO #tbl_GrowthData
SELECT
@vcDatabaseName DatabaseName
,@iNoSamples NoSampleDays
,@nMaxDataSize
,@nMaxLogSize
,@nMaxBackupSize / 1048576
,((size * 8192) / 1048576) TotalSpaceUsed
,@nMaxDataSize - @nMinDataSize
,@nMaxLogSize - @nMinLogSize
,(((@nMaxDataSize + @nMaxLogSize) - (@nMinDataSize+ @nMinLogSize)) / (@nMinDataSize+ @nMinLogSize)) * 100.00
--growth percentage is calculated based upon the original data size, before the growth. as a result it may look a little funny, but it is accurate. or at least I think so Smile
FROM ##tbl_DataSize

TRUNCATE TABLE ##tbl_DataSize
FETCH NEXT FROM file_cursor
INTO @vcDatabaseName

END
CLOSE file_cursor
DEALLOCATE file_cursor

SELECT
*
FROM #tbl_GrowthData

DROP TABLE ##tbl_DataSize
DROP TABLE #tbl_GrowthData

SET NOCOUNT OFF



Snorri Kris
Snorri Kris
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 111
Nice script -thanks for sharing.
I get multple errors when I run it though:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Service_1d756ce176df40ff88162ccaa'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Search_Service_Application_CrawlStoreDB_50d2dd819c.dbo.sysfiles'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Search_Service_Application_DB_78718af6ae114220b5fb.dbo.sysfiles'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Search_Service_Application_PropertyStoreDB_6c687a8.dbo.sysfiles'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'Secure_Store_Service_DB_9ea28dcf81de4bfb857b8121a0.dbo.sysfiles'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

Regards,
Snorri
Ed Zann
Ed Zann
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 1391
The script database name is defined for 50 chars. Your db names are longer so you will need to increase it. You may also have to enclose the DB name in brackets.

,@vcDatabaseName VARCHAR(50)



HildaJ
HildaJ
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 571
wakanni,

If you're getting an error it might be due to having a space in the database name. Add some brackets in the code below around the database parameter value. I hope this helps.

EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM ['+@vcDatabaseName+'].dbo.sysfiles')
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search