Large DB

Question

Post reply

Large DB

Michael Tocik

SSCertifiable

Points: 6657
More actions
June 19, 2006 at 2:21 am

#118419

Hi

We have a large database we look after (198GB). The application that feeds data into this database has nothing to do with us. The issue is the database grows at a high rate and we are running out of disk space.

What can I do to reduce the size of the database with out impacting on the applications performance?

Thanks

Mike

Viewing 6 posts - 1 through 5 (of 5 total)

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

Eshwaran SSC Veteran Points: 229 More actions · Answer 1

HI,

Can u say what Kind of backup methods you are following for this database.

1. TO Reduce the database size you have to shrink the database.

2. If you are having a single MDF and LDF file please check the file.

Michael Tocik SSCertifiable Points: 6657 More actions · Answer 2

Hi

I have done a bit more research into this problem and on this particular DB we took the decision to shrink the LDF files due to the type of back routine in use.

Thanks

Mike

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 3

Mike,

Do you have any disk on the server where you could add a second data file? Also, depending on the type of database it is you may be able to write a purge process to purge out old data. Other than that, there isn't much you can do to decrease the size of the database if the data is still coming in a high rate.

HTH,

John

Scott Arendt SSCrazy Eights Points: 8148 More actions · Answer 4

Mike-

I agree with John. Look into whether archiving some data is an option. We have a policy to archive data older than 13 months. It really depends on the business requirements of the applications that reference the data. Make sure that you get sign off from stakeholders before removing data from a production database.

Regards,

Scott

sqlgiant SSC Eights! Points: 985 More actions · Answer 5

Mike -

I gather since you shrunk the Transaction Logs (*.ldf) to gain some space that they are on the same storage array as Data files (*.mdf). That's not the best design, though you didn't ask about that. For a high volume transactional system you would want to get those on different storage arrays.... For good performance Data on RAID 5 and the Transaction Logs on a single RAID 10 mirrored disk pair is affordable and a recommended practice.

Now back to your capacity issue: It is a very simple operation to Move Data and Log files from one storage array to another, if you have the storage arrays that is. Use sp_detach_db/sp_attach_db.

Are Backups also going to that same storage array? Hopefully not as this is the Triple Axis of Death (Data, Logs and Backups all going to the same storage array). If the array goes down, so does everything.

Can you afford an external enclosure? Can you just put a cheap 400 GB External backup drive, or an Ultrium tape drive with Veritas Backup Exec on the server to hold backups for awhile?

In the end, you are going to have to either restructure your Data, Logs and Backups across multiple arrays or you are just plain going to need more storage. Sounds like you need a good plan to do both.

Stuart

"Chance is always powerful. Let your hook be always cast. In the pool where you least expect it, will be a fish" - Ovid