2008 R2(SP1) - 10.50.2500.0 (X64)
I am having the following problem intermittently on my development machine. I am running some T-SQL in a FOR loop. During each loop iteration, first we delete all rows in a table and then we add some rows to it (we are talking thousands of rows so nothing extraordinary). Then we query the rows in this table a few times and store the results in a table variable.
Sometimes (quite often) the process appears to completely hang. I cannot kill the sp_id. DBCC OPENTRAN indicates that we are stuck on an INSERT. After much research I now see the following in sys.sysprocesses:
Two different spids both attached to the database I am working on.
One of these is my T-SQL statement, waiting on 'WRITELOG', status is 'suspended' and cmd is 'INSERT'. The other is waiting on 'WRITELOG', status is 'background' and cmd is 'GHOST CLEANUP'.
As I requery sys.sysprocesses the wattime value is changing so I am not sure if things are actually being written to disk or not.
I am no DBA, but this seems to be quite an advanced topic and has had me frustrated for a few days now. It is interesting to note that I am only seeing this on my development machine at the moment so I am wondering if this has something to do with my hardware and the fact that we are inserting/deleting so quickly...
Any help appreciated...
-UPDATE-- So I realised that as the problem table is really just for staging that I could use some TVF and table variables. This seems to have stopped the problem occurring and has a nice performance boost side effect. I am still intrigued as to what the problem is though and wonder if it could reoccur anyway if SQL Server starts to make use of tempdb (as I said, I am no DBA, but http://msdn.microsoft.com/en-us/library/ms190768.aspx seems to indicate that tempdb could be used for table variables)