Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Database Growth History Expand / Collapse
Author
Message
Posted Monday, July 26, 2010 4:03 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,298, Visits: 2,781
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.
Post #959118
Posted Wednesday, November 7, 2012 7:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:54 AM
Points: 3, Visits: 34
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?

Post #1381961
Posted Wednesday, November 7, 2012 7:41 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
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.
Post #1381984
Posted Thursday, November 8, 2012 4:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:54 AM
Points: 3, Visits: 34
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..
Post #1382414
Posted Thursday, November 8, 2012 6:38 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 8, 2014 7:12 AM
Points: 263, Visits: 443
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?
Post #1382463
Posted Friday, November 9, 2012 5:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 4, 2014 11:54 AM
Points: 3, Visits: 34
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.
Post #1382978
Posted Tuesday, May 7, 2013 2:46 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 8:07 AM
Points: 1,364, Visits: 989
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 :)
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




Post #1450030
Posted Tuesday, May 7, 2013 5:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 11, 2014 3:53 AM
Points: 3, Visits: 70
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
Post #1450080
Posted Tuesday, May 7, 2013 7:09 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:03 AM
Points: 486, Visits: 1,221

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)



Post #1450120
Posted Tuesday, May 7, 2013 8:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 162, Visits: 467
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')
Post #1450160
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse