Post reply

Database become recovery status

  • April 12, 2011 at 11:41 pm

    #248851

    Hi,

    I have deleted the 5 million records, then rebooted server on database was goes recovery status.

    I think reason transaction was in-completed so that database was recover status.

    SQL server 2000 by default auto commit transaction, but why database recovery status after rebooted server.

    please tell me what could be reason?

    rgds

    ananda

  • April 13, 2011 at 12:07 am

    #1310807

    Because SQL has to do crash recovery (google the term) to ensure that any committed transactions whose changes had not been hardened into the data file at the time of shutdown are replayed to ensure a consistent database.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 13, 2011 at 12:47 am

    #1310824

    Thanks GilaMonster for reply...

    I received call from one of the client site, they did delete huge records for last 3 yrs records. It has taken long time for completed transaction. So they were not wait upto completed transaction. during this delete transaction rebooted server by them.

    I want to know spacial consideration for delete huge records in table.

    1. Take fullbackup

    2. Generate Index script if already exist in table

    3. Delete the index ( Reduse time taken for delete records)

    4. begin transaction

    delete statement

    commit trasaction

    5. dbcc updateusage for reuse deleted space

    6. run the index statement.

    Please tell me, Those points are suffecient or not?

    Thanks

    ananda

  • April 13, 2011 at 1:31 am

    #1310835

    You may want to take a look at my article - http://www.sqlservercentral.com/articles/T-SQL/72606/[/url] - which was recently published by SSC.

    In this post, I look at one of the strategies that we use to delete large number of records from a VLDB.

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

Viewing 4 posts - 1 through 3 (of 3 total)

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