June 15, 2009 at 1:09 pm
The following statement will show you information about your instance and the file space used. how ever the sp_MSFForEachDB produces 2 rows for each DB withing the instance. Can some one show me how to take the following information and export it to a csv file so it can be put into a pivot tables to produce KPI.
This information below will work in SQL Server 2005/2008
DECLARE @command VARCHAR(5000);
SELECT @command = 'Use ['+ '?'+ ']
Select @@servername AS ServerName,
(Select db_name() as DBNAME),
CAST(sysfiles.size /128.0 AS int) AS FileSizeMB,
sysfiles.name AS LogicalFileName,
sysfiles.filename AS PhysicalFileName,
CONVERT (sysname, DATABASEPROPERTYEX( ''?'' , ''status'' )) AS Status,
CONVERT (sysname, DATABASEPROPERTYEX( ''?'' , ''Recovery'' )) AS RecoveryMode,
convert(decimal(12,2),round(sysfiles.size/128.000,2)) as FileSizeMB,
convert(decimal(12,2),round(fileproperty(sysfiles.name,''SpaceUsed'')/128.000,2)) as SpaceUsedMB,
convert(decimal(12,2),round((sysfiles.size-fileproperty(sysfiles.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB,
CONVERT ( VARCHAR(10), GETDATE(),111) AS Date
from dbo.sysfiles'
EXEC sp_MSForEachDB @command
June 15, 2009 at 1:41 pm
Hello,
Possibly write the output to a Table (in your DBA DB) and then extract it at your leisure.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply