Is there a better way?

Question

Is there a better way?

sqlservercentral 49658

Newbie

Points: 2
More actions
April 8, 2010 at 8:38 pm

#233501

Hi All,
I'm wondering if anyone can suggest a more efficient way of accomplishing the following task as I am not a DB guru like I know many of you are...
I have a web application that reads multiple XML files and dumps the data into a MSSQL table named "tblHOLDING". I let the application do this instead of MSSQL directly because I am hosting the application in a shared environment and the DB and application are not on the same server.
Once tblHOLDING is populated, I want to do some simple filtering and migrate all the remaining records to the "tblTARGET" table.
When all is said and done tblHOLDING is empty. I do not need to preserve any logs for backup purposes or anything else for that matter.
The problem I keep running into is an error message stating that the transaction log is full.
Here's my procedure...
BEGIN
SET NOCOUNT ON;
Delete from tblTARGET where dtstamp < dateadd(d,-5,getdate());
Delete from tblHOLDING where dtstamp < dateadd(d,-5,getdate());
Delete from tblHOLDING
Where tblHOLDING.ExtLink in
(Select tblTARGET.ExtLink from tblTARGET
Where tblTARGET.Extlink = tblHOLDING.ExtLink
AND tblTARGET.fkCity = tblHOLDING.fkCity
AND tblTARGET.fkCategory = tblHOLDING.fkCategory);
Insert into tblTARGET (DtStamp, [Key],IP,fkCity,fkCategory,Email,Subject,Body,Agreement,fkType,fkSrc,IsValidated,ExtLink)
Select DtStamp, [Key],IP,fkCity,fkCategory,Email,Subject,Body,Agreement,fkType,fkSrc,IsValidated,ExtLink From tblHOLDING;
Delete from tblHOLDING;
DUMP TRANSACTION SK_DB WITH NO_LOG;
END
Any help would be appreciated.
Thanks!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 1

Try putting tblHolding into a database and then setting the Recovery to simple. That way the log will only hold open transactions and those transactions completed between checkpoints. You won't be able to recover to a point in time, but it ought to help.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

sqlservercentral 49658 Newbie Points: 2 More actions · Answer 2

Hi,

Good thought, but tbHOLDING and tblTARGET are already in the same MSSQL database. The recovery mode is currently "Simple".

Thanks

Grant Fritchey SSC Guru Points: 399289 More actions · Answer 3

Then you need to resort to traditional methods. Make a much bigger log file. Break up the transaction into smaller parts, either commit seperate sections of the process instead of as a single batch, or partition the processing so that it only does 100 rows at a time, something along those lines. There's not much magic to it. If the transaction is large, the log to support it must be too. Make the log bigger or the transaction smaller. Those are pretty much the choices.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning