Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find SQL Server data and log files that are almost out of space


By:   |   Read Comments (12)   |   Related Tips: 1 | 2 | More > Database Administration


Free MSSQLTips Webcast Today >> Optimize SQL Server Performance


Problem

On many SQL Servers database file size, either data or log, may be restricted to a maximum size to ensure there is adequate space on the server. The problem with this is that if your data or log file runs out of space you will get an error message such as the following and your transactions will fail.

Msg 1105, Level 17, State 2, Line 1 Could not allocate space for object 'dbo.table1' in database 'test' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

In this tip I will show you a rather simple procedure that can be run to report on potential space problems before they occur. This will help identify how much space is used for your sql files and any potential issues.

Solution

The general solution I'm suggesting here will use a stored procedure called dbo.usp_get_db_files_near_maxsize. The procedure takes a parameter for space percentage or it will use the default of 10%. This will then check each file for all databases on the server, including the system databases.

If the SP is run without passing in a parameter it will find all database files, both data and log, that are within 10% of being filled only for files where you have set a maximum size.

Here is the stored procedure and this can be created in master or in your admin database.

CREATE PROCEDURE dbo.usp_get_db_files_near_maxsize (@nearMaxSizePct DECIMAL (5,1) = 10.0)
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE
##ALL_DB_Files (
dbname SYSNAME,
fileid smallint,
groupid smallint,
[size] INT NOT NULL,
[maxsize] INT NOT NULL,
growth INT NOT NULL,
status INT,
perf INT,
[name] SYSNAME NOT NULL,
[filename] NVARCHAR(260) NOT NULL)

-- loop over all databases and collect the information from sysfiles
-- to the ALL_DB_Files tables using the sp_MsForEachDB system procedure
EXEC sp_MsForEachDB
@command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles',
@replacechar = '$'

-- output the results
SELECT
[dbname] AS DatabaseName,
[name] AS dbFileLogicalName,
[filename] AS dbFilePhysicalFilePath,
ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS ActualSizeMB,
ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) AS MaxRestrictedSizeMB,
ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS SpaceLeftMB
FROM ##ALL_DB_Files
WHERE maxsize > -1 AND -- skip db files that have no max size
([maxsize] - [size]) * 1.0 < 0.01 * @nearMaxSizePct * [maxsize] -- find db files within percentage
ORDER BY 6

DROP TABLE ##ALL_DB_Files

SET NOCOUNT OFF
END
GO

Here is a sample run. This shows that both the data file and log file are almost at their maximum size and there is 3MB free for the data file and 4MB free for the log file for database "test".

a sql server database size report

As a DBA you should run this weekly or even daily to find all database files that are approaching the maximum size limit. Then it is up to you to fix the problem by adding more space to the file.

Ideally disk space would not be an issue and you woudl not have to worry about a maximum file size, but even with disk space not costing much there are still some systems that are limited and as a DBA you have to make do with what you have.

Next Steps
  • Compile the stored procedure and test it to see if you have any issues.
  • Create a SQL Agent job that runs every day and have it send you an alert if there are any issues.
  • If you are using SQL Express you can setup a Windows Scheduled Task to do this. Refer to this tip for an example.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, June 16, 2014 - 4:15:11 AM - hans Back To Top

Hi,

 

would any of you be able to help me achieve sending the query result of the stored proc via email?

 

I am currently able to send mails for failed jobs but i dont know how to attach results from stored procedures.

 

Thanks,

 

Hans

 


Thursday, January 17, 2013 - 3:11:26 PM - Thanh Back To Top

It works for me-Thank you for the script


Wednesday, January 16, 2013 - 1:44:16 PM - Thanh Nguyen Back To Top

 

CREATE PROCEDURE dbo.usp_get_db_files_near_maxsize (@nearMaxSizePct DECIMAL (5,1) = 10.0)
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE
##ALL_DB_Files
(
dbname SYSNAME
,
fileid smallint
,
groupid smallint
,
[size] INT
NOT NULL,
[maxsize] INT
NOT NULL,
growth INT
NOT NULL,
status INT
,
perf INT
,
[name] SYSNAME
NOT NULL,
[filename] NVARCHAR(260
) NOT NULL)

-- loop over all databases and collect the information from sysfiles
-- to the ALL_DB_Files tables using the sp_MsForEachDB system procedure
EXEC
sp_MsForEachDB
@command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles'
,
@replacechar =
'$'

-- output the results
SELECT
[dbname] AS DatabaseName
,
[name] AS dbFileLogicalName
,
[filename] AS dbFilePhysicalFilePath
,
ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS ActualSizeMB
,
ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) AS MaxRestrictedSizeMB
,
ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(size * CONVERT(FLOAT,8) / 1024,0) AS
SpaceLeftMB
FROM
##ALL_DB_Files
WHERE maxsize > -1 AND
-- skip db files that have no max size
([maxsize] - [size]) * 1.0 < 0.01 * @nearMaxSizePct * [maxsize]
-- find db files within percentage
ORDER BY 6


