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?
Microsoft SQL Server

Avatar of undefined
Last Comment
Netstore

8/22/2022 - Mon
Aneesh

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

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
Aneesh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.