Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Detect LDF corruption

Author  Topic 

lithuanian98
Starting Member

2 Posts

Posted - 2015-02-18 : 10:41:46
Is there a script that I can run against multiple servers to see if the LDF file is corrupt? This is for error: 8985 when you try to shrink the log file. I want to see if multiple servers have a corrupt ldf file.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 12:34:25
You can run DBCC CHECKDB to verify the integrity of the database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

lithuanian98
Starting Member

2 Posts

Posted - 2015-02-18 : 12:42:37
quote:
Originally posted by tkizer

You can run DBCC CHECKDB to verify the integrity of the database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



I was looking for a script that I could run against a group of servers at once without doing DBCC CHECKDB. Thanks for the reply.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-18 : 12:50:33
But you have to run DBCC CHECKDB to detect database corruption, regardless if it's the data or log files. If you want to run it against a group of servers, then you'll need to utilize a Powershell script or similar. Here's a simple one using sqlcmd.exe that can be wrapped into a cmd file.

sqlcmd -E -S server1\instance -i c:\scripts\checkdb.sql
sqlcmd -E -S server2\instance -i c:\scripts\checkdb.sql

In checkdb.sql would be sp_foreachdb with DBCC CHECKDB.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2015-05-28 : 02:03:04
If your log file is corrupt, my concern is that a backup/restore would retain the corruption. My approach (which would probably be faster to complete) would be:
1. Run a full backup.
2. Detach database.
3. Delete/rename the log file.
4. Attach the database and rebuild the log file.
5. Take a second full backup.
To attach the database and rebuild the log file, it's just additional syntax in the CREATE DATABASE statement:
CREATE DATABASE [foo]
ON (FILENAME=<<Path to data file>>)
FOR ATTACH_REBUILD_LOG;
This will force SQL Server to rebuild the log file when it attaches the database.
The risk in this is losing whatever transactions happen between the completion of the full backup and detaching the database, but it sounds like this will be a minimal risk.

Go to Top of Page
   

- Advertisement -