| Author |
Topic |
|
xweb
Starting Member
6 Posts |
 Posted - 2007-12-28 : 00:22:23
|
| I run the Maintanace wizard and backup transaction logs every 20 minutes and do a full DB backup every 24 hours. I do not truncate the DB. My "ldf" file is 1 GB in size. My "mdf" file is only 900 mb. I beleive my ldf file should be smaller.My question is, is it safe/ok/proper to truncate the log after I do a full DB backup? Is there a reason not to? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 02:48:05
|
I would not truncate the logfile after a full. This would be breaking the LSN chain each time you do that manual truncate and you would not be able to recover.What is the problem with having a LDF of 1GB in size? are you running out of space? do you host your database on a USB thumb drive?  -ec |
 |
|
|
xweb
Starting Member
6 Posts |
Posted - 2007-12-28 : 13:16:47
|
| ec,I am concerned with the size of my backups as I transfer them externally. 1GB is not big on the file system, but for remote transfer it takes time.I just want to understand why I need the transaction log if I run a full backup. I understand that I need it between backups to rebuild data which is by I back the tran log every 20 mins. But after a full backup, aren't the old tran logs unnecessary? |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-28 : 13:22:39
|
| Transaction log becomes full when you have lots of logged activity.But as you are doing Transaction log backup ,it should truncate the inactive portion of files.If anything doesn't work, do these steps to truncate the transaction log:1) put your database in simple recovery mode( but your database won't recover to point in time)2) shrink the log files. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-28 : 13:27:53
|
quote:
Originally posted by sodeep
Transaction log becomes full when you have lots of logged activity.But as you are doing Transaction log backup ,it should truncate the inactive portion of files.If anything doesn't work, do these steps to truncate the transaction log:1) put your database in simple recovery mode( but your database won't recover to point in time)2) shrink the log files.
Please do not follow these steps. There is no reason to switch recovery models.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-28 : 13:29:13
|
quote:
Originally posted by xweb
ec,I am concerned with the size of my backups as I transfer them externally. 1GB is not big on the file system, but for remote transfer it takes time.I just want to understand why I need the transaction log if I run a full backup. I understand that I need it between backups to rebuild data which is by I back the tran log every 20 mins. But after a full backup, aren't the old tran logs unnecessary?
You need the transaction log backups for point in time recovery. Do not delete them just because you have a newer full backup. What happens if you need to restore to 2pm 5 days ago? You can't do that with just a full backup.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2007-12-28 : 13:32:44
|
| The reason i suggested to change the recovery model to simple is when your transaction log gets bigger in size. When you can't shrink the log files in Full recovery mode by any means. |
|
|
|
donpolix
Yak Posting Veteran
97 Posts |
Posted - 2007-12-28 : 13:33:01
|
| Depends on your database recoverability requirement, but for point in time recovery, all your tlog backups are necessary, including those old tlog backups. You have 1gb log file, but that doesn't mean you always have a gig tlog backup. Do dbcc sqlperf(logspace) to check on current log space used which would nearly define the size of your tlog backup. You can shrink your log file to the size appropriate allowing enough (or more) room for your transactions to fill up every 20 minutes.Donn Policarpio |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-28 : 13:49:52
|
quote:
Originally posted by sodeep
The reason i suggested to change the recovery model to simple is when your transaction log gets bigger in size. When you can't shrink the log files in Full recovery mode by any means.
And your reason is not correct. You are breaking the transaction log chain when you do this. You can always shrink the log files even in FULL recovery model. There are tricks when the active portion are at the end of the file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-28 : 13:51:13
|
| 1GB tlog file size sounds right for a 900MB data file, especially if you are running the optimizations maintenance plan. It gets much better with SQL Server 2005 though, but 2000 does have this "issue".Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
xweb
Starting Member
6 Posts |
Posted - 2007-12-28 : 13:59:15
|
| Here is the results of dbcc sqlperf(logspace):Log Size (MB): 984.36719Log Space Used (%): 1.8198557Status: 0The LDF file on the file system is 1.008 GB. It does not seem to fluctuate with the T-log backup every 20 mins. I am using the Maintenance Wizard to run the T-Log backups. The typical size of the T-log 20 minute increment backups are about 320KB (small).I keep all the t-log backups. That is not the worry. I am just trying to get the full DB backups (.BAK) to be smaller and wondering the ramifications of truncating the t-log after full backups. Based on what I am reading on this thread the main concern is point in time recovery, correct? |
|
|
|
xweb
Starting Member
6 Posts |
Posted - 2007-12-28 : 14:00:40
|
tkizer,I will be moving to 2005, shortly. Just doing some maintanance while it is slow.quote:
Originally posted by tkizer
1GB tlog file size sounds right for a 900MB data file, especially if you are running the optimizations maintenance plan. It gets much better with SQL Server 2005 though, but 2000 does have this "issue".Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
|
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-28 : 14:34:37
|
| You can not make the full backups smaller. Even a shrink of the database won't make it smaller. The size of the BAK file will be about the same size as the used portion of the MDF file.You should not truncate the tlog after full backups. It breaks the transaction log chain. If you want to do this, then you might as well use SIMPLE recovery model instead of FULL.LDF file size same size as what?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
xweb
Starting Member
6 Posts |
Posted - 2007-12-28 : 15:21:49
|
The reference to "I might be a bit confused: Is the LDF file size the same" was meant to be deleted. I meant to remove it from the post, and I just did.quote:
Originally posted by tkizerLDF file size same size as what?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/
|
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 15:31:12
|
quote:
Originally posted by sodeep
The reason i suggested to change the recovery model to simple is when your transaction log gets bigger in size. When you can't shrink the log files in Full recovery mode by any means.
this is bad advice my friend. you can always shrink a tlog in full recovery mode. just run a few transactions through your database and that will change the active portion of the LDF. If you have an active environment, just wait a few minutes and see. you can then shrink down the log to that point.-ec |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-28 : 15:34:11
|
quote:
Originally posted by xweb
ec,I am concerned with the size of my backups as I transfer them externally. 1GB is not big on the file system, but for remote transfer it takes time.
SQL 2008 will have a compression option for backups. you can also use a third party backup package like sql litespeed or the backup solution from red-gate and use their inline compression capability to make your backups smaller.you can also go old school and just zip your backup file prior to sending them offsite. this is the simplest and cheapest solution to implement.do not truncate your logs at full backup or switch to simple mode just to shrink your db. this is terrible advice and will eventually bite you when you cannot perform a recovery.If you do not need point-in-time recovery capability, then by all means switch to simple recovery mode. Full recovery mode means that you can recover to any point in time - as long as you do not interrupt the log sequence. If you do not require that level of recovery, and recovering to your last full or differential backup is good enough (you might lose hours of data) then go with simple recovery model and forget about log backups.-ec |
|
|
|
xweb
Starting Member
6 Posts |
Posted - 2007-12-28 : 16:15:16
|
| Thank you for the tips. I do need point in time, but limited. Maybe up to 2 days back. At the very least, 24 hours. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-12-29 : 03:26:59
|
quote:
Originally posted by xweb
Thank you for the tips. I do need point in time, but limited. Maybe up to 2 days back. At the very least, 24 hours.
the problem is 24 hours from what point? if you interrupt the log sequence then you break that capability. look into compression options that I mentioned, take more frequent transaction log backups and make sure you test whatever solution you come up with to make sure that it actually works.-ec |
|
|
|
|
Question about Transaction logs