| Author |
Topic |
|
olinmds
Starting Member
25 Posts |
 Posted - 2014-04-15 : 15:00:28
|
| Hello.Looking for info on creating a monthly maintenance plan that keeps log files from blowing up during Index reorganize/rebuild and Updating Stats. Been told to run full backup before running job, run Trans log backups every 15-20 mins during job and then run Full backup after maintenance is complete.Please advise.Thanks in advance!!! |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-15 : 15:56:32
|
| How often do you backup the transaction log outside of this special job? We backup ours every 15 minutes, always. You could increase it to every minute, but it'll still depend on the size of the index that's being rebuilt/reorganized. By the way, a reorg job will use less tlog space.How big is your largest index in GB? How much storage do you have on the device where the ldf file exists? How big is the ldf file?Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
olinmds
Starting Member
25 Posts |
Posted - 2014-04-16 : 10:11:49
|
| Hi Tara,There are two DBs I am looking at:The first DB is 7 GB in size with a log of .6 GB at a recommended Tlog bkup at every 4 hours. The largest index is .4 GB with 55 GB free space on log drive.The second DB is 21.3 GB in size with a log of .31 with Tlog Bkups running every 30 mins. Here the largest index is 2.9 GB and the log drive has 74 GB of free space.I am wanting to be proactive in completely understanding and maintaining DBs. Please know I apprciate your time and help!Thanks,D |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-16 : 19:34:10
|
| I would change the tlog schedule to every 15 minutes. I don't see any reason to do it less frequently. Your second ldf file is too small. It should be a minimum of 3GB, likely you'll need more as an index that size will complete in just a couple of minutes. So your maintenance job will likely rebuild lots of indexes in 15 minutes, and you need the ldf file to reflect that. I would expand both ldf files. I'd change the job schedule to every 15 minutes and then rebuild all indexes. I would then see what ldf file size was needed, and I would keep that as my baseline. I would have autogrowth enabled so that it could increase in size as needed, as more data flows into the system.I would not run a full backup before the index job and then a full backup afterwards. I am not sure who is making these recommendations, but this is not good advice. It's not bad advice, but it shows lack of experience. The "recommended" tlog backup at 4 hours is cause for concern too.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-17 : 12:16:59
|
| Hi Denise, I got your email, but let's continue the discussion here. In short, I wouldn't trust anything the vendor says about SQL Server best practices at this point. Here is my backup schedule for most systems:1. Daily full backup2. Incremental backups (tlog backups) every 15 minutes3. Differential backup 12 hours after full backupAll backups are done to SAN disk or NAS. All files are swept to tape.You can increase the tlog backup job schedule to every minute during the rebuild job, but I am not sure that I see the need for this given the size of the indexes and database that you have.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
olinmds
Starting Member
25 Posts |
Posted - 2014-04-17 : 13:09:28
|
| Thanks Tara. So how often do you updates stats and rebuild indexes? Does the size of the DB set the timeframe the Tlogs are backed up or is 15 mins the norm across the board?Denise |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-17 : 13:26:51
|
| Updating stats is dependent upon the system. As a default schedule, we have a job that does it daily. We have one system that has a second schedule. Some systems may need it hourly.Regarding rebuilding indexes, by default we do it daily or weekly. But I have some systems, our most critical, where we don't rebuild indexes or even reorg them. The size of the database does not impact my tlog job schedule. We use the 15-minute schedule across the board. I know some people have their systems set at 5 minutes.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
olinmds
Starting Member
25 Posts |
Posted - 2014-04-18 : 12:13:55
|
| Happy Friday Tara!!!So what steps do you perform on the most critical systems? Update stats on a daily/regular basis? How does not touching the indexes affect performance?Also, on the systems that you rebuild indexes on a weekly basis do you reorganize indexes on the other days?I thank you for your time and sharing your knowledge. I am learning that the vendor does not neccessarily know the best way around their systems and appreciate your help!D. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-18 : 15:47:41
|
| 1. By default, all systems start with daily update stats. Adjust as needed.2. Not touching indexes may or may not affect performance. It affects storage though. For the systems where we have no index maintenance but do have update stats, we have no performance degradation.3. We either reorg or rebuild but not both. I am not against having both though.No problem! Glad to help.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
olinmds
Starting Member
25 Posts |
Posted - 2014-04-21 : 11:21:16
|
Good morning Tara,Two more quick questions please....  With running frequent multiple tlog backups (every 15 mins) do you prefer to use a backup device and append to the device file or have multiple trn files per run and of course....why?Thanks!!!!!D. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-04-21 : 11:38:33
|
| The industry standard is to use a separate file for each backup. The main reasons are that it is easy to see what is contained in the file just by looking at the file name and also because it is easier to restore with separate files as you don't have to determine which file number inside the file is the one you need.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
olinmds
Starting Member
25 Posts |
Posted - 2014-04-22 : 09:13:25
|
| Thanks Tara!!!!I truly appreciate your time and knowledge.D. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Monthly Maintenance Plan