While working on one of the Notification services deployment I was getting following error message.
An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
SqlCommandText: CREATE PROCEDURE dbo.<procedure name>
Source: .Net SqlClient Data Provider
Number: -2
State: 0
Class: 11
Server: myServer
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Procedure:
Line Number: 0
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Timeout error for just creation of stored procedure? - I did check the stored procedure, it's T-SQL was 200 line long that; should not matter anyway. However I went ahead and ran the "CREATE PROCEDURE" code in query analyzer. I got created fine, just in slit seconds.
Did some search on web for "Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." - lot of forums were discussing about increasing timeout - command, connection everything, and some counter-argueing why increase timeout parametes its like "passing the buck to next developer " :) which is alo true. Anyway in my case I didn't had a way to change any parameters as NSControl.exe (the one which runs all the T-SQL scripts) is provided by MS and I am not aware of any way to set it. So we just gave a shot to reboot the server and it worked.
I did further research and found that this is a know issue with NSControl.exe. Here is the link http://support.microsoft.com/kb/870657. Per this article "problem occurs if the application databases for the instance of Notification Services are large", but in my case the databases are very small, like 50 subscriptions and 50 members in the list (ofcourse this was TEST environment). Anyway I have decided to apply the patch, with the hope that this issue will not occur again. If it occurs again, I will use my technique - "just reboot".
No comments:
Post a Comment