Transaction log back and point in time recovery

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Transaction log back and point in time...

Transaction log back and point in time recovery

Rate Topic

Display Mode

Topic Options

Author

Message

MSSQL_NOOB

Posted Monday, December 31, 2012 8:17 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 10:08 AM
Points: 37, Visits: 106

SQL Server 2000 - Full database back up at 9:00pm; transaction log backup at 12:00pm. Internal database, therefore, database use normally from 7:00am - 9:00pm.

Question 1) If backup mode is full; why doesn't it backup the transaction log and truncates / cleans it?

Question 2) Transaction log is getting big at 8:00am - before the transaction log gets full, what is the best course of action and at what time and still be able to restore to point in time if anything happens to database?

I was reading about log shrinking but

Question 3) do not understand reason for the double DBCC SHRINKFILE

Question 4) What is, if any the implications it'll have on point in time recovery if I do DBCC SHRINKFILE

Thanks

Post #1401402

Lynn Pettis

Posted Monday, December 31, 2012 9:06 AM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 8:34 AM
Points: 21,588, Visits: 27,385

You may want to read this article, Managing Transaction Logs.

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1401409

george sibbald

Posted Monday, December 31, 2012 9:12 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 5,265, Visits: 11,194

simple answer - do not shrink the log.

Backup your transaction log more frequently to avoid it filling, so instead of one transaction log backup job at 12pm , run it say 1 or 2 hourly during the day.

As to exactly how often that depends how much data you feel you can afford to lose, because you can always restore to the last transaction log backup. For a point in time restore that is the same as currently, just restore all the transaction logs in order, then do a point in time for the last log backup if necessary.

The full backup gives you a restore start point, it does not truncate the log so following transaction log backups can be taken without a break in the restore chain,

please read this : http://www.sqlservercentral.com/articles/Administration/64582/

---------------------------------------------------------------------

Post #1401412

MSSQL_NOOB

Posted Monday, December 31, 2012 10:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 10:08 AM
Points: 37, Visits: 106

Thank you for the quick response.

I read the article and it says "If (once the log backups have been setup and scheduled based on the database's RPO) the log is growing larger than is acceptable, then the log backup frequency can be increased in order to keep the size down."

I'm confused ... how is it supposed to do that? In my situation, I have a transaction log file that is 1.5gb and it has gone through a few days of transaction log backup at 12:00pm. However, the size is still 1.5gb.

I executed the following

SELECT * FROM sysfiles;
GO

This is actually the concern. My log file is larger than my data file. That was the reason I started researching on Transaction Logs. For that matter, should I even be concern with my log file larger than my data file?

In addition, I have a "Work in Progress to do" list from the former DBA to shrink log files on databases where the log file is larger than the data file.

Post #1401443

george sibbald

Posted Monday, December 31, 2012 12:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 5,265, Visits: 11,194

your log file has grown larger than your data file because it was not being backed up often enough, therefore at some point it has had to grow to accommodate the information being written to it.

The log backup removes the commited transactions from the log, freeing up space internally for reuse, this is what is meant by truncating. A log backup does not reduce the size of the file on disk, only shrinkfile does that.

A log file larger than the data file is not a problem in itself if you have the disk file space to accomodate it. If you have a large no. of vlfs however that will slow up database recovery and restores.

If you really want to shrink the log, find out the maximum log backup size when you instigate more frequent log backups and shrink the log to a size that will accomodate that (largest log backup is usually after index rebuilds). Do this shrink only once though.

to find out how many VLFs you have run dbcc loginfo(yourdb) - the no. of rows returned is the the no. of vlfs - if its in the thousands you have too many.

quick way to see space used within the log - dbcc sqlperf(logspace) - run that before and after a log backup to see the effect it has on the log.

check your growth factors are sensible - not the default 10%. For your size log 64mb would be good, then each growth will give you 4 more vlfs.

---------------------------------------------------------------------

Post #1401478

MSSQL_NOOB

Posted Monday, December 31, 2012 12:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, March 28, 2013 10:08 AM
Points: 37, Visits: 106

Here are the numbers after today's 12:00pm transaction log backup. Missed the pre-backup numbers.

