Backup - Full recovery question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Backup - Full recovery question

Author

Topic

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-26 : 04:21:34

I have setup my main database with the following backup schedule

Full backup 7am
TLog backups every 2 hours from 9am until 11pm

When I look in SQL man studio at the properties of the db under files, my TLog is sitting at 20Mb. I thought the tlog backups are supposed to truncate it back down again?

Or have I misunderstood how it works?

It's not a massively active system by the way, the main DB is only 26Mb in size

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-02-26 : 06:24:17

A transaction log backup does not change teh size of the file. It marks space within the file as reusable.

--
Gail Shaw
SQL Server MVP

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-26 : 06:36:57

Ah, so it's ok to leave that as it is then at 20Mb?

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 08:21:42

Its desirable for it to be sized for the "biggest normal transaction amount"., 20MB sounds small to me

I reckon about 120% the size of MDF file - as a very rough rule-of-thumb - so you are pretty close with your MDF = 26MB.

"TLog backups every 2 hours from 9am until 11pm"

You happy / prepared to lose two hours work?

My advice would be to make the TLog backups every 15 minutes - I can't think of a reason not to, and they might just save your skin one day

(You will have more backup files each day, but the total size of the files will be about the same)

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-26 : 08:34:23

How do I make the tlog bigger in size to suit your 120% rule?

I think I might change the tlog backup frequency when my users increase, but at the moment, the db is not heavily used (hence the size at the mo)

Kristen
Test

22859 Posts

Posted - 2010-02-26 : 09:12:48

"How do I make the tlog bigger in size to suit your 120% rule?"

It will grow by itself, if it needs to.

My 120% rule is just so I can judge if "it is way too small" or "it is ridiculously large" - relative to MDF size.

Yours is fine - but check its size periodically.

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-26 : 09:17:02

Ah, ok cool, thanks for the info. I'm using the brilliant SP by Tara (I think) and it's great! I'm also using her SP to defrag indexes etc at night

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-27 : 05:57:53

If I set the tlog backups to every 15 minutes, and there are no transactions between one backup and the next, does sql ignore that backup until there are transactions in the log to actually backup?

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 06:15:31

No (AFAIK!!), it will do the backup (and it will have a minimal filesize)

In fact I am 99.99% sure that's the case ... we have "dormant" databases and they still get all their Tlog backups.

Interesting idea though, I wonder if there is something you could do to check this, and skip the backup if no data to save. It would reduce the number of files in a Restore, and keep the place more tidy on systems that have quiet periods.

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 06:17:42

I've asked the question here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140556

bpsintl
Posting Yak Master

132 Posts

Posted - 2010-02-27 : 06:35:25

What does (AFAIK!!) mean?

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 06:43:35

http://en.wikipedia.org/wiki/AFAIK