increasing size of LDF file
-
Hi All, In my project database is SQL server. The LDF file size is increasing considerably(now it is 16 gb). It may be due to unCommited transactions. My question is that if I close the connection after each query execution will it commit the transaction so that the file size will be small.? or is there any other solution to decrease the file size? Thanks
-
Hi All, In my project database is SQL server. The LDF file size is increasing considerably(now it is 16 gb). It may be due to unCommited transactions. My question is that if I close the connection after each query execution will it commit the transaction so that the file size will be small.? or is there any other solution to decrease the file size? Thanks
anant.awadhut wrote:
My question is that if I close the connection after each query execution will it commit the transaction
In general terms, no, you have to commit the transaction yourself, provided you actually started one. If you start a transaction and don't explicitly commit it, then, upon disconnection from the database, it assumes the transaction is not complete and any changes within that transaction are rolled back. You might find this[^] MS KB article useful in helping you determine where the problem is. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
Hi All, In my project database is SQL server. The LDF file size is increasing considerably(now it is 16 gb). It may be due to unCommited transactions. My question is that if I close the connection after each query execution will it commit the transaction so that the file size will be small.? or is there any other solution to decrease the file size? Thanks
OK. This has been bugging me for a little while. I assumed you where doing the basics: Are you backing up the database regularly? Are you backing up the Transaction Log, and truncating the log to remove old, unneeded transactions? If not, you might want to go into the Enterprise Manager and setup a Maintenance Plan.... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
-
OK. This has been bugging me for a little while. I assumed you where doing the basics: Are you backing up the database regularly? Are you backing up the Transaction Log, and truncating the log to remove old, unneeded transactions? If not, you might want to go into the Enterprise Manager and setup a Maintenance Plan.... RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome
Yes I am backing up the database daily. I am taking the backup of complete database not just a ldf or MDF file. But I don't know how to truncate the log to remove old,unneeded transactions. How this can be done? Thanks
-
Yes I am backing up the database daily. I am taking the backup of complete database not just a ldf or MDF file. But I don't know how to truncate the log to remove old,unneeded transactions. How this can be done? Thanks
Well, you can read the docs on the BACKUP sql statement here[^]. If you're doing a Full backup of the database, and the log doesn't get truncated already, you can follow it up with the SQL statement
BACKUP LOG databaseName TRUNCATE_ONLY
to dump the old transactions in the log that are no longer required to rebuild the database. What you're trying to maintain is a backup of the data, which you're doing daily, and a backup of the transactions in the log since the backup of the data was last done. RageInTheMachine9532 "...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome -- modified at 12:31 Saturday 7th January, 2006