Data lost after rebuilding new txn log after disk failure

Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UKWhat exact steps did you take? How did you "build a new log file"?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Jon A" <jon@.pitstart.com> wrote in message news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...[vb
col=seagreen]
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB s
ervers failed.
> The DB server was given separate physical disks to split the data and txn
log files on different
> disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again pret
ty quickly by building a
> new log file for the DB. However, the last data in the DB was written at 1
1:45. I'm not expecting
> to recover the data now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed
> because of the disk failure. Am I missing something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>[/vbcol]|||Jon
Have you performed LOG file backup?
When the disk is faild you was be able to run BACKUP LOG file WITH NO
TRUNCATE option. Restore the whole database and apply all log files with NO
RECOVERY option and the last one with RECOVERY option.
For more details you have to read BOL.
Sorry about losing the data.
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
pretty
> quickly by building a new log file for the DB. However, the last data in
the
> DB was written at 11:45. I'm not expecting to recover the data now but was
> confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
something
> here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
Data are written only to the log upon commit and then written to data files
asynchronously. When you lose your log, the only way to guarantee a
consistent database is to restore from database backup and then apply
transaction log backups.
If you rebuilt your log using an unsupported method, run DBCCs to ensure the
database is physically consistent and be aware that logical consistency is
questionable. You may have lost committed data and may also have
uncommitted data in your database.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Had a problem recently with some lost data after a disk in one of our DB
> servers failed.
> The DB server was given separate physical disks to split the data and txn
> log files on different disks to guard against disk failure.
> The disk with the txn log failed at 14:30. We got the DB online again
> pretty quickly by building a new log file for the DB. However, the last
> data in the DB was written at 11:45. I'm not expecting to recover the data
> now but was confused to see the lost data.
> I thought the dat/mdf files should have contained nearly all the data upto
> the point the DB failed because of the disk failure. Am I missing
> something here?
> Is there anything I should be checking on the server ?
> TIA,
> Jon A
> London, UK
>|||If you lost the disk with the active transaction log, then the only thing
you will be able to restore and recover is up to the last FULL and
DIFFERENTIAL database backups and the subsequent LOG transaction log
backups. You will have to recover off of the last LOG transaction log
backup. Only committed transactions are backed up from the log.
If the transaction log was still available, you could have backed that up
and recovered any committed transactions within that as well.
Regardless, uncommitted transactions will never be able to be recovered.
The system will rollback any uncommitted transactions as it goes through the
recovery process.
Sincerely,
Anthony Thomas
"Jon A" <jon@.pitstart.com> wrote in message
news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
Hi,
Had a problem recently with some lost data after a disk in one of our DB
servers failed.
The DB server was given separate physical disks to split the data and txn
log files on different disks to guard against disk failure.
The disk with the txn log failed at 14:30. We got the DB online again pretty
quickly by building a new log file for the DB. However, the last data in the
DB was written at 11:45. I'm not expecting to recover the data now but was
confused to see the lost data.
I thought the dat/mdf files should have contained nearly all the data upto
the point the DB failed because of the disk failure. Am I missing something
here?
Is there anything I should be checking on the server ?
TIA,
Jon A
London, UK|||Thx for all your responses.
We rebuilt the log using the DBCC REBUILD_LOG command that needs the DB to
be put into "emergency mode" status
We got the DB back online OK which did not have data for the last 2-3 hours.
And yes - we had some data consistency problems on a number of tables which
we tried to fix using the DBCC fix commands. This fixed all the (mainly
allocation) errors on the tables except for something in one of the System
tables. This stopped certain queries running so I created a new DB and DTS
job to copy all schema and data from the corrupt DB into the new one which
worked OK.
We run a full backup of the DB and txn log each night. So, if I'd lost my
mdf/dat disk I'd be in a better position than losing the disk with the txn
log on.
I've worked with SQLServer since Version 6.0 and this is the first time I've
lost data but I'll certainly be re-thinking my DB set-up and bu strategy now
I think !
Rgds, Jon
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ec9ZpPm8EHA.3592@.TK2MSFTNGP09.phx.gbl...
> Data are written only to the log upon commit and then written to data
> files asynchronously. When you lose your log, the only way to guarantee a
> consistent database is to restore from database backup and then apply
> transaction log backups.
> If you rebuilt your log using an unsupported method, run DBCCs to ensure
> the database is physically consistent and be aware that logical
> consistency is questionable. You may have lost committed data and may
> also have uncommitted data in your database.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jon A" <jon@.pitstart.com> wrote in message
> news:ePC79xl8EHA.2568@.TK2MSFTNGP10.phx.gbl...
>