At first I thought the database has size problem, so I extended the file size of tempdb, but I was still not able to see the properties of tempdb, and I confirmed that the there was enough free space in tempdb.
Then I checked the sql errorlog file, there was no error message. Sql server looks good.
Then I run profiler trace, and monitor the whole process when I clicked "Properties" menu. unfortunately, there was still no error message regarding tempdb.
I thought it might be a client problem, not sql server problem, so I enabled the "User Error Message", this time I got an error message: "Lock request time out period exceed"
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 :)
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 :)
