October 7, 2002 at 12:26 am
On our production database, we have setup our Recovery Model to "Simple". But transaction log is expending in MB's and takes up all storage on the harddrive. When we watched the behaviour of transaction log we observed that on insertion of about 500K record in a 4 column table, there was no effect on transaction log whereas on deletion of same 500K records, transaction log starts expanding and on complete of deletion process it expanded about 350Mb where as 95% of the space is unused. and its keep on expanding with the same process of insertion and deletion.
It seems that it is not being shrinking automatically. Does any one help out how to solve this problem. Is there any issue with checkpoints??
October 7, 2002 at 3:50 am
None that I am aware of but simple mode is designed to keep the transaction log for all logged transactions until a backup that will truncate the log is performed. Now as for why the insert doesn't cause it I would have to assume some sort of bulk process since simple will not log these. But a delete which has no bulk couterpart other than truncate of course will log to the transaction log. Now with simple recovery model the database transacton log will grow still until the completion of the transaction, so the deletes will log but when done the process should truncate the data ad the file to conserve space. If it is no recovering the space then change the model to FULL and then back to SIMPLE to make sure, also what Service Pack are you running as may be a known issue at some level, I have not seen thou with SP2.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 7, 2002 at 7:39 am
Yes, I have applied the same solution. Changed the mode to full, took the backup of transaction log and then shrunk the database, it works but this is temporary solution requires users interaction.
True: Simple mode should log the transaction for any possible rollback but it should automaticaly truncate the log, but it does not.
I am currently using SP2.
October 7, 2002 at 7:51 am
SQL Server Books says::
Long-Running Transactions
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems:
If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.
The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint.
I am using Query Analyser and it shows deletion process completes successfully. That means Query Analyser COMMITS transaction and should move MinLSN.
Does this problem has any solution??
October 10, 2002 at 7:07 am
quote:
On our production database, we have setup our Recovery Model to "Simple". But transaction log is expending in MB's and takes up all storage on the harddrive. When we watched the behaviour of transaction log we observed that on insertion of about 500K record in a 4 column table, there was no effect on transaction log whereas on deletion of same 500K records, transaction log starts expanding and on complete of deletion process it expanded about 350Mb where as 95% of the space is unused. and its keep on expanding with the same process of insertion and deletion.It seems that it is not being shrinking automatically. Does any one help out how to solve this problem. Is there any issue with checkpoints??
October 10, 2002 at 7:09 am
[When you are using the simple recovery model you still need to backup the log.
Example:
backup log "dbname" with no_log
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply