File Size Errors and Recovery
If a database server requires additional free disk drive space, but this
additional space is unavailable and either the log or data files cannot
grow, the server reports one of two errors:
Message 1105, Level 17, State 1, Could not allocate space for object '
of % .*ls' in database ' of % .*ls' because the ' of % .*ls' filegroup
This SQL Server 7.0 message signals that the database server cannot allocate additional free space and that the filegroup specified
in the error message has run out of free space. In this case, SQL Server writes this error message to the Error Log, marking the database as suspect and taking it off-line.
Message 9002, Level 19, State 2. The log file for database ' of % .*ls'
This SQL Server 7.0 message signals that the database server cannot allocate additional free space on the hard disk which holds the transaction
log file specified in the error message. Back up the transaction log for the database to free up some log space.
In the case of an 1105 error message, you need to:
Free disk space on the disk containing a file in the filegroup mentioned in the 1105 error message (freeing disk space allows the files in the filegroup
to continue to grow) or create an additional data file on another hard drive/partition that contains a sufficient amount of a free space.
Add the data file to the specified database by using the ADD FILE clause
of the ALTER DATABASE statement; enlarge the data file by using the
MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE
and MAXSIZE syntax; or allocate a sufficient amount of free space on the disk drive/partition containing the primary filegroup.
Reset the suspect database's status by executing SP_resetstatus and run a recovery procedure by executing DBCC DBRECOVER (database_name).
In the case of a 9002 error message, you need to:
Free disk space on the disk drive containing the log file for the related database
(freeing disk space allows the log file to continue growing automatically) or create
additional transaction log file on another hard drive/partition with a sufficient amount of a free space.
Add a log file to the specified database by using the ADD FILE clause
of the ALTER DATABASE statement (adding an additional transaction log file
allows the existing one to grow); enlarge the log file by using the MODIFY
FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE
syntax; or allocate a sufficient amount of free space on the disk drive/partition
containing an initial transaction log file.
Reset the suspect database's status by executing SP_resetstatus and run a recovery
procedure by executing DBCC DBRECOVER (database_name).
Database File Size Monitoring
As a DBA you will need to periodically check the size increases of database files.
In the case of 80-90% allocated space usage (from the size initially allocated for the database
files), ensure that at least 0.1 times the size of an initial SQL database file is available on the disk drive(s) containing the (database) objects (i.e. data / transaction log files).
It is possible to receive automatic notification messages from the SQL Server Agent service about free space shortage in data/transaction log
files. I will cover this subject in one of my future articles.
Alternatively, to periodically manually monitor free space allocated for the data file(s)/transaction log file(s) in a User_Database, use this
Start SQL Server administrative console - Enterprise Manager (Start - >
Programs - > Microsoft SQL Server 7.0 - > Enterprise Manager);
Make a connection to a database server (enter the SA password in the dialog
window "Connect to SQL Server");
Open the database server objects up to User_Database and select it;
Select "View -> Taskpad" in the context-dependent menu (right mouse button
click on User_Database);
On «Space Allocated» bookmark check Data Space - "Used" / Data
Space - "Free" and Transaction Log Space - "Used" / Transaction Log Space
- "Free" values.
In the case of continual appearances of 9002 error messages, you can truncate the inactive portion of the transaction log by turning on the "Truncate Log on Checkpoint" database option, or you can manually execute the Transact-SQL command:
BACKUP LOG User_Database WITH TRUNCATE_ONLY. Note that executing this Transact-SQL command "by hand" can conflict with your corporate backup policy.
See All Articles by Columnist Alexzander Nepomnjashiy