Getting SQL Server 2005 Database out of Single-User Mode

Question

Post reply

Getting SQL Server 2005 Database out of Single-User Mode

Viewing 5 posts - 16 through 19 (of 19 total)

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

jeroen.staal SSC Journeyman Points: 76 More actions · Answer 1

Same here. Late at night after much troubleshooting, you don't always think of the obvious anymore.

Thanks!!

woodyb42 Valued Member Points: 59 More actions · Answer 2

I'm having an issue where I need a batch process to blow out a bunch of calculated tables and then refill them with updated results. The process worked fine on our dev boxes but on our server I keep getting caught with a lock on the table I'm trying insert into. I've tried setting the single user before executing it, but I still get the lock error despite it being able to do the numerous selects needed in order to get to the insert statement.

My only other thought is that to speed thing up we drop the table and recreate it each time without the indexes and then add the indexes at the end so we don't have to pay the price when inserting in 2-4 million rows.

Here is the error we get (Dropping Calculated Resets is a print from where we drop the table):

Dropping Calculated Resets

Msg 1204, Level 19, State 4, Line 99

The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions.

Any thoughts on how to get past this

vkingston SSC Veteran Points: 222 More actions · Answer 3

What happens when that sneaky connection is from a system process?

We use a 3rd party backup tool, which doesn't give me an inline script to put the whole thing in one session/batch. After putting the DB in single user mode, before kicking off the restore from 3rd party software, I lost my one connection to a system process, which for some reason kept hopping between different Systems SPIDs

My resolution was to Drop the DB with "Close existing connections" and then start a full db restore!!

Thoughts/comments?

vkingston SSC Veteran Points: 222 More actions · Answer 4

@woodyb42...

Your question appears to be in a wrong thread...

Typically "unable to get a LOCK" means, you're low on Memory. If you're in 64 bit, increase the RAM in the server and allocate more to SQL. If you're in 32 bit, try to use 3GB switch and allocate more to SQL. If you don't have these options, identify the other active sessions which need locks and try to fit in your query in a more quieter window of time.

larsa23 SSC Rookie Points: 37 More actions · Answer 5

Brilliant, this really saved my day!

While trying to find a good and simple solution how to rename a production database,

including both the logical name and the physical files, I stuck on this single user

"looking" problem.

Kindly,

Lars-Åke Åkesson, developer_Umeå, Sweden