Brennan’s Blog

Over the last few days I have noticed a big difference in performance between SQL Server 2005 Standed Edition and SQL Express. It seems the Standard Edition uses more memory, but it also had some issues with permissions which was causing a major performance degredation.

I attempted to diagnose and resolve the problems myself with limited success, so I ventured over microsoft.public.sqlserver.server to post my problem. Here is what I did to get myself into this trouble.

First I had SQL Express running my development databases. What you need to know about SQL Express is that it is just like the big SQL Server but it is limited to a single processor and 1gb of memory. It also does not run other features like Reporting and Integration Services, but it can do Query Notifications. With those exceptions it is still just a glorified service to manage .mdf files which are compatible across SQL Express and SQL Server 2005 Standard Edition.

What I did was uninstall SQL Express while leaving the .mdf files in place. Then I installed SQL Server Standard Edition, the one I won at Deeper in .NET 2006. Since the .mdf files were still in place I simply attached to them. I fired up Visual Studio and I was back in business.

After a while I was noticing that SQL Server was chewing up a lot of processor time even when I was not hitting the database. I found it was generating some errors, such as...

An exception occurred while enqueueing a message in the target queue.
Error: 15517, State: 1. Cannot execute as the database principal
because the principal "dbo" does not exist, this type of principal
cannot be impersonated, or you do not have permission.

Through the discussion on the news group I found it was due to how the permissions are set up on the files SQL Server uses, such as the .mdf and .ldf files. In some cases the files only had my personal user account with access while others had access for the system and MSSQL User accounts. I added those permissions which helped resolve the problem, but I eventually chose to start from scratch and did it right.

I cleared out all of my databases, deleted the .mdf and .ldf files and then created an empty database for each instance with SQL Management Studio. I took the backups and restored on top of those empty instances. Creating the empty databases with SQL Management Studio ensured the permissions on the files were set up properly. Now things are generally back to normal.

However, I am seeing that SQL Server is much more resource intensive in terms of memory and disk usage. So on my virtual host I created a new virtual disk and installed SQL Server on it so that it has plenty of room to grow. Now I just need a couple of 1gb memory chips from Dell to double my memory.