June 11, 2009 at 7:59 am
Hi,
In our environment we have a lot of log tables that the applications use for logging application data, as a matter of fact these tables get big during time and we are concerned with disk spaces and insert speed, we take the data to another server having free disk space.
My question is how to make archiving safely, without disturbing application and without make the transaction log of the production db big.
My approach is this:
1. Rename log table as LogTable_Temp
2. Create a new table with original name
after I export the data to Log server and truncate and delete LogTable_temp,
My concern is to make archiving isolated from application brcause application inserts hundereds of record in each second, is this a valid approach to accomplis this task? Or how can make this procedure by locking tables explicitly so that the application can wait without errors a few seconds until lock is released (because for a few seconds my table is not in the db),
Regards,
Serter Poro
June 11, 2009 at 8:46 am
I would suggest to use a date column within your logging table to move the data into a staging table (or directly out to your files or other server). Since this is a logging table maybe ISOLATION LEVEL READ UNCOMMITED might be an option to avoid locks.
The approach to rename the table and create a new one might cause a crash of some of your processes.
Flo
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply