SQL proactive maintenence

  • Do you guys have a list you use for SQL proactive maintenence? What should be on the list? I mean something that a DBA would go and check on a weekly/month basis just to make sure everything is working fine. Please advise.

    Please advise, thanks in advance.

  • Has your company not thought of any third party tools. If not, you would have a lot of work to do.

    M&M

  • What do you have so far?

    You could start by reading the Microsoft Press Books for SQL Server 2008 and start Googling.

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Do the following for Maintenance.

    1. Reorg indexes on Nightly Basis

    2. Update stats on Nightly Basis

    3. Rebuild indexes on Weekly Basis

    4. DBCC CheckDB on Weekly Basis

    The thresholds should something be like this

    Fragmentation > 5 % and < 30% Do Reorg

    > 30% Do Rebuilds

    You can use the wonderful scripts from the following site to setup the above tasks

    http://ola.hallengren.com/

    Also Collect a 24 Hr Perfmon trace with important counters every month which serves as a baseline whenever you need to troubleshoot the performance issues.

    Thank You,

    Best Regards,

    SQLBuddy

  • 1-Take Full Backup of the required database and system databases also first if possible otherwise take different backup at least of user database

    2-Rebuild or ReOrg indexes depends on the avg_fragment_percent > 30 then rebuild < 30 then ReOrg,you can check with this dynamic function

    Select * from sys.dm_db_index_physical_stats(db_id('database name'),null,null,null,null)

    3-Update the statistics

    4-Drop unused Indexes

    5-Delete SQL Server Agent history

    6-Delete OS events history

    If you have defined Fill Factor value around 70 to 90 ,or DML of that particular table which one is using that index then it will grow fastly then you have to rebuild index weekly or within 3 ,4 days its depend on the space available

    Update the statistics depends on the DML of a particular table,If DML is performing intensive then you have to update statistics weekly or daily basis

    Drop Index which those are not using at least in a week

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • In addition to what other people have posted, I'll add the additional things I either do manually or have automated to some degree or other.

    I perform daily audits on our file server (where we store our database backup file copies), our 2 database servers, and our SAN.

    The audits consist of some pretty common sense things. For instance, I:

    1. Execute some custom SQL queries to determine levels of inbound traffic and reporting activity.

    2. Inspect current system values as they relate to certain thresholds related to our application software.

    3. Inspect the values of about 15 counters in Windows Performance Monitor that relate to hardware resource utilization and software resource utilization specifically associated with SQL Server.

    4. Verify that all SQL Agent jobs executed without errors or warnings since the last audit, just in case one did and I wasn't properly notified.

    5. Verify that all database backup files have been correctly produced and copied onto our file server. This inspection verifies that all full and differential backups are accounted for and in the expected locations.

    6. Have numerous scripts that I've developed over the years to allow me to look at various aspects of our server. I execute them to determine

    current database data and log file utilizations, and a few other things specific to our application environment. I inspect "High water marks" for these to see if they've gone up since the last audit.

    7. Examine the Windows System logs, specifically the Application, Security, and System logs, to see if there were warnings and/or errors.

    8. Examine our SAN logs for warnings and/or errors.

    9. Examine disk/volume free space on all volumes.

    10. Once per week, I examine the elapsed execution time for all SQL Agent jobs and record any new "high water marks" in an Excel spreadsheet along with other pertinent details I keep about our SQL Agent jobs and their execution schedules.

    11. Once per week, I audit our server hardware.

    All of these activities have preempted developing problems at one time or another, so I can tell you that they are time well spent, and I consider them to be highly proactive. If you have a large number of servers to monitor and inspect, you may have to reduce the amount of time and energy devoted to these activities, but if you can afford it, they are a good use of your time.

    Not only will they catch developing problems but they will attune you to the normal operating characteristics of your servers. Instantly knowing the difference between a normal and abnormal condition has been extremely valuable to me and my employer.

    For instance, we normally reboot our database servers monthly, since software states deteriorate with time. (That statement includes the Windows O.S., SQL Server, and our application software.) For several reasons, we've had to skip that maintenance reboot for the last 3 months. Last week, I noticed that on one of our database servers, Available Memory had dropped by about .8 GB overnight and that Page File utilization was near 27% and steady. These are abnormal conditions for this database server. If I see them or something like them, I know that there has been a software state change and we need to reboot the servers, which I recommended to my management. We'll be rebooting them Monday night next week.

    I once caught a developing ventilation and cooling problem in our data center by noticing a change in a temperature sensor reading on one of our servers.

    Items 7, 8, 9, 10, and 11 in my list might seem mundane but they have caught many problems and impending problems that otherwise would not have been caught.

    I hope this is helpful.

    Sincerely,

    LC

  • Since your focus is on proactive SQL Server management, I also recommend that you read this article posted by Brad McGehee:

    http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/?utm_source=simpletalk&utm_medium=email&utm_content=BradChecklist-20100125&utm_campaign=SQL

    Brad McGehee is a Microsoft SQL Server MVP and his recommendations are the source of many, maybe most, of the best practices I observe.

    Best practices are inherently proactive. They are implemented and executed to keep your server up and in optimum operating condition.

    LC

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply