• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2544
  • Last Modified:

Shrink Massive Database? How to estimate how long it will take?

I have a pretty massive DB.  It's currently at 425,209 MB and growing at about 2G/day (36 million recs/day!).  

There is more space on the server, but I've got to share it with a couple other depts.  Once upon a time I had to go in and fix a big db with a few tables that were about a billion recs each.  I decided I'd just do a quick shrink and reindex those tables and then I'd be able to work more efficiently.  Ooops.  So, 3 days later when it was finished.... I managed to get the job finished and was never hired at that firm again.  :)

This one makes that DB look like a kid's toy.  It is a If I shut this thing down for a full day, well.... it could be bad.  Anyway, before I start shrinking this monster, is there a way to figure out how long it might take?
0
Danielcmorris
Asked:
Danielcmorris
  • 3
  • 2
  • 2
1 Solution
 
imran_fastCommented:
2G/day  in the file option if the file growth for your database is by percentage make it 100 mb.

first you have to check what is the free space on your file by right click database --> all tasks --> shrink database

and if all the space inside the file is used you cannot shrink it..

you can shrink the log file for your database. but not data file if it is used.

if you are taking full backups then change the recovery of your database to be simple.

and then run this command in query analyzer
dump transaction yourdatabase with no_log
then shrink your log file it wont take log time may be in minutes.
0
 
TAB8Commented:
Why shrink a DB that is growing at 2gb day ?   You will shrink it and it will just have to autogrow and this will slow down your server .....

I would look at archiving old data off the server onto a seperate db if possible .....

As for reindex ......  look at sql 2005 ent edition it allows online reinexing

or use sql 2000 and reindex table by table where needed CAREFULY

0
 
DanielcmorrisAuthor Commented:
I'm actually not concerned about speed.  It's a data-warehouse - kinda.

Basically, I'm using this system to produce OLAP reports on inventory trends for a pretty big company.  I've been getting their sales/inventory data (35million recs) each day and storing it.  Right now I've got about 6months and I'm startting to "hit the wall" as far as space on the server.  If I had more funding, this would be a non-issue.  

I think archiving the data is going to be my only solution.  I spent all night writing a script that would export, zip and archive tables so they can be dropped.  It's still a pain in the butt, as I've also got to program the system, if it doesn't have a table it needs, to pull the archive, unzip it, and import the data - followed by dropping the table once it is finished with the data.

yuck.  Anything I can do to make the dam system smaller would be fantastic.

To be honest, I've never actually worked on a system this huge.  I mean, it's almost a terabyte.  On my normal systems, if things got a little big, I'd spend a couple hundred bucks and get a fat hard-drive.  Expanding this RAID array isn't quite as easy, expecially since the company's IT is oursourced and it costs about 1000x as much as telling one of my guys to go to compusa and get a new set of HDs for the array.

Any ideas other than the indexing (--already been there --)?
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
imran_fastCommented:
if you can archive the data i think you can dts the unsude tables to access and then change the extention of the access file as .txt this will make huge difference.

0
 
DanielcmorrisAuthor Commented:
export to access?
change extention to .txt?

I don't get it....  

will the access DB, if zipped up, be smaller than a text file that is zipped up?
Will the access db, holding one massive table of 35,000,000 records, make an import faster than a tab-delminted text file?

What does changing the extention to .txt do?

This is a solution I never would have thought of....
0
 
TAB8Commented:
do you have another server you can use? eg on one of you bigger tables divide it in two  ie by date before / after ...   mover all the before data to another db on another server .. then create it as a linked server ...  then add an if (date > value) then select from linked server  else select from  local server ...   this will decrease the  size of your db but still allow you to query historical data easily ....  
0
 
DanielcmorrisAuthor Commented:
Sorry for the long delay imran_fast.  Initially the solution didn't work, but it turned me on to a great idea.

Since the backups were these massive flat files, the text or access format didn't seem to make much of a difference, however, using MS Access, I was able to save an enormous amount of space by exporting the archives in a relational format.  I really couldn't do this before, using text files, because I simply couldn't trust my employees to keep all the files together.  Even in zip files we had problems in the past.

So, it is a bit of a hit to the processor to break it up so much, the size issue is somewhat alleviated.

Thanks for the push in the right direction.

-Dan
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now