November 5, 2004 at 2:54 pm
Despite nightly full backups and several transaction log backups per hour 24/7, our transaction log continues to grow. Does this approach seem reasonable, safe to do on a regular basis. ( backup log, backup DB, truncate log, shrink database )
N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\genconv119_Log_200411051300.trn'
N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\genconv119_Data_200411051300.bak'
--step three shrink database
November 5, 2004 at 3:26 pm
I wouldnt' shrink the log just because of the shrinking. When you shrink the log, it will sooner or later need to grow again. This process is relative expensive and can lead to file fragmentation, which doesn't positively affect performance at all. I would watch this for a while and set the log to a reasonable size to avoid autogrowth operations between two full or differential backup processes.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 5, 2004 at 3:40 pm
Another thing about your schema is that you are defeating the purpose of the transaction log backups. As soon as you issue the BACKUP LOG WITH TRUNCATE_ONLY, you have broken the chain and no longer have point in time recovery. And besides, a transaction log backup truncates the log as part of the backup, so your additional truncate isn't necessary. If point in time recovery doesn't matter to you, it would be MUCH simpler to just change the recovery model of the database to simple. Then you don't have to worry about the transaction logs at all.
BUT, don't do that without careful consideration! Those transaction log backups can be very handy! If I were you, I would stick with the nightly full backups, and backup that transaction log as often as necessary to keep the log growth under control. We backup logs every 15 minutes in the shop where I work. If the log still grows, then you should probably let it. It should settle out at some point and become stable.
Steve
November 5, 2004 at 4:16 pm
Thanks for your quick responses. Yes, the regular log backups truncate the log, but don't shrink its size on disk. Our database has a data file of about 11 gigabytes with the log at 8 gigabytes in spite of the regular log backups.
When I took the steps mentioned, the log file went from 8GB to under 1MB -- the question is whether it will fairly rapidly wind up at 8GB again or just grow slowly until I do this process again.
As far as point in time recovery, at the point in time of the full backup, prior logs wouldn't be needed, and our database maintenance plan will start right back in doing 6 log backups per hour going forward.
January 5, 2005 at 1:45 pm
I understand that normally you should let your database activity determine the size of your log file ( assuming regular backups are happening ), rather than manually shrinking it's size on disk. However what about when DBCC SQLPERF(LOGSPACE) shows that the log really only needs a fraction of the disk space it is using? ( in this case less than one percent )
Database Name,Log Size (MB),Log Space Used (%), Status
MyDB 9036.6797 0.31766152 0
January 6, 2005 at 8:12 am
One thing to remember...you MUST do a FULL backup after truncating the log.
As stated before, the truncating of the log breaks the 'chain' of log backups. Therefore any log backups after the truncating CAN NOT be applied to a full backup done prior to the truncation. You must do a full backup to restart the 'chain'
-SQLBill
January 6, 2005 at 2:47 pm
rpetty,
The usage of the transaction log file can be EXTREMELY dynamic. If you are loading, deleting, updating large amounts of data during a batch process, or rebuilding indexes, the transaction log will require a large (proportionately) amount of space. Then, when you take a transaction log backup, the utilized space in the log could (and probably will) be reduced to a very small percentage. If you had a one-time load that caused the log to grow dramatically, and don't anticipate that happening again, then it won't hurt to shrink the log (DBCC SHRINKFILE). However, if it needs that large space on a regular basis, you'll be creating a lot of overhead by shrinking it and forcing it to grow again. Chances are that when you ran DBCC SQLPERF(LOGSPACE), you caught it after a log backup, and before the process that requires the additional log space. One way to see that would be to simply look at the size of your log backups. If you are needing the larger space, you should see some log backups that are close to the size of the log file.
Steve
January 7, 2005 at 2:01 pm
Thanks Steve, that answer is the most helpful I've seen. The only log backup that is near the 9 Gig size of our log ( ldf ) file is the first one following the nightly full backup. And there is very little database activity between the full backup and the first log backup following. Now, just before the full backup I'm running maintenance plans that reorganize the indexes
(Reorganize data and index pages, change free space per page percentage to 10%)
But I thought the full backup would "absorb" those transactions and not cause the next trans log backup to be large. ??
All backups etc are running out of maintenance plans.
Randy
January 8, 2005 at 12:27 pm
The full backup doesn't affect the transaction log backup size. For example, if you have a database set to the full recovery model, do daily full backups and forget to do the log backups, then the log will grow until it fills up the drive.
Probably your reog of indexes is causing the large logs.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
January 11, 2005 at 1:09 pm
Your reindex and maintenance are whats causing the log file to grow so large. As Kathi said, the full backup won't clear anything from the log, so your first log backup after the maintenance is going to be big. If you can afford the space, you should allow the log to stay at the 9gb size, which will allow the maintenance to run without having to expand the log. If you can't afford the space, you may need to consider not doing the reindex part of your maintenance, because the log is going to grow for that process.
Also, please consider SQLBill's advice. Your BACKUP LOG WITH TRUNCATEONLY will break the recoverability chain, and you won't be able to apply any of the transaction logs after it until a full backup is taken. With the backup sequence that you described, none of your transaction log backups can be used in recovering your database. Any process that is defined as being a nonlogged operation will cause this! Once you have arrived at a backup plan that you think will work, you should put it to a complete test on a test server. Try every scenario that you can think of to ensure that you are indeed covered in a disaster situation. A disaster is a terrible time to figure out that you can't recover your database!
Steve
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply