SQL Server Performance

dbcc checkdb fails

Discussion in 'SQL Server 2008 General DBA Questions' started by serrano, Oct 3, 2011.

  1. serrano New Member

    Ok, a nightmare scenario:
    A partitioned database got wiped and then block-restored, it's now attached but wont go online since the log files are gone.
    Upon starting SQL service, the db went in suspect mode, so i put it in emergency & single_user and executed dbcc checkdb('dbname',repair_allow_data_loss).
    After a few seconds i get the result that there is not enough of database data to begin restoring the log.
    The database is again put in suspect-mode and i'm back where i started.

    Is the situation beyond all hope of repair? Biting nails here, any ideas?
  2. Shehap MVP, MCTS, MCITP SQL Server

    First , welcome Serrano to Forms

    Kindly please assure the following steps:

    1- You put DB Emergency mode then in single mode as next :

    ALTERDATABASE DB Name SETEMERGENCY;

    ALTERDATABASE DB Name SETSINGLE_USER;

    2- Stop SQL Server service>>Cut log file exist there to another place.>> to replace the log file which looks like apparently complain of much VLFs (as discussed below )

    3- Run SQL Server service

    4- Then repair it as following :

    DBCC CHECKDB (DB name, REPAIR_ALLOW_DATA_LOSS)WITHNO_INFOMSGSDBCC

    5-Then bring it online then multiuser

    ALTERDATABASE DB Name SETonline;

    ALTERDATABASE DB Name SETMulti_USER;

    Irrespective that , Why much VLFs…?

    It is due to much (virtual log files) generated by:

    · Almost a small transaction log growth exist there that doesn’t match up with rapid log growth (Recommended to set it by 10 MB at least for such circumstances )

    · Or some times small initial Log file size , recommended to keep it around 1 GB for both shrink jobs or initial log file setting
  3. satya Moderator

    Just a thought, what is the backup status and strategy on thsi database. If the recent backup is available you can restore that and then repair what Shehap suggested above, then transfer the relevant data as you need.

    I would always gowith BACKUP first than depending upon DBCC statement.

Share This Page