October 24, 2007 at 2:34 am
Hi Guys,
This is my first post to the forums.
What I want to achieve is to get the details of the space used per database?
I thought that by putting my query (included below into a stored procedure and then calling it with Sp_msforeachdb would have the desired effects.
But unfortunately it returns the same results for all databases.
I would also like to findout if I could set the query up so that it can gather the information from all my servers (about 24 servers).
I need this information so that I can findout what the growth trend is across all my servers and if I might be running out of space
1.) Sp_msforeachdb "SingleLineSpaceUsed"
The code below was found on the web (unfortunately I can't remember where and who to credit). I only modified the info to make it into a stored proc
2.)CREATE PROCEDURE [dbo].[SingleLineSpaceUsed]
AS
declare @reserved dec(15,0)
declare @data1 dec(15,0)
declare @data2 dec(15,0)
declare @indexp dec(15,0)
declare @unused dec(15,0)
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @databasename varchar(30)
select @databasename = db_name()
DBCC UPDATEUSAGE (@databasename)
select @dbsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size)) from dbo.sysfiles where (status & 64 <> 0)
select @bytesperpage = low from master.dbo.spt_values where number = 1 and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
select @reserved = sum(reserved) from sysindexes where indid in (0, 1, 255)
select @data1 = sum(dpages) from sysindexes where indid < 2
select @data2 = sum(used) from sysindexes where indid = 255
select @indexp = sum(used) from sysindexes where indid in (0, 1, 255)
select @unused = sum(reserved) - sum(used) from sysindexes where indid in (0, 1, 255)
select @databasename as 'DB Name',
ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + ' MB') as 'DB Size',
ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB') as 'DB Data Size',
ltrim(str((@dbsize - (select sum(convert(dec(15),reserved)) from sysindexes where indid in (0, 1, 255))) / @pagesperMB,15,2)+ ' MB') as 'DB Space Available',
ltrim(str((@reserved*8)/1024) + ' MB') as Reserved ,
ltrim(str(((@data1 + @data2)*8)/1024) + ' MB') as Data,
ltrim(str(((@indexp - (@data1+@data2))*8)/1024) + ' MB') as Indexes ,
ltrim(str((@unused*8)/1024) + ' MB') as 'Unused'
GO
October 24, 2007 at 6:45 am
Since you can't code 'USE @Dbname' you need to do something like this:
sp_msforeachdb 'USE ?;exec master.dbo.SingleLineSpaceUsed'
But when you do this, it still runs from within the master db.
You could modify the procedure like this:
ALTER SingLineSpaceUsed
AS
sp_msforeachdb 'USE ?
declare @reserved dec(15,0)...'
That works great on my machine.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply