Disk space full

  • sanjaydut26

    SSCommitted

    Points: 1968

    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

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    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

  • Thom A

    SSC Guru

    Points: 98505

    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.

  • sanjaydut26

    SSCommitted

    Points: 1968

    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

  • sanjaydut26

    SSCommitted

    Points: 1968

    Hey Thom, Sorry that's what i meant, free up the space.

  • John Mitchell-245523

    SSC Guru

    Points: 148519

    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

  • sanjaydut26

    SSCommitted

    Points: 1968

    Thanks John, but no such files. 🙂

    Thanks

    Sanjay

  • Phil Parkin

    SSC Guru

    Points: 243910

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Alvin Ramard

    SSC-Forever

    Points: 41190

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



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    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]

  • Saurabh.D

    Ten Centuries

    Points: 1051

    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

  • kevaburg

    SSCoach

    Points: 17910

    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.

  • Ed Wagner

    SSC Guru

    Points: 286962

    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.

  • Ed Wagner

    SSC Guru

    Points: 286962

    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.

  • kevaburg

    SSCoach

    Points: 17910

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

  • Ed Wagner

    SSC Guru

    Points: 286962

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

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