purposefully lock a database

[divinyl]

hi guys n gals

does anyone know how to purposefully lock a database (say northwind or adventureworks) as i've come across a great looking sproc that returns loads of info on blocking processes on a given database - but i cant test it without db locks!!

If anyone knows how to do this please let me know!

Thanks,
Div

 

[UpstateNYAdmin]

You can set the database to single user mode in SQL EM. Just right-click on the database and select Properties.
Goto the options tab. Under Access, check Restrict Access and choose the single user radio button.

 

[SQLDenis]

That won't create locks, that will lock out users
You have to lock a table by using XLOCK table hints

open up query analyzer
in 1 window type

SELECT au_lname FROM authors WITH (XLOCK)

in another window type the same
execute both queries
now open up a 3rd window and run your proc

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mrdenny]

You can do it without the second window.

--Window 1
begin tran
select * from table with (xlock)

Then run your code in another window. Until you type and run commit in the first window you'll see the locks.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com