Today when I clicked Properties of tempdb in SSMS, I got the error message below:
the last script showed in the pic upper was the problem script. It was blocked by other session, so the lock request timeout, then SSMS raised up the error message Property Size is not available for Database "[tempdb]"
Obviously it was a simple blocking issue. next step is trying to find out the blocking script. I reproduced the error message, at the same time, checking the blocking script
so the blocking session is 79, and wait resource is key 2:327680. I got the table and index name with the script below:
and session 79 had xlock on the key 2:327680
but why SSMS queried table sysrowsets when I clicked "properties" menu? by checking the timeout query, it will retrieve data from sys.partitions table, and sys.partitions table is a view based on sys.sysrowsets
as to why session 79 had xlock on sys.sysrowsets , it was running a very big transaction involving some temp tables and some complicated queries, and I can not change the code of that application, so the only way was waiting it finish, but at least I know the tempdb is fine and sql server is runnning well, I can totally ignore this error 🙂