Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Corrupted DB after transaction log full Expand / Collapse
Author
Message
Posted Sunday, September 30, 2012 3:56 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
One-time DB job was scheduled at night to add two columns (computed, persistent) on a large table (5 GB together with indexes, witout compression about 10GB).
It is a compressed heap with several nonclustered indexes also compressed, partitioned by month with aligned partitions.
The command in the job was just "ALTER TABLE xy ..." that adds two columns.

The transaction lasted for several hours, and failed.
After that, the database was online, but most of users users got very odd errors every time:

Error: 615, Severity: 21, State: 1
Could not find database ID 6144, name '6144'. The database may be offline. Wait a few minutes and try again.

Some users could use the db normally, but most of the users got hundreds of that message, sometimes with different number as the "name" of the database. Such numeric names of DB of course do not exist and have never existed on the server.
Totally crazy!

DB was online and DBCC CHECKDB found no errors.
But almost none of the users could use the DB, so I had to fix it somehow.

Error log for the job reported this set of errors:

TCP Provider: An existing connection was forcibly closed by the remote host.
[SQLSTATE 08S01] (Error 10054)

Communication link failure [SQLSTATE 08S01] (Error 10054)

The transaction log for database '*DBNAME*' is full.
To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
[SQLSTATE 42000] (Error 9002)

The log for database '*DBNAME*' is not available.
Check the event log for related error messages.
Resolve any errors and restart the database.
[SQLSTATE HY000] (Error 9001)

During undoing of a logged operation in database '*DBNAME*', an error occurred at log record ID (388242:11656:483).
Typically, the specific failure is logged previously as an error in the Windows Event Log service.
Restore the database or file from a backup, or repair the database.
[SQLSTATE HY000] (Error 3314)


During a job execution, a Transaction log grew from 6 to 36GB. That is still not enough to fill-up the disc space, but not far from it (several GB were free).
So, I wonder why the "Transaction log full" error ?

After "transaction log full" striked, a huge rollback was performed. But that rollback also failed.

I ended up in a corrupted DB (although DBCC CHECKDB says it is ok, it is NOT) that is unrecoverable, except restoring it from the backup.

So, I did restore it to the point in time just before that ALTER TABLE started. Losing all the data changes and inputs after that point.
Now users can use it and looks fine, but I'm afraid that it can happen again if I don't find a reason why this severe corruption occurred.

So, please help. I have to find out. Because, I can't believe it is normal that one gets a corrupted db if transaction log fills the disk (and I don't believe that disk was totally full).

The point is - a simple ALTER TABLE ADD COLUMN on 5GB table generates 30+GB of transaction log, and unrecoverable corruption of the database.
So, I wont run that command again until I find the cause of corruption.
Is it really common in SQL server if you run out of disk space that you get corrupted DB ?

Version is SQL 2008R2 RTM on a 2-node cluster.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1366298
Posted Sunday, September 30, 2012 4:13 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
http://connect.microsoft.com/SQLServer/feedback/details/684806/rollback-causes-db-to-go-suspect-only-repairable-with-repair-allow-data-loss


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366300
Posted Monday, October 1, 2012 1:15 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
Thanks for the link. But I'm not sure it is the same case.
My database was not in a suspect state, it was online.
And DBCC CHECKDB did not find any errors.
Error occured in a transaction log record, and after rollback failed, db started to spit "Could not find database name '5463463456346'" errors when calling tsql that normally works.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1366337
Posted Monday, October 1, 2012 1:45 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
Call CSS. Sounds like you hit a rather strange bug.

Is it reproducible?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1366349
Posted Wednesday, October 3, 2012 9:17 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 2:57 AM
Points: 369, Visits: 1,208
After investigation, I found the drive was full indeed at the time of error, but in the morning when I looked at it it was not full, so that explains "Transaction log full" error.

But why does "Transaction log full" results with corruption of DB data?
Is that expected behavior ? (shouldn't be)


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1367780
Posted Wednesday, October 3, 2012 9:33 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
It shouldn't, I've never heard of it happening before. If it has, it's a bug and needs to be reported to CSS, preferably with a reproduction.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1367802
Posted Monday, October 8, 2012 4:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 8, 2012 4:52 AM
Points: 1, Visits: 0
The MDF files of SQL database may get corrupt due to some problem in the database. If such a situation arises, the user can use the software which recovers the lost MDF file. But before choosing the software, make sure that it is efficient enough to recover the unique keys, primary keys, indexes, stored procedures, views etc from your corrupt MDF file.
Post #1369713
Posted Monday, October 8, 2012 8:41 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 17, 2014 3:03 AM
Points: 173, Visits: 566
NehwalSaina12 (10/8/2012)
The MDF files of SQL database may get corrupt due to some problem in the database. If such a situation arises, the user can use the software which recovers the lost MDF file. But before choosing the software, make sure that it is efficient enough to recover the unique keys, primary keys, indexes, stored procedures, views etc from your corrupt MDF file.


MDF repair software?! Are they actually any good?


-------------------------------------------
I'm not afraid of storms, for I'm learning to sail my ship
Post #1369861
Posted Monday, October 8, 2012 8:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
a.SQL.com (10/8/2012)
NehwalSaina12 (10/8/2012)
The MDF files of SQL database may get corrupt due to some problem in the database. If such a situation arises, the user can use the software which recovers the lost MDF file. But before choosing the software, make sure that it is efficient enough to recover the unique keys, primary keys, indexes, stored procedures, views etc from your corrupt MDF file.


MDF repair software?! Are they actually any good?


Most aren't. Buyer beware in this area.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1369865
Posted Monday, October 15, 2012 12:08 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 16, 2014 5:10 AM
Points: 112, Visits: 1,207
Agree with Gilamonster,

You first check the demo version of MDF Repair software. The demo version shows the preview of repairable objects. If the software does not show the objects in preview section, it means the software cannot repair the file. You can easily search MDF repair software On Google


SQL Database Recovery Expert
Post #1372566
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse