June 24, 2014 at 9:30 am
Hi I archive a table daily:
The ArchiveDB (mdf file) continues to grow in size. Even though I delete old records from the archiveDB before archiving the current batch.
I believe because of the clustered index on the a_tblLog, I'm only removing from the "front of the file" So total file size is going up but the actual space used is the same.
After a couple of Archives the file size used stays approx the same at 80GB but the total file size keeps increasing, 100GB, 120GB, 140GB etc etc.
CREATE TABLE [dbo].[tblLog](
[Message] [nvarchar](255) NOT NULL,
[CustomerID] [int] NULL,
[PKID] [bigint] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_tblLog] PRIMARY KEY CLUSTERED ([PKID] ASC)
)
to a_tbllog in ArchiveDB simple recovery mode.
CREATE TABLE ArchiveDB.[dbo].[a_tblLog](
[Message] [nvarchar](255) NOT NULL,
[CustomerID] [int] NULL,
[PKID] [bigint] NOT NULL,
CONSTRAINT [PK_a_tblLog] PRIMARY KEY CLUSTERED ([PKID] ASC)
)
1. I remove very old records:
DELETE FROM ArchiveDB..a_tblLog WHERE PKID < ?
2. Then add new records and remove from tblLog
Is it ok to periodically shrink the ArchiveDB? will it get fragmented and cause later problems
Is there a better way to keep the file size down.
Thanks
June 24, 2014 at 9:59 am
terry999 (6/24/2014)
Is it ok to periodically shrink the ArchiveDB? will it get fragmented and cause later problems
You could periodically shrink the database, but I would run a reindex operation after doing so. Will the database just grow again? If so, it would be better to just let it stay at the current size.
Is there a better way to keep the file size down.
The file size will grow to what is needed and then stay that way. So if the amount of data during the load is more than the size can hold it will have to grow. Once it has grown it will not shrink by itself (unless you have auto shrink on). This is a good thing because shrinking the database is a very bad idea for performance.
Another option would be to use partitioning and then just switch out old partitions when they are no longer needed.
June 24, 2014 at 11:05 am
If you have time, add a rebuild step to your process:
1. I remove very old records:
DELETE FROM ArchiveDB..a_tblLog WHERE PKID < ?
1B. Rebuild a_tblLog table WITH ( FILLFACTOR = 100, ONLINE = ON /*if available*/, SORT_IN_TEMPDB = ON )
2. Then add new records and remove from tblLog
Presumably that should allow SQL to free space to be used for the new rows, without having to extend the file itself.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 24, 2014 at 6:52 pm
Thanks for the replies
I will try the rebuilding the index after the delete.
I wasn't really clear in previous post. The Archive process.
1. deletes approx 5GB per day from a_tbllog.
2. Then insert 5GB per day into a_tblLog.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply