Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pphillips001
Starting Member
23 Posts |
 Posted - 2010-03-23 : 21:03:45
|
| Hi,I have a database that is currently growing 2.5Gb daily.There is only about 100Gb left on the db partition (the db taking up about 300Gb at present). Using the cunning application of maths it indicates that I'm facing pretty imminent issues.I do have an application audit table that is not required that accounts for 60% of the db.The question is - if I truncate the table - will that allow the database to utilise the effectively dropped space and won't start growing again until that space is used? Or will I need to DBShrink to reclaim the truncated space back?I realise that DBShrinking will mean less of a nightly back-up - is this the only advantage? I admit I am apprehensive taking the production database offline to do the shrink.Any help is appreciated.Regards,Paul===========================There is no reality - only perspective. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
pphillips001
Starting Member
23 Posts |
Posted - 2010-03-23 : 21:20:16
|
| Thanks Tara,This should hopefully get me out of a tight fix.(Paul bows to Almighty SQL Goddess)===========================There is no reality - only perspective. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-24 : 03:38:45
|
| If you have Audit tables then you need some sort of maintenance housekeeping on them - e.g. "delete all audit entries that are more than 1 month old"Personally I think even considering truncating the tables is worrying. If you can run with no audit data then why not drop the tables and no have it?If you are going to put in maintenance routines so that, say, you only store one months audit data and lets say that that reduces the size of (the used part of) your database by 20GB, then I would shrink the database by 20GB - I would not carry that extra space if it was not needed, but I would also aim that the database does not get extended more than once a month.If your database has been growing in small amounts to accommodate the audit data then I would look at making the MDF and LDF files contiguous by defragging the disk - e.g. using CONTIG.EXE from Sysinternals (now part of Microsoft) next time you have some scheduled downtime. (Do any one-time shrink first) |
|
|
|
pphillips001
Starting Member
23 Posts |
Posted - 2010-03-24 : 06:19:49
|
| Kristen - thanks for the reply.The idea is to syphon off the recent audit data to another table, truncate the main one and then copy the data back. There are no unique id fields on the table - so don't see it causing an issue. I will be looking into setting up a regular plan for removing the older data as you say.May I ask the reasoning about shrink/expand when you will eventually need to move into that space eventually anyway?Also - what does making the MDF/LDF file contiguous do? Is it a performance thing?CheersPaul===========================There is no reality - only perspective. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-24 : 06:27:23
|
| "the reasoning about shrink/expand when you will eventually need to move into that space eventually anyway?"If the used size of the database decreases "significantly" I think it is worth a one-time shrink to a more appropriate size. But not if the database will grow back into that space "imminently"Carrying excessive empty space has two disadvantages that I can think of:1) Restoring to A.N. Other machine will pre-create the database to its full size - and that might be a squeeze on the target machine (and is a REAL pain to work around)2) You are taking away disk space from something else - if that happens to be backups, say, then you might cause a disk full that could be avoided - e.g. in some unusual circumstances. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-24 : 06:28:56
|
| Sorry, pressed save too soon!"what does making the MDF/LDF file contiguous do? Is it a performance thing?"Yup. If the file is contiguous on the disk there is less head travel to read it. AFAIK all files on disk benefit from being de-fragmented in this way, but a large database file that has grown bit-by-bit is liable to be stored on the disk in hundreds / thousands of "fragments" which may impact adversely on performance |
|
|
|
|
|
|
|
|
To DBShrink or not to DBShrink
Tara Kizer