By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,524 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,524 IT Pros & Developers. It's quick & easy.

Set Single_User Timeout Error

P: n/a
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
Nov 21 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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.

Nov 21 '05 #2

P: n/a
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
""Peter Huang"" <v-******@online.microsoft.com> wrote in message
news:HU**************@cpmsftngxa10.phx.gbl...
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.

Nov 21 '05 #3

P: n/a
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
Nov 21 '05 #4

P: n/a
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.

Nov 21 '05 #5

P: n/a
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
""Peter Huang"" <v-******@online.microsoft.com> wrote in message
news:We****************@cpmsftngxa10.phx.gbl...
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.

Nov 21 '05 #6

P: n/a
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.

Nov 21 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.