This morning, I’m continuing to work with the HCMGA database. I need to get a maintenance plan implemented to keep the database tuned up. The Performance purge job ran well into the night, and when I logged in to the server this morning, the memory was maxed out, and performance in the application was very sluggish. So I rebooted the box and it’s working better now.

Next, I wanted to see if I could shrink down the 50GB Performance.mdf file. I opened SSMS and executed the following query:

It took several minutes to run, but it did not release any space back to the OS. I then configured Database Mail for Alerts in prod as per my prior post. I set up a new Operator and a test alert to verify messages are being sent out.

I found a good post on MS SQL 2008 maintenance plans best practices. I also ran across free redgate eBooks on Sql Server.

What I ended up doing for now is to open SSMS and log into prod. I then Opened the Management tab and right clicked on Data Collection and chose Disable Data Collection. I then closed out of SSMS and opened the Sql Server Configuration Manager and shut down the prod database and restarted just the SQL Server process. I then logged back into SSMS as prod and dropped the Performance database. I then went back into Sql Server Configuration Manager and started the remaining services: Sql Server Analysis Services, Sql Server Reporting Services, Sql Server Agent. This freed up about 40GB of disk space. There is now 77GB of free disk space on the C: drive.

One of the eBooks from redgate, author Brad McGehee, entitled Brad’s Sure Guide to SQL Server Maintenance Plans, ISBN: 978-1-906434-33-5, references several online sites where DBA’s have published their own maintenance plans. I chose to install Ola Hallengren’s THREE-IN-ONE Database Maintenance SOLUTION. It’s pretty simple to install. Just follow the “Getting Started Guide”. I downloaded the scripts and logged into SSMS dev database. I ran the MaintenanceSolution.sql script (after changing the backup directory). After it executes, new Jobs are installed in the SQL Server Agent | Jobs. I changed the login to “sa” on all the jobs and ran them independently to verify that they are indeed working. I then set up a test schedule of the System_Databases_full script.

I’ll set up these jobs in dev for a few days, then complete the tasks in the production database.