Tempdb ????

[VBXL]

I have to insert about million records (400MB) into a database daily into a table using cursors and the Tempdb fill up before i can complete the task. Would increasing the tempdb help. If what is a large size for a tempdb that would not crash my system. I was thinking about 200MB.

Cheers

 

[AshleyRickards]

Create a secondary file .ndf on a drive with enough space and limit the primary file to whatever it's currently at.. Also, you could stop and restart the service after your insert as this recreates the temp db. Ashley L Rickards
SQL DBA

 

[VBXL]

Is the a sql statment that could stop and start the service ????

 

[fluteplr]

I would also have serious look at why you are using cursors. In my experience at least 1/2 the sql code I have seen written using cursors, could and should have been written without them. If you would like to post the code you are using we could take a look and see if that is the case here.

 

[VBXL]

CREATE PROCEDURE example
AS

SET QUOTED_IDENTIFIER OFF

DECLARE @sqlexec varchar(2000)
DECLARE @examplea varchar(30),
@exampleb varchar(12),
@examplec varchar(20),

DECLARE @crsrvar Cursor

SET @crsrvar = Cursor FOR

{select statment }

OPEN @crsrvar
FETCH NEXT FROM @crsrvar INTO
@examplea,
@exampleb,
@examplec

WHILE @@FETCH_STATUS = 0 /* For checking that you have not crossed the end of the cursor*/
BEGIN
/* your processing based on the rows given by the server*/


SET @sqlexec = 'INSERT INTO exampletable ' (exa,exb,exc) VALUES (' +
@examplea + ',"' +
@exampleb + '","' +
@examplec + ')'


exec (@sqlexec)


FETCH NEXT FROM @crsrvar INTO
@examplea,
@exampleb,
@examplec

END

CLOSE @crsrVar
DEALLOCATE @crsrVar

 

[smin]

why wouldn't the simple statement like below work?

Select cola as examplea, colb as exampleb, colc as examplec
into exampletable
from ( {select statement} ) as atable

 

[VBXL]

No because i am creating or amending to the curent table when i insert. In the record it has a date so the record would go into a coresponding date

i.e if the record has a date field with the value of 18/01/02, it would go into Jan18 table. If the table does not exist, it would create it.

 

[tlbroadbent]

I recommend processing the records in smaller batches within transactions. Begin a transaction, insert/update 10000 records, commit the transaction. Repeat the loop until all records are processed.

SQL Server uses the transaction log to record all the updates so they can be rolled back if an error occurs. When transactions are committed, SQL no longer needs to keep the transactions and so they are cleared. When you process a million recs in one transaction SQL Server must keep track of all updates until the end of the transaction. If you commit after processing a smaller batch, SQL will not have keep so many records and the log will not grow as big.

Additionally, TempDB will not need to grow as large when processing smaller batches.

Let me know if you have any questions about this recommendation. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[VBXL]

in the DTS sectio non the import, there is an option that you can alter "insert commit size" in Data Transformation Properties. Is i set this to 1000 would this commit the database every 1000 records that are inserted ????

 

[tlbroadbent]

You can control the commit batch size in DTS imports if the Use Fast Load option is ON. Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm