none
SP_ATTACH_SINGLE_FILE_DB failed with error message "The FILE SIZE property is incorrect."

    Pregunta

  • Hi,


    we had an SQL Failout on a development SQL Server with no backups. All we have are the .mdf files.
    Because of the failout the .mdf files are not in a detached state!

    I have tried to reatach this mdf file to another SQL Server by using: SP_ATTACH_SINGLE_FILE_DB and received the following error message:


    Msg 5172, Level 16, State 15, Line 1
    The header for file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\CRM2011RC_MSCRM2.mdf' is not a valid database file header. The FILE SIZE property is incorrect.

    Are there any methods to clean the mdf file and make it attachable?
    Thanks for any hints!

    regards
    Matthias

    martes, 06 de septiembre de 2011 18:05

Respuestas

Todas las respuestas

  • Hi

    Have a look to Paul Randall's post series about recovery...

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

    http://www.sqlskills.com/BLOGS/PAUL/post/Search-Engine-QA-21-File-header-pages-and-file-header-corruption.aspx

    But according to the second post I've doubt that's possible.

    Regards


    Christian Robert - MVP SQL Server - Microsoft Certified Master - SQL Server 2008
    Blog : http://www.sqlnco.ch
    Groupe des Utilisateurs Francophone de SQL Server : http://www.guss.fr
    martes, 06 de septiembre de 2011 21:58
  • Hi Christian,

    thank you for that links! Now I'have attached the database and set in emergency state & Single User mode  but when I run the

    DBCC CHECKDB (CRM2011RC_MSCRM, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
    GO

    Statement I only receive the Message:
    "Database 'CRM2011RC_MSCRM' is being recovered. Waiting until recovery is finished."

    The database has a size of 511 MB, is there any  opportunity to check if theres is really running a recovery job?

    Regards
    Matthias

     

    miércoles, 07 de septiembre de 2011 7:55
  •  

    The database has a size of 511 MB, is there any  opportunity to check if theres is really running a recovery job?

     

    You can run the following T-SQL to check if there is any task used for recovery:
    SELECT 
        d.PERCENT_COMPLETE AS [%Complete],
        d.TOTAL_ELAPSED_TIME/60000 AS ElapsedTimeMin,
        d.ESTIMATED_COMPLETION_TIME/60000   AS TimeRemainingMin,
        d.TOTAL_ELAPSED_TIME*0.00000024 AS ElapsedTimeHours,
        d.ESTIMATED_COMPLETION_TIME*0.00000024  AS TimeRemainingHours,
        s.TEXT AS Command
    FROM    sys.dm_exec_requests d
    CROSS APPLY sys.dm_exec_sql_text(d.sql_handle)AS s
    ORDER   BY 2 DESC, 3 DESC
    


    Best Regards
    Alex Feng | Forum Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help. If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.
    jueves, 08 de septiembre de 2011 0:53
  • I guess you should also add the session_id as a filter, passing the id of the session which is running the dbcc, and also add waittime and waitype column, to see if the executions change state, which means that it's actually running.

    kind of

    select

    wait_type, wait_time, percent_complete from sys.dm_exec_requests where session_id =

    lunes, 12 de septiembre de 2011 21:11