DROP TABLE ##ALL_DB_Files

SET NOCOUNT OFF
END
GO

=============

Command(s) completed successfully.

 ========

how can I see the

Here is a sample run. This shows that both the data file and log file are almost at their maximum size and there is 3MB free for the data file and 4MB free for the log file for database "test".

1) Do I need to put the Database name..


Friday, June 22, 2012 - 5:38:05 AM - pramod Back To Top

Here "ActualSizeMB" is the size of a file created which does not mean usaing that size completely. You can use system function, FILEPROPERTY to get the actual space usage of a file.

Explaining above same example, 

If a DB has been created with 147MB(data file) & 51MB (log file) andif  actual usage is only 47MB(data file) & 11MB(log file). So the "SpaceLeftMB" of these files should be  103MB(150-47) & 44MB(55-11). Incase if the file usage reaches its initial size and goes beyond that then the "ActualSizeMB" will show the new values.


Sunday, September 25, 2011 - 1:28:03 AM - anna Back To Top

Thanks for the script, I wonder how email notification can be added to the script?

That would be very helpful


Thursday, May 12, 2011 - 4:35:16 PM - Jeremy Kadlec Back To Top

Shivani,

 

Thank you for sharing your experience and code with the community.

 

Thank you,

Jeremy Kadlec


Wednesday, May 11, 2011 - 12:37:23 PM - Shivani Back To Top

At my previous workplace, we had setup scripts\jobs on all our servers (using a similar script) to check the data files daily and send an alert if they were close to 75% full. It would be better to have a separate check for transaction log growth maybe every hour or so and run log backups if the file was almost full.

The sample alert script below needs to be compiled and tested before use:

IF EXISTS
 (
         SELECT DatabaseName
         FROM ##ALL_DB_Files
      WHERE ROUND(size * CONVERT(FLOAT,8) / 1024,0) >= ROUND(maxsize * CONVERT(FLOAT,8) / 1024,0) * 0.75
 )

BEGIN

DECLARE @xml  NVARCHAR(MAX)
DECLARE @body  NVARCHAR(MAX)

SET @xml =
    CAST ( (  SELECT  b.[dbname] AS 'td', '', b.[name] AS 'td', '', b.[filename]  AS 'td', '', ROUND(b.size * CONVERT(FLOAT,8) / 1024,0) AS 'td', '',
     ROUND(b.maxsize * CONVERT(FLOAT,8) / 1024,0) AS 'td', '',ROUND(b.maxsize * CONVERT(FLOAT,8) / 1024,0) - ROUND(b.size * CONVERT(FLOAT,8) / 1024,0) AS 'td'

              FROM ##ALL_DB_Files AS b
       WHERE ROUND(b.size * CONVERT(FLOAT,8) / 1024,0) >= ROUND(b.maxsize * CONVERT(FLOAT,8) / 1024,0) * 0.75
              ORDER BY b.[dbname]
              FOR XML PATH('tr'), ELEMENTS
    ) AS NVARCHAR(MAX) )


SET @body ='<html><H1>Database Files with Low Space</H1><body bgcolor=white><table border = 2><tr><th>DatabaseName</th><th>dbFileLogicalName</th>   <th>dbFilePhysicalFilePath</th><th>ActualSizeMB</th><th>MaxRestrictedSizeMB</th><th>SpaceLeftMB
</th></tr>' SET @body = @body + @xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail @recipients='dbagroup@companyname.com',
     @subject = 'Alert for Databases Low on Filespace',
  @body = @body,
  @body_format ='HTML' ;

END


Wednesday, May 11, 2011 - 9:10:26 AM - Jeremy Kadlec Back To Top

Jason,

You are correct.  The problem could be resolved a number of different ways.  Here is one approach Eli is offering up to try to help the community.

Thank you,
Jeremy Kadlec


Wednesday, April 27, 2011 - 8:00:55 PM - Jason Back To Top
Just wondering why you don't just run the select query inside the call to sp_MSforEachDB? That way you don't need a temp table, and you don't need to perform and insert and then a query against it. Then you also don't really need it as a stored procedure. Heck you could run it as a job and pass it as a query to sp_send_dbmail to mail the list to you on a schedule.

Tuesday, April 26, 2011 - 10:41:01 AM - Janet Back To Top

Thanks Greg.  It worked.  I missed a character on my copy/paste. 


Tuesday, April 26, 2011 - 10:29:57 AM - Greg Robidoux Back To Top

This should work for SQL Server 2000 as well.  The structure of sysfiles looks to be the same in SQL Server 2000.  http://msdn.microsoft.com/en-us/library/aa260411(v=sql.80).aspx

What is the error you are getting?


Tuesday, April 26, 2011 - 10:12:56 AM - Janet Back To Top

I executed this on SQL2008 and it ran fine but cannot execute on SQL 2000.  Does it not work on an older version of SQL?


Learn more about SQL Server tools