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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Data Corruption Issues
 Detect LDF corruption
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lithuanian98
Starting Member

USA
2 Posts

Posted - 02/18/2015 :  10:41:46  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/18/2015 :  12:34:25  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

USA
2 Posts

Posted - 02/18/2015 :  12:42:37  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/18/2015 :  12:50:33  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 05/28/2015 :  02:03:04  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000