Monthly Archives: May 2013

Page Life Expectancy Issues

Posted on by
Reply

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

Posted on by
Reply

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.