September 18, 2003 at 9:46 am
I was wondering if there was a stored procedure that could be run to calculate the total size of each or all the databases by using the actual DATA(.MDF), Transaction(.LDF) files size. This comes useful when someone does not have direct access to those files to simply be able to view the size.
September 18, 2003 at 9:52 am
Do you want them split out? sp_helpdb gives you the total size of the two.
sp_helpfile
gives each for the current database. Need to run this in the context of all dbs to get the totals.
Steve Jones
http://www.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://www.sqlservercentral.com/bestof/
September 18, 2003 at 10:02 am
select sum(size) from master.dbo.sysaltfiles
-or- for $5 more you can have the database names.
select db_name(dbid), sum(size) from master.dbo.sysaltfiles group by dbid
To add on to Steve's, try this: exec sp_msforeachdb '?.dbo.sp_helpfile'
Edited by - spongemagnet on 09/18/2003 10:07:24 AM
-Ken
September 19, 2003 at 5:33 am
Hi,
Just try follwoing usp. U will get all DB sizes in a server.
CREATE PROCEDURE Usp_FindAllDBSizes
AS
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @counter1 SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @size INT
DECLARE @size1 DECIMAL(15,2)
SET @size1=0.0
SELECT @counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @counter > 0
BEGIN
SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
TRUNCATE TABLE sizeinfo
EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES')
SELECT @counter1=MAX(fileid) FROM sizeinfo
WHILE @counter1>0
BEGIN
SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1
SET @size1=@size1+@size
SET @counter1=@counter1-1
END
SET @counter=@counter-1
SELECT UPPER(@dbname) AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
SET @size1=0.0
END
SET NOCOUNT OFF
madhusudannaidugundapaneni
Madhu
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply