I am trying to view the properties of a database but it fails in SSMS with below error in screenshot.
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
(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]
FROM
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
-2means 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).