Opening Database properties in SSMS fails with lock

Question

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?

Answer 1

0

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.

answered Dec 08 '09 at 12:55 PM

Grant Fritchey ♦♦
64.9k ● 13 ● 20 ● 66

I have run into this before if someone has locks on the database itself, such as making modifications to the database state.

Dec 08 '09 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 '09 at 05:18 AM Rob 2

(comments are locked)

Answer 2

0

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

answered Dec 08 '09 at 12:08 PM

sp_lock
8.2k ● 20 ● 26 ● 29

The result was: Altiris SQL_Latin1_General_CP1_CI_AS 1 ONLINE

Dec 08 '09 at 12:16 PM Rob 2

(comments are locked)

Answer 3

0

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

answered Dec 08 '09 at 12:18 PM

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 '09 at 12:25 PM sp_lock

(comments are locked)

Opening Database properties in SSMS fails with lock

Follow this question

Related Questions