Larry Brouwer

... just my personal technology sandbox

  • About
  • Blog
  • Archives
  • Contact

Connect

  • Email
  • Facebook
  • LinkedIn

Powered by Genesis

Sql Server Administration for HCMGA (cont.)

May 11, 2010 By Larry Brouwer Leave a Comment

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:

USE [Performance]
GO
DBCC SHRINKDATABASE(N’Performance’, 10, TRUNCATEONLY)

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.

Filed Under: Daily Log Tagged With: SqlServer

Leave a Reply

You must be logged in to post a comment.

Recent Posts

  • Weaver’s Bamboo “Bambusa textilis” clumping bamboo for sale
  • configuring NTP Service on FreeNas, XenServer, and virtualized Windows Server 2012 R2 Domain Controller
  • CentOS 7 Warning: Your Magento folder does not have sufficient write permissions.
  • AOE Scheduler 1.3.0 cron issue with Magento 1.9.2.2

Tag cloud

Linux DotNetNuke mstsc.exe web.config BlogEngine.Net Outlook 2007 Magento Maytag Spam PST 301 TomatoCart Visual Studio Neptune Silverlight Log Parser PHP SBS2011 AWStats DIR-655 Comcast iframe SmarterMail Windows Remote Desktop Yahoo Mail OfficeLive ASP.Net Windows 7 Tools Google Analytics SSMS Live Writer Exchange 2010 SqlServer OpenCart D-Link SMTP Azure SVN .NET Framework Port 25 C# redirect WordPress MySQL