February 11, 2010 at 4:09 pm
I am looking for suggestions to improve our nightly job on our 100+ gb database. The database is being logged shipped, and our nightly job causes some pretty big tran log backup files.
Is there anything we can do to our nightly job to try and bring down the size of the tran log backup files? We are backing up the tran log every 15 minutes. I didn't want to increase the frequency of the backups for the entire day just because there is a 1.5 hour window when they grow so big. And when I say big, I mean anywhere from 1 gb to 12 gb in that 1.5 hour window. Outside of that window they're anywhere from 20 mb to 75 mb.
Here is how our nightly job is set up:
1) switch to bulk_logged
2) Reorg index if fragmented < 30%. Rebuild index in fragmented 30% or more
3) Update stats
4) Return to full recovery
5) Check db integrity
6) Backup db
7) check backup integrity
We are using SQL Server 2005 Standard SP2.
Any suggestions would be appreciated.
February 11, 2010 at 4:50 pm
Looking at your plan, I don't see that you are checking for the level of fragmentation on your indexes. If you are not doing that, then you are reorganizing a lot of indexes that don't need to be touched. The general rule is to exclude indexes with less than 1000 pages - and anything with less than 10% average fragmentation.
Also be aware of the fact that reorganizing indexes is fully logged regardless of the recovery model.
Then, you are updating statistics. Are you only selecting those stats that need to be updated? Or, are you updating all with full scan? If you are selecting all - then you are updating stats that have just been updated during the previous step. When you perform an index rebuild, the stats associated with that index are already updated with full scan.
And finally, is there a reason you are performing two integrity checks? That really seems to be overkill and not necessary unless you really think you could have an I/O issue that would cause corruption in your data files during your backup. This is not likely.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2010 at 10:40 pm
Have you considered creating your own sp that will reindex only certain tables on a specified day? In other words, reindex your tables in batches, which are run on different days (it differs for everyone, but I personally do not reindex all tables every day). Just spread out your larger tables in the reindex job so that they don't all reindex on the same day.
February 12, 2010 at 8:19 am
Hi Marcia Q,
As mentioned before, your probably best only working on the indexes you need to.
I have a script (would be happy to pass it over to you) that checks the fragmentation level for all indexes, records this to a table, and then picks the top (x) from those with a fragmentation greater than (y)%. This means I only update those that need doing, and also gives me an trail, so I can see which ones are fragmenting fastest etc and then deal with resolving these.
If you'd like the script let me know and I'll get it over to you.
March 1, 2010 at 2:50 pm
Thanks everyone for your replies.
We already are checking for the degree of fragmentation (mentioned in step 2). Index is reorged if fragmented < 30% (and > 10%). It's rebuilt if fragmented 30% or more.
To determine the fragmentation we are basing it on these results:
SELECT object_id , index_id, partition_number, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 8
So did you mean something different than that?
Also, we are doing ours based on > 1 extent. Jeff, any reason why you're saying 1000 pages?
Interesting point about running the stats 2x. Our step to update stats is simply "EXEC sp_updatestats" But are we really updating them twice? BOL states "In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items."
So that leads me to believe that those tables that were just rebuilt probably aren't having stats run against them again. Is there any way to tell all of the times that stats were updated on a table? In other words, can I check to see if stats were updated two times during my job window?
March 1, 2010 at 3:13 pm
the STATS_DATE() function can tell you the last time the stats were updated.
I think if you're using sp_updatestats you're fine, but I've found that it actually isn't quite enough sometimes and I end up with out-of-date stats when I use it.
That's timing more than anything.
I ended up writing my own version of sp_updatestats that is a little more aggressive on what it considers out-of-date and I'm very happy with it.
Craig Outcalt
March 2, 2010 at 12:28 am
Here is how our nightly job is set up:
1) switch to bulk_logged
2) Reorg index if fragmented < 30%. Rebuild index in fragmented 30% or more
3) Update stats
4) Return to full recovery
5) Check db integrity
6) Backup db
7) check backup integrity
Hi Marcia,
In above steps, when you switch the recovery model to bulk_logged, will the log shipping working fine?
and can you post the job script, if possible. I want to try this option(switching to bulk-logged model while performing index rebuilds) for our large database, if log shipping won't break
thanks
March 2, 2010 at 7:46 am
Aeterna - Thanks!
klnsuddu - Take a look at Jeffery's post. Bulk_logged recovery mode doesn't matter with indexing.
To answer your question though - According to BOL (http://msdn.microsoft.com/en-us/library/ms187103(SQL.90).aspx): "The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning."
As far as a script - it's just an ALTER database command.
March 2, 2010 at 1:39 pm
Marcia Q (3/2/2010)
Aeterna - Thanks!klnsuddu - Take a look at Jeffery's post. Bulk_logged recovery mode doesn't matter with indexing.
To answer your question though - According to BOL (http://msdn.microsoft.com/en-us/library/ms187103(SQL.90).aspx): "The primary database must use the full or bulk-logged recovery model; switching the database to simple recovery will cause log shipping to stop functioning."
As far as a script - it's just an ALTER database command.
Marcia, not quite what I stated. Switching to bulk_logged recovery model will have an effect when you REBUILD and index. However, performing a REORGANIZE is always a fully logged operation regardless of the recovery model.
Now, if you are on Enterprise Edition - I would recommend switching to bulk_logged recovery model and then rebuild (not reorganize) all selected indexes ONLINE.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 2, 2010 at 2:58 pm
Oh - thanks for the correction!
Currently we are using Standard. We are talking about upgrading to 2008 Enterprise. I will keep that in mind.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply