SQL Server Performance

Space decreases when deleting records

Discussion in 'General Developer Questions' started by d17may, Jan 7, 2007.

  1. d17may New Member

    When i delete records(about 10 lakh rows) from table space on hard disk decreases .I guess
    this is because of the log written by sql server .What are the other posssible reasonI want that when i delete records it should not consume space .
    Thanks


    Thanks
    d17may
    d17may, Jan 7, 2007
    #2

  2. MohammedU New Member

    If you delete all million rows in single transaction your log file will grow and will take free space...

    YOu can delete them in small chunks using while loop so that you log file will not grow...


    Mohammed U.
    MohammedU, Jan 7, 2007
    #2

  3. d17may New Member

    quote:Originally posted by MohammedU

    If you delete all million rows in single transaction your log file will grow and will take free space...

    YOu can delete them in small chunks using while loop so that you log file will not grow...


    Mohammed U.
    Even if i use it takes space difference is only that log file not grow instantly
    .But i need that it not write log for this particular transaction.
    Thanks

    Thanks
    d17may
    d17may, Jan 7, 2007
    #2

  4. dineshasanka Moderator

    if you want to delete all rows of a table, use TRANCATE TABLE command, it will use less Log resources.

    You can change the recovery model of the database to simple. But you will not have chance to recover your data in case you need them

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
    dineshasanka, Jan 7, 2007
    #2

  5. madhuottapalam New Member

    is it a 24x7 kind of machine ? what is the isolation level u have?

    Madhu
    madhuottapalam, Jan 7, 2007
    #2

  6. d17may New Member

    We dont store the data which is 3 months old.If the table has more data then it slow the select query .Since there are so much data which is 3 months less i need space .
    Thanks


    Thanks
    d17may
    d17may, Jan 7, 2007
    #2

  7. MohammedU New Member

    create a job to delete the rows older than 3 months and schedule it to run everyday...so that you will delete every one day records only....
    Eventhough I will advise you to use while loop to delete the records few at a time without locking the table...

    Make sure you do the reindexing every week otherwise your table will be fragemented and slows your select also...


    Mohammed U.
    MohammedU, Jan 8, 2007
    #2

  8. danny123 New Member

    How about if we change the mode to Bulk-Logged while deleting the rows.. As in this mode transactions are not logged.

    Best
    Danny

    "We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true."
    danny123, Jan 9, 2007
    #2

  9. HankS New Member

    quote:Originally posted by danny123

    How about if we change the mode to Bulk-Logged while deleting the rows.. As in this mode transactions are not logged.

    Best
    Danny

    "We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true."


    Some bulk operations are not logged, that does not mean transactions aren't logged. Doesn't apply to DELETE.

    Hank
    HankS, Jan 11, 2007
    #2

Share This Page