January 27, 2014 at 3:11 pm
I would like to combine output from these two queries...there data is completely different..any thoughts?
/*
This should give you following:
i)Total space reserved for each file.
ii)Free space on each file.
iii)Actual space used for each file.
*/
DECLARE @DBInfo TABLE
(
ServerName VARCHAR(100),
DatabaseName VARCHAR(100),
TotalFileSizeMB INT,
LogicalFileName SYSNAME,
PhysicalFileName NVARCHAR(520),
Status SYSNAME,
Updateability SYSNAME,
RecoveryMode SYSNAME,
FreeSpaceMB INT,
FreeSpacePct VARCHAR(7),
FreeSpacePages INT,
PollDate DATETIME
)
DECLARE @command VARCHAR(5000)
SELECT @command = 'Use [' + '?' + '] SELECT
@@servername as ServerName,
' + '''' + '?' + ''''
+ ' AS DatabaseName,
CAST(sysfiles.size/128.0 AS int) AS FileSize,
sysfiles.name AS LogicalFileName, sysfiles.filename AS PhysicalFileName,
CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS RecoveryMode,
CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, '
+ '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int) AS FreeSpaceMB,
CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,
' + '''' + 'SpaceUsed' + ''''
+ ' ) AS int)/128.0)/(sysfiles.size/128.0))
AS decimal(4,2))) AS varchar(8)) + ' + ''''
+ '%' + ''''
+ ' AS FreeSpacePct,
GETDATE() as PollDate FROM dbo.sysfiles'
INSERT INTO @DBInfo
(ServerName,
DatabaseName,
TotalFileSizeMB,
LogicalFileName,
PhysicalFileName,
Status,
Updateability,
RecoveryMode,
FreeSpaceMB,
FreeSpacePct,
PollDate)
EXEC Sp_msforeachdb
@command
----- Individual file level stats
--SELECT
----ServerName,
--DatabaseName,
--LogicalFileName,
--PhysicalFileName,
----Status,
----Updateability,
--RecoveryMode,
--TotalFileSizeMB,
--FreeSpaceMB,
--( TotalFileSizeMB - FreeSpaceMB ) ActualSpaceUsed,
--FreeSpacePct
----PollDate
--FROM @DBInfo
--ORDER BY
----ServerName,
--DatabaseName
-- DB level stats
SELECT
--ServerName,
DatabaseName,
Sum(totalfilesizemb) TotalDbSize,
Sum(freespaceMB) TotalFreeSpaceMB,
--sum(ActualSpaceused) TotalActualSpaceUsed,
--LogicalFileName,
--PhysicalFileName,
--Status,
--Updateability,
--RecoveryMode,
--TotalFileSizeMB,
--FreeSpaceMB,
( Sum(TotalFileSizeMB) - Sum(FreeSpaceMB) ) TotalActualSpaceUsed
--sum(FreeSpacePct) TotalFreeSpacePct
--PollDate
FROM @DBInfo
GROUP BY databasename
ORDER BY
--ServerName,
DatabaseName
SET NOCOUNT ON
DECLARE @hr INT
DECLARE @fso INT
DECLARE @drive CHAR(1)
DECLARE @odrive INT
DECLARE @TotalSize VARCHAR(20)
DECLARE @MB NUMERIC;
SET @MB = 1048576
CREATE TABLE #drives
(
drive CHAR(1) PRIMARY KEY,
FreeSpace INT NULL,
TotalSize INT NULL
)
INSERT #drives(drive,FreeSpace) EXEC
master.dbo.xp_fixeddrives EXEC @hr=sp_OACreate
'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
EXEC Sp_oageterrorinfo
@fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD FOR
SELECT drive
FROM #drives
ORDER BY drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC @hr = Sp_oamethod
@fso,
'GetDrive',
@odrive OUT,
IF @hr <> 0
EXEC Sp_oageterrorinfo
@fso
EXEC @hr = Sp_oagetproperty
@odrive,
'TotalSize',
@TotalSize OUT
IF @hr <> 0
EXEC Sp_oageterrorinfo
@odrive
UPDATE #drives
SET TotalSize = @TotalSize / @MB
WHERE drive = @drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=Sp_oadestroy
@fso
IF @hr <> 0
EXEC Sp_oageterrorinfo
@fso
SELECT drive,
TotalSize AS 'Total(MB)',
FreeSpace AS 'Free(MB)'
FROM #drives
ORDER BY drive
DROP TABLE #drives
GO
January 29, 2014 at 8:39 am
Here is a pretty cool script: http://www.sqlservercentral.com/Forums/Topic409505-690-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply