DB taken offline by getspinlock pre-Sleep() error

We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
At 00:30 at random day intervals, databases on two different servers are
taken offline which causes jobs to be terminated and sessions left open. We
have obtained a trace log using DebugView which contains:
00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
We have no SQL jobs running at 00:30 so this looks like an internal SQL
maintenance job, possibly performance related?This problem was caused by Veritas Backup Exec trying to backup the database
files (which were obviously in use!). The scripts used to include the
SQLBackup folder but had been changed to include SQLData as well, so removing
SQLData from the script resolved the problem.
"David Grant" wrote:
> We are using SQL 2000 v8.00.194 on Server 2003 SP1 with 2GB RAM
> At 00:30 at random day intervals, databases on two different servers are
> taken offline which causes jobs to be terminated and sessions left open. We
> have obtained a trace log using DebugView which contains:
> 00571101 12:30:40 AM [516] 2006-09-21 00:30:40.15 getspinlock pre-Sleep():
> spid 8, 10000 yields on lock type "LOGFLUSHQ" (adr 0x1a406f7c)
>
> 00571105 12:30:47 AM [516] 2006-09-21 00:30:47.05 getspinlock pre-Sleep():
> spid 0, 10000 yields on lock type "SRVPROC" (adr 0x1a5fe3f8)
> We have no SQL jobs running at 00:30 so this looks like an internal SQL
> maintenance job, possibly performance related?
>|||David Grant wrote:
> This problem was caused by Veritas Backup Exec trying to backup the database
> files (which were obviously in use!). The scripts used to include the
> SQLBackup folder but had been changed to include SQLData as well, so removing
> SQLData from the script resolved the problem.
>
Veritas shouldn't have been able to take the databases offline like
this, the data files should have been "in use" by SQL at the time. The
only way I can picture this happening is if you have "Auto-Close"
enabled on your databases. If SQL "closed" the database, Veritas comes
along and starts backing up the data file, and SQL tries to "open" the
database again, it's going to fail. You shouldn't use the AutoClose or
AutoShrink options on a production database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||AutoClose is not enabled on any of our live or test systems. AutoShrink is
only enabled on our test systems.
"Tracy McKibben" wrote:
> David Grant wrote:
> > This problem was caused by Veritas Backup Exec trying to backup the database
> > files (which were obviously in use!). The scripts used to include the
> > SQLBackup folder but had been changed to include SQLData as well, so removing
> > SQLData from the script resolved the problem.
> >
> Veritas shouldn't have been able to take the databases offline like
> this, the data files should have been "in use" by SQL at the time. The
> only way I can picture this happening is if you have "Auto-Close"
> enabled on your databases. If SQL "closed" the database, Veritas comes
> along and starts backing up the data file, and SQL tries to "open" the
> database again, it's going to fail. You shouldn't use the AutoClose or
> AutoShrink options on a production database.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||I've seen similar things when the network admins were using
Veritas and VSS. I think it was along the lines that if you
have some activity or high activity in SQL Server, it can
prevent or interfere with the freeze I/O and then you can
get different errors. You may want to check the event logs
to see if you can find more information in there - look for
issues with VSS. The solution is to exclude the SQL Server
data directories. There might be some other configurations
in Veritas that impact this as well - don't know for sure.
-Sue
On Tue, 3 Oct 2006 06:11:02 -0700, David Grant
<DavidGrant@.discussions.microsoft.com> wrote:
>AutoClose is not enabled on any of our live or test systems. AutoShrink is
>only enabled on our test systems.
>"Tracy McKibben" wrote:
>> David Grant wrote:
>> > This problem was caused by Veritas Backup Exec trying to backup the database
>> > files (which were obviously in use!). The scripts used to include the
>> > SQLBackup folder but had been changed to include SQLData as well, so removing
>> > SQLData from the script resolved the problem.
>> >
>> Veritas shouldn't have been able to take the databases offline like
>> this, the data files should have been "in use" by SQL at the time. The
>> only way I can picture this happening is if you have "Auto-Close"
>> enabled on your databases. If SQL "closed" the database, Veritas comes
>> along and starts backing up the data file, and SQL tries to "open" the
>> database again, it's going to fail. You shouldn't use the AutoClose or
>> AutoShrink options on a production database.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com