SQL Server Actvity Monitor Error
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...
September 4th, 2015 6:28am
Can you check how much space is on system drive I mean C drive on the server
September 4th, 2015 6:43am
C drive has 64GB free space ...
September 4th, 2015 6:44am
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 ?
September 4th, 2015 7:20am
This is likely a tempdb issue. Activity monitor is unable to store the temp tables in tempdb. Please check your tempdb size and growth.
September 4th, 2015 7:55am
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 ...
September 4th, 2015 8:26am
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
drive
where the files are sitting has 50GB free
September 4th, 2015 8:30am
Can you uninstall SSMS and install it again. Seems something internally wrong with Activity Monitor
September 4th, 2015 8:31am
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...
September 4th, 2015 8:36am
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.
September 4th, 2015 8:40am
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 ...
September 4th, 2015 8:44am
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.
September 4th, 2015 8:48am
If you are well versed with
procmon use it to track the issue, its quite helpful
September 4th, 2015 8:50am
It's happening from within the server and also from a different machine SSMS where we have SQL 2014...
September 4th, 2015 9:04am
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
September 4th, 2015 9:05am
Are you running SSMS on the server?
September 4th, 2015 9:15am
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
September 4th, 2015 9:17am
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.
September 4th, 2015 9:26am
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...
September 4th, 2015 9:33am
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
September 4th, 2015 3:15pm
Here is the output of the command ...
September 5th, 2015 3:40am
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
September 6th, 2015 12:33am