Post reply

Where did our 30GBs go?

  • January 2, 2019 at 6:57 am

    #2017733

    Okay, so I come back to you all to (possibly) hang my head in shame and to seek further guidance.

    What Jeff et al. have said makes complete sense to me. However, doing the rebuilds did nothing as far as the ability to regain this 'missing' space. This seems to be because while everyone has probably given me great advice for what I had  described, what I had described is possibly incorrect - well one term. I said 'unused space'. Well, the unused space has been released; the problem is that the space we're missing is not unused space it's unallocated space. I didn't realize there was a distinction between the two until staring at an SSMS report :blink:

    So, is it unusual to have ~5GB of unallocated space on a DB that's ~40GB? I know that shrinking the file would rid it of the unallocated space, but I'm not doing that. I've read enough to know not to unless there's something really wrong. How do I look at resolving this 'issue' if it even is one?

    Thanks!!

  • January 2, 2019 at 7:44 am

    #2017740

    Jonathan AC Roberts - Monday, December 17, 2018 7:37 PM

    Jeff Moden - Monday, December 17, 2018 4:52 PM

    I recommend that you move the VARCHAR(7000) COMMENT column to VARCHAR(MAX) and force existing values out of row as I outlined above.  

    I thought the rows were only moved if the length of the text exceeded 8000 characters?

    That's true if you only use the default functionality when you create the table.  You can use sp_tableoption to force LOBs to always be "Out-of-Row" and, especially in the last year of intense study on how indexes have their way with us, I strongly recommend forcing them out of row whether the columns are part of the "ExpAnsive" row problem or not.  Updating an existing table to force the LOBs to be out of row takes a little extra work but I've found the one time effort to be well worth it.

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

  • January 2, 2019 at 9:54 am

    #2017761

    scarr030 - Wednesday, January 2, 2019 6:57 AM

    Okay, so I come back to you all to (possibly) hang my head in shame and to seek further guidance.

    What Jeff et al. have said makes complete sense to me. However, doing the rebuilds did nothing as far as the ability to regain this 'missing' space. This seems to be because while everyone has probably given me great advice for what I had  described, what I had described is possibly incorrect - well one term. I said 'unused space'. Well, the unused space has been released; the problem is that the space we're missing is not unused space it's unallocated space. I didn't realize there was a distinction between the two until staring at an SSMS report :blink:

    So, is it unusual to have ~5GB of unallocated space on a DB that's ~40GB? I know that shrinking the file would rid it of the unallocated space, but I'm not doing that. I've read enough to know not to unless there's something really wrong. How do I look at resolving this 'issue' if it even is one?

    Thanks!!

    Not trying to sound like a jerk, but what problem are you trying to solve? 
    Having free space is not a problem. It's probably desirable.  I'm guessing that the databases will actually grow in size.  Free space is needed to prevent a file growth.  When SQL has to grow a file, it is a very intrusive process.  These should be prevented, or kept to a minimum, as much as possible.  
    Making sure that there is enough free space, and allocating the space in a database file, is part of a DBA's job.  

    Using one of my DB's as an example,  there is approximately 120 GB of data added per year.  This is seasonal, there is more growth in certain months than others. That averages 10 GB per month.
    The auto growth settings for the data file on this database are set to grow by 2048 MB.  This never rarely occurs however, because I have created the data file with ~30 GB free space.  As part of the the nightly maintenance, I capture data growth for the last 6 months. If the average daily growth exceeds 10% of the remaining free space, I increase the file size in the next maintenance window.  

    Unless you are fighting a battle with old hardware, or server admins who cannot understand why you need more disk space, I'm betting that removing this free space is not worth the effort.  In fact, I would probably increase the free space!

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • January 2, 2019 at 10:08 am

    #2017765

    Michael L John - Wednesday, January 2, 2019 9:54 AM

    scarr030 - Wednesday, January 2, 2019 6:57 AM

    Okay, so I come back to you all to (possibly) hang my head in shame and to seek further guidance.

    What Jeff et al. have said makes complete sense to me. However, doing the rebuilds did nothing as far as the ability to regain this 'missing' space. This seems to be because while everyone has probably given me great advice for what I had  described, what I had described is possibly incorrect - well one term. I said 'unused space'. Well, the unused space has been released; the problem is that the space we're missing is not unused space it's unallocated space. I didn't realize there was a distinction between the two until staring at an SSMS report :blink:

    So, is it unusual to have ~5GB of unallocated space on a DB that's ~40GB? I know that shrinking the file would rid it of the unallocated space, but I'm not doing that. I've read enough to know not to unless there's something really wrong. How do I look at resolving this 'issue' if it even is one?

    Thanks!!

    Not trying to sound like a jerk, but what problem are you trying to solve? 
    Having free space is not a problem. It's probably desirable.  I'm guessing that the databases will actually grow in size.  Free space is needed to prevent a file growth.  When SQL has to grow a file, it is a very intrusive process.  These should be prevented, or kept to a minimum, as much as possible.  
    Making sure that there is enough free space, and allocating the space in a database file, is part of a DBA's job.  

    Using one of my DB's as an example,  there is approximately 120 GB of data added per year.  This is seasonal, there is more growth in certain months than others. That averages 10 GB per month.
    The auto growth settings for the data file on this database are set to grow by 2048 MB.  This never rarely occurs however, because I have created the data file with ~30 GB free space.  As part of the the nightly maintenance, I capture data growth for the last 6 months. If the average daily growth exceeds 10% of the remaining free space, I increase the file size in the next maintenance window.  

    Unless you are fighting a battle with old hardware, or server admins who cannot understand why you need more disk space, I'm betting that removing this free space is not worth the effort.  In fact, I would probably increase the free space!

    Don't worry about sounding like a jerk - I'm still learning and that includes learning when something is actually an issue. I was stressing this so much because our server admin jumped me one morning with the question that became the title of this post - "Where did my 30Gbs go?" Thinking that this was an issue, I came here. I guess the 'issue' I'm trying to solve is just accounting for all of our space and making sure it's necessary to have as space is at a bit of a premium 'round my parts.

    Thanks for the information, all! While there was no actual issue to solve, and a large misuse of terminology on my part, I've learned a lot!

  • January 2, 2019 at 10:16 am

    #2017767

    scarr030 - Wednesday, January 2, 2019 10:08 AM

    Michael L John - Wednesday, January 2, 2019 9:54 AM

    scarr030 - Wednesday, January 2, 2019 6:57 AM

    Okay, so I come back to you all to (possibly) hang my head in shame and to seek further guidance.

    What Jeff et al. have said makes complete sense to me. However, doing the rebuilds did nothing as far as the ability to regain this 'missing' space. This seems to be because while everyone has probably given me great advice for what I had  described, what I had described is possibly incorrect - well one term. I said 'unused space'. Well, the unused space has been released; the problem is that the space we're missing is not unused space it's unallocated space. I didn't realize there was a distinction between the two until staring at an SSMS report :blink:

    So, is it unusual to have ~5GB of unallocated space on a DB that's ~40GB? I know that shrinking the file would rid it of the unallocated space, but I'm not doing that. I've read enough to know not to unless there's something really wrong. How do I look at resolving this 'issue' if it even is one?

    Thanks!!

    Not trying to sound like a jerk, but what problem are you trying to solve? 
    Having free space is not a problem. It's probably desirable.  I'm guessing that the databases will actually grow in size.  Free space is needed to prevent a file growth.  When SQL has to grow a file, it is a very intrusive process.  These should be prevented, or kept to a minimum, as much as possible.  
    Making sure that there is enough free space, and allocating the space in a database file, is part of a DBA's job.  

    Using one of my DB's as an example,  there is approximately 120 GB of data added per year.  This is seasonal, there is more growth in certain months than others. That averages 10 GB per month.
    The auto growth settings for the data file on this database are set to grow by 2048 MB.  This never rarely occurs however, because I have created the data file with ~30 GB free space.  As part of the the nightly maintenance, I capture data growth for the last 6 months. If the average daily growth exceeds 10% of the remaining free space, I increase the file size in the next maintenance window.  

    Unless you are fighting a battle with old hardware, or server admins who cannot understand why you need more disk space, I'm betting that removing this free space is not worth the effort.  In fact, I would probably increase the free space!

    Don't worry about sounding like a jerk - I'm still learning and that includes learning when something is actually an issue. I was stressing this so much because our server admin jumped me one morning with the question that became the title of this post - "Where did my 30Gbs go?" Thinking that this was an issue, I came here. I guess the 'issue' I'm trying to solve is just accounting for all of our space and making sure it's necessary to have as space is at a bit of a premium 'round my parts.

    Thanks for the information, all! While there was no actual issue to solve, and a large misuse of terminology on my part, I've learned a lot!

    Here's a story. At a previous position, we were constantly begging, pleading, and fighting for more disk space with the SAN and server admins.  
    When we upgraded/virtualized the servers, we created empty databases named "MT" (get it, empty?) on the servers, and pre-sized them to fill the drives.  
    The admins saw that the drives were full, and gave us more space.  
    So, we then just shrunk the MT databases when we needed space to allocate to the databases that actually needed it.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • January 2, 2019 at 10:33 am

    #2017769

    Michael L John - Wednesday, January 2, 2019 10:16 AM

    scarr030 - Wednesday, January 2, 2019 10:08 AM

    Michael L John - Wednesday, January 2, 2019 9:54 AM

    scarr030 - Wednesday, January 2, 2019 6:57 AM

    Okay, so I come back to you all to (possibly) hang my head in shame and to seek further guidance.

    What Jeff et al. have said makes complete sense to me. However, doing the rebuilds did nothing as far as the ability to regain this 'missing' space. This seems to be because while everyone has probably given me great advice for what I had  described, what I had described is possibly incorrect - well one term. I said 'unused space'. Well, the unused space has been released; the problem is that the space we're missing is not unused space it's unallocated space. I didn't realize there was a distinction between the two until staring at an SSMS report :blink:

    So, is it unusual to have ~5GB of unallocated space on a DB that's ~40GB? I know that shrinking the file would rid it of the unallocated space, but I'm not doing that. I've read enough to know not to unless there's something really wrong. How do I look at resolving this 'issue' if it even is one?

    Thanks!!

    Not trying to sound like a jerk, but what problem are you trying to solve? 
    Having free space is not a problem. It's probably desirable.  I'm guessing that the databases will actually grow in size.  Free space is needed to prevent a file growth.  When SQL has to grow a file, it is a very intrusive process.  These should be prevented, or kept to a minimum, as much as possible.  
    Making sure that there is enough free space, and allocating the space in a database file, is part of a DBA's job.  

    Using one of my DB's as an example,  there is approximately 120 GB of data added per year.  This is seasonal, there is more growth in certain months than others. That averages 10 GB per month.
    The auto growth settings for the data file on this database are set to grow by 2048 MB.  This never rarely occurs however, because I have created the data file with ~30 GB free space.  As part of the the nightly maintenance, I capture data growth for the last 6 months. If the average daily growth exceeds 10% of the remaining free space, I increase the file size in the next maintenance window.  

    Unless you are fighting a battle with old hardware, or server admins who cannot understand why you need more disk space, I'm betting that removing this free space is not worth the effort.  In fact, I would probably increase the free space!

    Don't worry about sounding like a jerk - I'm still learning and that includes learning when something is actually an issue. I was stressing this so much because our server admin jumped me one morning with the question that became the title of this post - "Where did my 30Gbs go?" Thinking that this was an issue, I came here. I guess the 'issue' I'm trying to solve is just accounting for all of our space and making sure it's necessary to have as space is at a bit of a premium 'round my parts.

    Thanks for the information, all! While there was no actual issue to solve, and a large misuse of terminology on my part, I've learned a lot!

    Here's a story. At a previous position, we were constantly begging, pleading, and fighting for more disk space with the SAN and server admins.  
    When we upgraded/virtualized the servers, we created empty databases named "MT" (get it, empty?) on the servers, and pre-sized them to fill the drives.  
    The admins saw that the drives were full, and gave us more space.  
    So, we then just shrunk the MT databases when we needed space to allocate to the databases that actually needed it.

    It's a damned shame that such trickery is sometimes necessary instead of people just doing what is needed.

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

Viewing 6 posts - 16 through 20 (of 20 total)

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