This morning, I decided to have a look at the Harris County Master Gardeners web server to see if everything is ok. I noticed that the hard disk space is dwindling down. So did a search for large files to see what I could find. To perform a search, I Clicked Start | Search | For Files or Folders… . I then clicked advanced search and put *.* in the Name, selected Include non-indexed, hidden, and system files (might be slow), selected Size (KB) is greater than 10000. I also added Size, Path, Attributes, Date accessed, Date created, Date modified to the results window.

So the biggest file by far is performance.mdf (45GB)! Yikes! It’s now on to finally setting up a maintenance plan for the SqlServer database.

First step is to set up an Operator in SqlServer. How to setup SQL Server alerts and email operator notifications article shows how to do this.

Configured new account in Database Mail for HcmgaSupport.

Configured new profile in Database Mail for SqlAlerts.

Defined new Operator by double click on SQL Server Agent

Right Click on Operators and select New Operator

Right Click on SQL Server Agent select Properties and choose Alert System. Click Enable mail profile and choose SqlAlerts.

Restart the SQL Server Agent

Set up test alert Right click Alerts and choose New Alert… Name it TEMPDB Growing, SQL Server performance condition alert, MSSQL$MSSQLDEV:Databases, Data File(s) Size (KB), tempdb, rises above, 0. Click on Response and check Notify operators and select DBA. Then click on Options check Include alert error text in: Email and set Delay between responses to 5 minutes. Click Ok.

At this point, I’m unable to get test emails sent to me for the above alert. After much trial and error, I came across SQL Agent Job Notification – Database Mail problem which looked promising to solve my problem. I applied the changes. To fix, open Database Mail, check Manage profile security, click Next, check Public Profiles check Public next to SqlAgents choose Default Yes then click Next.

At this point, emails are being sent out, but not consistently. They are being queued and then sent out all at once. When I restart the Sql Server Agent.

After looking at the Error Logs, I am seeing errors: ‘SQLServer Error: 15404, Could not obtain information about Windows NT group/user ‘Domain\User’. I’m also now getting pop up messages stating ‘ControlService Could not locate the service.’

Also, in the SQL Server Agent Properties | Alert System | Mail session, I’m still unable to save the SQLAlerts Mail profile and click the Test button. I can select SQLAlerts, but when I save it, restart the SQL Server Agent and go back to look at it, it has changed back to the first profile on the list.

So the ControlService pop up coincides with the 15404 error. A clue is the domain name. It is referencing the original domain name, and not the current one. So somewhere I need to alter the configuration to refer to the current domain name.

Read this Microsoft article How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server. The server is already renamed in SqlServer.

I browsed the server properties by right clicking on the SqlServer Instance and selected properties. I then went to Permissions and I see the old domain Logins and not the new ones. This is also viewable under Security | Logins.

So I next went into Security | Logins and scripted all the users that were attached to the old domain login and dropped and recreated them to the new one. I then right clicked each updated user and selected properties to manually update the Server Roles and User Mapping. I’m sure their are scripts to automate this process, but there are only six (6) logins to change. After verifying that I could log into the database as the updated users, I then rebooted the server.

Upon rebooting, I’m still not able to complete the Alert System Mail session | Mail system profile. It just won’t save my changes, and the Test… button is still grayed out. I’m also still getting the ControlService Could not locate the service. pop ups.

May 10, 2010 1:15pm. Continuing to work on this problem now.

Ever since the last reboot of the system (May 5th), I am now receiving my test Alert email every five (5) minutes. I’ve also checked the SQL Server Agent logs, and there are no more 15404 errors. So it would seem that I’m making progress. But I am still not able to set the Mail Session | Mail Profile to SqlAlerts and send a Test… message. I did some more Googling and found references that SQL Server 2005 64bit hadn’t been enabled for Database Mail Database Mail not working with SQL Server Agent. At this point, I disabled the SQL Server Agent | Alert System | Mail Session | Enable mail profile and moved on.

Back to my original issue. That being to set up a maintenance plan to keep the database under control. I had set up a couple test plans, but when I attempted to execute them, I get the following error “The job failed. The owner … of the job MaintenancePlan1.Subplan_1 does not have server access.”. So I double-clicked on the job and changed the owner of the job to the SQLServer owner account and reran the job. This time it worked fine.

I found out that when I’m logged into SSMS as Administrator, I can’t restart the SQL Server Agent from within SSMS. I have to open the Sql Server Configuration Manager and restart the service there. If I try to restart the SQL Server Agent service within SSMS, I get a pop up message box stating “ControlService could not locate the service”.

I also found out that when I changed the email profile within the SQL Server Agent | Alert System | Mail session | Mail profile to a profile that isn’t the public/default messages aren’t being sent out. When I change the profile back to the public/default one it works fine.

Lastly, when you go into the dialog screen for the Mail profile, it doesn’t show the currently active profile. It only shows the first alphabetical one listed in the Database Mail. This is a bug in my opinion, and I’m not sure why this would be the case. So you have to set it, and then trust later that it’s set to the correct mail profile.

Now, I’m on to fixing the production database space issues. The production database has performance statistics turned on, and it’s now taking up about 50GB worth of disk space. The first thing I had to correct is the server name change security login issues. I dropped and recreated all the Sql Server logins to correct and get the current server name. I then rebooted the machine.

I next logged into SSMS prod database and opened up the System Data Collection Sets | Disk Usage | Properties and reduced the Retain data for down to 90 days. I checked the other retention periods for Query Statistics and Server Activity. I then went into SQL Server Agent | Jobs | mdw_purge_data_[Performance] | Properties and changed the Owner to SA. Clicked Ok and executed the job. This job took well over two (2) hours to complete.