x

Opening Database properties in SSMS fails with lock

I try to open the database properties window using SSMS but it fails with a lock request time-out. Other databases on the same server work normaly.

I have counted the number of locks using the sys.dm_tran_locks; dm-view and it returned 2,741,636, there are no deadlocks or any other problems in that database.

I ran a databse check and the datase is fine.

My question is why is the SMSS database properties window failing an a lock request while de dynamic databse views work properly. Can an application prevent SSMS being used?

more ▼

asked Dec 08, 2009 at 11:52 AM in Default

Rob 2 gravatar image

Rob 2
57 3 3 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

It sounds like some kind of modification was being done to the database itself and you were unable to get a lock on it while that was occurring. Once it cleared you were then able to lock. Do you have other users with administrative privileges on your system? How about automated process that might make changes to the database?

Also, you're not going to see "deadlocks" as a blocking process. A deadlock occurs pretty much instantaneously (except for the rollback on the victim) when SQL Server identifies the situation. Don't confuse blocks and deadlocks. They're not the same.

more ▼

answered Dec 08, 2009 at 12:55 PM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

I have run into this before if someone has locks on the database itself, such as making modifications to the database state.
Dec 08, 2009 at 06:57 PM TimothyAWiseman
I think youre right, although there are no other dba's, there was probaly a process running doing things to the databse unknown to me.
Dec 16, 2009 at 05:18 AM Rob 2
(comments are locked)
10|1200 characters needed characters left

Are you create a new query in SSMS and review the properties?

SELECT name,  DATABASEPROPERTYEX(name, 'Collation'),  DATABASEPROPERTYEX(name, 'IsAutoUpdateStatistics'),  DATABASEPROPERTYEX(name, 'Status') FROM master.dbo.sysdatabases 
more ▼

answered Dec 08, 2009 at 12:08 PM

sp_lock gravatar image

sp_lock
9.2k 24 28 31

The result was: Altiris SQL_Latin1_General_CP1_CI_AS 1 ONLINE
Dec 08, 2009 at 12:16 PM Rob 2
(comments are locked)
10|1200 characters needed characters left

The problem is gone, the number of locks is now down to about 90 and SSMS is working fine again. Any thoughts?

more ▼

answered Dec 08, 2009 at 12:18 PM

Rob 2 gravatar image

Rob 2
57 3 3 3

I have had this issue if CPU was high therefore serving the request for the db properties were delayed,it resulted in a time-out.
Dec 08, 2009 at 12:25 PM sp_lock
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x343
x125
x38

asked: Dec 08, 2009 at 11:52 AM

Seen: 1831 times

Last Updated: Dec 08, 2009 at 11:59 AM