How to free up the drive space in SQL that has data files

Question

Post reply

How to free up the drive space in SQL that has data files

WhiteLotus

SSChampion

Points: 12120
More actions
November 27, 2014 at 8:16 pm

#298989

Hi all ,
I am having a problem with running out of space of E:\ ... 75 GB free space out of 500 GB
The data file of my databases( 100 databases ) are saved there . When I check the fragmentation index in some databases are very high .. So I rebuild the indexes and shrink the data file ... I get more space from shrinking 1 database but got nothing from others ... and I checked the index fragmentation becomes very high after shrinking ....
Any idea would be very appreciated ...
Cheers

Viewing 14 posts - 1 through 13 (of 13 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Shrinking data files is indeed bad for index fragmentation.

You should check for each data file how much free space there actually is.

If a data file has a lot of free space, much more than is needed to account for growth, then you can shrink the file.

Other options are to use page compression (if you have Enterprise edition), archiving data or adding more disk space.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Add more disk space or delete data.

Or move some of the data files to other drives if there are any.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

arrjay Hall of Fame Points: 3455 More actions · Answer 3

If you are running SQL Enterprise edition you could use the data compression feature.

--------------------------------------------

Laughing in the face of contention...

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 4

A 1 TB 15k-RPM drive costs less than $300 for a SAN drive. For a local drive, you can get one for about $70. You've probably already spent that amount just in your time alone.

Open a req and buy an additional disk. You're going to need it soon one way or the other.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WhiteLotus SSChampion Points: 12120 More actions · Answer 5

Jeff Moden (11/28/2014)
A 1 TB 15k-RPM drive costs less than $300 for a SAN drive. For a local drive, you can get one for about $70. You've probably already spent that amount just in your time alone.
Open a req and buy an additional disk. You're going to need it soon one way or the other.

Hi All,

Thanks for the reply . Much appreciate it ..

It means the rebuild will not increase the disk space ?

yes I will suggest my team to increase the disk space for sure

cheers,

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 6

WhiteLotus (11/30/2014)
Jeff Moden (11/28/2014)
A 1 TB 15k-RPM drive costs less than $300 for a SAN drive. For a local drive, you can get one for about $70. You've probably already spent that amount just in your time alone.
Open a req and buy an additional disk. You're going to need it soon one way or the other.
Hi All,
Thanks for the reply . Much appreciate it ..
It means the rebuild will not increase the disk space ?
yes I will suggest my team to increase the disk space for sure
cheers,

As you noticed, doing a shrink after the "rebuilds" causes horrible fragmentation, which is not good for performance. If you do a REORGANIZE of ALL the indexes after the shrink, it'll help a lot without causing the database to grow much especially if you're able to use "SORT IN TEMPDB", although it'll be pretty tough on your log file and it won't rebuild the B-TREE of the indexes. If you REBUILD the indexes, any index larger that 128 extents (that's only 8MB) will have a new index rebuilt before the old one is dropped. If you can do a CREATE WITH DROP EXISTING while the DB is in the BULK LOGGED mode, it'll help keep the growth of the database down to a reasonable size and will also help keep the log file in check. That will mean that the database will grow by at least the same size as your largest index, which is generally the clustered index of the largest table (remember the clustered index IS the table + a B-TREE). It's also a huge PITA to use WITH DROP EXISTING if you have FKs involved unless you also drop those first and rebuild them afterwards.

Get the extra disk space, do proper regular maintenance on the database (index maintenance, integrity checks, etc), never shrink it again. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233915 More actions · Answer 7

Jeff Moden (11/28/2014)
A 1 TB 15k-RPM drive costs less than $300 for a SAN drive.

High end FC disks are a lot pricier than $300!!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 8

Perry Whittle (12/1/2014)
Jeff Moden (11/28/2014)
A 1 TB 15k-RPM drive costs less than $300 for a SAN drive.
High end FC disks are a lot pricier than $300!!

I'll try to find the links for the prices I found but, even at double the price, it's a whole lot less expensive can constantly trying to wedge growing data into a too small disk set.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 233915 More actions · Answer 9

High end FC disks generally arent available in TB sizes and are usually in the order of $1000s each

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 10

Perry Whittle (12/1/2014)
High end FC disks generally arent available in TB sizes and are usually in the order of $1000s each

Oh my! I guess I'm not sure what type of disks you're talking about. IIRC the conversation correctly, the folks at work have been getting 1TB 15KRPM disks for our SAN at $485 USD each. I'll do a double check on the make/model, etc.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100952 More actions · Answer 11

Also, as additional stop-gap measures:

1) review indexes to see if there any you can drop

2) if you have any heaps, see if they consume a lot of space beyond what the # of rows would indicate

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 12

Jeff Moden (12/1/2014)
Perry Whittle (12/1/2014)
High end FC disks generally arent available in TB sizes and are usually in the order of $1000s each
Oh my! I guess I'm not sure what type of disks you're talking about. IIRC the conversation correctly, the folks at work have been getting 1TB 15KRPM disks for our SAN at $485 USD each. I'll do a double check on the make/model, etc.

I've checked with the folks in NetOps. Here's what we're paying for disks...

HP EVA 4400 1TB @ 10KRPM: $485 (I thought these were 15K RPM but they're "only" 10s)

HP FIBERCHANNEL 900GB @ 15KRPM: About $550-600 each depending on the "going price".

Maybe those aren't the "high end" disks you were thinking about but they're not "$1000s each" either.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WhiteLotus SSChampion Points: 12120 More actions · Answer 13

ScottPletcher (12/1/2014)
Also, as additional stop-gap measures:
1) review indexes to see if there any you can drop
2) if you have any heaps, see if they consume a lot of space beyond what the # of rows would indicate

thanks a lot . I will check it 🙂