Page Life Expectancy Issues

When I took this new job in January, I inherited a Windows 2003 Standard server running Sql Server 2005 with only 4 gigs of ram.  How in the world does this server even run Sql Server with only 4 gigs of ram.  As I always do, I set the min and max memory setting for Sql Server with min being 1 gig of ram and the max getting 2 gigs of ram.  If you are not aware, Windows 2003 Standard maximum memory is 4 gigs.

As I began to monitor this server, the first thing I did was ask for more ram since I was seeing Page Life Expectancy issues everyday.  If I could get a page to last 3 seconds, I was lucky.  I knew to get better performance from the Sql Server, I had to solve this issue.  Rebuilding the server was not an option since development was building a newer version of the product.  To make it even worse, the server has the web and app layer also on this server.  I know you are not suppose to do this, but remember I inherited it.  Lucky me!

Due to a great resource of BrentOzar.com, I ran sp_blitzindex on the server to see if what type of indexing issues might be occurring.  I saw several indexes not being used and missing indexes.  I also saw tables with no clustered indexes.  Last night with a change control ticket, I fixed all the missing indexes issues and on one query which was timing out some times, I added the missing clustered index.  I also dropped indexes not in use.  This morning when I came in to review the monitoring of the server, I noticed my Page Life Expectancy had changed dramatically.  The server when from 3 seconds to 2 hours 17 minutes and 51 seconds.  Wow, don’t tell my system administrators but fixing the index issues also solved memory pressure issues.  You know a DBA always wants more memory.  Yes, I knew it was going to help but not to the degree of change I am seeing now.

Check out Brent Ozar and look for the sp_blitzindex and sp_blitz scripts.  Brent and team, you make me look very smart.  Thanks for all you do for the community.

Missing mssqlsystemresource database

Last night, one of our Development servers had a service pack get pushed to the server and automatically install.  I was unaware our patching process was automatically patching all of our servers which are not in Production.  Not a good idea, I would rather control this process and deploy the patches in a uniform process.

After the server was patched, the patch required a reboot.  When the server rebooted, Sql Server would not start up.  After reviewing the Windows Application log and Sql Server log, I quickly discovered the mssqlsystemresource database was missing.  I did search for it just in case it was placed somewhere else, but to no avail.

If the database was misplaced, I could have used this article just to move it back to the proper location, http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/01/22/error-945-severity-14-state-2-database-mssqlsystemresource-cannot-be-opened.aspx.

In my situation, the database was not misplaced, but it was gone.  Next I read the blog, http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9ddc5d52-5835-405f-ad38-ac25022373f4/.  As you can read, I needed to find a server with the same version number to copy the files from the working server to the broken server.  I opened the Sql Server error log to get the version of Sql Server.  I scrambled around the office looking for a server with the same version number of the broken server.  No luck.

What is a DBA to do?  I have the version number but what do I need to install.  Thanks to our http://sqlserverbuilds.blogspot.com/ which shows use all the versions and links to the website for the download. I started to think how could I get this server back online, create a VM, install Sql Server 2008 R2 with cumulative update 1. I could then copy the files I needed from the new server to the old server.  Do I need to save the Master and MSDB databases?  This could take a while.  I need this server back online.

Instead, I went to Add/Remove program, found the Sql Server program and did a repair installation.  After the repair completed, my server was back online.  Yea!

But I am not finished, what do I need to do to prevent this same issue in the future.  The files look like a database, why can’t I just back them up in Sql Server like other databases. Microsoft does not let you do that.  But per this article, http://msdn.microsoft.com/en-us/library/ms190190.aspx, you can backup the files for emergency recover.  If needed, you can use the backup files for recovery but be carefully as the article states.  Finally, I think I am protected here.  Time to move on to the next issue.