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.
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.
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)
SET NOCOUNT ON
CREATE TABLE ##ALL_DB_Files (
[size] INT NOT NULL,
[maxsize] INT NOT NULL,
growth INT NOT NULL,
[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
@command1='use [$];Insert into ##ALL_DB_Files select db_name(), * from sysfiles',
@replacechar = '$'
-- output the results
[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
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
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".
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.
- 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: 2011-04-26
About the author
View all my tips