April 14, 2010 at 7:56 am
Hi Everyone,
We have a nightly job which stops the sql server service prior to an O/S backup taking place on the server. Last night the job abended for the second time in 6 months because the SQL Server service was stuck in the "stopping" status. I looked in the SQL Server error log and noticed the following errors which were also present the last time the job abended:
2010-04-14 00:30:52.95 spid56 Error: 18056, Severity: 20, State: 23.
2010-04-14 00:30:52.95 spid56 The client was unable to reuse a session with SPID 56, which had been reset for connection pooling. The failure ID is 23. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2010-04-14 00:35:49.50 spid11s Timeout occurred while waiting for latch: class 'SERVICE_BROKER_TRANSMISSION_WORKTABLE', id 0000000003D51620, type 4, Task 0x0000000005E68988 : 0, waittime 300, flags 0x1a, owning task 0x0000000007904BC8. Continuing to wait.
The first two errors seem to occur every night so I don't think they are the cause. I believe they are simply the result of the service trying to stop and new connections trying to come in. The last error which references the 'SERVICE_BROKER_TRANSMISSION_WORKTABLE' is the one that concerns me. On a night when the job runs successfully.. the following message appears in the log after the first two errors:
2010-04-12 00:31:01.31 spid13s Service Broker manager has shut down.
This leads me to believe that the service broker is failing to shut down. We don't use the service broker at all so I wonder if I should just turn off the broker in every database. Any help would be appreciated...Thanks!
April 14, 2010 at 8:15 am
I know this isn't quite what you were talking about but it might fit the bill:
http://support.microsoft.com/kb/940286
Also, the backup method you are using is not typical. In most cases the SQL Server is never brought down and database backups are handled through maintenance plans.
Why don't you tell us about your backup and maintenance methodology.
CEWII
April 14, 2010 at 8:23 am
speja01 (4/14/2010)
We have a nightly job which stops the sql server service prior to an O/S backup taking place on the server.
I agree with Elliot, this is highly unusual in a SQL Server environment.
Backup strategy looks like old Oracle's cold-backup strategy, no reason to do that on a SQL Server environment where you can safely take a consistent full backup at the time of your choice.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply