the MSSQL transaction log is a some space that is required by MSSQL server to do roll backs and roll forwards rite? does the transaction log in MSSQL ever flush itself after the transaction is completed? or does it just stay there, and accumulate itself? because i am having a problem, whereby i am posting 5000 sales entries in SQL and it is taking up more than 3GB of transaction log file space. can anypone pls advice?
correct. no - the transaction log never flushes itself (even after a full database backup - i may be corrected on that one). You can turn on bulk/insert which will not right to the transaction log your 5000 sales entries. Otherwise, i recommended either: a) backing up the transaction log regulary (hence truncating the log) b) create a SQL job/alert that checks the %full of the transaction log and automatically truncates the log. See the commands ‘backup log’ on SQL Books Online. Good luck.
Hi Jordan, To Decrease the size of the Transaction Log follow these Steps: 1. Take full backup of the DB so as to restore it if need be. 2. open the database properties from the enterprise manager & go to the Transaction Tab. 3. Set the Backup property to Simple from Bulk Logged. 4. Now Shrink the database. 5. The Transaction Log file would decrease to 1 MB. 6. Now open the Database Properties again & change the property back again to Bilk Logged. I hope that would solve your problem. Good Luck.
I really like to second the idea of reading the MS SQL documentation. Here is what happens: 1. When you create your database you have defined a size for the Transaction Log. Within normal daily operation /without changing recovery model of your database or other dirty things :)/ it will never go under this size even when Shrink Database is executed. 2. By default a Full recovery model is selected /and I strongly recommend leaving it there!/. This means that every transaction is written to the transaction log and nothing is flushed until a transaction log backup is done. Everytime you make a transaction log backup it takes “inactive” portion of the log and writes it to the backup, while the active portion is leaved. The size of the physical file, however, remains unchanged, but the logical log “inside” the physical file is highly reduced /leaving there just the active portion/transactions /. This is explained in greater detail in the SQL Server Books. brgds.
actually, what i have done to overcome the huge file size of the transaction log is to change the recovery model to simple. but frankly i do not know what it does… even after reading the online manual. some questions… 1. how will the recovery model affect MSSQL’s rollback and rollforward? 2. and why does the full recovery model not flush itself while the simple model does? even if the full model is made to behave that way, why would anyone want to keep commited transaction’s data? last but not least, thank you all… at least the forum is better than microsoft’s help line in malaysia…
i have found a great site with lots of detailed info on transaction logs and recovery model. it is in http://www.sqlmag.com/Articles/Index.cfm?ArticleID=8551