December 8, 2016 at 7:18 am
Hello everyone,
I have a server setup where my data files sits on d drive(currently which is full)
My tempdb,logfiles and backups are on separate drives each.
How do I increase my disk space on d drive?
I don't want to shrink files.
Please suggest on this.
Thanks in advance
Sanjay
December 8, 2016 at 7:46 am
Sanjay
You may be able to claw back some space by deleting unneeded files from D, or shrinking any data or log files that you know are too large and will not grow back. If you can't do that, ask your SAN/server administrator to extend the drive for you.
John
December 8, 2016 at 7:51 am
What exactly do you mean by "increase"? The only way the literally increase the disk capacity of a drive is to buy a new disk drive, with a larger capacity. You can't make an existing Disk Drive bigger, a 500GB Disk has a capacity of 500GB, and won't be anything else.
You could, possibly, play with your partitioning, however, I get the idea that that isn't a good idea for you to do.
Perhaps a better question here is "How do I free up space on my Disk Drive?". Are you creating back ups on your D drive as well? If so, are you using Compression when doing so, and how far back are you storing file for?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
December 8, 2016 at 7:51 am
Hey John,
As i told you it's only Data files log files are on a different drive.
Can you please explain,what do you mean by unneeded files?
Thanks
Sanjay
December 8, 2016 at 7:53 am
Hey Thom, Sorry that's what i meant, free up the space.
December 8, 2016 at 8:01 am
sanjaydut26 (12/8/2016)
Hey John,As i told you it's only Data files log files are on a different drive.
Sanjay
So you did. Please scrub "log files" from my response.
Can you please explain,what do you mean by unneeded files?
Any file on the disk that you don't need. Maybe an old database was put offline and then dropped and the data files are still there. Maybe someone once made a backup file in the wrong place. Perhaps the installation media or service pack files were placed on the drive and not subsequently removed.
John
December 8, 2016 at 8:16 am
Thanks John, but no such files. 🙂
Thanks
Sanjay
December 8, 2016 at 8:23 am
sanjaydut26 (12/8/2016)
Thanks John, but no such files. 🙂Thanks
Sanjay
I think you are out of luck. There is no magic 'reduce the size of files without shrinking them' command.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 8, 2016 at 8:40 am
If you can't create more space on D then you can consider adding one or more disk(s) and either moving some of the files or creating additional database files on the new disk(s).
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
December 9, 2016 at 4:45 am
I guess you can Detach... the Database then move DataFiles from D drive to other drive, later Attach... the DataFiles/Database back. Else create additional FileGroup and Add DataFiles (place it other drive) so once PRIMARY file group is full, it will start using Secondary file Group.
ThanksSaurabh.D
December 9, 2016 at 5:36 am
Try a shrinkdatabase:
exec sp_msforeachdb 'DBCC SHRINKDATABASE [?]';
It is far from ideal and you will most likely experience performance problems during the run (do it in a maintenance window) but if you have significant white space in a datafile you could win it back. That could win you enough time to physically increase the volume by whatever means.
The problem is what happens if you have a lot of data movement and this white space builds up at a rapid rate again.
December 10, 2016 at 9:04 am
kevaburg (12/9/2016)
Try a shrinkdatabase:
exec sp_msforeachdb 'DBCC SHRINKDATABASE [?]';It is far from ideal and you will most likely experience performance problems during the run (do it in a maintenance window) but if you have significant white space in a datafile you could win it back. That could win you enough time to physically increase the volume by whatever means.
The problem is what happens if you have a lot of data movement and this white space builds up at a rapid rate again.
This will get you some space back, but it'll also fragment your indexes to 99%. Rebuilding them will bloat the size of the data file and you'll be right back where you are now.
December 10, 2016 at 9:13 am
Your reaction to the drive space problem depends on the type of environment you're talking about.
If this is a production database that feeds a live website for a business and your data, log and backup files live on a SAN, then have the SAN administrator allocate more space to the volumes. You may get into the game of who pays for it, but you have a clear path forward. After the crisis is addressed, set up a longer-term solution to monitor your space. After you collect some history, you can use it to forecast when you'll run out of space and let your SAN administrator know before it becomes an emergency. There's an article on this at http://www.sqlservercentral.com/articles/Drive+space/134523/.
If this is a home lab used for development and play and you're on local drives, then take whatever approach you want because you don't have to worry about keeping production up and running. Buy a different drive, move some (or all) of your database files to it and run them from the new drive. Then you'll have the old drive for whatever you want.
Whatever you do, you have to stay within the constraints of your physical media. If you don't have any files on there that can be deleted or moved and you don't have any way of adding space to the drive, then you're in a tough spot. I'd say you've outgrown the hardware you have and it's time to either add some, upgrade what you have or get new hardware that's appropriately sized to the anticipated growth over the life of the server.
December 10, 2016 at 10:16 am
Ed Wagner (12/10/2016)
kevaburg (12/9/2016)
Try a shrinkdatabase:
exec sp_msforeachdb 'DBCC SHRINKDATABASE [?]';It is far from ideal and you will most likely experience performance problems during the run (do it in a maintenance window) but if you have significant white space in a datafile you could win it back. That could win you enough time to physically increase the volume by whatever means.
The problem is what happens if you have a lot of data movement and this white space builds up at a rapid rate again.
This will get you some space back, but it'll also fragment your indexes to 99%. Rebuilding them will bloat the size of the data file and you'll be right back where you are now.
Agreed. Then perhaps now is a good time to identify unused and duplicate indexes and remove them before rebuilding them. Then identify only the databases whereby a significant amount of space could be reclaimed and rebuild the Indexes only on them.
Another idea (or perhaps complimentary to it) is (and now some will laugh) buy a USB3 harddisk and move the databases that have very little to do onto it as a temporary measure.
A question like this doesn't Sound to me like a professional storage Team is on Hand......
December 10, 2016 at 12:57 pm
kevaburg (12/10/2016)
Ed Wagner (12/10/2016)
kevaburg (12/9/2016)
Try a shrinkdatabase:
exec sp_msforeachdb 'DBCC SHRINKDATABASE [?]';It is far from ideal and you will most likely experience performance problems during the run (do it in a maintenance window) but if you have significant white space in a datafile you could win it back. That could win you enough time to physically increase the volume by whatever means.
The problem is what happens if you have a lot of data movement and this white space builds up at a rapid rate again.
This will get you some space back, but it'll also fragment your indexes to 99%. Rebuilding them will bloat the size of the data file and you'll be right back where you are now.
Agreed. Then perhaps now is a good time to identify unused and duplicate indexes and remove them before rebuilding them. Then identify only the databases whereby a significant amount of space could be reclaimed and rebuild the Indexes only on them.
Another idea (or perhaps complimentary to it) is (and now some will laugh) buy a USB3 harddisk and move the databases that have very little to do onto it as a temporary measure.
A question like this doesn't Sound to me like a professional storage Team is on Hand......
You're probably right about the storage team. With the lack of specifics, I guess my best advice would be to add another drive and move some databases.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply