How to find a table is locked or not?

Question

How to find a table is locked or not?

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
November 13, 2012 at 11:30 am

#265301

How can you check if a table is getting locked or not? I ran sp_who2 and found no blocking.
Some process is running longer than normal. I see a non-clustered index on this particular table.
What are some of the other things I should look into? Please advise.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 8 posts - 1 through 7 (of 7 total)

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

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

You can check if there are any locks at all on a table by looking at the sys.dm_tran_locks table, but somehow I don't think that's the real question here.

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Hi,

May be something like this can help you

create table #locked

(spid int,dbid int,objid int,indid int,type varchar(10),resource varchar(10),mode char(3),[status] varchar(10))

insert into #locked

exec sp_lock

select l.spid, l.dbid,object_name(l.objid) [object],l.indid,l.type,l.resource,l.mode,l.status

from #locked l

Regards

IgorMi

Igor Micev,My blog: www.igormicev.com

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 3

GilaMonster (11/13/2012)
You can check if there are any locks at all on a table by looking at the sys.dm_tran_locks table, but somehow I don't think that's the real question here.

Thanks,

After further research, I have noticed that database is almost full, no free space. Also, I was thinking of setting up a maintenance plan like, update statistics, index re-org/rebuild, etc.

But I need to justify that this will improve performance.......any suggestions on this idea?

Thanks,

SueTons.

Regards,
SQLisAwe5oMe.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Justify it on the basis that no maintenance is just plain bad practice.

If the DB is nearly full, you need to grow the files.

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 5

GilaMonster (11/13/2012)
Justify it on the basis that no maintenance is just plain bad practice.
If the DB is nearly full, you need to grow the files.

Thanks Gail, I have increasted the autogrowth size.

SueTons.

Regards,
SQLisAwe5oMe.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Grow the files, not increase the autogrow increment.

Autogrow is for when the DBA has messed up and not noticed that the DB files are nearly full and need to be grown.

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

GilaMonster (11/13/2012)
Grow the files, not increase the autogrow increment.
Autogrow is for when the DBA has messed up and not noticed that the DB files are nearly full and need to be grown.

When you grow the files, you mean, increase the initial size?......

I have a data file on primary - 34 GB(Initial size)

Index - 16 GB (initial size)

Log - less than MB (initial size)

SueTons.

Regards,
SQLisAwe5oMe.