Transaction Log Full - Msg 9002, Level 17, State 2 - SQL Server 2005 Standard

_{Friday, July 6, 2007 9:48 PM}

Hi,

I have database with unlimited growth in Data with 2MB growth and  Log File with 1MB growth. The continuous  data is inserted with min 20KB per minute. We struct with following error and Log file size is too large (85GB)  than Data file(20GB).  Now server is not allowing to take backup as diskspace is not available.

Msg 9002, Level 17, State 2, Procedure DumpData, Line 10
The transaction log for database '%DB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

status at SYS.DATABASES:

log_reuse_wait log_reuse_wait_desc

2              LOG_BACKUP

Please help me to resolved the issue and let me know hints to shrink LOG file. Data inserted are using ADO applications that uses some insert/select stored procedures that does not have begin transaction-Commit Transaction block as appliction issues the Commit over connection object. 

Regards,

Pavan

_{Sunday, July 8, 2007 11:51 AM ✅Answered}

this is the high time to have a proper backup policy for your database. You should findout the reason of the growth of the TL.

(a) Do u really need full recovery model , if not change to simple

(b) if there are many dataload process , check u really want to keep the db in full recovery

(c) if the db is in replication then check for any open transaction (dbcc opentran)

(d) What is the frequency of the TL backup. you must increase the frequecy so that the system will automatically re-use te inactive space of the TL

(f) At this stage you must request for a maintenance window for your database and you should shrink the TL log to a comfortable size and then schedule the TL backup more frequently

(g) if its a 24x7 kind of db , as a workaround you can add new TL file to a new drive where you have enough space . (but i would not recommend it ... its a workaround only)

Refer this link for more

http://support.microsoft.com/kb/873235

Madhu

_{Thursday, August 2, 2007 1:32 AM ✅Answered}

I caution the need to constantly shrink tranx log. I highly recommend you take a quick read of this article.

http://www.karaszi.com/sqlserver/info_dont_shrink.asp

_{Saturday, July 7, 2007 2:47 PM}

Pavan,

         How long is the bulk insert operations going on is it happening frequently ? ? ? if not jus change the recovery model to bulk logged during bulk operations as in ur case......coz in full recovery model the log file will grow drastically if you dont take log backups frequently .......either u schedule log backups frequently or change recovery model....do not perform shrinking frequently as it mite cause fragmentation.....u can very well shrink log files weekly once.....use this command,

dbcc shrinkfile (logfile name, target size) > refer BOL for further assistance............

cool

_{Sunday, July 8, 2007 1:50 AM}

Is the db is used for logshipping as primary db or did you backed up log file at that time?  You can shrink the log file so that it vill return de freespace. use the following query.

backup log dbname with truncate_only
dbcc shrinkfile(logfile name, targetsize)

_{Wednesday, August 1, 2007 7:34 PM}

Hi Madhu,

Thanks for your response. I just kept this issue under observasion. After backup and shrinking the Transaction Log for the first time, it was shrinked to almost initial size. I also added a job to take full backup periodically for every week  and truncate log. Currently Primary Data file is of 12G and Transactional Log is of 8G, here i wondered of Transactional log size though open transactions are nil

1. The current database operations are 24x7 basis with continuos data add, modify and also deletions.

2. It has Merge Replication.

3. Recovery model is Simple.

Is there anything i can try to reduce Log Size?

Best Wishes,

Pavan