Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

PROBLEM sql server management studio 2008 sucks up with memory yielding a .net runtime error. It crashes with an empty message in an error-dialog but there can be found a little eventlog entry, reading:

<EventData>
  <Data>.NET Runtime version 2.0.50727.5446 - Fatal error in the execution engine (7269AECA) (0).</Data> 
</EventData>

Original Eventlog Entry (German):

<EventData>
  <Data>.NET Runtime version 2.0.50727.5446 - Schwerwiegender Fehler im Ausführungsmodul (7269AECA) (0).</Data> 
</EventData>

RECREATING THE ERROR

After about every 10 Million-th Insert-Statement Management Studio 2008 executed in a loop against a remote instance of SQL Server 2005, the 4GB of memory on my dev-workstation are up which leads to the stated error above. Why the hell i'm executing insert-statements in a loop? - Since SQL Server doesn't allow one to set the actual TIMESTAMP of a DB manually, dummy-data-manipulation-statements are needed to help one out (the TIMESTAMP-manipulation-lock is no bad idea but on a dev-environment sometimes needed).

CREATE TABLE __DUMMY (I INT, TS TIMESTAMP)
DECLARE @FROM INT
DECLARE @TO INT
SET @FROM = 666
SET @TO = 666666666
WHILE (@FROM < @TO)
BEGIN
    INSERT INTO __DUMMY (I) VALUES(@FROM)
    SET @FROM = @FROM +1
END

On another session to SQL Server I truncate the __DUMMY-table from time to time:

SELECT @@DBTS, CONVERT(INT, @@DBTS), 'SERVER'
UNION
SELECT CONVERT(TIMESTAMP, 666666666), 666666666, 'CLIENT'

EXEC sp_spaceused '__DUMMY'
TRUNCATE TABLE __DUMMY

--writes    ~10.000 Tuple/Second
--allocates ~100.000 Byte/10.000 Tuple
--      ==> ~10 Byte/Tuple (are "roughly" made up of: INT = 4 BYTE, TIMESTAMP = 8 BYTE)
--insert/truncate is ~100.000 times more performant than doing a insert/delete in the loop or a permanent update on one tuple (haven't tried indices yet)

See http://www.flickr.com/photos/74806161@N03/6732847259/in/photostream for image showing sucker-process-descriptions and http://www.flickr.com/photos/74806161@N03/6732846981/in/photostream for image showing the memoryconsumption of the sucker-process

Anyone any Ideas?

share|improve this question
1  
So you are looping through performing dummy inserts until @@DBTS gets up to some target value? Why does it matter to you what specific value these have? In any event don't do this as single row inserts. set based inserts will be massively quicker. I presume it is the accumulation of the "1 row(s) affected" messages that is crashing SSMS –  Martin Smith Jan 20 '12 at 22:23
2  
+1 to Martin's suggestion. SET NOCOUNT ON; will drastically reduce the footprint from the done_in_proc messages. –  Aaron Bertrand Jan 20 '12 at 22:47
    
Martin, thanks a lot. Your answer was very valuable. Detto Aaron, NOCOUNT ON kept my memory usage low. –  exp'ix' Jan 22 '12 at 15:22
add comment

2 Answers

up vote 4 down vote accepted

Looks like up until SQL Server 2000 there was an undocumented command DBCC CHECKDBTS that did exactly what you want but it is no more. It looks like it is stored on the database boot page with no (safe, see below) way of manually setting it.

I'm not sure why you want to do this and I suggest that you don't bother but if you must then you will likely find set based inserts considerably quicker.

(NB: The below might need tweaking to avoid overshooting the target as I found that @@DBTS incremented by 1003921 for each million row insert but should be an improvement...)

CREATE TABLE __DUMMY (I TINYINT, TS TIMESTAMP)

DECLARE @Target INT, @Batch INT
SET @Target = 666666666

WHILE @Target >  CONVERT(INT, @@DBTS)
BEGIN
RAISERROR ('@@DBTS = %d',0,1,@@DBTS) WITH NOWAIT;

SET @Batch = @Target - CONVERT(INT, @@DBTS);

IF @Batch > 1000000 
    SET @Batch = 1000000;

   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 INSERT INTO __DUMMY (I) 
  SELECT 1
   FROM cteTally
  WHERE N <= @Batch;


TRUNCATE TABLE __DUMMY
END


DROP TABLE __DUMMY

Or alternatively if this is only a development database, you have backups and you aren't going to come complaining to me if it all goes wrong you could do some experimenting with the undocumented and highly warned against DBCC WRITEPAGE command.

USE tempdb;

IF DB_ID('TestDBTS') IS NOT NULL
    DROP DATABASE TestDBTS;

CREATE DATABASE TestDBTS;

USE TestDBTS;

DBCC WRITEPAGE(TestDBTS, 1, 9, 412, 8, 0xAA86BC2700000000);

ALTER DATABASE TestDBTS SET OFFLINE WITH ROLLBACK IMMEDIATE;

ALTER DATABASE TestDBTS SET ONLINE WITH ROLLBACK IMMEDIATE;

USE TestDBTS;
SELECT @@DBTS;

DBCC TRACEON (3604);
DBCC DBINFO ('TestDBTS');
DBCC TRACEOFF (3604);
share|improve this answer
1  
as soon my new stackexchange account is enabled to vote answers up, i'll vote your's up –  exp'ix' Jan 23 '12 at 16:06
add comment

Do you have SET NOCOUNT ON in your script? If not SSMS is going to be getting back a lot of data which it needs to keep in memory.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.