August 22, 2008 at 3:53 am
Hi all,
I used sp_helpdb stored procedure to obtain summary information about databases in an instance. Since I am interested in the overall size and not only in the size of individual databases, is there a way to find out the overall size? In practice I want to know how much disk space all databases are using together. I need to do this within the frame of capacity evaluation.
Thanks for your hint
Regards
Niyala
August 22, 2008 at 4:02 am
you can use sp_MSforeachdb with sp_helpdb
August 22, 2008 at 4:13 am
Thanks indeed. That's quite good, and yet I would like to know the sum of all db_size, gran sum so to say.
Regards
Niyala
August 22, 2008 at 4:20 am
You can use this script as a starter. It doesn't sum up the database size, but you can add that without too much effort.
USE [master]
GO
SET NOCOUNT ON
CREATE TABLE #helpfile (
ObsvDate smalldatetime NULL,
ServerName varchar(50) NULL,
DbName varchar(100) NULL,
FileLogicalName varchar(100) NULL,
FileID int NULL,
FileGroupID int NULL,
FilePath varchar(100) NULL,
FileGroupName varchar(50) NULL,
FileTotalSizeKB varchar(20) NULL,
FileMaxSizeSetting varchar(20) NULL,
FileGrowthSetting varchar(20) NULL,
FileUsage varchar(20) NULL,
FileTotalSizeMB varchar(30) NULL,
FileUsedSpaceMB varchar(30) NULL,
FileFreeSpaceMB varchar(30) NULL,
)
CREATE TABLE #filestats (
DbName varchar(100) NULL,
FileID int NULL,
FileGroupID int NULL,
FileTotalSizeMB varchar(30) NULL,
FileUsedSpaceMB varchar(30) NULL,
FileFreeSpaceMB varchar(30) NULL,
FileLogicalName varchar(100) NULL,
FilePath varchar(100) NULL
)
CREATE TABLE #sqlperf (
DbName varchar(100) NULL,
LogFileSizeMB DEC(19,4) NULL,
LogFileSpaceUsedpct DEC(19,4) NULL,
Status int NULL
)
--#region Put Region Description Here
INSERT #sqlperf (DbName, LogFileSizeMB, LogFileSpaceUsedpct, Status)
EXEC ( 'DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS ')
EXEC sp_MSForeachDB
--@command1 = 'USE [?]; DBCC UPDATEUSAGE(0)',
@command1 = 'USE [?];INSERT #helpfile (FileLogicalName, FileID, FilePath, FileGroupName, FileTotalSizeKB, FileMaxSizeSetting, FileGrowthSetting,FileUsage) EXEC sp_helpfile; UPDATE #helpfile SET dbname = ''?'' WHERE dbname IS NULL',
@command2 = 'USE [?];INSERT #filestats (FileID, FileGroupID, FileTotalSizeMB, FileUsedSpaceMB, FileLogicalName, FilePath) EXEC (''DBCC SHOWFILESTATS WITH NO_INFOMSGS ''); UPDATE #filestats SET dbname = ''?'' WHERE dbname IS NULL'
-- remove ANY db's that we don't care about monitoring
DELETE FROM #filestats
WHERE CHARINDEX(dbname, 'model-pubs-northwind') > 0
DELETE FROM #helpfile
WHERE CHARINDEX(dbname, 'model-pubs-northwind') > 0
DELETE FROM #sqlperf
WHERE CHARINDEX(dbname, 'model-pubs-northwind') > 0
UPDATE #filestats SET FileTotalSizeMB = CONVERT(varchar(30),ROUND(CONVERT (DECIMAL(19,3),FileTotalSizeMB)*64/1024,2)),
FileUsedSpaceMB = CONVERT(varchar(30),ROUND(CONVERT (DECIMAL(19,3),FileUsedSpaceMB)*64/1024,2))
WHERE FileFreeSpaceMB IS NULL
UPDATE #filestats
SET FileFreeSpaceMB = CONVERT(varchar(30),(CONVERT(DECIMAL(19,3),FileTotalSizeMB) - CONVERT(DECIMAL(19,3),FileUsedSpaceMB )))
WHERE FileFreeSpaceMB IS NULL
UPDATE #helpfile
SET FileGroupID = 0
WHERE FileUsage = 'log only'
UPDATE #helpfile
SET FileGroupID = b.FileGroupID,
FileTotalSizeMB = b.FileTotalSizeMB,
FileUsedSpaceMB = b.FileUsedSpaceMB,
FileFreeSpaceMB = b.FileFreeSpaceMB
FROM #helpfile a
JOIN #filestats b
ON a.FilePath = b.FilePath
AND a.FileUsage = 'data only'
UPDATE #helpfile
SET FileTotalSizeMB = CONVERT(varchar(30),ROUND(CAST(REPLACE(FileTotalSizeKB,' KB', '')AS DECIMAL(19,3))/1024,2) )
WHERE FileTotalSizeMB IS NULL
UPDATE #helpfile
SET FileUsedSpaceMB = CONVERT(varchar(30),ROUND(FileTotalSizeMB * b.LogFileSpaceUsedpct * 0.01,2)),
FileFreeSpaceMB = CONVERT(varchar(30),ROUND(FileTotalSizeMB * (100 - b.LogFileSpaceUsedpct) * 0.01,2) )
FROM #helpfile a
JOIN #sqlperf b
ON a.dbname = b.dbname
AND a.FileUsage = 'log only'
DECLARE @obsvdate datetime
SET @obsvdate = GETDATE()
UPDATE #helpfile
SET ObsvDate = @obsvdate
WHERE ObsvDate IS NULL
-- 97 : 122 = a TO z
-- 65 : 90 = A TO Z
UPDATE #helpfile
SET FilePath = STUFF (FilePath , 1, 1, UPPER(LEFT(FilePath,1)) )
WHERE UNICODE(LEFT(FilePath,1)) BETWEEN 97 AND 122
UPDATE #helpfile
SET servername = @@SERVERNAME
WHERE ServerName IS NULL
--#endregion
--Insert Into AFMDB05.SQLH2Repository.dbo.hs_databasefiles
SELECT ObsvDate,
ServerName,
DbName,
FileLogicalName,
FilePath,
CONVERT(decimal(18,2),FileTotalSizeMB)AS TotalFileSizeMB,
CONVERT(decimal(18,2),FileUsedSpaceMB) AS UsedSpaceMB,
CONVERT(decimal(18,2),FileFreeSpaceMB) AS FreeSpaceMB
FROM #helpfile
--WHERE filepath like 'E:%'
ORDER BY ServerName, DbName,FileId
DROP TABLE #helpfile
DROP TABLE #filestats
DROP TABLE #sqlperf
SET NOCOUNT OFF
[font="Verdana"]Markus Bohse[/font]
August 22, 2008 at 5:09 am
Thanks indeed. I will try to work myself into the script and try it.
Regards
Niyala
August 22, 2008 at 5:12 am
you can also use this query ....
select CAST(CAST(sum(size) * 8192 / 1048576 AS DECIMAL(10, 2) ) AS VARCHAR(20)) + 'MB' from sys.master_files where database_id = DB_ID('Global_DB')
August 22, 2008 at 5:24 am
Hadrian,
alternate way is..
create table #tbsize (dbname sysname, dbsize dec(15))
declare @b-2 numeric(12,2) select @b-2 = low from master.dbo.spt_values where number = 1 and type = 'E'
exec sp_MSforeachdb ' use ? insert into #tbsize select db_name(), sum(convert(dec(15), size)) from dbo.sysfiles'
select dbname, dbsize/(1048576/@b) from #tbsize drop table #tbsize
Regards
Manoj
August 22, 2008 at 5:45 am
I tried the following, nonetheless it returns NULL
you can also use this query ....
select CAST(CAST(sum(size) * 8192 / 1048576 AS DECIMAL(10, 2) ) AS VARCHAR(20)) + 'MB' from sys.master_files where database_id = DB_ID('Global_DB')
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply