July 16, 2008 at 1:27 am
Hi, here's an example to explain : DBCC SHRINKFILE(testlog, 3)
Now if the target size is not reached with the above command, we can go on to truncate the transaction log
BACKUP LOG pubs WITH TRUNCATE_ONLY
🙂
July 16, 2008 at 1:31 am
rinu philip (7/16/2008)
Hi, here's an example to explain : DBCC SHRINKFILE(testlog, 3)Now if the target size is not reached with the above command, we can go on to truncate the transaction log
BACKUP LOG pubs WITH TRUNCATE_ONLY
🙂
If you like discarding log records and breaking your recovery chain, yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 3:21 am
rinu philip (7/16/2008)
Hi, here's an example to explain : DBCC SHRINKFILE(testlog, 3)Now if the target size is not reached with the above command, we can go on to truncate the transaction log
BACKUP LOG pubs WITH TRUNCATE_ONLY
🙂
dont truncate the log file willy nilly. If you must truncate follow immediately by a full backup to re initialise the backup chain
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
July 17, 2008 at 3:33 am
thanks!!
July 17, 2008 at 4:48 am
There may be another choice here.
SQL determines when to issue a checkpoint for that database. I've seen several instances of checkpoints being issued way too infrequently on large databases, causing large log files.
In these instances rather than issueing a backup log with truncateonly command we sheduled an hourly CHECKPOINT (for that database)
added benefit of this is that you can run in either simple or Full recovery mode without having to find all of your truncate log commands....
but also look at the size of your largest clustered index - this will determine the amount of storage space used when reindexing - thismigt account for why your log grows so much....
MVDBA
July 17, 2008 at 5:03 am
michael vessey (7/17/2008)
In these instances rather than issueing a backup log with truncateonly command we sheduled an hourly CHECKPOINT (for that database)added benefit of this is that you can run in either simple or Full recovery mode without having to find all of your truncate log commands....
Checkpoint only truncates the logs if you are running in Simple recovery mode. In Full recovery mode, only a backup log truncates the inactive portion of the transaction log
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 17, 2008 at 5:45 am
understood - that's why it's less risk - the checkpoint command will not truncate the log in FULL recovery mode.... full mode you would have a t-log backup (hopefully) anyway so the point is irrelevant.
as i understand it the original posting was for a database in simple mode
- implementing truncate log commands could easily turn into a nightmare if anyone takes over and decides to put the database in full recovery mode - expecting point in time restore.
i'd recommend trying the checkpoint otion first to see if it solves your problem - nothing ventured nothing gained, if not then you need to find out why your log is not trunctating as regularly as you want, or figure out if you have a large transaction such as re-index or BCP that is forcing the log to grow in a SINGLE tranaction - in that case shrinking the log is a futile excercise as it will only grow again, and slow the large transaction down further while it waits for NTFS to allocate disk space at 10% of file size (or whatever your settings are)
MVDBA
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply