Recovering from resource problems

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
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.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
> 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
>
|||That is pretty harsh!!! I was hoping that SQL2005 was more robust than
SQL2000.
Thanks
Chris
"Jason Massie" <jason**R3move**@.statisticsio.com> wrote in message
news:ud9ZVO1jIHA.4940@.TK2MSFTNGP02.phx.gbl...
> 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.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:O$Ta6z0jIHA.5084@.TK2MSFTNGP04.phx.gbl...
>
sql