Technical Article

Next grow for all databases per disk

,

BEGIN  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  (  DriveLetter VARCHAR(10),  MB_Free DEC(20, 2)  ) 
 CREATE TABLE #datafilestats  (  DBName VARCHAR(255),  DBId INT,  FileId TINYINT,  [FileGroup] TINYINT,  TotalExtents DEC(20, 2),  UsedExtents DEC(20, 2),  [Name] VARCHAR(255),  [FileName] VARCHAR(400)  ) 
 CREATE TABLE #logsizestats  (  DBName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED,  DBId INT,  LogFile REAL,  LogFileUsed REAL,  Status BIT  ) 
 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 @MinId INT  DECLARE @MaxId INT  DECLARE @DBName VARCHAR(255)  DECLARE @tblDBName TABLE (  RowId INT IDENTITY(1, 1),  DBName VARCHAR(255),  DBId INT) 
 INSERT INTO @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) 
 INSERT INTO #fixeddrives  EXEC master..Xp_fixeddrives 
 SELECT @MinId = Min(RowId),  @MaxId = Max(RowId)  FROM @tblDBName 
 WHILE ( @MinId <= @MaxId )  BEGIN  SELECT @DBName = [DBName]  FROM @tblDBName  WHERE RowId = @MinId 
 SELECT @SQLString = 'SELECT ServerName = @@SERVERNAME,' +  ' DBName = ''' +  @DBName +  ''',' +  ' LogicalFileName = [name],' + ' UsageType = CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,' +  ' Size_MB = [size]*8/1024.00,' +  ' SpaceUsed_MB = NULL,' + ' MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize/1024.00*8 END,'+ ' NextExtent_MB = CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*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()' + 'FROM [' + @DBName +  ']..sysfiles' 
 PRINT @SQLString 
 INSERT INTO #dbfileinfo  EXEC (@SQLString) 
 UPDATE #dbfileinfo  SET SpaceUsed_MB = Size_MB / 100.0 * (SELECT LogFileUsed  FROM #logsizestats  WHERE DBName = @DBName)  WHERE UsageType = 'Log'  AND DBName = @DBName 
 SELECT @SQLString = 'USE [' + @DBName +  '] DBCC SHOWFILESTATS WITH NO_INFOMSGS' 
 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],  [Size_MB] - [SpaceUsed_MB]  AS FreeSpace_MB,  Cast(( [Size_MB] - [SpaceUsed_MB] ) / [Size_MB] AS DECIMAL(4, 2))  AS  FreeSpace_Pct,  [MaxSize_MB],  [NextAllocation_MB],  ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] )  AS alert_switch,  ( B.MB_Free ) + ( ( [Size_MB] - [SpaceUsed_MB] ) -  ( [NextAllocation_MB] ) ) AS  will_be_on_drive,  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],  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,  [DateChecked]  FROM #dbfileinfo AS A  LEFT JOIN #fixeddrives AS B  ON Substring(A.PhysicalFileName, 1, 1) = B.DriveLetter  ORDER BY ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] ) 
 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 
 SET nocount OFF END

Rate

3.75 (4)

You rated this post out of 5. Change rating

Share

Share

Rate

3.75 (4)

You rated this post out of 5. Change rating