Post reply

Data Archive

  • January 15, 2017 at 1:38 am

    #325462

    Hi Experts,

    We have a job in place to Insert data older than a year from Prod table to an archive database table and the next step will delete the inserted  data from Prod table. Is there anyway to reclaim the space to disk other than shrinking the database? The table in Prod DB was around  30GB and not its 12Gb and i can see 18GB freespace in DB but dont know how to reclaim the space to disk.

    Or is there a better Archive method to move the data in short interval

  • January 15, 2017 at 5:44 am

    #1922802

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 15, 2017 at 11:09 am

    #1922809

    And, rebuilding the indexes will NOT reclaim the space reserved for the database.  Of course, you should always have a bit of headroom in your database so that you don't have to tolerate mid-day growth spurts.

    --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 15, 2017 at 11:28 pm

    #1922876

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

  • January 16, 2017 at 10:30 am

    #1923024

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    --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 16, 2017 at 10:40 am

    #1923027

    i was thinking of recovering table space, typically tied up in indexes or in heaps.
     and not reserved space for the whole database.

    AS Jeff mentioned,you have to shrink either the database or the files of the database in order to make more space on the drives where the database reside.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 16, 2017 at 10:19 pm

    #1923100

    Lowell - Monday, January 16, 2017 10:40 AM

    i was thinking of recovering table space, typically tied up in indexes or in heaps.
     and not reserved space for the whole database.

    AS Jeff mentioned,you have to shrink either the database or the files of the database in order to make more space on the drives where the database reside.

    Hi Lowell,

    Is it possible to recover table space in indexes and heap.

  • January 16, 2017 at 10:23 pm

    #1923101

    Jeff Moden - Monday, January 16, 2017 10:30 AM

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    Thanks Jeff,

    So with current archiving method we can just delete the data and nothing much right. It wont give any improvement i was hoping for? Also when we do shrink in what all way it affects the DB? Is there any best practice for shrinking , As I mentioned earlier the table itself is 18Gb and deleting the data will increase the log file also. When we do shrink do we need to mention the size (Total size 40 GB (mdf 30 + log 10) and out of it this table holds 18GB) to which the size has to be reduced.

  • January 16, 2017 at 10:52 pm

    #1923104

    from https://msdn.microsoft.com/en-us/library/ms189035(v=sql.120).aspx

    you can use the following command to shrink both the data and log of a db to include 10% free space for growth:

    DBCC SHRINKDATABASE (UserDB, 10);

    But I was thinking, why do you want to shrink the db, as over the period of next year it will again need to expand to store new data ... 
    Disk space is cheap ...

    You can also of course consider running the job monthly instead of annually, in case you can easily identify which records to archive and purge.

  • January 16, 2017 at 11:05 pm

    #1923105

    ash304 - Monday, January 16, 2017 10:52 PM

    from https://msdn.microsoft.com/en-us/library/ms189035(v=sql.120).aspx

    you can use the following command to shrink both the data and log of a db to include 10% free space for growth:

    DBCC SHRINKDATABASE (UserDB, 10);

    But I was thinking, why do you want to shrink the db, as over the period of next year it will again need to expand to store new data ... 
    Disk space is cheap ...

    You can also of course consider running the job monthly instead of annually, in case you can easily identify which records to archive and purge.

    When you tell someone to shrink the database, you must also tell them to rebuild or reorganize all of the indexes that need it because shrinking the database causes a huge amount of "unnatural" fragmentation.  It won't hurt single lookups but it can have a devastating effect on batch runs.

    That, notwithstanding, I agree.  Unless you need the space, you'll end up eventually using it so why bother?

    And, no... if you don't actually have it installed already, disk space isn't cheap. 😉  Buying the disks is actually a fraction of the cost provided that you have the budget to buy it.  Someone has to install it.  Someone has to present it to SQL Server.  If there's not enough rack space, someone has to buy a new rack and install that.  Any new hardware also always adds to the cooling and electrical loads.  And then there's the problem of doing restores.  Although free space won't be saved in the MDF file, the system still has to build out the file to it's original size.  Instant file initialization certainly helps for the MDF file but it won't help for the LDF, which has for format the VLFs.  And, if you one of those folks fortunate enough to be able to copy prod to dev, every new dev instance of the database will allocate the same amount of deadspace.  If you don't have the disk space for that in Dev, you're SOL unless you can hurry up and buy some more expensive space. 😉

    --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 16, 2017 at 11:12 pm

    #1923106

    VastSQL - Monday, January 16, 2017 10:23 PM

    Jeff Moden - Monday, January 16, 2017 10:30 AM

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    Thanks Jeff,

    So with current archiving method we can just delete the data and nothing much right. It wont give any improvement i was hoping for? Also when we do shrink in what all way it affects the DB? Is there any best practice for shrinking , As I mentioned earlier the table itself is 18Gb and deleting the data will increase the log file also. When we do shrink do we need to mention the size (Total size 40 GB (mdf 30 + log 10) and out of it this table holds 18GB) to which the size has to be reduced.

    Once you recover the original space, then nightly archive processes won't take up much space and the new data will have room to go in probably without any growth.  If you actually need to recover the space because of the items I mentioned in the post above this one, then you will need to shrink your files and properly regrow the LDF.  Then the nightly process will live comfortably in the relatively small area that it will occupy.  If you do the archive only once per month, then I wouldn't do any shrinking (other than the very first shrink after you massive initial archive process) because it will grow to be the same size at a monthly level as ash304 was suggesting... just at a different level.

    --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 17, 2017 at 12:52 am

    #1923119

    Jeff Moden - Monday, January 16, 2017 11:12 PM

    VastSQL - Monday, January 16, 2017 10:23 PM

    Jeff Moden - Monday, January 16, 2017 10:30 AM

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    Thanks Jeff,

    So with current archiving method we can just delete the data and nothing much right. It wont give any improvement i was hoping for? Also when we do shrink in what all way it affects the DB? Is there any best practice for shrinking , As I mentioned earlier the table itself is 18Gb and deleting the data will increase the log file also. When we do shrink do we need to mention the size (Total size 40 GB (mdf 30 + log 10) and out of it this table holds 18GB) to which the size has to be reduced.

    Once you recover the original space, then nightly archive processes won't take up much space and the new data will have room to go in probably without any growth.  If you actually need to recover the space because of the items I mentioned in the post above this one, then you will need to shrink your files and properly regrow the LDF.  Then the nightly process will live comfortably in the relatively small area that it will occupy.  If you do the archive only once per month, then I wouldn't do any shrinking (other than the very first shrink after you massive initial archive process) because it will grow to be the same size at a monthly level as ash304 was suggesting... just at a different level.

    Thanks Jeff,

    What if i go for deleting 10 months old data every day and shrink the DB (not individual files) on a weekly basis before the Index maintenance job?

  • January 17, 2017 at 8:17 am

    #1923182

    Generally speaking, there is no good reason to repeatedly shrink a database on a schedule. What this does is reduce the physical size of the data file on disk. The only time to shrink a database would be if it's a one-off operation to permanently reduce the size of the database to a point from which you never expect it to grow again. For example, if you're dropping unused tables that were previously consuming a significant percentage of total storage, or if a legacy database is to be retired from write activity and kept online in read-only mode.

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • January 17, 2017 at 9:22 am

    #1923206

    VastSQL - Tuesday, January 17, 2017 12:52 AM

    Jeff Moden - Monday, January 16, 2017 11:12 PM

    VastSQL - Monday, January 16, 2017 10:23 PM

    Jeff Moden - Monday, January 16, 2017 10:30 AM

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    Thanks Jeff,

    So with current archiving method we can just delete the data and nothing much right. It wont give any improvement i was hoping for? Also when we do shrink in what all way it affects the DB? Is there any best practice for shrinking , As I mentioned earlier the table itself is 18Gb and deleting the data will increase the log file also. When we do shrink do we need to mention the size (Total size 40 GB (mdf 30 + log 10) and out of it this table holds 18GB) to which the size has to be reduced.

    Once you recover the original space, then nightly archive processes won't take up much space and the new data will have room to go in probably without any growth.  If you actually need to recover the space because of the items I mentioned in the post above this one, then you will need to shrink your files and properly regrow the LDF.  Then the nightly process will live comfortably in the relatively small area that it will occupy.  If you do the archive only once per month, then I wouldn't do any shrinking (other than the very first shrink after you massive initial archive process) because it will grow to be the same size at a monthly level as ash304 was suggesting... just at a different level.

    Thanks Jeff,

    What if i go for deleting 10 months old data every day and shrink the DB (not individual files) on a weekly basis before the Index maintenance job?

    As Eric and a couple of other people have stated, shrinking every day or any other scheduled basis is a really bad idea.  Shrinks should only be done once to recover a massive amount of space and then the indexes must be rebuilt because of the massive "inversion" fragmentation that will occur.  Any plan that includes a scheduled or frequent manual shrink is seriously flawed.

    --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 18, 2017 at 3:02 am

    #1923360

    Jeff Moden - Tuesday, January 17, 2017 9:22 AM

    VastSQL - Tuesday, January 17, 2017 12:52 AM

    Jeff Moden - Monday, January 16, 2017 11:12 PM

    VastSQL - Monday, January 16, 2017 10:23 PM

    Jeff Moden - Monday, January 16, 2017 10:30 AM

    VastSQL - Sunday, January 15, 2017 11:28 PM

    Lowell - Sunday, January 15, 2017 5:44 AM

    After a large delete, you'll need to rebuild the indexes on the table.
    the deleted items still take up the pages they used to occupy, and reindexing rearranges and deletes those empty pages.

    Thanks Lowell,

    We have weekly index maintenance task for this , but how can we reclaim space without fragmenting and then rebuilding the whole DB?

    You can't.  It's a huge short-coming that MS built into SQL Server a long time ago.  About the best you could do is to try a "shrink" that does NOT use the "reorganize" option.

    Thanks Jeff,

    So with current archiving method we can just delete the data and nothing much right. It wont give any improvement i was hoping for? Also when we do shrink in what all way it affects the DB? Is there any best practice for shrinking , As I mentioned earlier the table itself is 18Gb and deleting the data will increase the log file also. When we do shrink do we need to mention the size (Total size 40 GB (mdf 30 + log 10) and out of it this table holds 18GB) to which the size has to be reduced.

    Once you recover the original space, then nightly archive processes won't take up much space and the new data will have room to go in probably without any growth.  If you actually need to recover the space because of the items I mentioned in the post above this one, then you will need to shrink your files and properly regrow the LDF.  Then the nightly process will live comfortably in the relatively small area that it will occupy.  If you do the archive only once per month, then I wouldn't do any shrinking (other than the very first shrink after you massive initial archive process) because it will grow to be the same size at a monthly level as ash304 was suggesting... just at a different level.

    Thanks Jeff,

    What if i go for deleting 10 months old data every day and shrink the DB (not individual files) on a weekly basis before the Index maintenance job?

    As Eric and a couple of other people have stated, shrinking every day or any other scheduled basis is a really bad idea.  Shrinks should only be done once to recover a massive amount of space and then the indexes must be rebuilt because of the massive "inversion" fragmentation that will occur.  Any plan that includes a scheduled or frequent manual shrink is seriously flawed.

    Thanks Jeff.

Viewing 15 posts - 1 through 14 (of 14 total)

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