SQL Script for Space Allocated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Script for Space Allocated

Author

Topic

dewacorp.alliances

452 Posts

Posted - 2006-10-03 : 17:02:51

Hi there

Does anyone know the script for generating Space Allocated like ine the TaskPad (Total - Used and Free for Data and Transaction Log) for each databases.

Thanks

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-10-03 : 17:07:12

Try:
exec sp_msforeachdb 'use ? exec sp_spaceused'

dewacorp.alliances

452 Posts

Posted - 2006-10-03 : 18:12:26

quote:
Originally posted by Luis Martin

Try:
exec sp_msforeachdb 'use ? exec sp_spaceused'

sp_spaceused returns combined size database as whole intead of individual DATA and TRANS LOG. Need the one that have 2 entries: Data and Trans Log.

Luis Martin
Yak Posting Veteran

54 Posts

Posted - 2006-10-03 : 18:27:48

A good start:

SET NOCOUNT ON

-- Database Size
PRINT '*** Database Size ***'

CREATE TABLE #DB_SIZE (
dbno INT IDENTITY,
dbdate DATETIME,
dbname NVARCHAR(50),
datasize FLOAT,
logsize FLOAT(53),
actual FLOAT,
filename VARCHAR(1000))

DECLARE @datasize FLOAT
DECLARE @logsize FLOAT
DECLARE @usedspace FLOAT
DECLARE @dbsize FLOAT
DECLARE @datacmd NVARCHAR (100)
DECLARE @logcmd NVARCHAR (100)
DECLARE @usedspacecmd NVARCHAR (1000)
DECLARE @dbsizecmd NVARCHAR (1000)
DECLARE @actual NVARCHAR (100)
DECLARE @dbname CHAR (25)
DECLARE @filename VARCHAR(1000)

DECLARE c1 CURSOR FOR SELECT name, filename FROM master..sysdatabases
OPEN c1
FETCH c1 INTO @dbname, @filename

WHILE @@fetch_status = 0

BEGIN
SET @datacmd='SELECT @datasize=size FROM ['+@dbname+'].dbo.sysfiles WHERE fileid=1'
EXEC sp_executesql @datacmd, N'@datasize FLOAT OUTPUT', @datasize OUTPUT
SET @datasize=ROUND(@datasize/128,0)

SET @logcmd='SELECT @logsize=size FROM ['+@dbname+'].dbo.sysfiles WHERE fileid=2'
EXEC sp_executesql @logcmd, N'@logsize FLOAT OUTPUT', @logsize OUTPUT
SET @logsize=ROUND(@logsize/128,0)

INSERT INTO #DB_SIZE (dbdate,dbname,datasize,logsize,actual,filename)
VALUES (GETDATE(),@dbname,@datasize,@logsize,@usedspace,@filename)
FETCH NEXT FROM c1 INTO @dbname, @filename
END

SELECT CAST(dbno AS VARCHAR(5)) "DBno",
CAST(dbdate AS VARCHAR(20)) "DBDate",
CAST(dbname AS VARCHAR(20)) "DBName",
CAST(datasize AS VARCHAR(10)) "datasize",
CAST(logsize AS VARCHAR(10)) "logsize",
filename
FROM #DB_SIZE

DROP TABLE #DB_SIZE

CLOSE c1
DEALLOCATE c1

dewacorp.alliances

452 Posts

Posted - 2006-10-04 : 07:49:37

Thanks for this but the size (data and log) doesn't give the breakdown of actual data and space used.

Any other system tables that I can find this info?

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources