| Author |
Topic |
|
NJDba
Starting Member
31 Posts |
 Posted - 2007-12-11 : 17:37:57
|
| We do full backup every day and recovery model is Full, but we never done transaction log backup, so the transaction log files keep growing. What should I do? I think I should set recovery model to Simple, and actually we do DBCC Shrinkdatabase after full backup every day, but the transaction log file is still around 15GB.Any suggestion would be appreciated. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-11 : 18:35:31
|
| You should not be running the shrink after a full backup. You are causing performance problems by doing this. People need to understand the commands that they are running and the impact that they have on systems before scheduling them to run.Do you require point in time recovery? If you do, then you must start backing up the transaction log on a regular basis. We backup ours every 15 minutes. If you don't require point in time recovery, then switch to SIMPLE recovery model and perform a one-time shrink on the LDF file.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
NJDba
Starting Member
31 Posts |
Posted - 2007-12-11 : 18:46:59
|
| Could you tell me what performance problems are caused by running shrinkdatabase after full backup? Thank you very much. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-11 : 18:49:35
|
| Well you are modifying the size of the file which causes the first performance problem. Then once SQL Server needs that space again, it has to expand to accomodate the transactions. That's the second performance problem. The performance problem can be very significant too. There is no point in shrinking a file down when the system needs to use it in the very near future. Only shrink files down (and do it manually only) when you know you do not need that space again such as when you purge the system of lots of data and will not have that much data again for a few months.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
NJDba
Starting Member
31 Posts |
Posted - 2007-12-11 : 18:54:07
|
| Thank you very much for your post, and learn a lot from you and this forum. |
|
|
|
NJDba
Starting Member
31 Posts |
Posted - 2007-12-11 : 19:00:41
|
| One more question, does it sound reasonable to set a production database's recovery model to SIMPLE although we do not backup transaction log? Because I was thinking if I set it to SIMPLE, all the committed transactions will be removed from the transaction log, when I do FULL backup, there is no much information in the tranction log file. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-11 : 19:03:52
|
| There is no point in keeping it at FULL if you aren't going to backup the transaction log. Deciding which recovery model to use is dependent upon your data recovery requirements. Your current recovery is going to be to restore to the last full backup, so you could lose 24 hours worth of data. We can't afford that kind of data loss due to how mission critical our systems are, so we backup the transaction log every 15 minutes.We can't decide what recovery model for you to use. Your company should be deciding that. So how much data are you willing to lose in the case of a major problem?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-11 : 19:05:02
|
| In your scheduled job, are you also running the command to truncate the transaction log?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
NJDba
Starting Member
31 Posts |
Posted - 2007-12-11 : 19:10:35
|
| Actully, I just started working here, and the previous DBA schedule the full backup first and then DBCC SHRINKDATABASE every day. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-11 : 19:25:40
|
| That DBA must not have had very much experience with SQL Server then as this is common knowledge stuff.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
|
Truncate transaction log