October 11, 2007 at 7:06 am
Comments posted to this topic are about the item Drive Space Usage/Free Monitoring Script
November 26, 2008 at 8:31 am
-- some small fixes...
-- Drive Space Usage/Free Monitoring Script
-- By Sriram Ramamoorthy, 2006/10/26
BEGIN
-- 20081125 fix @DBName embedded spaces
-- 20081125 fix LogFileUsed is %, not MB
-- 20081125 fix Arithmetic overflow error converting expression to data type int. -- do or [growth] too?
-- 20081125 add DatabasePropertyEx()
-- 20081125 add FreeSpace amounts
-- 20081125 add SET NOCOUNT OFF
SET NOCOUNT ON
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo'))
BEGIN
DROP TABLE #DBFileInfo
END
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))
BEGIN
DROP TABLE #LogSizeStats
END
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))
BEGIN
DROP TABLE #DataFileStats
END
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives'))
BEGIN
DROP TABLE #FixedDrives
END
CREATE TABLE #FixedDrives
(DriveLetterVARCHAR(10),
MB_FreeDEC(20,2))
CREATE TABLE #DataFileStats
(DBNameVARCHAR(255),
DBIdINT,
FileId TINYINT,
[FileGroup] TINYINT,
TotalExtents DEC(20,2),
UsedExtents DEC(20,2),
[Name] VARCHAR(255),
[FileName] VARCHAR(400))
CREATE TABLE #LogSizeStats -- DBCC SQLPERF -- Provides statistics about how the transaction-log space was used in all databases. It can also be used to reset wait and latch statistics.
(DBNameVARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED, -- Database Name -- Name of the database for the log statistics displayed.
DBIdINT,
LogFileREAL, -- Log Size (MB) -- Actual amount of space available for the log. This amount is smaller than the amount originally allocated for log space because the SQL Server 2005 Database Engine reserves a small amount of disk space for internal header information.
LogFileUsedREAL, -- Log Space Used (%) -- Percentage of the log file currently occupied with transaction log information.
StatusBIT) -- Status -- Status of the log file. Always 0.
CREATE TABLE #DBFileInfo
([ServerName]VARCHAR(255),
[DBName]VARCHAR(65),
[LogicalFileName]VARCHAR(400),
[UsageType]VARCHAR (30),
[Size_MB]DEC(20,2),
[SpaceUsed_MB]DEC(20,2),
[MaxSize_MB]DEC(20,2),
[NextAllocation_MB]DEC(20,2),
[GrowthType]VARCHAR(65),
[FileId]SMALLINT,
[GroupId]SMALLINT,
[PhysicalFileName]VARCHAR(400),
[DateChecked]DATETIME)
DECLARE@SQLString VARCHAR(3000)
DECLARE@MinIdINT
DECLARE @MaxIdINT
DECLARE @DBNameVARCHAR(255)
DECLARE@tblDBNameTABLE
(RowIdINT IDENTITY(1,1),
DBNameVARCHAR(255),
DBIdINT)
INSERTINTO @tblDBName (DBName,DBId)
SELECT[Name],DBId FROM Master..sysdatabases WHERE (Status & 512) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/ ORDER BY [Name]
INSERT INTO #LogSizeStats (DBName,LogFile,LogFileUsed,Status)
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')
UPDATE#LogSizeStats
SET DBId = DB_ID(DBName)
INSERTINTO #FixedDrives EXEC Master..XP_FixedDrives
SELECT@MinId = MIN(RowId),
@MaxId = MAX(RowId)
FROM@tblDBName
WHILE (@MinId <= @MaxId)
BEGIN
SELECT@DBName = [DBName]
FROM@tblDBName
WHERERowId = @MinId
SELECT@SQLString=
'SELECTServerName = @@SERVERNAME,'+
'DBName = '''+@DBName+''','+
'LogicalFileName= [name],'+
'UsageType= CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,'+
' Size_MB= *8/1024.00,'+
' SpaceUsed_MB= NULL,'+
-- 20081125 Arithmetic overflow error converting expression to data type int.
-- ' MaxSize_MB= CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN *8/1024.00 ELSE maxsize*8/1024.00 END,'+
' MaxSize_MB= CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN *8/1024.00 ELSE maxsize/1024.00*8 END,'+
-- 20081125 end
' NextExtent_MB= CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*(*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'+
'GrowthType= CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'+
' FileId= [fileid],'+
' GroupId= [groupid],'+
' PhysicalFileName= [filename],'+
'CurTimeStamp= GETDATE()'+
-- 20081125 begin @DBName embedded spaces
-- 'FROM '+@DBName+'..sysfiles'
'FROM ['+@DBName+']..sysfiles'
-- 20081125 end
PRINT @SQLString
INSERT INTO #DBFileInfo
EXEC (@SQLString)
UPDATE#DBFileInfo
-- 20081125 begin LogFileUsed is %
-- SETSpaceUsed_MB = (SELECT LogFileUsed FROM #LogSizeStats WHERE DBName = @DBName)
SETSpaceUsed_MB = Size_MB / 100.0 * (SELECT LogFileUsed FROM #LogSizeStats WHERE DBName = @DBName)
-- 20081125 end
WHEREUsageType = 'Log'
ANDDBName= @DBName
-- 20081125 begin @DBName embedded spaces
-- SELECT@SQLString = 'USE ' + @DBName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'
SELECT@SQLString = 'USE [' + @DBName + '] DBCC SHOWFILESTATS WITH NO_INFOMSGS'
-- 20081125 end
INSERT #DataFileStats (FileId,[FileGroup],TotalExtents,UsedExtents,[Name],[FileName])
EXECUTE(@SQLString)
UPDATE #DBFileInfo
SET [SpaceUsed_MB] = S.[UsedExtents]*64/1024.00
FROM #DBFileInfo AS F
INNER JOIN #DataFileStats AS S
ON F.[FileId] = S.[FileId]
AND F.[GroupId] = S.[FileGroup]
AND F.[DBName] = @DBName
TRUNCATE TABLE #DataFileStats
SELECT @MinId = @MInId + 1
END
SELECT[ServerName],
[DBName],
[LogicalFileName],
[UsageType] AS SegmentName,
B.MB_Free AS FreeSpaceInDrive,
[Size_MB],
[SpaceUsed_MB],
-- 20081125 begin FreeSpace amounts
[Size_MB] - [SpaceUsed_MB] AS FreeSpace_MB,
CAST(([Size_MB] - [SpaceUsed_MB]) / [Size_MB] AS decimal(4,2)) AS FreeSpace_Pct,
-- 20081125 end
[MaxSize_MB],
[NextAllocation_MB],
CASE MaxSize_MB WHEN -1 THEN CAST(CAST(([NextAllocation_MB]/[Size_MB])*100 AS INT) AS VARCHAR(10))+' %' ELSE 'Pages' END AS [GrowthType],
[FileId],
[GroupId],
[PhysicalFileName],
-- 20081125 begin DatabasePropertyEx
CONVERT(sysname,DatabasePropertyEx([DBName],'Status')) AS Status,
CONVERT(sysname,DatabasePropertyEx([DBName],'Updateability')) AS Updateability,
CONVERT(sysname,DatabasePropertyEx([DBName],'Recovery')) AS RecoveryMode,
CONVERT(sysname,DatabasePropertyEx([DBName],'UserAccess')) AS UserAccess,
CONVERT(sysname,DatabasePropertyEx([DBName],'Version')) AS Version,
-- 20081125 end
[DateChecked]
FROM #DBFileInfo AS A
LEFT JOIN #FixedDrives AS B
ON SUBSTRING(A.PhysicalFileName,1,1) = B.DriveLetter
ORDER BY DBName,GroupId,FileId
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo'))
BEGIN
DROP TABLE #DBFileInfo
END
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))
BEGIN
DROP TABLE #LogSizeStats
END
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))
BEGIN
DROP TABLE #DataFileStats
END
IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives'))
BEGIN
DROP TABLE #FixedDrives
END
-- 20081125 begin SET NOCOUNT OFF
SET NOCOUNT OFF
-- 20081125 end
END
September 4, 2009 at 5:32 pm
Hey,
I run the script and got results but i want to dump the data from the script in another table. How do i do that. Since all the tables are drop.
thanks in advance
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy