SQL Server Database Monitoring – Recovery 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

Alexzander Nepomnjashiy
Alexzander Nepomnjashiy
I am a Microsoft SQL Server Database Designer for Neo-Systems North-West - a security services, consulting, and training company. I have over eight years of experience in the IT field. I am currently working on several projects which involve the deployment of Microsoft Windows NT Server/Microsoft SQL Server within an enterprise business/financial environment. My typical role in these projects includes extending and improving our clients' corporate ERP systems to manage retail sales data, predict market changes and calculate trends for future market situations (DSS, OLAP). Also among my responsibilities are the design and administration of Microsoft SQL Server 7.0/2000 databases. I am available to work on a contract basis for the following types of projects: - Technical authoring, including books, articles, and white papers; - Network and systems design and analysis; - Database and software development and analysis; - Short-term consulting projects. I hope you find these articles useful. If you have any ideas for future articles (in a field of Microsoft SQL Server databases design, administration, performance optimization), or if you have anything to say about the ones below, please do not hesitate to contact me! Feel free to forward these articles to all interested associates. Thank You!

Latest Articles