MSSQL transaction log

[Ng Lay Yong]

Hi,

I'm doing data purging for MSSQL database currently. But the deletion was failed by prompting "Transaction log was full, please backup transaction log."

I had tried the following:

BACKUP LOG database_name WITH TRUNCATE_ONLY

and also set the trunc.log on chekpt. option to true. But it doesn't help at all. Any idea about this?

Thanks in advance.

[Ron Kinner]

I would thin the following advice would apply:

"Transaction Log Full
When importing to an MS-SQL database, the transaction log file will grow quickly. If you have your database properties configured so the transaction log file is limited to a specific size, you may want to remove that setting until after the import has completed."

http://www.ablecommerce.com/Migrating-to-AbleCommerce-52-ASPNET-W21C43.aspx

Ron

[Ng Lay Yong]

Hi Ron,

The database transaction log was set to unlimited growth. But the hard disk was full currently, and i tried to do the data purging to free up the hard disk space.

Is there any ways that MSSQL will not write to the transaction log?


Thank you

[Chad Miller_5]

I don't think you can prevent the TRN log from being written. It's an integral part of how SQL works and should not be prevented. There is a better way. Read on.

You can change the properties of the dB on the Options tab: Change recovery model to Simple. "The SIMPLE recovery model is the easiest of the three to implement and it uses the least amount of storage space. However, recovery is limited to when the database was last backed up." If your data changes often and there is a lot of it, it may not be too uncommon to back up the dB every 15 minutes. Read more on recovery models. There are 3.

A transaction log is necessary in order for the dB to rollback a transaction between backups. If your dB has a lot of activity (like deleting and recreating virtual tables often) then the TRN log will grow very fast. Unrestricted growth is the SQL default parameter. Your TRN log has grown out of control because you failed to make a maintenance plan which includes the TRN log. The TRN log is basically a snapshot of the dB before a change is made. The bigger the dB, the bigger each snapshot.
I have had this happen, too. I had a calendar application that grew to 2.5GB in a week of activity using all the HDD space. What I did to alleviate the mess was to shrink the dB, then backed up the TRN log. I already had a backup, so possibly losing data was not an issue, so evaluate this carefully before you do it. After the dB was reduced, the HDD space became available and a standard backup and TRN back up were run without issue. I then set up a maintenance plan to back up the dB and TRN log every night. Problem solved. It now grows no more than 5 or so megs.

Chad

[Ganesh Babu]

Hi U can try this also..

1) When u r purging the data.. open another connection to SQL query analyser connection and do the following

a) dump tran with truncate_only (this case to be run using master DB)
b) then using the actual DB do this
DBCC SHRINKFILE (, , TRUNCATEONLY)

This should reduce your file..

Ganesh

[Ganesh Babu]

u can try deleting the data phase by phase like 1000 or 10,000 records at once and next 1000 or 10,000 records..

check whether the transaction log is going to hit the free hard disk space..

when it is near close, then stop deleting and try this

) dump tran with truncate_only (this case to be run using master DB)
b) then using the actual DB do this
DBCC SHRINKFILE (, , TRUNCATEONLY)

this should reduce the file size and once again u can try deleting the records in phase by phase..

Ganesh

[Ng Lay Yong]

Hi Ganesh,

The commands does help to free up the hard disk space and now i'm able to perform the data purging by phase.

Thank you