SQL Server Performance Forum – Threads Archive

Log Error

last night i got an error. its SQL Server 2005 ‘Error: 9002, Severity: 17, State: 2.’
The transaction log for database ‘TYMSMS’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases’ Funny thing is backup was done as schedule and disk has almost 13GB space. I dont know why did i get this error. any idea please

Look at the following http://support.microsoft.com/kb/317375 Also BOL states that : The reuse of log space can be prevented by any of the following: CHECKPOINT, LOG_BACKUP, ACTIVE_BACKUP_OR_RESTORE, ACTIVE_TRANSACTION, DATABASE_MIRRORING, REPLICATION, DATABASE_SNAPSHOT_CREATION’, LOG_SCAN, OTHER_TRANSIENT Check what your sys.databses indicates. Didn’t you enable autogrowth for yur log file? Satya

Autogrowth option is Enabled
Restricted File Growth(MB) is 2GB. should I do Unrestricated file growth? sys.database indicated log_reuse_wait (2) and Log_reuse_wait_desc(log backup)

i just checked windows log file and there is an error before starting the maintenance job SQL Server Integration Service failed to start due to the following error.

You need to either unrestrict file growth, or manually increase the size of the log. Whatever you are trying to run requires a bigger transaction log than you have given it so far.

Follow the Thomas recomendations…
What exactly you are doing with SSIS package? Are you copying the large tables?
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Check this article:
http://msdn2.microsoft.com/en-us/library/ms345414.aspx
Enable autogrowth log file.

Hi,
Follow as suggested by Thomas and Mohammed. Copying large tables,bulk copy/insert or index defregment job will occupy more log space. Recently we have discussion on T-Log growth issue plz refer this thread http://sql-server-performance.com/Community/forums/p/25978/143197.aspx#143197

]]>