July 28, 2014 at 2:47 am
Hey all, I need some help / advice. Newly started at a company (purchased by a crew trying to turn things around) and there's a lot of basic, IT-101 type stuff that isn't being done. One is that there has been no data archival. Predictably, we're near to filling up a filesystem (7-8% left). Looking at the tables there are a couple that can readily lose 3/4 of their data. That will give us room to plan for a proper archival strategy.
When we clean up those tables, the database file size doesn't shrink but that can be done point-and-click or with a small script. BUT - I've read that shrinking tables can create tremendous index fragmentation. That, and the test I ran took nearly 10 hours. SO, the questions:
- Will SQLServer efficiently manage that database file? Do I _need_ to shrink that file?
- Can I simply monitor the total sizes of the tables in the database and as long as the sum of this well below the size of the volume, I'm OK?
My background is that I'm an EA who's getting back into being hands-on. So I'm not totally green, but I'm somewhat new to SQLServer (most of my past is *nix and OSS), so this has been a bit of an adventure! 🙂
Advice, thoughts, strategies and so on - all appreciated!
July 28, 2014 at 4:47 am
Hi John, and welcome.
Shrinking files can indeed create fragmented indexes. I'd only recommend it if you're deleting a load of data, AND you know the newly shrunken database files won't just grow back into the reclaimed space. This does sound something like your situation.
If you do shrink the files, carry out index rebuild/reorganise as appropriate afterwards.
Also, don't shrink the files to their minimum possible size, the index rebuild/reorg process does require some empty 'working space' within the data file.
If you delete the data but don't shrink the files, SQL Server will use the empty space in the files before growing them any further.
Note, if it's transaction log files rather than data files that are taking up the space, you can shrink these freely. Just pick a sensible size so you're not constantly growing then shrinking them - you may need to monitor this to find the right sizes.
Cheers
Gaz
July 28, 2014 at 5:36 am
Thank you! We don't need to reclaim that space, it's the only database running on that box. I'm content to let SQLServer manage that database file as it sees fit and we'll implement some proper monitoring around the tables within the database.
I appreciate the quick response!
- John
July 28, 2014 at 5:51 am
No problem, and good luck!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply