I have C# application that uses SQLDependency
to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the production server the server crashed. I can not be absolutely sure that it is this because we picked it up ~10 minutes after I ran the application.
From what I understand from how the SQLDependency
works it uses stored procedures to interact with the service broker.
The errors I received
SQL Server must shut down in order to recover a database (database ID 1). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.
Could not find an entry for table or index with partition ID 428869310480384 in database 2. This error can occur if a stored procedure references a dropped table, or metadata is corrupted. Drop and re-create the stored procedure, or execute DBCC CHECKDB.
We are running SQL Server 2005 SP1.
Edit:(removed code the link explains it better) I followed this example and mainly changed the query and what I do with the data.
http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/