Database Growth History

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Nicholas Williams » Database Growth History

24 posts, Page 2 of 3

««1 2 3 »»

Database Growth History

Rate Topic

Display Mode

Topic Options

Author

Message

SQLDCH

Posted Monday, July 26, 2010 4:03 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:19 AM
Points: 891, Visits: 2,266

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.

----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.

Post #959118

rituchaudhary.work

Posted Wednesday, November 07, 2012 7:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, December 22, 2012 7:39 PM
Points: 3, Visits: 12

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

gregory.anderson

Posted Wednesday, November 07, 2012 7:41 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:10 AM
Points: 263, Visits: 429

rituchaudhary.work (11/7/2012)

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

rituchaudhary.work

Posted Thursday, November 08, 2012 4:43 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, December 22, 2012 7:39 PM
Points: 3, Visits: 12

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

gregory.anderson

Posted Thursday, November 08, 2012 6:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 07, 2013 8:10 AM
Points: 263, Visits: 429

rituchaudhary.work (11/8/2012)

What version of SQL Server are you running?

Post #1382463

rituchaudhary.work

Posted Friday, November 09, 2012 5:27 AM

Forum Newbie

Group: General Forum Members
Last Login: Saturday, December 22, 2012 7:39 PM
Points: 3, Visits: 12

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

henrik staun poulsen

Posted Tuesday, May 07, 2013 2:46 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 1,131, Visits: 854

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

snorri 58999

Posted Tuesday, May 07, 2013 5:45 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:01 AM
Points: 2, Visits: 26

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

Ed Zann

Posted Tuesday, May 07, 2013 7:09 AM

SSC-Addicted

Group: General Forum Members
Last Login: Today @ 9:00 AM
Points: 470, Visits: 1,052

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

HildaJ

Posted Tuesday, May 07, 2013 8:06 AM

SSC Journeyman

Group: General Forum Members
Last Login: Today @ 10:36 AM
Points: 89, Visits: 262

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 »

24 posts, Page 2 of 3

««1 2 3 »»

Permissions