The mystery of transaction logs

Question

The mystery of transaction logs

WhiteLotus

SSChampion

Points: 12122
More actions
April 17, 2016 at 9:35 pm

#315705

Hi guys ,
I notice my transaction log grew bigger starting from last week . I checked the log backup at that time was not running half day for all databases ( it should be 24 hours running – every hour log backup )
I am wondering what is the cause of this issue ?
I didn’t change the recovery model from Full to Simple .
Any feedback are highly appreciated
Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

Perry Whittle SSC Guru Points: 234013 More actions · Answer 2

WhiteLotus (4/17/2016)
Hi guys ,
I notice my transaction log grew bigger starting from last week . I checked the log backup at that time was not running half day for all databases ( it should be 24 hours running – every hour log backup )
I am wondering what is the cause of this issue ?
I didn’t change the recovery model from Full to Simple .
Any feedback are highly appreciated
Thank you

Are you saying the databases are still in full recovery model, it's unclear what you are saying.

Is the backup job producing errors, are there any errors in the log

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

"Ya can't make an omelette without breaking just a few eggs" 😉

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 3

If the log backup is not running (and that's how I read the explanation), then the committed transactions won't be removed from the log and it will continue to grow. Get the log backups running, and the log will empty (it won't however shrink, you'll have to manually go and do that).

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

WhiteLotus SSChampion Points: 12122 More actions · Answer 4

I have sorted it out guys , It is due to the maintenance of disk so there were 6 hours downtime