Recovering from resource problems

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris Wood - 26 Mar 2008 15:22 GMT

Hi,

Yesterday we had some 701 resource messages on one of our production
servers. This was followed by a number of Downgrading backup log buffers
from 1024K to 64K messages in our backup jobs. This is an SQL2005 build 3159
server on W2K3 SP2. We had similar problems last year with an SQL2000 server
and we found that a reboot or stop/start SQL appeared the only way to stop
the problem. We did this last night on this server.

Is this the best way on SQL2005 or will it recover from its resource
problems without a reboot or stop/start SQL?

Thanks

Chris

Reply to this Message

Jason Massie - 26 Mar 2008 16:10 GMT

It is hard to say if this would address the specific problem but this is
about as close as you can get w/o a restart:
dbcc dropcleanbuffers
DBCC FREESYSTEMCACHE ( 'ALL' ) WITH MARK_IN_USE_FOR_REMOVAL
exec sp_msforeachdb 'alter ? set single_user with rollback immediate'
exec sp_msforeachdb 'alter ? set multi_user with rollback immediate'

Be careful. I'd use this like a hail mary with 2 seconds left.

Signature

Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio

> Hi,
>
[quoted text clipped - 11 lines]
>
> Chris

Reply to this Message

Chris Wood - 26 Mar 2008 18:17 GMT

That is pretty harsh!!! I was hoping that SQL2005 was more robust than
SQL2000.

Thanks

Chris

> It is hard to say if this would address the specific problem but this is
> about as close as you can get w/o a restart:
[quoted text clipped - 20 lines]
>>
>> Chris

Reply to this Message