SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQLServer file space management


SQLServer file space management

Author
Message
johnw_nerd
johnw_nerd
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
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! Smile

Advice, thoughts, strategies and so on - all appreciated!
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7592 Visits: 6045
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
johnw_nerd
johnw_nerd
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 17
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
Gazareth
Gazareth
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7592 Visits: 6045
No problem, and good luck!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search