Managing Transaction Logs

Question

Managing Transaction Logs

Viewing 15 posts - 106 through 120 (of 128 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

DBCC ShrinkFile(<name of log file>,<size log file was before abnormal growth>)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Bad_Karma Default port Points: 1439 More actions · Answer 2

Very enlightening. I was actually shrinking my log files in the first and last step of an ETL run LOL. We need a real DBA so badly.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Good luck in you search. In the meantime, may I suggest this? http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Bad_Karma Default port Points: 1439 More actions · Answer 4

What a great book title :). To give you an idea of how far out of my element I am..I have to read this after I pass my CPA exams, 1 down 3 to go.

lital-494950 SSC Veteran Points: 283 More actions · Answer 5

By mistake I choose one star I'm sorry this is great article thanks a lot

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 6

Echoing everyone else's comments - great article!

However, I was all prepared to query your statement in the article:

It is important to note that while a full or differential backup starts a log chain, full and differential backups don't break the log chain

My understanding was always that performing a Full backup would break the log chain, and if you wanted to restore to a given point using your log backups, you would have to restore from the last full backup. That's what I thought copy_only backups were introduced for, to ensure an ad-hoc full backup would not disrupt the log chain.

However, having now tested it against a 2008 R2 instance sure enough taking a full backup does not break the log chain, so I have learned something new today! Thank you! 🙂

But having said that, it begs the question what is the point of copy_only backups now?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SQLPhil (1/12/2012)
But having said that, it begs the question what is the point of copy_only backups now?

This: http://sqlinthewild.co.za/index.php/2011/03/08/full-backups-the-log-chain-and-the-copy_only-option/

You wouldn't be the first to argue with me about full backups breaking the log chain, it's an (unfortunately) commonly held belief. They've never broken the log chain though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

SQLPhil SSCarpal Tunnel Points: 4204 More actions · Answer 8

Thank you very much Gail for clarifying all of this (and for the excellent article on COPY_ONLY). You have certainly added a lot of clarity to my understanding of backups and restores.

Speaking amongst my fellow DBA colleagues we were all under the (misguided) understanding that full backups broke the log chain. In fact, even in some training courses we've been told that this has been the case. It'll be nice to have the opportunity to turn round and inform the instructor for once! 😉

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SQLPhil (1/12/2012)
It'll be nice to have the opportunity to turn round and inform the instructor for once! 😉

😀

Just be polite if you do. Speaking as a occasional trainer and presenter, there's nothing worse than a student who thinks he's right and you're wrong and makes a public issue out of it.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Johan Bijnens SSC Guru Points: 135376 More actions · Answer 10

GilaMonster (1/12/2012)
...Speaking as a occasional trainer and presenter..

And a darned good one too, may I say ! :w00t:

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

quackhandle1975 SSChampion Points: 11055 More actions · Answer 11

Great article, company I contract for are constantly changing the recovery model and shrinking the log then changing the RM back - have presented them with the URL for this, fingers crossed they will see sense! 😉

qh

[font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

best_yunus SSC-Addicted Points: 409 More actions · Answer 12

Can we do point in time recovery in Bulk loged recovery model?

If yes then how?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

best_yunus (5/12/2012)
Can we do point in time recovery in Bulk loged recovery model?
If yes then how?

Yes but only if there were no minimally logged transactions in the final log file that you want to recover up to the point in time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

best_yunus SSC-Addicted Points: 409 More actions · Answer 14

best_yunus

SSC-Addicted

Points: 409

May 13, 2012 at 1:14 pm

#1486886

Now i got...

Thanks 🙂

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 15

Take a read through this: http://www.sqlservercentral.com/articles/Recovery+models/89664/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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