Running out of disk space

Question

Running out of disk space

callison

Old Hand

Points: 330
More actions
January 12, 2005 at 6:06 pm

#361909

We have a database that has grown to fill almost all of the available space on a drive. The server has other drives with space, and I'd like to be able to extend the database on to one of these drives. Do I simply alter the database adding a new file pointing to the new disk? Is is as simple as that?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

You can add a file to the filegroup and the second file will automatically be used once the first file is full. Turn off the "automatically grow file" setting so that the first file doesn't fill up the drive.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

Good suggestion Kathi,

I'd also have a look at moving the log file to a seperate disk if it's currently on the same one as the data file (assuming that we're not talking about a small database and log file here).

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

I second the last suggestions, especially if you have separate physical disks available.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

callison Old Hand Points: 330 More actions · Answer 4

Thanks for the replies. The transaction log is already on another drive. This is the only database on this drive.

So, I will set auto grow off and alter the database to add another data file pointing to another drive.

Believe it or not, this is the first time I've had to do this in over 9 years of working with SQL Server. In the past, I've had the luxury of moving the database somewhere else. I don't have that option this time.

Talk about learning something new every day!

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 5

how about creating second filegroup and moving text and indexes to the new filegroup -

performance would be up(as one set of disks scan indexes, the other scans the data) and some space freed up as a bonus

p.s you can't move a clustered index to the second file group.

also if you make a second file group - you could move common lookup tables to the new filegroup. queries using these as well as data from other tables will also run faster.

MVDBA