Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQLServer file space management Expand / Collapse
Author
Message
Posted Monday, July 28, 2014 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:02 AM
Points: 2, Visits: 9
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!
Post #1596728
Posted Monday, July 28, 2014 4:47 AM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 1,958, Visits: 3,294
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
Post #1596752
Posted Monday, July 28, 2014 5:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 4, 2014 2:02 AM
Points: 2, Visits: 9
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
Post #1596783
Posted Monday, July 28, 2014 5:51 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 1,958, Visits: 3,294
No problem, and good luck!
Post #1596790
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse