SINGLE_USER mode error

Question

SINGLE_USER mode error

markoz.job

SSC-Addicted

Points: 478
More actions
December 5, 2014 at 5:37 am

#313643

Hi Experts,
I've executed this script on our production SQL 2008 sp3
Database changed state to SINGLE_USER but second step failed with error:
Cannot backup log, database 'test' is already open and can only have one user at a time.
Question:
Could you explain me why it failed ?
According to may knowledge it should disallow access to this DBbut not for this session.
I've executed this script before on test server and worked without any errors.
USE [master]
GO
ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
go
BACKUP LOG [test] TO DISK = N'path\test_TLOG_tail.trn'
WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'test-tail', SKIP, NOREWIND, NOUNLOAD, NORECOVERY ,
STATS = 10
GO

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

You set the database to single user and someone else got the single allowed connection before the log backup did. The connection you ran that from wasn't using that database (it was using master) and so it wasn't made the single allowed connection.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 2

If you ran SSMS to connect, the Object Explorer might have taken the single connection and prevented this from working.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Just as a matter of interest, why are you switching to single user mode? It's not necessary under normal circumstances to do so before taking a transaction log backup.

John

markoz.job SSC-Addicted Points: 478 More actions · Answer 4

Hi Gail,

Thank you very much for answer,

but I've tried to reproduce your scenario

1)From one session

USE [master]

GO

ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

2)From second session

use [test]

Msg 924, Level 14, State 1, Line 1

Database 'test' is already open and can only have one user at a time.

So it is a behaviour as I expected

Maybe this is problem with SSMS as Steve wrote

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

John Mitchell-245523 (12/5/2014)
Just as a matter of interest, why are you switching to single user mode? It's not necessary under normal circumstances to do so before taking a transaction log backup.

Because he's doing a log backup with NORECOVERY, which requires that no one else is using the DB, as it takes the DB into the RESTORING state.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

John Mitchell-245523

SSC Guru

Points: 148809

December 5, 2014 at 7:28 am

#1762958

Ah yes - didn't notice that! Thanks, Gail.

John

markoz.job SSC-Addicted Points: 478 More actions · Answer 7

Hi John

I know that it is not necessary but i need that for further processing

I've got system 7/24 and need to move some databases to new storage.

So I

1)Restore copy of database [xxx_copy] with standby on new storage

2)Backup tail log on [xxx]

3)restore tail log on [xxx_copy]

4)restore with recovery both xxx and xxx_copy

5)rename xxx to xxx_old and xxx_copy to xxx

Between point 4 and 5 i need single mode which prevents from inserting new data into xxx.

markoz.job SSC-Addicted Points: 478 More actions · Answer 8

Hi,

But object explorer uses different connection so it shouldn't take this one session.

Cheers,

Martin

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 9

I've usually found single_user to problematic. That's why I prefer restricted_user. But then, you have to have had your apps set up correctly. If everyone is 'sa' or 'dbo' (or both), it won't work. However, everyone shouldn't have that kind of access.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Steve Jones - SSC Editor SSC Guru Points: 738663 More actions · Answer 10

markoz.job (12/5/2014)
Hi,
But object explorer uses different connection so it shouldn't take this one session.
Cheers,
Martin

It depends.

Object Explorer has a connection to the server instance. If the connection has a context of that database, then when you switch to single_user in your other connection (from masteR) , you won't be able to run the command from a second window.

markoz.job SSC-Addicted Points: 478 More actions · Answer 11

Hi All,

Thank you very much for you answers

The conclusion is as someone write in this forum 'that there is no guarantee that in between the execution of the alter database statement (putting it in single use) and the next statement, that another person or process can grab the only process'

I'll use restricted access instead

Cheers,

Martin