keeping size of DB down

Question

keeping size of DB down

terry999

SSCertifiable

Points: 5036
More actions
June 24, 2014 at 9:30 am

#309466

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

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

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

Keith Tate SSCrazy Eights Points: 9938 More actions · Answer 1

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.

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

ScottPletcher SSC Guru Points: 101103 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

terry999 SSCertifiable Points: 5036 More actions · Answer 3

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.