By: Eli Leiba | Comments (12) | Related: 1 | 2 | More > Database Administration
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.
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".
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.
About the author
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
