September 13, 2012 at 6:46 am
Hi,
If your data drive is almost full.....how you trouble shoot it. There are no files to delete. I know you can shrink data file, but is that a best practice? What else can I do? Please advise.
Thanks,
SueTons.
Regards,
SQLisAwe5oMe.
September 13, 2012 at 7:38 am
Surely you can shrink datafile if there are no options left. No disk space might cause database goes into suspect mode. Else check if you do have space on anyother drive.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 13, 2012 at 8:11 am
Sounds to me that you need to add disks to your server so you have more space. Another alternative is to look at archiving old data from your database(s) to free up space.
September 13, 2012 at 8:14 am
Lynn Pettis (9/13/2012)
Sounds to me that you need to add disks to your server so you have more space. Another alternative is to look at archiving old data from your database(s) to free up space.
I am trying to shrink to see how much it will free up......if that does not help enough, what about creating a secondary file.....ndf...in another drive......we have another drive with more space.
SueTons.
Regards,
SQLisAwe5oMe.
September 13, 2012 at 8:20 am
SQLCrazyCertified (9/13/2012)
Lynn Pettis (9/13/2012)
Sounds to me that you need to add disks to your server so you have more space. Another alternative is to look at archiving old data from your database(s) to free up space.I am trying to shrink to see how much it will free up......if that does not help enough, what about creating a secondary file.....ndf...in another drive......we have another drive with more space.
SueTons.
Shrinking the data file(s) of your databases is going to cause fragmentation issues for your indexes which will cause performance issues. If you have anther drive available, you may want to consider moving some of your databases to that drive, not just adding additional files and placing those files on that drive. This will require some down time for the databases you move, but it is the direction I would probably go, but not sure without knowing more.
Unfortunately, we really can't give you a lot of answers because we can't see from here what you see there.
September 13, 2012 at 10:38 am
There are many things to consider e.g. the additional drive is local drive, SAN, if SAN how it has designed etc.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
September 13, 2012 at 12:32 pm
SQLCrazyCertified (9/13/2012)
I am trying to shrink to see how much it will free up......if that does not help enough, what about creating a secondary file.....ndf...in another drive......we have another drive with more space.
Things to remember with secondary files. This will not help if you put it in the PRIMARY filegroup, because the database spreads data evenly across all files in the group. This means you're just encouraging the main file / disk to fill up and freeze the database.
What you'll need to do is create a SECONDARY filegroup, put a new file in there, then switch the SECONDARY to PRIMARY to get all the new data to go to the new disk. But I can't guarantee that will work because it all depends on the setup of the other drive. How it's attached, how the LUNS are formatted (assuming this is a SAN), etc. Still, it's a decent option for the "I need to fix it now and have few options" approach.
After the current crisis is resolved, however, I strongly recommend getting a new server / SAN setup so you can migrate to a better designed solution that won't cause problems. I'd also look at your AUTOGROW settings and see what data you can archive off.
September 13, 2012 at 12:54 pm
Brandie Tarvin (9/13/2012)
SQLCrazyCertified (9/13/2012)
I am trying to shrink to see how much it will free up......if that does not help enough, what about creating a secondary file.....ndf...in another drive......we have another drive with more space.Things to remember with secondary files. This will not help if you put it in the PRIMARY filegroup, because the database spreads data evenly across all files in the group. This means you're just encouraging the main file / disk to fill up and freeze the database.
What you'll need to do is create a SECONDARY filegroup, put a new file in there, then switch the SECONDARY to PRIMARY to get all the new data to go to the new disk. But I can't guarantee that will work because it all depends on the setup of the other drive. How it's attached, how the LUNS are formatted (assuming this is a SAN), etc. Still, it's a decent option for the "I need to fix it now and have few options" approach.
After the current crisis is resolved, however, I strongly recommend getting a new server / SAN setup so you can migrate to a better designed solution that won't cause problems. I'd also look at your AUTOGROW settings and see what data you can archive off.
Slightly confusing, unless I missed something. The PRIMARY file group will always be the PRIMARY file group. This is where all the database related system tables reside. By default, this file group is also the DEFAULT file group.
When you create a new file group and can designate it as the default file group, which is where tables, indexes, and views will be created unless you specifically designate which file group any of these should be created in.
September 14, 2012 at 4:43 am
Lynn Pettis (9/13/2012)
Brandie Tarvin (9/13/2012)
SQLCrazyCertified (9/13/2012)
I am trying to shrink to see how much it will free up......if that does not help enough, what about creating a secondary file.....ndf...in another drive......we have another drive with more space.Things to remember with secondary files. This will not help if you put it in the PRIMARY filegroup, because the database spreads data evenly across all files in the group. This means you're just encouraging the main file / disk to fill up and freeze the database.
What you'll need to do is create a SECONDARY filegroup, put a new file in there, then switch the SECONDARY to PRIMARY to get all the new data to go to the new disk. But I can't guarantee that will work because it all depends on the setup of the other drive. How it's attached, how the LUNS are formatted (assuming this is a SAN), etc. Still, it's a decent option for the "I need to fix it now and have few options" approach.
After the current crisis is resolved, however, I strongly recommend getting a new server / SAN setup so you can migrate to a better designed solution that won't cause problems. I'd also look at your AUTOGROW settings and see what data you can archive off.
Slightly confusing, unless I missed something.
Well, sort of kind of. You're right that I forgot that the name won't change. I meant to say change which group is the DEFAULT. My bad.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply