Actually yes, this is a SCCM's SQL instance. Thanks for the pointer, however it seems there's no solution to the problem. Guess I'll have to configure our monitoring system to...

Hi guys, thanks for the responses.

I found the culprit, it's embarrassing that I haven't noticed this before - actually the backup drive was running out of space and log backups...

Unfortunately nothing fruitful. The problem got dissolved in company-client politics since there was no conclusive proof. Actually the transaction log corruption happened again after couple weeks of everything going well,...

Just an update, even Dell (the server manufacturer) didn't find any hardware problem.

Looks like, I'll have to open a case with Microsoft, not sure they'll be of any help though.

Thanks everyone for responding.

The problem came back (different DB's trans. log this time).

As for the questions, no there are no other I/O related errors on the SQL server or the...

Thanks GilaMonster, I'll try the recovery switch mode once again and see if it comes back again.

I'll do some additional HW diagnostics if it reveals something.

I was aware of the...

crazy4sql:

re 1) I'm fairly sure as I've ran chkdsk on the drive without errors. The drive is logical partition on RAID1 array and there are other databases and their logs...

That was an immediate switch, the recovery model is Full ever since the switch.

The switch was made based on the following artictle: http://sqlskills.com/BLOGS/PAUL/post/How-can-a-log-backup-fail-but-a-full-backup-succeed.aspx

I know it breaks backup chain,...

Thanks for the hints, I ended up using sp_MSforeachdb, because I couldn't figure the way around putting the whole cursor in dynamic statement.

At least it works finally.

Thanks GilaMonster,

but when I put the Shrinkfile inside the dynamic SQL statement, it complains that I have to define the scalar variable @targetsize. When I put the declaration of that...

Jeff, thank you for responding. Here's the code - please bear with me, as I've already stated I'm no T-SQL expert 😉

use Maint_db

drop table dba_logusage_temp

create table dba_logusage_temp

( database_name nvarchar(255),

...

Ok, 2 days and 10 lines of code later I've moved somewhere....

I have created a table which is a result of DBCC SQLPERF(logspace), so it contains DB names, log usage,...

It seems my initial post was bit unclear judging by the responses.

I'm not a DBA, I'm a sysadmin. I have very limited knowledge of T-SQL (can write simple SELECT statements...

Thanks, but I know it's done via DBCC SHRINKFILE, but I have very little knowledge of T-SQL, so that's why I was asking for the code. For someone knowledgeable, this...

Thanks Spencer, exactly what I was looking for.