March 26, 2008 at 8:39 am
Hello,
We have a SQL 2000 production server currently hosting about 300 databases on a Raid 5 server with two partitions and we are running into space disk issues on one of our partition c: which contains the transaction log files. The data and backup are on another partition which contains much more space. Currently there is a maintenance plan running once a day with a full backup for all databases.
All databases are running in full recovery mode and we would like to decrease the transaction log files. As I understood on other posts, to be able to shrink the transaction log in full recovery mode we need first to backup the transaction log.
I read a lot of topics about how to shrink a database but I am still not sure how to achieve it safely. My idea is to keep the full backup every night and to run hourly transaction log backup followed by truncate and shrink the transaction log files to decrease their sizes.
I planned to run a script like this on all databases :
1. Backup transaction log
backup log 'DBLogFileLogicalName' TO DISK = 'xxxxx_log.back'
2. Shrink the transaction log to minimum size
dbcc shrinkfile('DBLogFileLogicalName', 0)'
3. Release space physically on hard drive
dbcc shrinkfile('DBLogFileLogicalName', notruncate)'
I tested it on a test server and the log files are effectively reduced. My question is more about the safety of the procedure.
A. If I understand it correctly in case of a data deletion or server crash I could restore database using the full backup and the additional log backups ?
B. What is the performance impact of making a backup every hour of every transaction log files ?
C. For the full backup, do I need to include the logs in the backup or is data backup enough (currently logs are not included) ?
Thank you for your advises
Gilles
March 26, 2008 at 8:32 pm
I'd like to verify a few points in your post...
You have 300 databases on a single RAID 5 disk set with two logical partitions C: and something else (I'll refer to it as E: ). Your data and backup is on the larger partition (E: ) with plenty of free space and your t-log files are on the C: partition which is limited.
There is a once a day full backup that backs up the databases on E: and puts the backup file on E:.
Presuming the above is accurate...
A: Yes, if you have the full backup and every transaction log backup up to the point you need to restore your data, you can recover your database(s) to this point in time. If you are putting your backup files on the same drive set, your "server crash" scenario would have to exclude drive loss/corruption to make this a valid backup plan.
B: You will see increased disk IO during the t-log backups. This will be magnified by the fact that you are taking a backup to and from the same RAID 5 physical drive set. If you require FULL recovery, you have little choice but to take regular t-log backups. The frequency of these backups should be based upon usage patterns and recovery requirements.
C: I'm a little confused by your third question. Doing a full backup is a distinct operation from log backups. The full backup is standalone and capable of restoring the database up to the moment the backup was taken and only to this point in time. You cannot use a full backup to restore to a user defined point in time.
Shrinking a log file after EVERY backup is generally a bad idea. You are wasting a lot of drive IO to shrink the file then SQL Server is having to grow it again. General rule of thumb is that if SQL Server needed the space, it'll likely need the space again. If space is really tight, you should consider moving some of your t-log files to the larger partition (it's on the same physical set anyway, so you're not seeing any performance benefit from having them separate) while you acquire some additional hardware to physically separate your data and logs.
You might also want to consider reviewing your acceptable downtime and recovery intervals for each database. You might be able to reduce some to SIMPLE recovery, which would eliminate the need to do t-log backups on these databases.
March 26, 2008 at 11:43 pm
I'll second Kyle's advice. The only time you should consider shrinking a transaction log is if it's grown excessivly large due to some long running transaction.
In general, what you want for the log file is a steady size. Get the log to a size where it doesn't need to grow to accomodate thee transactions that occur between the log backups, but where it's not 99% empty after an hour's transactions.
Repeatedly shrinking and growing the log can result in file-system fragmentation, which will decrease performance.
Never, ever truncate the transaction log (Backup log with truncate...) It breaks the log chain and means that you will not be able to restore to a point after the truncation, unless you take another database backup.
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
March 27, 2008 at 3:14 am
Hi,
Thank you for your advises.
Well for the server hard drives configuration we have currently two arrays :
C: (70gb) containing the logs and OS in raid 1+0
D: (210gb) containing data and backups in raid 5
Problem is the scsi disks are already a few years old and very very expensive to buy so we can't increase the storage size on this server.
Ok i understand that shrinking the log must be an exceptional maintenance.
Currently on the server there is only a full backup of all dbs once a day. No log backups at all. IfI unterstand it right the logs continues to grow between the daily backups. So if I would make 3 T-Log backups during the day the allocated transaction log size needed would be about ~3 times less depending on the activity repartition on the dbs during the day ?.
If I can reduce the log files just by making 3 backups of the logs during the day then I could shrink all the logs once at the beginning and then let the log grow to a steady size with 3 T-Log backups during the day ??
Concerning point C, the full backup is currently configured as a maintenance plan on the server and is configured as complete backup without backup of the transaction log as part of the maintenance plan. Is this correctly configured for a crash recovery. Files are kept 3 days.
Thank you for your help
Gilles
March 27, 2008 at 4:02 am
Faessler Gilles (3/27/2008)
Hi,Currently on the server there is only a full backup of all dbs once a day. No log backups at all. IfI unterstand it right the logs continues to grow between the daily backups.
Full backups do not truncate the transaction log. Only a log backup truncates inactive log records and makes the space in the log available for reuse. Without transactionlog backups, the logs will continue to grow.
I would suggest you start with log backups every 2-4 hours (depending on the activity in the DB), and drop that interval down if the logs are still growing too big
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
March 27, 2008 at 9:10 am
Thank you for your response gail.
I will try this option with a log backup every 4 hours and see how big the log are growing. I will come back here to inform about the problem status
Regards
Gilles
March 27, 2008 at 10:52 am
The log backups should let you know what total size your logs need to be to handle the load. Track this across a few weeks, multiple days to see the peaks.
You do not want to shrink logs every day. Size them to the peak with a little pad.
You never want to shrink the data files unless you've had a bad load or something that caused them to grow like crazy. This includes the shrink database command. Use shrinkfile instead. The data files being changed causes fragmentation and can worsen performance.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 29, 2008 at 2:42 pm
I have a database with full recovery mode.Log file size is now grown to 50 gb
If i try to run dbcc shrinkfile('xx_log',2000) without taking log backup
what will happen ?
May 29, 2008 at 8:50 pm
I would say before taking any action take full backup then Tlog and then use DBCC shrinkfile command. After use DBCC again take Full backup immediately.
If possible try to check with changing the DB mode from FULL to SIMPLE.
MCP, MCTS (GDBA/EDA)
July 11, 2008 at 4:21 pm
Hi Manoj,
As you said:If possible try to check with changing the DB mode from FULL to SIMPLE.
Could you please explain why it is needed to change the "Full" to "Simple" recover model.
Thanks.
July 12, 2008 at 6:13 am
Because it throws away the inactive portion of the transaction log. Fine if you don't care about recovering to a point in time after the change to simple, a really, really bad idea if you do.
Basic rule:
If you care about point in time recovery, put the db in full recovery mode and schedule regular log backups
If you don't care about point in time recovery, put the database into simple recovery model and don't worry about the tran 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
January 12, 2009 at 7:56 am
I'm a bit confused about why my TLog backups aren't freeing up the space in the log files. My TLogs grow to mammoth proportions once I run my db maintenance job. (I use DBCC CHECKDB and also a script that will re-org any indexes with >10% fragmentation and re-build any indexes with >30% fragmentation.) However, I also include a step to backup the TLog. However, the TLogs are still huge and when I go in to "Tasks--Shrink" from SSMS, the log file usually has 98% free space. Shouldn't the TLog backup have reduced and reallocated that unused space?
Everything I've read has cautioned against shrinking files. So, I'm trying not to do it, but it's disconcerting to see these huge log file sizes!
January 12, 2009 at 8:32 am
lduvall (1/12/2009)
Shouldn't the TLog backup have reduced and reallocated that unused space?
No. The tran log backup will have removed old log records and made the space inside the file available for reuse. It will not change the size of the file on disk. It's perfectly normal that right after a log backup the tran log's 98% empty.
Everything I've read has cautioned against shrinking files. So, I'm trying not to do it, but it's disconcerting to see these huge log file sizes!
Does your log ever use all of that space? If so, leave the log alone, it needs to be that size.. If not, work out what size, at worst case, the log needs to be, add a safety factor (10, 15%) and then do a once-off shrink file on the log to get it that size.
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply