Export (0) Print
Expand All
Expand Minimize

Error 9002

SQL Server 2000

Error 9002

  Topic last updated -- January 2004

Severity Level 19
Message Text

The log file for database '%.*ls' is full. Back up the transaction log for the database to free up some log space.

Explanation

The transaction log file for the indicated database has run out of free space.

Action

The user action that is appropriate to you depends on your situation. Potentially, possible actions include:

  • Backing up the transaction log

  • Freeing disk space

  • Moving the log file to a disk drive with sufficient space

  • Adding or enlarging a log file

These possible actions are discussed below.

Regardless of which action you adopt, you should also follow them up by considering what caused the transaction log to fill. Likely causes include a long running transaction or a published transaction. To look for such transactions, use DBCC OPENTRAN.

  • A long-running transaction prevents truncation and reclamation of transaction log space, which normally happens either automatically (under the Simple Recovery model) or as a result of taking a log backup (under the Full Or Bulk-Logged Recovery model).

    You may have to use the KILL statement. Use KILL very carefully, however, especially when critical processes are running. For more information, see KILL.

  • If replication is turned on for the database and has fallen behind, a published transaction that has not been passed into the distribution database may be preventing log truncation. For information about replication and the transaction log, see Planning for Transactional Replication.

The remainder of this section discusses possible actions, any one of which should suffice.

Backup the transaction log

If the database is using the Full or Bulk-Logged Recovery model, you should back up the transaction log immediately to free up space. If you are not taking log backups, you should either start taking log backups or switch to the Simple Recovery model. If the database is using the Simple Recovery model, backing up the transaction log is not possible.

For more information on recovery models, see Using Recovery Models.

Regardless of the recovery model, consider the following actions.

Free disk space

You may want to free disk space on whatever disk drive contains the transaction log file for the database. Freeing disk space allows the recovery system to enlarge the log file automatically.

Move the log file to a disk drive with sufficient space

If you cannot free sufficient disk space on the drive that currently contains the log file, consider moving the file to another drive with sufficient space. If you choose to use another drive:

  1. After ensuring that the other drive has sufficient free space for the transaction log, detach the database by executing sp_detach_db.

    Detaching a database makes it unavailable until it is reattached.

  2. Move the transaction log files with insufficient space to the other drive.

  3. Attach the database by executing sp_attach_db, pointing to the moved log file(s).

For more information see, Insufficient Disk Space.

Adding or enlarging a log file

Alternatively, you can gain space by adding an additional log file for the database or enlarging the existing log file (if disk space permits).

  • To add a log file to the specified database, use the ADD FILE clause of the ALTER DATABASE statement. Adding an additional log file allows the existing log to grow.

    For information about adding files, see Adding and Deleting Data and Transaction Log Files.

  • To enlarge the log file, use the MODIFY FILE clause of the ALTER DATABASE statement, specifying the SIZE and MAXSIZE syntax.

For more information on these Transact-SQL clauses, see ALTER DATABASE.

See Also

ALTER DATABASE

Errors 9000-9999

Expanding a Database

Insufficient Disk Space

sp_attach_db

sp_detach_db

sp_add_log_file_recover_suspect_db

Show:
© 2014 Microsoft