BackOffice Products
1748211 Members
5044 Online
108759 Solutions
New Discussion юеВ

MSSQL transaction log

 
Ng Lay Yong
Occasional Contributor

MSSQL transaction log

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.
6 REPLIES 6
Ron Kinner
Honored Contributor

Re: MSSQL transaction log

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
Occasional Contributor

Re: MSSQL transaction log

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
Trusted Contributor

Re: MSSQL transaction log

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
Honored Contributor

Re: MSSQL transaction log

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
Honored Contributor

Re: MSSQL transaction log

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
Occasional Contributor

Re: MSSQL transaction log

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