Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqlvijay
Starting Member
16 Posts |
 Posted - 2009-06-03 : 11:25:33
|
| Dear Sql Experts, I am new to sql server. we are using sql server 2005 .How can i identify which sessions are having blocking locks and how to kill the blocking session.Is there any script like oracle to find blocking locks in the database and to kill it. I have many the shared locks in my database(production).please anybody know how to kill the blocking sessions.Also I have saw the disk is currupt or unusable plesae run chkdsk utility in the event log.Also in my database maintence plan is running daily.In that the following task are running daily as a job 1.check db 2.shrink db if it exceeds 50mb3.reoganise all tables/indexes 4rebuild all tables/indexes5.clear old history the database is in simple recovery model.Also autoshrink option is disabled.please let me how to release all shared locks and Also is it advisable to run the job daily or weekly?How to confirm there is no lock in the database?please share your ideas Thanks in Advance Vijay.S |
|
|
shaunc
Starting Member
28 Posts |
Posted - 2009-06-03 : 15:52:03
|
| Running sp_who2 is probably the easiest way to look for blocked/blocking processes. If you find one blocking, you can run DBCC INPUTBUFFER(spid#) to see part of the query it's executing. The system DMVs are also a good way to gather info about blocking. If I'm seeing contention I tend to runSELECT * FROM sys.dm_os_waiting_tasks WHERE session_id > 50 |
 |
|
|
|
|
|
sql server 2005 blocking locks