Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2011-08-10 : 11:01:01
|
| Folks, Im in confusion here, we have about 30 database's that are in FULL recovery model in our PROD & STG environment and have FULL backup being done every night before which a shrinkdb job runs to shrink the data file and log file size. There is no transaction log file backup. This setup was done long before and Ive been asked to revisit and improve it.My manager has asked me to review the size of the ldf file before and after the nightly backup and based on that if I notice the size of the ldf file grows till 150MB from 10MB. Increase the intial size of the ldf file to 150 MB instead.Also he asked me to review if there is a need to execute the shrinking of log and data file job afterwards.Is it a good idea to set the size of the ldf file to what the max it can reach ? What are the pros and cons of doing this ? Does this have something to do with fragmentation. Also is shrinking the data and log file everyday before the full backup good ? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-08-10 : 12:18:00
|
| You've got two things wrong here:1. You are performing shrinks nightly. Very bad.2. You are using FULL recovery model and aren't backing up the transaction logs.I always use unlimited growth for my files and then monitor the storage. Others like to set the max and then monitor the free space inside the files. Either way is fine, just make sure you are monitoring things to know when you need to take action.Either start backing up the tlogs regularly, such as every 15 minutes, or change your recovery model to SIMPLE.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-10 : 13:02:01
|
| Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]--Gail ShawSQL Server MVP |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2011-08-10 : 15:17:04
|
| Gail,Thanks for the site.Tara, I agree this setup is wrong.I executed DBCC LOGINFO(@dbname) on all the 30 databases's and I see lots of VLF with status =2 (In Use).Here are the questions that I have 1) Out of 30 database's , there 6 db's that are involved in Data Warehousing.Nightly data load jobs run after the full bak. Currently set at FULL recovery model. What is the advised recovery model for DW database's ? I was thinking of setting it to SIMPLE since they are no transaction and have no direct user interaction, apart from the SSIS packages that run nightly with huge data dumps.But what if in the middle of the DW job, some failure happens and I need to recover, what would be the impact then ?2) For the transactional database's.Ive tought of increasing the ldf file size based on the analysis(ldf growth) that Ive been doing for past 3 days. If I were to set the ldf file large enough.It would not create many VLF which I understand from reading some sites are not good , and instead of shrinking the database nightly.I would run transaction log backup at a frequent interval. The other question that I have is that if Ive already enabled transaction log every hour maybe I dont have increase the ldf file size. Since the ldf file will not grow as much ?Kindly advise |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-10 : 15:56:48
|
| Please read the article I referenced. It addresses how to manage the logs (that's why I posted it)--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Estimating LDF file size