SQL Server Database Monitoring - Recovery from 1105 and 9002 Errors

Monday Nov 4th 2002 by Alexzander Nepomnjashiy
Share:

Alexzander Nepomnjashiy continues his new series that focuses on SQL Server alerts and errors with a detailed look into monitoring for and recovering from 1105 and 9002 errors.


This article is the fourth in a series focusing on alerts and errors within the SQL Server Agent subsystem. In this article I am going to detail how to recover from SQL Server 1105 / 9002 errors. This article will also describe monitoring and recovery procedures for SQL Server databases. I am also planning to periodically publish additional guides to ensuring the health of Microsoft SQL Server and its databases.


Database Files and File Size Controls

Each Microsoft SQL Server 7.0 database consists of, at a minimum, two files: the data file (typically *.mdf file) containing all data and objects and the transaction log (typically *.ldf file) which holds the log information used to recover the database.

In SQL Server 7.0, files can grow automatically from their initially specified size. When you define a file, you can specify a growth increment. Each time the file fills, it increases its size by the growth increment specified (10% percent by default).

Each file can also have a specified maximum size. If a maximum size is not specified, the file continues to grow until it has used all available space on the disk. The files that the database consists of can grow automatically, reducing the administrative burden of monitoring the amount of free space in the database and of allocating additional space manually.

One general recommendation: set the initial size of the database to a reasonable size to avoid the premature activation of the autogrow feature, which can degrade database server performance and probably leads to unnecessary hard drive(s) fragmentation.

An insufficient free space in data file(s) / transaction log file(s) event for the database can result for any of the following reasons:

  • The database (data and/or transaction log) files were created without the "Automatically grow file" option checked (or at some point the option has been manually unchecked by the DBA)

  • The database (data and/or transaction log) files were created with the "Maximum file size : Restrict filegrowth ( MB ) = some_value" option checked;

  • The amount of free space on the disk drive where the data file/transaction log file are stored is insufficient for further database growth.

As a rule, once the event is triggered, users will not be able to continue their work with database. Thus, some amount of free space on the disk drive(s) is critical for normal database server operation.

The Microsoft SQL Server 7.0 database administrator (DBA) determines file sizes (both for data file(s) and transaction log file(s)) at the moment of database creation. Unlike in Microsoft SQL Server 6.5, the data and transaction log cannot be part of the same file or filegroup in version 7.0.

Another difference from version 6.5 to 7.0 is that in v7.0 all databases are required to have at least one data file and one transaction log file. Data and transaction log information are never mixed on the same file, and individual files are used by only one database. Separating data and transaction log files lead to following advantages:

  • It is possible to implement an incremental backup copying scheme (for the transaction file -- only from the last full database backup);
  • It is possible to implement independent backup copies (for the data file only, the transaction file only, or data and transaction log together);
  • It is possible to more efficiently control the amount of free space, both for an initial data file(s) and for transaction log file(s);
  • It is possible to achieve maximum performance by spreading data and log files across as many physical drives as possible, which should be advantageous as it improves throughput by using parallel data access;
  • It is possible to receive clearly described warnings when data (filegroup) and/or log files have run out of space.


Page 2: File Size Errors and Recovery



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 is full.

    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' is full.

    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.

    or


  • 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.

    and then:


  • 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.

    or


  • 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.

    and then:


  • 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 procedure:

  • 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


Share:
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved