Finding the Fullness of all Database files

Posted on by

One of the common things a Database Admin would need to monitor is the size of the database files and along with that monitor the level to which those files are full, so as to plan for the future growth of the database or to be proactive in allocating additional data files if the existing one are full.

Such a task is fine with a few databases at a time, but if there are lot of databases on a server, it is difficult to do such a task. Writing T-SQL scripts for doing such a task a solution to overcome repetitive tasks. The following script enables the user to list the database files which are full to the desired level.

To change the format so that you get the list of all database files, change the @PCTFULL variable value to 0. This script assumes that the user requires the list of the database files which are nearly full (80 % or more)

For the convenience of users, the script can be copied from the text at the end of the post.

You can find the code here..

DECLARE @PCTFULL INT

SET @PCTFULL = 0

DECLARE @sql VARCHAR(4000), @dbName varchar(255),
@LFNAME varchar(255), @PFNAME varchar(255),
@PCTUSD varchar(255)

CREATE TABLE #MainTemp (DBNAME varchar(128),LFName Varchar(500),
PhysLoc Varchar(500), TotalExts DECIMAL(10,2),UsedExtents
DECIMAL(10,2))

CREATE TABLE #Temp1 (FileID INT, FileGroup INT, TotalExts INT,
UsedExtents INT, LFName Varchar(500) , PhysLoc Varchar(500))

DECLARE  DataBaseNamesCursor CURSOR FOR
SELECT  [name] FROM master.sys.databases

OPEN DataBaseNamesCursor

FETCH NEXT FROM DataBaseNamesCursor INTO @dbName
WHILE @@fetch_status=0

BEGIN

SET @sql = ' USE ' + @dbname + CHAR(13)
+ 'Insert into #Temp1 (FileID,FileGroup,TotalExts,UsedExtents,
LFName,PhysLoc )' + CHAR(13) +  'EXEC (''DBCC showfilestats
with no_infomsgs'')' + CHAR(13) + 'Insert into #MainTemp'
+ CHAR(13)
+  'Select ''' + @dbname + ''', Temp.LFName,Temp.PhysLoc,
Temp.TotalExts,Temp.UsedExtents from #Temp1 Temp INNER JOIN '
+ @dbname + '.dbo.sysfiles SF ON Temp.FileID = SF.FileID
AND Temp.LFName = SF.Name AND Temp.PhysLoc = SF.FILEName '

EXEC(@sql)

FETCH next FROM DataBaseNamesCursor INTO @dbName

END

CLOSE DataBaseNamesCursor
DEALLOCATE DataBaseNamesCursor

SELECT  DBNAME, LFName , PhysLoc,
CEILING((UsedExtents / TotalExts) * 100)
AS PERCENTAGE_USED FROM #MainTemp WHERE
CEILING((UsedExtents / TotalExts) * 100) > @PCTFULL
ORDER BY PERCENTAGE_USED DESC

DROP TABLE #Temp1

DROP TABLE #MainTemp

— Bru Medishetty

Leave a Reply