How to get the least amount of transaction log activity

[unclerico]

Windows 2003 Server SP1
SQL Server SP4

I have a table in one of my databases (tblLogging) that logs all activity in a users session (ie what button someone clicked, what data they entered/changed, what forms were opened/closed etc.) and as you can imagine the table is enormous. Now that the table has become so big it is slowing down the application. I have been given the task of creating a procedure that will truncate the table and leave only the last two days worth of data in tact. My plan is to query the table for the last two days worth of data, move it to a temp table, truncate the tblLogging table, and then move the data from the temp table back to the tblLogging table. Even though there is going to be only two days worth of data there are still thousands and thousands of rows so I was wondering two things:

1) Does this sound like a good plan or is there a better way to go about it
2) If it sounds ok, what steps should I take to minimize the transaction log activity?? Meaning would it be better to drop the table and then use

SELECT field1, field2, field3
  INTO tblLogging
  FROM #tmpTable

OR

INSERT INTO tblLogging
SELECT field1, field2, field3
  FROM #tmpTable

Thanks for any help you can give.

 

[MDXer]

just off the top of my head I would

1) BCP out the data to be saved to a csv
2) Truncate the table
3) drop all indexes
4) BCP in the data from the CSV
5) Rebuild all indexes.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[unclerico]

thanks for your reply MDX...I am trying to do the BCP like so from query analyzer to make sure that it works:

exec xp_cmdshell ('bcp anotimetest.dbo.tblLogger "SELECT l.[key], l.[who], l.[when] FROM tblLogger l WHERE l.[when] BETWEEN ''4/9/2006 12:00 AM'' AND ''4/11/2006 11:59 PM''" queryout "D:\Shared\FileDump\2006-4-11.csv" -b1000 -c')

but I get the following error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'bcp anotimetest.dbo.tblLogger "SELECT l.[key], l.[who], l.[when] FROM tblLogger l WHERE l.[when] BETWEEN '4/9/2006 12:00 AM' AND'.

I am very weak at using BCP (first time actually) so can you tell me where I am going wrong?? Thanks

 

[DBASkippack]

I had a very similar situation as you. I also opted to BCP OUT, TRUNCATE, DROP, BCP IN - yaddy ya. Then I stumbled across this simple solution:

If you have a datetime to work with, use the SET ROWCOUNT feature to do a Transactional DELETE. I was able to DELETE about 1M rows / minute using this w/ virtually NO contention. (If possible, set your DB to SIMPLE RECOVERY MODE)

Run this:

DECLARE @x INT, @y INT
SELECT @y = 1
WHILE @Y > 0
BEGIN
SET @x = 1
SET ROWCOUNT 5000 --< set this to however many DELETES you want to perform in the TXN
SELECT @y = COUNT(*) FROM Your_TableName
WHERE AND Your_DateTime_Col < '04/09/2006'
WHILE @x > 0
BEGIN
BEGIN TRAN
DELETE Your_TableName
WHERE Your_DateTime_Col < '04/09/2006'
SET @x = @@rowcount
COMMIT TRAN
END
END
GO