November 9, 2012 at 5:27 am
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.
May 7, 2013 at 2:47 am
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
(
SizeDECIMAL(20)
)
CREATE TABLE #tbl_GrowthData
(
DatabaseNameVARCHAR(50)
,NoSampleDaysDECIMAL(20,3)
,DataSizeMBDECIMAL(20,3)
,LogSizeMBDECIMAL(20,3)
,BackupSizeMBDECIMAL(20,3)
,TotalSpaceMBDECIMAL(20,3)
,DataGrowthDECIMAL(20,3)
,LogGrowthDECIMAL(20,3)
,GrowthPercentageDECIMAL(20,3)
)
DECLARE
@iNoSamplesbigINT
,@nMaxBackupSizeDECIMAL
,@nMinBackupSizeDECIMAL
,@nMaxLogSizeDECIMAL
,@nMinLogSizeDECIMAL
,@nMaxDataSizeDECIMAL
,@nMinDataSizeDECIMAL
,@vcDatabaseNameVARCHAR(50)
,@dtMaxBackupTimeDATETIME
,@dtMinBackupTimeDATETIME
,@iMinBackupIDbigINT
,@iMaxBackupIDbigINT
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.backupfileWHERE backup_set_id = @iMaxBackupID AND file_type = 'L')
SET @nMinLogSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMinBackupID AND file_type = 'L')
SET @nMaxDataSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE backup_set_id = @iMaxBackupID AND file_type = 'D')
SET @nMinDataSize= (SELECT ((CAST((SUM(file_size)) AS DECIMAL(20,3))) / 1048576) FROM msdb.dbo.backupfileWHERE 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
May 7, 2013 at 5:45 am
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
May 7, 2013 at 7:09 am
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.
,@vcDatabaseNameVARCHAR(50)
May 7, 2013 at 8:06 am
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')
May 7, 2013 at 9:11 am
I had a syntax error also; a database name on my system is a reserved word. Fix the issue by surrounding the database name in the dynamic sql with square brackets.
EXEC ('
INSERT INTO ##tbl_DataSize
SELECT CAST((SUM(size)) as DECIMAL(20,3)) FROM ['+@vcDatabaseName+'].dbo.sysfiles'
)
May 7, 2013 at 10:06 am
Thank you - problem solved.
(Needed also brackets for DB name: '.....FROM ['+@vcDatabaseName+'].dbo.sysfiles')
May 7, 2013 at 4:12 pm
Very nice script. Thanks.
May 8, 2013 at 8:32 pm
Very nice script.
I noticed that some of those ungodly long-named SharePoint databases were getting their names chopped off so I changed the variable and the #temp table column names to VARCHAR(128)
CREATE TABLE #tbl_GrowthData
(
DatabaseNameVARCHAR(128)
...
...
DECLARE
...
,@vcDatabaseNameVARCHAR(128)
G. Milner
January 6, 2014 at 4:54 am
Hi,
Absolutely this is a nice script.
i have a small doubt in this. Please assist.
.. Is it possible to see the file wise growth of the each DB ?.
So that we can confidently say that So and so file in the particular disk has grown this much %.
Thanks,
Arjun
February 3, 2014 at 9:16 am
Thanks for sharing. Cool script.
You also might want to limit the data collection for only 'ONLINE' databases.
SELECT [name] FROM master.dbo.sysdatabases
WHERE DATABASEPROPERTYEX(name, 'status') = 'ONLINE'
ORDER BY [name]
OPEN file_cursor
~Leon
March 8, 2016 at 12:03 pm
A nice script.
2 cents
If you run this script against SharepPoint database, please increase the following two variables.
1. DatabaseNameVARCHAR(250)
2. ,@vcDatabaseNameVARCHAR(250)
Thanks
June 13, 2019 at 6:54 pm
getting below error
Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply