Single User Mode

Question

Single User Mode

SinisterPenguin

SSC Eights!

Points: 898
More actions
October 26, 2009 at 3:49 am

#212885

Hi - I have a re-ocurring problem where my Data Warehouse ETL process drops the database into Single User mode in a SPROC & then another process jumps on the Single connection before the next step in the SPROC.
The SPROC is then locked out of the database & my ETL falls over in a heap....
I have set AUTO_UPDATE_STATISTICS_ASYNC off which I thought had solved it but I still get the problem.
Has anyone got any ideas about other system processes which might be causing this??
There is nothing else connecting to the database during ETL & I'm getting fed up with using the DAC every few days to take it out of Single user mode & re-run ETL....
Thanks For any help with this!!

Viewing 10 posts - 1 through 9 (of 9 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

Activity Monitor connections are a favourite.

If you need exclusive access to a database (for some reason...?) the traditional approach is to change context to that database (with the USE [database_name] command) and then switch to single user mode specifying the WITH ROLLBACK [IMMEDIATE | AFTER n [SECONDS]] termination clause.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 2

Yep - this is exactly what I am doing

ALTER DATABASE AttendaDW

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE

DECLARE @FGName varchar(50)

DECLARE @SQLvarchar(200)

DECLARE FG_cursor CURSOR FOR

SELECT FileGroupName FROM maintenance.getRequiredOldFGs

OPEN FG_cursor

FETCH NEXT FROM FG_cursor INTO @FGName

WHILE (@@FETCH_STATUS <> -1)

BEGIN

IF (@@FETCH_STATUS <> -2)

DO STUFF HERE......

END

CLOSE FG_cursor

DEALLOCATE FG_cursor

ALTER DATABASE AttendaDW

SET MULTI_USER

WITH ROLLBACK IMMEDIATE

But ocassioanlly something jumps the connection before the Sproc can get back in after the ALTER DATABASE.....

The reason I am in single user mode is to set some ReadOnly Filegroups ReadWrite so I can add a few rows of data.

And Thank You for the reply!

ps. One Orange Chip Points: 29422 More actions · Answer 3

Can u disable TCP/IP and then put the database in single user mode?

Pradeep Singh

Paul White SSC Guru Points: 150467 More actions · Answer 4

Ok, but in which database is the stored procedure defined? AttendaDW? My guess would be not ;c)

The stored procedure needs to running in the context of the database in order for the SINGLE_USER trick to work.

You can use PRINT DB_NAME() inside the procedure if you are interested to see what the database context is at run time. Moving the procedure to master and prefixing it sp_ will allow you to call it from any database of course - the important benefit being that the procedure will run in the context of the current database context - so long as you EXECUTE dbo.sp_ProcName that is.

If you EXECUTE master.dbo.sp_ProcName, the database context will be master.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 5

Sproc is in the same database (AttendaDW) which is what makes in so damn annoying!! the next statement in the SPROC should just hop onto the connection & away I go (which is does do 9 out of 10 times).

I really thought I had it cracked with the ASYNC statistics as welll!

I though about wrapping it all in a transaction but I don't think you can put ALTER DATABASE into a transaction - GGrrrrr

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 6

I see where you a re coming from in disabling TCPIP but that's a step too far for me & it would affect other databases as well....

Paul White SSC Guru Points: 150467 More actions · Answer 7

Interesting - and no, ALTER DATABASE is not allowed within a transaction.

Are you sure you're not just encountering an error of some kind between the SINGLE_USER and MULTI_USER changes?

An error within the cursor 'stuff' would bomb the proc, leaving the db in single user mode. The next thing to use the database would then lock you out (as I say, Activity Monitor is often the culprit).

I would wrap the whole thing in a TRY...CATCH and include a SET MULTI_USER in the catch. In fact, I'd probably put the TRY...CATCH in an enclosing procedure, just so I could be sure to catch scope-terminating errors.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

SinisterPenguin SSC Eights! Points: 898 More actions · Answer 8

It does seem to be straight after it goes into single user mode something else beats the SPROC to the single connection but I like the idea of a TRY CATCH Block I think I will give that a punt.

Even if I can't trace the problem down if I can use TRY CATCH to catch the error & have another go that should fix the main issue of ETL failing.

Thanks!

Paul White SSC Guru Points: 150467 More actions · Answer 9

Good luck, and you're welcome of course.

edit: posting that, it's just occurred to me to ask you to check that your code doesn't switch context away from the target database during the time the database is in single user mode...I'm sure you've already checked, but still...

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi