Hello Guys,

I am facing some strange issue with activity monitor in SQL Server .. when I try to open actvity monitor it's giving me below error, I verfied the disk space it has 50GB free space and also the tempdb files are configured with AutoGrow unlimited option and tempdb is of 160GB which has 159GB free space. I ran a trace and found it's created some tables and procedures to collect the stats when you open the activity monitor and I didn't find any error.. I tried to open the actvity monitor connecting from different SQL Server SSMS on another server still the same error.. 



Let me know your inputs on this... 

Can you check how much space is on system drive I mean C drive on the server

C drive has 64GB free space ...

Ok what is output of

select @@Version

Are you connecting to DB with same version of SSMS. Like if you have 2008 r2 are you connecting with 2008 r2 SSMS or express 2008 r2 SSMS ?

This is likely a tempdb issue.  Activity monitor is unable to store the temp tables in tempdb.  Please check your tempdb size and growth.

version of sql in problem : SQL 2012 x64, Enterprise

windows : Windows Server 2012 R2 x64, Enterprise...

I tried using activity monitor by connected to SQL 2014 SSMS(Enterprise x64) on the other server .. no luck ...

I can see the temp tables and temp procedures are created when I open the activity monitor.. Tempdb is of 160GB which has 159Gb unallocated space ... Assume if it's tempdb space issue.. tempdb files are configured with Unlimited autogrowth option and the drivewhere the files are sitting has 50GB free

Can you uninstall SSMS and install it again. Seems something internally wrong with Activity Monitor

I may consider to do a fresh installtion of tools from the scratch as a last option.. since this server is a production server.. and more over this issue is not stopping the business .. but before I do that, I wanted to know what's causing this issue... 

Also, check disk space on your SSMS client machine.  SSMS uses generally uses temp files to cache data, although I don't know if Activity Monitor does this.

drive C has 64GB free space on the server, if it's uses binary locations on C drive .. Other drive where the database files hosted is having 50GB free space ... 

I am confused. 

Is this only happening on the server itself when you use Activity monitor, or any connection to the server using SSMS (on a PC)?

Please post the actual results of SELECT @@VERSION.

If you are well versed with procmon use it to track the issue, its quite helpful

It's happening from within the server and also from a different machine SSMS where we have SQL 2014...

I tried using procmon.. didn't help .. also if the tools are the issue it should connect when I try to open it from different PC where I have SQL 2014 as I said

Are you running SSMS on the server?

Yes.. tried from two different versions of SSMS's  one is on the server which is SSMS 2012 and from a different PC which is SSMS's 2014 

Hi, are tempdb and the pagefile using the same drive?  this could be an issue that both tempdb and the pagefile could be competing for resource and tempdb is losing out.

Tempdb and pagefile is on different drives ...  Highlighted drive E is the one where the database files are hosted and drive D is dedicated for page file...

Can you run this command in powershell window and let us know what output you got?

Get-WmiObject -Query "SELECT NumberOfLogicalProcessors from Win32_ComputerSystem"

Thanks

Sethu Srinivasan [MSFT]

SQL Server

Here is the output of the command ... 

Thanks Suresh,

I am following up with WMI team to understand the root cause of this  issue. I shall keep you posted in next update

Thanks

Sethu Srinivasan [MSFT]

SQL Server