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.
| 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
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 KizerSQL Server MVP since 2007http://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. |
|
|
|
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.sqlsqlcmd -E -S server2\instance -i c:\scripts\checkdb.sqlIn checkdb.sql would be sp_foreachdb with DBCC CHECKDB.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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. |
|
|
|
|
|
|
Detect LDF corruption