Rebuild indexes in SQL Server 2005

If you have a database with millions of records on which you provide search facility to customers then you will need to maintain indexes so that the visitor experience is enhanced.

SQL Server 2005 allows you to create indexes. Its up to you to decide which columns should be indexed. Most probably those columns should be indexed which serve as the search criteria for the visitor for e.g. name, address, phone etc.

Now you may say that you had created indexes once then why should you rebuild them?
You should remember that as new data is inserted into the table the index is not updated. So after some time you will start to see performance drop when searching. This is the time when your index requires you to rebuild it.

Use the following SQL statement in SQL Server 2005 to rebuild the index

ALTER INDEX [ALL|] ON REBUILD

If you are an experienced DBA then you can also specify the FILLFACTOR after the REBUILD. If you are not then the above statement will do the trick.

You will see a marked improvement in the performance.

Hope the above helps

Grant EXEC permission on all stored procedures in SQL Server 2005

If you have a large number of stored procedures in your SQL Server 2005 and you have to give EXEC permission to a database user on all of them then the best way to do it is by running the following SQL

 
SELECT ' GRANT EXEC ON '+ name +' TO <database_user> ' from sys.procedures

The above statement will generate your GRANT statements and you will just have to copy and run them. That’s it really.

Hope the above helped

No website response due to auto close in SQL Server 2005

I had been working on a website based on ASP.NET 2.0 and SQL Server 2005 Express Edition. However I was facing a problem on it. Problem was that the website worked all right during the busy hours such as in the morning and afternoon but in the wee hours of the night the website would hang i.e. website did not give response at all and some how the website used up all the server memory and processing speed.

The problem was peculiar in that it happened when there was no traffic on the website and thereafter it worked fine. I ignored it thinking it might be due to the server maintenance that usually went on at that time however when this started to happen frequently and the client began to lose patience I knew this had to be tackled soon.

So I looked at the SQL Server logs. I found nothing in it. Then I looked at the web server logs thinking some one may be trying to use brute force or DOS attack on the website. I again found nothing.  This started to become confusing.

Out of nowhere I thought why not look at the Event Viewer and surely I saw that the SQL Server database was being closed at some time and then it was opened. I deduced that the time when it was opened must be the same time at which the website did not respond and caused that hanging effect.

I knew that SQL Server had an auto-close feature but this feature was by default unchecked in SQL Server 2000 where as after going through Books Online I found that it was by default checked in SQL Server 2005 due to which I faced this problem.

I unchecked the Auto Close option in SQL Server 2005 and the problem was resolved.