June 7, 2007 at 9:32 pm
Hi all
I have 2 databases, one is the main/live, the other is an archive database (so it is identical but just contains archived data, obviously).
The scripts that archive the data basically does INSERTs into the archive database, and the DELETEs from the main database the rows that were archived.
Now, before archiving my database sizes (looking at space used only) were as follows:
MAIN: 80.5Gb
ARCHIVE: 54.5Gb
And after:
MAIN: 76.9Gb
ARCHIVE: 83.97Gb
Now, how on earth can my main database only decrease by approx 3Gb, but my archive database grew nearly 30Gb?!? Keep in mind we are looking at "space used" here, and this is the space used in the data files (I am ignoring the log file sizes).
Any ideas as to why the huge difference in sizes?
TIA!
June 8, 2007 at 1:59 am
Check whats the size of log file. That might occupy more space than what you have expected.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 8, 2007 at 5:07 am
Moving data from the main database does not shrink the space reserved for the main database... nor should you shrink it unless this is the very first time you've archived.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 8, 2007 at 9:56 am
Shrinking is bad.
Paul Randal, PM for the storage engine, says you should never shrink the data file unless you think you'll NEVER need the space.
http://blogs.msdn.com/sqlserverstorageengine/archive/2007/03/28/turn-auto-shrink-off.aspx
June 8, 2007 at 10:52 am
Or if you might need that space for other databases...
June 11, 2007 at 5:13 pm
As I said, I was only looking at the DATA file; not the log file size.
Anyway, I ran DBCC (via maintainance plan) on my database and it reduced the "space used" amount down to 60Gb (the total size of my datafile is still at 105Gb).
June 11, 2007 at 7:09 pm
Krissy,
How big are the hard-drives associated with the datafile? The other question would be, how many months or years does the current 60 gig of space used represent? The reason I'm asking is "size doesn't matter" unless you need to grow the database... if the 60 gig (for example), only represents one year of growth, you might want to consider leaving the datafile at 105 gig to prevent long dwell times when it does grow from 60 gig.
If the 60 gig represents 5 years, then you could safely reduce the size of the file to, say, 80 gig using "shrink file". Just make sure you have a good defrag tool or that your SAN (if you have one) automatically defrags. You may want to defrag the hard-disk, anyway... most folks erroneously leave the default settings of 1 Mb initial size and a 10% growth rate... that will create 73 "segments" just growing the first gig of space... that's a lot of fragmentation and you can get some pretty good performance gain by defragging the disk.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply