[thelist] [TIP] Finding File Space Info for MS SQL Databases....
Anthony Baratta
anthony at baratta.com
Mon Apr 23 12:07:49 CDT 2007
<tip author="Anthony Baratta" type="MS SQL DB File Space Stored Procedure">
I found a nifty stored procedure that I've modified for my own purposes, but I wanted to post the link and the modified code for wider distribution. There is probably many ways to tighten and speed up this script, but I was in a hurry and it gets the job done for now.
The original source for the SP lives here:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84895&enterthread=y
I tweaked is so that the data returned was a bit more useful for my needs, and also added a parameter that switches the output to XML. I now have a quick and dirty dotNet Application that uses a drop down to select the SQL Instance, and then builds the connection string on the fly to the target SQL Instance, runs this SP, and formats the XML to a nice HTML layout for viewing. This gives me a quick window into the database file and log sizes with one screen. No more Enterprise Manager drill downs, and having to pull up file explorer on the target machine to see DB File Sizes.
Anyway - use as you see fit.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_GetDBFileSpaceInfo] Script Date: 04/23/2007 09:56:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetDBFileSpaceInfo]
@Format as varchar(5) = null
AS
BEGIN
SET NOCOUNT ON
Create Table #t
(
dbid int,
name varchar(64),
datafile varchar(64),
dfileLogical varchar(64),
datapages int,
datapagesUsed int,
logfile varchar(64),
lfileLogical varchar(64),
logpages int,
logpagesUsed int
)
declare @sql nvarchar(500)
Declare @param nvarchar(500)
declare @i int
declare @iOUT int
declare @j int
declare @jOUT int
Declare @dfile varchar(255)
declare @lFile varchar(255)
Declare c Cursor
Read_Only
For
SELECT dbid, name
FROM sysdatabases
Declare @dbid int
Declare @name varchar(60)
Open c
Fetch Next From c into @dbid, @name
While (@@fetch_status <> -1)
Begin
IF (@@fetch_status <> -2)
Begin
exec('
INSERT #t (dbid, name, datafile, datapages, dfilelogical)
SELECT ' + @dbid + ', ''' + @name + ''',
Reverse(left(ltrim(reverse(filename)), charindex(''\'', ltrim(reverse(filename)))-1)),
size, name
from [' + @name + ']..sysfiles
WHERE fileid = 1
')
exec ('
UPDATE #t
Set logfile = (select Reverse(left(ltrim(reverse(filename)), charindex(''\'', ltrim(reverse(filename)))-1)) from [' + @name + ']..sysfiles where fileid = 2),
logpages = (select size from [' + @name + ']..sysfiles where fileid = 2),
lfilelogical = (select name from [' + @name + ']..sysfiles where fileid = 2)
where dbid = ' + @dbid
)
select @dfile = dfileLogical, @lfile = lFileLogical from #t where dbid = @dbid
set @sql = N'use ' + @name + char(13) + 'select @iOUT= fileproperty(''' + @dfile + ''', ''spaceused''), @jOUT = fileproperty(''' + @lfile + ''', ''spaceused'')'
set @param = N'@iOUT int out, @jOUT int out'
exec sp_executesql @sql, @param, @iOUT = @i out, @jOUT = @j out
update #t
set datapagesused = @i,
logpagesused = @j
where dbid = @dbid
End
Fetch Next From c into @dbid, @name
End
close c
deallocate c
if @Format = 'XML'
select
dbid
, name dbName
, datafile
, datapages
, datapagesused
, CAST((CAST(datapages as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataTotalMb
, CAST((CAST(datapagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataUsedMb
, CAST((CAST((datapages - datapagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataFreeMb
, logfile
, logpages
, logpagesused
, CAST((CAST(logpages as decimal(20,3)) * 8) / 1024 as decimal(20,3)) logTotalMb
, CAST((CAST(logpagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) logUsedMb
, CAST((CAST((logpages - logpagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) logFreeMb
from #t as DBFileSpaceInfo
order by [name]
FOR XML AUTO, ELEMENTS
else
select
dbid
, name dbName
, datafile
, datapages
, datapagesused
, CAST((CAST(datapages as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataTotalMb
, CAST((CAST(datapagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataUsedMb
, CAST((CAST((datapages - datapagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) dataFreeMb
, logfile
, logpages
, logpagesused
, CAST((CAST(logpages as decimal(20,3)) * 8) / 1024 as decimal(20,3)) logTotalMb
, CAST((CAST(logpagesused as decimal(20,3)) * 8)/1024 as decimal(20,3)) logUsedMb
, CAST((CAST((logpages - logpagesused) as decimal(20,3)) * 8)/1024 as decimal(20,3)) logFreeMb
from #t as DBFileSpaceInfo
order by [name]
drop table #t
END
</tip>
More information about the thelist
mailing list