SQL Server is waiting for an internal operation to complete, message on SQL Server Management Studio
Interesting popup message on the down-right corner of my laptop when I'm performing SQL Server tasks against a 2005 version database using SQL Server Management Studio (SSMS), displays the message "Microsoft SQL Server Management Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, pelase report this problem to Microsoft."
For a moment I was stumbled to see that message and I can see the SSMS is hung and awaiting to display results, what I was doing is to create a table and add 3 columns, then save it. While saving the table the SSMS window went blank and came to the popup. I was waiting for a minute to disapper that message and expecting to get back to SSMS window, no luck.
The table I'm creating is a small table on a small database, not used by any other application. It gave me thought about locking during this process as by default SQL Server handles concurrency between connections, not between users. So, if there are two parallel connections, which try to obtain incompatible locks on the same resources, under the same login or not, one will be blocked utill another completes transaction and releases the locks. But at that point of time I was the only user against that database. Further I have checked under SQL Server error log for more reference, nothing found.
Interestingly I have found a MSDN forum post that refer this problem is occurring whenever a database diagram is used and bug has been submitted to Connect Site, for the information.
After 3 minutes the SSMS has responded and I can see the newly created table, eventually this message was persistent whenever I wanted to run a query or view any property of SQL Server instance, old to back drawing board!
To reproduce the same issue I have tried to access the same SQL instance from another machine, where no issues reported with a trials such as opening nearly 10 windows of query editor and running [select * from table] simultaneously. So I suspect something wrong with my laptop causing this popup and slow response of SQL Server Management Studio, also you will see such occurrence when you are trying a connection to SQL Server instance with a RDP connection. As the total process is controlled from your laptop to that remote connection, a definite network contention is expected.
So I have closed all other applications on my laptop and using only SSMS to perform similar changes, 4 out of 10 times this message has appeared and causing slow performance of my process. To update more to the information I have latest service pack which is Service Pack2 for SQL Server 2005 on this client's installation, eventually I found that anti-virus program has been using lot of memory on my laptop (configuration is 2GB RAM Intel Centrino DUO) and I have stopped and restarted the Anti-Virus services to clear up the memory usage, this has cleared the initial problem with Management Studio and able to work without any further delays.