VLFs or dbcc loginfo(yourdb) returns 288 rows.

dbcc sqlperf(logspace) returns Log Size of 1588 MB (or the 1.5GB I had mentioned); and 1.23xxx % Log Space Used.

If the percentage used is averaging around, say 5% BEFORE transaction log backup; does that mean there's just a spike at one point in time of use? If so, does that also means I should be issuing SHRINKFILE commands from time to time, after the full backup?

I'm not trying to 2nd guess you ... but I just do not understand why the following recommendations: "Do this shrink only once though."

I understand that everytime a log file needs to auto expand, it takes up resources. But, if it's only using say 75MB (5% of 1.5GB); wouldn't it be good measures to shrink it from time to time - to save space not used 99% the time and the space allocated is only for 1 time use?
And considering my anticipated users usage is from 7am - 9pm; wouldn't a "cleanup" be recommended after 9pm? All data should be committed (and written to data file), full backup will make sure I have a copy of data file of all committed transactions, wouldn't it?

At least, that's what my noob dba brain is telling me.

Thanks!

Post #1401487

GilaMonster

Posted Monday, December 31, 2012 1:45 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 37,650, Visits: 29,901

MSSQL_NOOB (12/31/2012)

If the percentage used is averaging around, say 5% BEFORE transaction log backup; does that mean there's just a spike at one point in time of use?

Maybe. You'd need to investigate and see if it was a one-time spike or if it's a regular spike. Spikes in log usage are often regular.

I used to have a DB that had a 250GB log file. During the week it used 10% of that at most. On a sunday night, when we did imports, it reached 85%.

If so, does that also means I should be issuing SHRINKFILE commands from time to time, after the full backup?

Nope. Do not schedule shrink operations. Once off, or after uncommon operations like large once-off data imports or archiving processes.

I'm not trying to 2nd guess you ... but I just do not understand why the following recommendations: "Do this shrink only once though."

Because growing a log is an expensive operation and causes internal fragmentation if the growth increments are inappropriate. Shrink is also an expensive operation.

So shrink often and you get into this nice shrink-grow cycle that takes huge amounts of resources and time, fragments the log and achieves nothing.

I understand that everytime a log file needs to auto expand, it takes up resources. But, if it's only using say 75MB (5% of 1.5GB); wouldn't it be good measures to shrink it from time to time - to save space not used 99% the time and the space allocated is only for 1 time use?

Not really. There's no penalty or problem from lots of free space. If the log really needs that space, it'll just regrow (and if the space isn't there you'll get log full errors and failed transactions)

And considering my anticipated users usage is from 7am - 9pm; wouldn't a "cleanup" be recommended after 9pm? All data should be committed (and written to data file), full backup will make sure I have a copy of data file of all committed transactions, wouldn't it?

What's the full backup got to do with anything? It doesn't truncate the log, it doesn't shrink the log.
SQL is not MS Access that requires regular compact and repair to function properly.

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 #1401495

GilaMonster

Posted Monday, December 31, 2012 1:47 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:35 PM
Points: 37,650, Visits: 29,901

MSSQL_NOOB (12/31/2012)

SQL Server 2000 - Full database back up at 9:00pm; transaction log backup at 12:00pm.

Backing up the log once a day? That's useless for recovery. It means you have a maximum data loss of 24 hours in the case of a disaster. If that's acceptable, switch to simple recovery and leave the log alone. If that's not acceptable, you need more frequent log backups.

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 #1401497

george sibbald

Posted Monday, December 31, 2012 3:31 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:24 AM
Points: 5,265, Visits: 11,194

MSSQL_NOOB (12/31/2012)

check the size of the log backup to tell you how much space was used in the log file.

288 VLFs would not concern me.

Its not a 'cleanup', its a logically consistent snapshot of your data, but yes you still need to take regular full backups as well as log backups, if only so you don't have too many log backups to work through in a recovery scenario. Work out a backup strategy that gives you the recovery you require. Its being able to recover the database within acceptable data loss limits (to your organisation) that matters.

---------------------------------------------------------------------

Post #1401519

« Prev Topic | Next Topic »

Permissions