I am trying to view the properties of a database but it fails in SSMS with below error in screenshot. SSMS database properties error

I searched online for solutions but none have worked. These include change db owner to sa. The server files all look ok and all other databases on the server are ok. I also ran DBCC CHECKDB and that is clear of errors.

I try running the below SQL but it is blocked by session id -2 with wait type LCK_M_S There are no other connections open on this database.

Do I have any other options than a SQL Service restart?

(SELECT SUM(CAST(df.size as float)) FROM sys.database_files AS df WHERE df.type in ( 0, 2, 4 ) ) AS [DbSize],
(SUM(a.total_pages) + (SELECT ISNULL(SUM(CAST(df.size as bigint)), 0) FROM sys.database_files AS df WHERE df.type = 2 )) AS [SpaceUsed]
sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id left join sys.internal_tables it on p.object_id = it.object_id
  • 1
    A blocking session_id of -2 means that there is an orphaned Distributed Transaction. However, I don't have any advice to your actual question of how to resolve the issue without restarting (unless you happen to have a good understanding of your distributed transactions & can track down the issue based on just that).
    – AMtwo
    Feb 11, 2022 at 13:57


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.