SQL server 2005 database suspect

  • SQL 2005 ,64-bit, SP3 database went into suspect mode. The only error in the errorlog is

    .mdf" appears to have been truncated by the operating system. Expected size is 471232 KB but actual size is 462016 KB

    Any thoughts /advice?

    Thanks in advance

  • The best option would be to restore the database from good backups

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Thanks Pardeep..but this is happening every now and then Microsoft confirmed it's hardware issue so we built a new server with new SAN but still no luck 🙁

  • Restore from a clean backup, there's no repair for this.

    The problem is somewhere in the IO subsystem. Something (operating system, drivers, firmware, SAN controller, etc) is damaging the file. The trick is going to be finding what it is.

    Make sure your firmware (HBA, fibre switch, SAN controllers, etc) is up to date

    Remove any filter drivers from the IO stack (antivirus, IO optimisers, etc)

    Check the SAN for errors

    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
  • I concur with Gail. There is serious issue with the underlying storage. Please involve the storage vendor to look into this.

    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @pradeepadiga

  • Thanks a lot guys ,will have my Storage team look into the issue

Viewing 6 posts - 1 through 5 (of 5 total)

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