0

I have a database with the database files on the E: drive, and logs files on the D: drive.

I'm getting the following error when trying to run a transaction log backup:

Read on "D:\LOG\AOA_Log.ldf" failed: 23(Data error (cyclic redundancy check).)

I'm also getting a bad block message in the Windows event log.

A Full database backup works fine.

Can I change the database property to simple restore and shrink the log file?

Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

The server is a VMWare guest with the data residing in a disk array.

| improve this question | |
  • 3
    Why do you think shrinking the file will fix a potentially imminent catastrophic problem with the underlying drive? I would prefer to restore the database properly to a drive that is not exhibiting such problems, even if that means temporarily the data and log files will live on the same drive. – Aaron Bertrand Sep 11 '19 at 14:52
  • It was really a question, not a statement. Because the only issue is with the transaction log file. The database is fine. I read a post earlier today stating this.... Put the database in single recovery mode. Shrink the transaction log. Put the database back into full recovery mode. Make a full database backup. Start again your transactional backups regular jobs. – Ken A Sep 11 '19 at 15:15
  • 2
    I would move everything off this drive/array until the infrastructure people have had a chance to look at it... Shrinking just gives you yet another chance to give some bad parts of the drive/array. I'm with Aaron on this. – Sean Gallardy - Retired User Sep 11 '19 at 15:19
  • I am the "Infrastructure People" I'm also the default database admin. Can I preform a chkdsk on that drive that will unmount the D: drive to preform the chk without messing up the connection to the ldf file? The drive/array is part of a VMWare virtual disk. Do I detach the database then move the location of the log file without messing up the data? – Ken A Sep 11 '19 at 15:38
  • 2
    You will need to stop SQL Server or detach all databases that have files on D: before you dismount it. If you can afford the downtime, I would recommend disconnecting all connections, doing a full backup, testing the full backup, then restoring it. The disk corruption is a serious issue and needs to be fixed, although I know it's often quite difficult to determine cause of issues like this. Upgrade any firmware and drivers involved. – Tony Hinkle Sep 11 '19 at 15:44
0

To answer the two specific questions in your post:

Can I change the database property to simple restore and shrink the log file?

You can switch to the simple recovery model and shrink the log. This might workaround the problem for a little while if it moves the data off of the faulty area. Or it might cause more problems (if there are more "bad" areas in this drive / array that get written to).

Can I run chkdsk on the D: drive (need to dismount) without messing up the database?

Again you can do that, but there are some caveats. Tony mentioned one in a comment:

You will need to stop SQL Server or detach all databases that have files on D: before you dismount it.

So there will be downtime involved for this. Tony also mentioned that, before disconnecting everything, you should take a full backup and restore it somewhere else, to make sure the backup was good. That way, once you have finished chkdsk and resolved any issues there, you can confidently restore from that backup.

| improve this answer | |
  • Thanks you guys a ton for the information. I will be working on the server this weekend. A lot of great info to go with here. It's a Microsoft Dynamics NAV system so the restore to a different location might be a bit tricky for NAV. I was thrown into managing this system. – Ken A Sep 11 '19 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.