SQL Server is waiting for an internal operation to complete
When saving a database diagram, SQL Server 2005 frequently "hangs" for an indeteminate amount of time and 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 one thing, this is an extremely lame and unhelpful message, as it gives no clue as to specifically *who* to report the problem to (Microsoft is pretty big, after all), and an "internal operation" is sufficiently ambiguous to mean anything.
I cannot find any references to this problem, and it happens frequently enough that I'm finding it very difficult to use Management Studio for editing diagrams. When this problem occurs, the "hang" sometimes last for several minutes, but frequently last longer than 20 minutes, after which I simply kill the process in Task Manager.
I have also experienced the problem when simply saving table modifications, though less frequently than with diagram edits.
Does anyone know what causes this, or have any resolution for the problem?
Thanks!
[1179 byte] By [
Zoomy8] at [2007-12-23]
Obviously, while saving diagram, you change database schema, and while changing a schema, SQL Server aquires Schema Modification Lock which is not compatible with any other lock, so your process just waits when any other locks will be released. Normally, there should be no longtime locks, but they are. So, you need just identify processes, which hold the locks, and kill them (generally speaking, much better is to determine the cause of these longtime locks and eliminate it). To identify concurrent processes you may use sp_who and sp_who2 sp's.
Thanks for the reply. I don't understand how locks could be the problem, though, because I'm working with a SQL Server machine on which I'm the only user/login, and I don't have any other processes that could be locking any portion of the database.
The only possibility (to do with locks) that I can think of is this:
What happens if I concurrently have the table editor (right-click on the table and choose "Modify") open on a given table *when* I save a diagram (of which the table is a part)? Is there some kind of locking mechanism at work in this case? If so, it seems that SQL Server should be sophisticated enough to detect that the schema is being modified by the *same* user, and allow the most recent action to take precedence. At the very least, it could display a dialog saying something like "Please close the table editor(s) you've opened before saving your diagram". That would be much more acceptable than an indeterminate "hang".
Thanks for the reply and any further ideas!
Zoomy
>>If so, it seems that SQL Server should be sophisticated enough to detect that the schema is being modified by the *same* user
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. This is what your scenario is looks like. Anyway, you can simply check it.
>> At the very least, it could display a dialog saying something like "Please close the table editor(s) you've opened before saving your diagram". That would be much more acceptable than an indeterminate "hang".
Well, try to understand, that Management Studio just sends queries to the server as any other application does, and just waits for the response from the server. But the cause of slowly running query can be much wider than simple lock problem, though this may be obvoius is some cases.
I am having the same problem as the OP in this thread. I have run sp_who and sp_who2. There are no connections to the sql server database that are not sleeping and yet Management Studio reports still that it is waiting for an internal operation to complete. I could be wrong but, based on the results from sp_who and sp_who2, I think that the message is correct; it's a Managment Studion internal operation and not a database access or locking problem.
I opened another instance of Management Studio and checked to see if my changes had actually been saved. The table and relationship changes are all saved but the diagram changes are not saved - the new tables I had created within the diagram exist in the table but do not show up in the diagram in the newly opened instance.
In the work on the current database I am creating, this has happened two times. Both times represented the only two times in the database when I had created a bit column and assigned a default value of 0 to the column. If there's a connection, I don't know.
I am hesitant to end-task on the hung Management Studio instance because I don't want to leave my database in an unknown or corrupted state.
Below is the output from sp_who2 with my login, servername, database name changed but otherwise unedited.
Thanks for any suggestions,
Dale Preston
MCAD C#, MCSE, MCDBA
SP_WHO2 OUTPUT:
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID
1 BACKGROUND sa . . NULL RESOURCE MONITOR 20 0 01/29 18:30:41 1 0
2 BACKGROUND sa . . NULL LAZY WRITER 590 0 01/29 18:30:41 2 0
3 SUSPENDED sa . . NULL LOG WRITER 10 0 01/29 18:30:41 3 0
4 BACKGROUND sa . . NULL LOCK MONITOR 130 0 01/29 18:30:41 4 0
5 BACKGROUND sa . . master SIGNAL HANDLER 0 0 01/29 18:30:41 5 0
6 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 6 0
7 BACKGROUND sa . . master TRACE QUEUE TASK 50 0 01/29 18:30:41 7 0
8 sleeping sa . . NULL UNKNOWN TOKEN 0 0 01/29 18:30:41 8 0
9 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 9 0
10 BACKGROUND sa . . master TASK MANAGER 0 0 01/29 18:30:41 10 0
11 SUSPENDED sa . . master CHECKPOINT 130 81 01/29 18:30:41 11 0
12 BACKGROUND sa . . master BRKR EVENT HNDLR 0 33 01/29 18:30:41 12 0
13 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 13 0
14 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 14 0
15 BACKGROUND sa . . master BRKR TASK 0 0 01/29 18:30:41 15 0
17 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 17 0
18 BACKGROUND sa . . master BRKR TASK 0 0 01/29 18:30:41 18 0
19 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 19 0
20 sleeping sa . . master TASK MANAGER 0 0 01/29 18:30:41 20 0
51 sleeping NT AUTHORITY\SYSTEM MyPCName . ReportServer$SQL2K5 AWAITING COMMAND 0 0 01/31 10:32:39 Report Server 51 0
52 sleeping MYDOMAIN\mylogin MyPCName . master AWAITING COMMAND 30826 7 01/31 10:09:40 Microsoft SQL Server Management Studio 52 0
53 sleeping MYDOMAIN\mylogin MyPCName . master AWAITING COMMAND 10 0 01/31 10:22:52 Microsoft SQL Server Management Studio 53 0
54 sleeping NT AUTHORITY\SYSTEM MyPCName . ReportServer$SQL2K5 AWAITING COMMAND 0 0 01/31 10:33:49 Report Server 54 0
55 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 1121 22 01/31 10:09:40 Microsoft SQL Server Management Studio 55 0
56 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:28:44 Microsoft SQL Server Management Studio - Query 56 0
57 RUNNABLE MYDOMAIN\mylogin MyPCName . master SELECT INTO 161 14 01/31 10:24:23 Microsoft SQL Server Management Studio - Query 57 0
58 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:28:47 Microsoft SQL Server Management Studio - Query 58 0
59 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:28:56 Microsoft SQL Server Management Studio - Query 59 0
61 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:28:57 Microsoft SQL Server Management Studio - Query 61 0
62 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:29:05 Microsoft SQL Server Management Studio - Query 62 0
64 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:29:10 Microsoft SQL Server Management Studio - Query 64 0
66 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:29:26 Microsoft SQL Server Management Studio - Query 66 0
68 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:29:33 Microsoft SQL Server Management Studio - Query 68 0
70 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:29:39 Microsoft SQL Server Management Studio - Query 70 0
72 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:30:02 Microsoft SQL Server Management Studio 72 0
73 sleeping MYDOMAIN\mylogin MyPCName . MyDatabaseName AWAITING COMMAND 0 0 01/31 09:30:02 Microsoft SQL Server Management Studio - Query 73 0
If the table editor and the database diagram are connected to the same database using the same credentials, then they share state. Any changes made in the table designer are reflected in the database diagram and vice-versa. Saving either the table designer or the database designer, saves all the changes made in either designer .
If the table designer and database diagram are open on different connections or on different client machines, there is no such sharing and changes made in one designer can collide with the other. The designers work by making the database state look like their internal model. So if you open a designer on Machine A and then open another designer on Machine B, make different changes on each machine, and then save on Machine A, when you save on Machine B, the designer on Machine B will effectively undo the work on Machine A.
Neither designer keeps locks on the database while they are open. It's only when you are saving changes that any locks are taken, and then only for as long as the operations are running.
FWIW, I don't think the hanging issue has anything to do with server-side operations. I suspect this is actually an issue with window management inside the management studio shell. I haven't seen this issue since I installed Visual Studio SP1 on my machine. (Management Studio and Visual Studio share infrastructure, so VS SP1 does affect SSMS.)
Hope this helps,
Steve
You can report the defect with saving the diagram hanging SSMS on the http://connect.microsoft.com/sqlserver website. Defects reported there go directly into our internal defect tracking system so we don't lose any information.
Thanks,
Steve
Thanks, Steven. I submitted the issue on the connect site coincident with posting here.
I did not have a table editor open separately from the diagram editor. The table changes were being made within the diagram editor so I don't think that has anything to do with it.
Because you haven't seen this since installing VS2005 SP1, does that mean you did see it before SP1? I was unable to find a list of fixes included in SP1 to see if this was listed there. That said, I'll update anyway and hope it works. Since the problem is pretty sporadic, it could be a long time before I know for sure.
Thanks again,
Dale
I forgot to add one thing here that I updated the connect site with. I may have a workaround that un-sticks the problem. I can't be sure unless I see the problem a few more times to test but:
Both times that Management Studio locked up, I was going to force the program closed because clicking the X failed to close the program. To force the program to close, I right-clicked on the taskbar button for the stuck app. As sometimes happens when right-clicking stuck programs, the context menu popped up and then instantly closed. The instant that happened, then the application closed in one instance. Remember I had clicked the X so closing was expected behavior. In the other instance, I had un-saved query windows open and, instead of closing, I got the file save dialog. Again, I had clicked the X and had un-saved query windows open. Displaying the file save dialog was the expected behavior.
So, it seems that right-clicking on the task bar button a time or two may be a way to unstick the program. It might also be a clue for anyone troubleshooting the problem.
HTH,
Dale
I had this exact problem. Right clicking on the start bar button a couple times solved my occurance, too.
Odd.
Ditto! Google "Management Studio Hang Diagram" to get to this thread...
Right click on Management Studio in the task bar a few times and it comes back.
I love Management Studio over the old Enterprise Manager, but it's a shame:
i. it's not able to open SQL Server 7.0. Again following the Microsoft policy of shafting anything to do with backwards compatibility (.Net 1.1 compilation in VS 2005 anyone?)
ii. it's not able to open SQL Server 2000 diagrams
iii. Not quite as stable as it could be.
-- gripe end.
Dan.
Men, I was worried for a second, this thread do is a life saver I thought my work was doomed but then again thanks to you all (b'.')b it came out nice. Thanks
P.S. Too bad for MSSMES, microsoft still has a long way to go.
