question

Rob 2 avatar image
Rob 2 asked

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?

sql-serverssmslocking
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

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
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob 2 avatar image Rob 2 commented ·
The result was: Altiris SQL_Latin1_General_CP1_CI_AS 1 ONLINE
0 Likes 0 ·
Rob 2 avatar image
Rob 2 answered

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

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image sp_lock commented ·
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.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.