On the Third Day…

Mais Oui!

But of course, we must have some french hens for today!  Today we have the third day of pre-Christmas.  And as was mentioned we have more undercarriage work in store.

So far we have talked about the following:

  1. Maintenance Plan Logs on the First Day
  2. Service Broker lost Messages on the Second Day

On the Third Day of Pre-Christmas…

My DBA gave to me more old fat.  Like the past few days, we are looking at more tables that have too much fat.  The kind of fat that has been sitting around for too long and is far beyond stale.

Here, I am going to cover two more tables that can get large.  This is very much similar to how Nicholas Cain (twitter) has covered the topic, though I do it slightly different.  You can read what he has done here. *

The biggest difference comes in how we determine large tables.  You can read my method for finding large tables here.

The key here is that there are tables in the msdb database that are in use for maintaining mail history and backup history.  These tables need to be given TLC.

To maintain these tables, I do much the same as Nic does.  To maintain these two tables, I create a SQL Job to run scripts.  The scripts are as follows.

 

Now, this is the third day of pre-Christmas so I have one more routine that Nic didn’t discuss in that post.  This one is to clean out Agent Job history.  This is done because I have seen the sysjobhistory table get rather large like the other tables I have discussed so far in this series.  To run maintenance for this table, it is rather similar to the prior two scripts from today.

The stored procedure being used can take different parameters.  It allows you to purge either for a specific job or by date or everything if no parameter is supplied.  You can read more of that from the documentation here.

Another reason I like to do this is to make it easier to read the agent job history when I am troubleshooting.  When there is a lot of history, it can be rather slow and even possibly cause timeouts trying to get in to troubleshoot a failed job.

Running this procedure comes in very handy when somebody has forgotten to set the SQL Agent properties for job history retention.  Then again, maybe they didn’t forget.

This type of setting of course is a blanket example.  As I alluded to earlier, this procedure can take different parameters.  And if somebody has not set the history retention properties for SQL Agent, then we have some flexibility.

With this flexibility, you can set a custom purge cycle for each of several different jobs.  Maybe some jobs only need 2-3 days of retention but another needs to have 90 days.  This method allows you to do that.

Upcoming

So far we have worked to maintain several different large tables within the msdb database.  In the next article, we will discuss how to release the space from the table that should be released after removing large amounts of data stored in these tables due to a lack of maintenance and TLC.

Notes

* The page previously linked to the blog for Nicholas Cain appears to now be broken. Due to that break, here are the indexes that were referenced in that blog post.

No Comments - Leave a comment

Leave a comment

Your email address will not be published. Required fields are marked *








Calendar
December 2012
M T W T F S S
« Nov   Jan »
 12
3456789
10111213141516
17181920212223
24252627282930
31  
Content
SQLHelp

SQLHelp

  • @DesertDBA: Is it possible to rename an existing SQL 2012 Availability Group? #sqlhelp
  • @AirborneGeek: @tjaybelt How well the Gateway will work for you will depend on what your end goal is. #sqlhelp
  • @AirborneGeek: @tjaybelt As Tom said, you can use the Desktop app, or, you can use the Personal Gateway to get to an on-prem DB.#sqlhelp
  • @tjaybelt: #sqlhelp where does my simple SQL data need to reside for #PowerBI to access it? does it need to be in a cloud azure sql db? or local?
  • @AnupWarrier: I understand that SQL2014 SP1 with a CU has a fix, however dont think 2008 R2 has any #sqlhelp 2/2
  • @AnupWarrier: If I disable TLS1.0 on any servers which hosts SQL 2008 R2, then SQL Service wont start...Is this a true statement..#sqlhelp 1/2
  • @flyingcod: 'Cannot execute as the db principal because principal dbo does not exist' All dbs have full perms #servicebroker error. #sqlserver #sqlhelp
  • @Joe_E_O: Can you get the object from the offset for error 1117 - or do you need DBCC output #SQLHELP
  • @retracement: @kevine323 Don't do anything until you have checked the logs! And no, don't go removing WSFC. #sqlhelp.
  • @kevine323: 2/2 but wanting to make sure 0 downtime. should I just restart cluster services? Win 2012R2 w/ SQL 2012. Cluster is multi-subnet. #sqlhelp

Welcome , today is Friday, August 28, 2015