I have posted the following to the SQL Server forums as well, but no luck so far :

I am using SQL Server full text search with SQL 2008. The database we use is mostly read-only. It is updated by an external process on a weekly basis. The database has 4 tables on which full text index has been defined. I am only using the CONTAINS clause in my SQL to utilize the full text search, Nothing fancy.

The external process mentioned above, truncates the tables of the database and imports data to them from CSV files. After the external process completes its task, I am rebuilding the search index. This seems to work fine, for after a few minutes of the external process completion, I am able to get search results correctly. However the full text index seems to get empty for some reason after a few hours/days (I have not been able to find any pattern to when it happens). When this happens, I sometimes get either no search results, or partial results. If I manually rebuild the index from SQL Server Management tool, it works fine after that. Again the tables that have the index defined really do not have any data change, but I still have setup a sql server task to rebuild the search index both on the catalog as well as the individual tables. But this has not helped much

A few things I would like to understand:

1. What is causing the index to be empty (or partially filled)? And how to fix it?
2. Is there any way to monitor the status of the index to know how much has been rebuild?
3. The sql server jobs mentioned above : where can I see the history for them?

Please let me know if any other information is needed to help me solve this issue. It has been driving me nuts.

Recently I was doing some code review and saw that one of the developers called a new SQL Server stored procedure sp_GetSomething. I explained to her that it is not a good practice to do so but thought I might write a blog so it is useful to other programmers out there as well.

So why should you not name your stored procedure with sp_? Here is why :

This is from SQL Server Books OnLine (BOL) – search for "Creating a Stored Procedure""

“System Stored Procedures

Many of your administrative activities in Microsoft® SQL Server are performed through a special kind of procedure known as a system stored procedure. System stored procedures are created and stored in the master database and have the sp_ prefix. System stored procedures can be executed from any database without having to qualify the stored procedure name fully using the database name master.

It is strongly recommended that you do not create any stored procedures using sp_ as a prefix. SQL Server always looks for a stored procedure beginning with sp_ in this order:

1. The stored procedure in the master database.
2. The stored procedure based on any qualifiers provided (database name or owner).
3. The stored procedure using dbo as the owner, if one is not specified.

Therefore, although the user-created stored procedure prefixed with sp_ may exist in the current database, the master database is always checked first, even if the stored procedure is qualified with the database name.

Important If any user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.”

See that ? So it has the following disadvantages:

1. There is a little performance hit since the sql server processor needs to go through the above list before it arrives at your stored procedure
2. There may be a stored procedure in the master database by the same name as yours and hence your stored procedure will not execute
3. Say you named your stored procedure something generic like sp_BackupData. Microsoft may choose the same name in their future SQL Server release and then the disadvantage mentioned in #2 above will materialize

As a general guidance give your stored procedures meaningful names and if you have to add a prefix, add the prefix based on the functionality group that the SP belongs to. Example : CUST_GetCustomerDetails. We will cover SQL Server naming conventions in one of the future blog posts

Hopefully this was helpful.

Technorati Tags: SQL Server,Stored procedure,naming conventions

Typical problem

While working with a client, recently we had an issue where the web server was just getting unresponsive for no apparent reason. This server was an IIS box serving several websites and of course having a large number of SQL Server databases. When we looked at the server resources, we found SQL Server was occuping good 3GB of the available 4GB of RAM ! Huh ? Yes!

Obviously there was some leak somewhere, some query taking more resources and not releasing it. We had to investigate that, but had a situation where there are several non-responsive websites.

What are your options?

SQL Server 2008 (and older versions as well) allow you to specify the maximum memory utilized by the sql server process before it is recycled. Right-click on "Properties" for your SQL server through SQL Server Management Studio. Click on "Memory" and then put in the value that works for you in the "Maximum Server Memory (in MB)" option.
Note : The default value is 2147483647 MB which pretty much gives a free rein to SQL Server to do as it pleases. The minimum amount of memory you can specify for max server memory is 16 megabytes (MB).

NOTE : This is not a solution to your memory problems, but only the first step. Remember the SQL Server process will be recyled once the maximum memory limit is reached, and it will need to use resources to start everything it needs for the process to be recycled.