transction log error

Hi,
I was trying to delete some record from table and I received the error message below.
can someone tell me what's wrong with my database?



Error: 9002, Severity: 17, State: 2
The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Explanation :

Changes made to the database are recorded in Transaction log. As the transaction log is full its not able to insert into transaction log.

Resolution :

To solve this you can take following approach.

1) Take transactional backup for the database
Transaction backup will take backup of all the backup and remove all inactive part from the log file, thus decreasing the size of the log file.

Or

2) Execute the following command

DUMP TRAN <database name> WITH NO_LOG

This will dump the transaction log and decrease the log file size.


Regards
Sachin

This is probably silly question but why do I have to type database name? shouldn't be the table name...

This table has automated insert stmt. I just set the log size to autogrowth. was this right way to fix the solution???

I ran the delete query and there was no error. But I would like to make sure if i did approch the correct way to solve the issue.
pls let me know

quote:Originally posted by sachinsamuel

Explanation :

Changes made to the database are recorded in Transaction log. As the transaction log is full its not able to insert into transaction log.

Resolution :

To solve this you can take following approach.

1) Take transactional backup for the database
Transaction backup will take backup of all the backup and remove all inactive part from the log file, thus decreasing the size of the log file.

Or

2) Execute the following command

DUMP TRAN <database name> WITH NO_LOG

This will dump the transaction log and decrease the log file size.


Regards
Sachin

Hi,
thanks for the replay. I max the size and it's working now..
thnks for the all help.

however, i had question regarding defrag command for sql table.

does it improve performance on the sql box? i have never run this command on my sql bos and wanted to know if it runs againg my sql table & index, would it affect on my perfornamce .. right now my sql box is at 100% cpu and i am using sql 2005 server.
can you pls let me know if the Indexdefrag command works or not?
pls help me..

quote:Originally posted by satya

BOL clairfies the action for 9002 error
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:

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.

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


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.



Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

If your fragmentation is too high then definately the defrag would help you. You can check the fragmentation level using the DMVs. Generally it says that fragmentation level lower than 30 % needs a REORGANIZE while anything above would need a REBUILD