Post reply

Creating a suspect database.....

  • December 28, 2011 at 2:02 am

    #248900

    Hi Experts,

    For practice purpose iam creating a suspect database,

    I don't know these are the correct steps,

    first iam creating one database and inserted the values to that table.

    Now I'll perform an update in an explicit transaction and force it to be written out to disk with a CHECKPOINT.

    now in another window i'll simulate a crash using

    SHUTDOWN WITH NOWAIT;

    GO

    But this msg iam getting

    Server shut down by NOWAIT request from login servername.

    SQL Server is terminating this process.

    please help....tell me the steps how to create a suspect mode database?

  • December 28, 2011 at 4:10 am

    #1426014

    That won't create a suspect DB, all that will do is make the DB go through crash recovery after startup. It'll come online fine after that. SQL can handle crashes just fine.

    To get a suspect DB, you need SQL to encounter corruption either during a transaction rollback, during crash recovery. To simulate that corruption, you'd need to take a hex editor to the database file.

    Easiest way:

    1) Explicit transaction, update a table. Don't commit.

    2) Checkpoint

    3) Terminate the SQL process (shutdown with nowait or kill process from task manager)

    4) Open the database file in a hex editor

    5) Find the row that you modified in step 1 and corrupt it (write zeros over, that's the easiest)

    6) Save the file

    7) Start SQL. If you've done things correctly, the DB will become suspect when SQL tries to run crash recovery.

    It should go without saying that this should never, never, never be done on a production or shared server. Do it on your local machine.

    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

  • December 28, 2011 at 4:24 am

    #1426018

    Very similair to Gail's description, but with pictures:

    http://sqlskills.com/BLOGS/PAUL/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

    Steve Hall
    Linkedin
    Blog Site

  • December 28, 2011 at 4:40 am

    #1426022

    Thank you for your help.

    Let me try now on my local machine.

  • December 30, 2011 at 7:14 am

    #1426940

    Hi,

    I have created database and enter the values in to that table.

    and also perform the explicit transaction after that in another window i wrote the below steps

    SHUTDOWN WITH NOWAIT;

    GO

    but server is not shutdown. 🙁

    result shows:Server shut down by NOWAIT request from login XXXXXXX-PC\XXXXXXX.

    SQL Server is terminating this process.

    kindly tell me how can i create suspect db in my local machine?

  • December 30, 2011 at 7:23 am

    #1426945

    jr.sqldba (12/30/2011)

    Hi,

    I have created database and enter the values in to that table.

    and also perform the explicit transaction after that in another window i wrote the below steps

    SHUTDOWN WITH NOWAIT;

    GO

    but server is not shutdown. 🙁

    result shows:Server shut down by NOWAIT request from login XXXXXXX-PC\XXXXXXX.

    SQL Server is terminating this process.

    kindly tell me how can i create suspect db in my local machine?

    Power off your computer... wait twenty seconds... power it on.

    After SQL Server services are started, what's the status of your database?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • December 30, 2011 at 7:30 am

    #1426948

    PaulB-TheOneAndOnly (12/30/2011)

    jr.sqldba (12/30/2011)

    Hi,

    I have created database and enter the values in to that table.

    and also perform the explicit transaction after that in another window i wrote the below steps

    SHUTDOWN WITH NOWAIT;

    GO

    but server is not shutdown. 🙁

    result shows:Server shut down by NOWAIT request from login XXXXXXX-PC\XXXXXXX.

    SQL Server is terminating this process.

    kindly tell me how can i create suspect db in my local machine?

    Power off your computer... wait twenty seconds... power it on.

    After SQL Server services are started, what's the status of your database?

    First of all it’s crazy idea :w00t:. Second, I am very sure databases nowadays are smart enough to auto-recover from power outage scenarios (at least).

  • December 30, 2011 at 7:30 am

    #1426949

    jr.sqldba (12/30/2011)

    I have created database and enter the values in to that table.

    and also perform the explicit transaction after that in another window i wrote the below steps

    SHUTDOWN WITH NOWAIT;

    GO

    but server is not shutdown. 🙁

    That command terminates SQL Server (it doesn't shut the machine down) If you check services, you'll see that SQL is no longer running.

    I gave you the steps to create a suspect database, a hard shutdown will not do it (whether it's terminating SQL or shutting the machine down). To get a suspect database you need corruption to be encountered during crash recovery or a rollback.

    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

  • December 30, 2011 at 7:47 am

    #1426958

    Thank you Gail Shaw. 🙂

    Now my db is in suspect mode.I Just followed your steps.

  • December 30, 2011 at 8:51 am

    #1427013

    Dev (12/30/2011)

    PaulB-TheOneAndOnly (12/30/2011)

    jr.sqldba (12/30/2011)

    Hi,

    I have created database and enter the values in to that table.

    and also perform the explicit transaction after that in another window i wrote the below steps

    SHUTDOWN WITH NOWAIT;

    GO

    but server is not shutdown. 🙁

    result shows:Server shut down by NOWAIT request from login XXXXXXX-PC\XXXXXXX.

    SQL Server is terminating this process.

    kindly tell me how can i create suspect db in my local machine?

    Power off your computer... wait twenty seconds... power it on.

    After SQL Server services are started, what's the status of your database?

    First of all it’s crazy idea :w00t:. Second, I am very sure databases nowadays are smart enough to auto-recover from power outage scenarios (at least).

    mmmhh... you have to read more carefully. Did you notice poster was already supposed to corrupt the database? power off/on ensures - in a very dramatic way - that sqlserver service gets recycled then if the database was corrupted as expected it will show up as "suspected". Got it?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 10 posts - 1 through 9 (of 9 total)

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