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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Monthly Maintenance Plan

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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 backup
2. Incremental backups (tlog backups) every 15 minutes
3. Differential backup 12 hours after full backup

All 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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.

Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

olinmds
Starting Member

25 Posts

Posted - 2014-04-22 : 09:13:25
Thanks Tara!!!!

I truly appreciate your time and knowledge.

D.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-04-22 : 14:12:58


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -