Running out of space on the data drive.

Question

Running out of space on the data drive.

Viewing 7 posts - 1 through 8 (of 8 total)

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

Andrew G SSChampion Points: 12830 More actions · Answer 1

The RAID 5 will obviously be slower, so you'll need to be careful, especially with tables that are written to quite a lot.

You could move the non-clustered indexes over to a new filegroup, rather than entire tables. Check this link for moving the non-clustered indexes:

http://technet.microsoft.com/en-us/library/ms175905%28v=sql.90%29.aspx

You could also consider table partitioning http://technet.microsoft.com/en-us/library/ms188706%28v=sql.90%29.aspx and archiving older data.

Also check out the fill factor for the indexes and data_compression on the larger tables.

Make sure you test everything on a copy of production first!

With that database size, you are moving into areas where you should have a DBA or consultant take a look and give some advice.

Jack Corbett SSC Guru Points: 184395 More actions · Answer 2

As far as index sizes, there certainly are cases where an index can be nearly as large as the base table itself, and I'd argue that if that is the case that there is probably some kind of application or database design issue. Before I'd go that far I need to know if any index maintenance is being done on the database? Are you ever rebuilding or reorganizing the indexes to reduce fragmentation? Fragmentation can cause the size of an index to grow beyond what is really needed.

You should look at potentially moving indexes onto a separate file group and possibly partitioning, but as this is a vendor database you'd need to work with them, especially for partitioning as changes like this could violate a support agreement.

Since you are on a SAN (I'm no SAN expert), I'd think you could just expand LUN that is being presented to the server for the data drive and have space. My understanding is that this is one of the benefits of running on a SAN.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 3

You will need to know what kind of growth rate these two databases will have over the next year to 5 years unless your SAN admin will simply give you a new drive that is 2 TB or so. Once you have another drive you would simply schedule downtime, have everyone disconnect from the database, back it up and then restore it to the new drive.

pssudarshan SSC Eights! Points: 971 More actions · Answer 4

Thank you for all your suggestions. I shall start considering all the options presented. I will be working closely with our Product vendor on this and so hopefully we will not have any support issues later on.

pssudarshan SSC Eights! Points: 971 More actions · Answer 5

Table partitioning - one of the solutions suggested above really had me excited for a minute. Then I realized that we were on SQL 2005 Std edition for 64 BIT. I should have mentioned this earlier :blush:

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

pssudarshan (8/18/2014)
Table partitioning - one of the solutions suggested above really had me excited for a minute. Then I realized that we were on SQL 2005 Std edition for 64 BIT. I should have mentioned this earlier :blush:

Lookup "Partitioned Views", a "ppor man's" version of "Partitioned Tables". Sometimes I think they're actually better than table partitioning although they DO have their own set of whacko caveates.

--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)

pssudarshan SSC Eights! Points: 971 More actions · Answer 7

Jack Corbett (8/18/2014)
As far as index sizes, there certainly are cases where an index can be nearly as large as the base table itself, and I'd argue that if that is the case that there is probably some kind of application or database design issue. Before I'd go that far I need to know if any index maintenance is being done on the database? Are you ever rebuilding or reorganizing the indexes to reduce fragmentation? Fragmentation can cause the size of an index to grow beyond what is really needed.
You should look at potentially moving indexes onto a separate file group and possibly partitioning, but as this is a vendor database you'd need to work with them, especially for partitioning as changes like this could violate a support agreement.
Since you are on a SAN (I'm no SAN expert), I'd think you could just expand LUN that is being presented to the server for the data drive and have space. My understanding is that this is one of the benefits of running on a SAN.

This was a great idea. Instead of splitting the data file across 2 LUNs as per my original concept, we basically took 2 hard drives from the RAID 5 LUN and added it to the RAID 10 Data LUN and extended the drive from windows disk management thereby increasing the space by 135 GB. So, the issue is resolved for now. Thanks for the idea Jack.:-)