Set Single_User Timeout Error

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

Hi -

I'm have a VB.NET Windows application that uses an MSDE database. I'm
trying to code backup, restore, and checkdb/repair functions.

Before each of these operations, I change the connection database to
"master" and then execute an "ALTER DATABASE SC01 SET SINGLE_USER"
statement. Very occasionally, this progression works fine. But most of the
time, I get a Timeout expired error on the set single-user command. I've
tried setting commandtimeout to 60, and I get the same result (it just takes
longer).

What am I doing wrong, and/or how can I get these functions to work
correctly and reliably??

My database name is 'SC01'. The connection string for conSCdata is "packet
size=4096;user id=sa;data source="(local)\InstName";persist security
info=True;initial catalog=SC01;password=SPassword" (and the connection is
open). I have already created SCBackupDevice using sp_addumpdevice.

Here's my code for the backup operation:

frmMain.conSCdata.ChangeDatabase("master")

strSQL = "ALTER DATABASE SC01 SET SINGLE_USER"
SQLcmd = New SqlCommand(strSQL, frmMain.conSCdata)
SQLcmd.ExecuteNonQuery()
SQLcmd = Nothing

strSQL = "BACKUP DATABASE SC01 TO SCBackupDevice"
SQLcmd = New SqlCommand(strSQL, frmMain.conSCdata)
SQLcmd.ExecuteNonQuery()
SQLcmd = Nothing

strSQL = "ALTER DATABASE SC01 SET MULTI_USER"
SQLcmd = New SqlCommand(strSQL, frmMain.conSCdata)
SQLcmd.ExecuteNonQuery()
SQLcmd = Nothing

frmMain.conSCdata.ChangeDatabase("SC01")


Thanks for your help.

- Jeff
 
Hi Jeff,

I think this may be caused by the database is using by other user. e.g. if
User A is updating the database, the Set Single_User will be blocked, it
the timeout is expired, the command will fail.

So far, I think you can try to increase the timeout value. But it is better
to do the database maintain job at the low acess time period, e.g. in the
mid night, there is no user access the database.

Or you may try to move the backup database issue into the database server
side, for this issue, you may try to post in the sql related group.

Hope this helps.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I don't think so, Peter -

This is happening on my development machine, and I'm the only user logged
on.

Any other ideas??

- Jeff
 
Jeff,

Just an idea, do you get this while debugging and did not close the
connection before stopping the debugger nice or even better, are you closing
your connections always in the right way?

Cor
 
Hi Jeff,

There are many possibility which may cause the database to block, multiple
user is one of the possibilities.
The database blocking issue is a complex problem.
You may take a look at the KB below to trouble INF: How to Monitor SQL
Server 2000 Blocking
http://support.microsoft.com/?id=271509

http://support.microsoft.com/?id=224453

Also I have reviewed the thread and find there is another similar issue in
the sql group, you may go and take a look.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
I believe I've resolved the problem by replacing the SET SINGLE_USER
statement with ALTER DATABASE SC01 SET SINGLE_USER WITH ROLLBACK IMMEDIATE,
as suggested by Adam Machanic in the microsoft.public.sqlserver.programming
group.

Thanks for trying, Peter. But, unlike Adam, you really gave me nothing
useful to try.

- Jeff
 
Hi Jeff,

I apologize for not giving a proper solution timely. The research I have
done is all on application level. It seems it will be very easier to
resolve on database level. ROLLBACK IMMEDIATE option breaks unqualified
connections immediately except the one issuing the ALTER DATABASE
statement. This makes the command won't be blocked by other connections.

Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top