January 25, 2005 at 2:13 pm
Does anyone have any script that would give me database size, unallocated space, reserved space, data space, index size and unused space practically -whatever we see in sp_spaceused without any parameter. any help will be greatly appreciated
TIA
January 25, 2005 at 2:33 pm
How about this ?
http://www.sqlservercentral.com/scripts/contributions/1294.asp
Just aggregate out of the final temp table instead of reporting by individual table.
January 25, 2005 at 2:34 pm
Well, you always can have a look at the source code of sp_spaceused to see what it does and modify it for your needs. However, I would first search the script section here. It's likely you'll find something useful already existing.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 25, 2005 at 2:48 pm
Thanks but i need to run thru every database on a server. any hints on that?
January 25, 2005 at 3:30 pm
If this is for your own maintenance, I'd say there is not wrong in using a cursor.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 25, 2005 at 8:36 pm
Hi, hope I am not offending anyone by covering old ground. As previously mentioned, I looked at sp_spaceused and some other stored procedures and created my own. I intend now to modify it to give me the free space within each mdf and ldf.
Hope below is of use.
Rgds
Derek
set nocount on
declare @filename varchar (100)
declare @sqlcode nvarchar (600)
declare @dbname varchar(25)
declare @dbstatus int
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @recovery_model varchar(10)
declare c1 cursor for
select name, status from master.dbo.sysdatabases (nolock)
select @bytesperpage = low
from master.dbo.spt_values (nolock)
where number = 1
and type = 'E'
select @pagesperMB = 1048576 / @bytesperpage
open c1
fetch c1 into @dbname, @dbstatus
while @@fetch_status >= 0
begin
set @dbsize = 0
set @logsize = 0
If @dbname = 'XXXXX'
Begin
select @dbsize = sum(convert(dec(15),size))
from xxxxx.dbo.sysfiles (nolock)
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from xxxxx.dbo.sysfiles (nolock)
where (status & 64 <> 0)
End
If @dbname = 'distribution'
Begin
select @dbsize = sum(convert(dec(15),size))
from Distribution.dbo.sysfiles (nolock)
where (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))
from Distribution.dbo.sysfiles (nolock)
where (status & 64 <> 0)
End
If @dbsize > 0
Begin
If @dbstatus <> 24
Begin
Select @Recovery_Model = 'Full'
Select 'Database' = @dbname,
'Total mdf Space' =
ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB'),
'Total ldf Space' =
ltrim(str((@logsize) / @pagesperMB,15,2) + ' MB'),
'Recovery Model' = @Recovery_Model
Select ' '
End
If @dbstatus = 24
Begin
Select @Recovery_Model = 'Simple'
Select 'Database' = @dbname,
'Total mdf Space' =
ltrim(str((@dbsize) / @pagesperMB,15,2) + ' MB'),
'Total ldf Space' =
ltrim(str((@logsize) / @pagesperMB,15,2) + ' MB'),
'Recovery Model' = @Recovery_Model
Select ' '
End
End
fetch c1 into @dbname, @dbstatus
End
deallocate c1
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply