Link to home
Start Free TrialLog in
Avatar of Torus
Torus

asked on

log recovery questions

I have some questions on the log recovery.  I have a database which set to full recovery mode. The mdf and ldf are set to unlimited growth. However, the ldf will grow very fast. This is not I want.  If I backup the database once a day and 3 hours on the ldf,

1.   I don't want the ldf kept growing. Before I backup, I can truncate the ldf and shrink it?
If the problem occurs after backup, I can use the backup as a checkpoint to recover? I saw some article saying that if using backup, the log file will be truncated automatically, is it true?


2.   for example, if I backup at  2:00 am ,  4:00 am, 7:00 am, 11:00 am will backup the log and the mdf is corrupted at  1:00 pm before the next log backup, how can I use the backup mdf and logs and existing ldf to recover all data including those from 11:00 am to 1pm.

3.   if I accidentailly delete some records, can I recover the data using the active log file? or it is really no way to recover?

4.   Can I specify a time for the point of recovery? e.g. I just want to recover 10:00 am data, how can I use the 11:00 am backup log to just get back the data before 10:00 am?

5.   How come if the database is set to full recovery, the log file will increase the size every time I shrink the database? it is normal?
Avatar of Aneesh
Aneesh
Flag of Canada image

Torus,
> I don't want the ldf kept growing. Before I backup, I can truncate the
> ldf and shrink it?

Take backups of your log... no need for truncate


> 3.   if I accidentailly delete some records, can I recover the data using the active log file?

depends, If you have the backups then possbly you can recover the deleted records

Avatar of Torus
Torus

ASKER

can you eleborate more?

I have tried to backup the log. Just the backup has smaller size of file. But the size of the active log, i.e the ldf still keep the same.


I have backup on 2:00 am and if delete the records at 3:00 am? so can recover?
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Torus

ASKER

I just checked the physical size of the ldf.  Do you mean that after backup, the logical size of the active log ldf will be decreased automatically? what truncate means I use  backup log mydb with truncate_only and the shrink the file. If I need to reduce the physical size of the log, it should be shrunk, rite?

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial