Transaction log growth with no db activity

Question

Post reply

Transaction log growth with no db activity

Viewing 4 posts - 16 through 18 (of 18 total)

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

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

Paresh Prajapati (1/2/2009)
I thik he is not taking transactional backup..

Exactly. That's why I'm asking if he's aware his backup/recovery strategy does not allow for point-in-time recovery. 😉

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

WayneS SSC Guru Points: 95460 More actions · Answer 2

PaulB (1/2/2009)
WayneS (1/1/2009)The differential backups are taking about 1-2 seconds, even at the end of the day. This way, I would only need to restore, at most, from 2 backup files... the most recent full, and the most recent differential.
Are you aware your recovery strategy does not allow for point-in-time recovery?... In your recovery scenario all transactions after your most recent differential would be gone; can you afford that?

Yes, I am. This database has been at about 700mb for several years, the daily growth is very tiny. They can live with having to redo the last 15 minutes of activity. (Actually, until the beginning of Dec, they were living with just a single daily backup, with the ability to reenter their days activity.)

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

S. Kusen SSChampion Points: 10936 More actions · Answer 3

I would also suggest that shrinking the files is a bad practice. I'm surprised Gail didn't mention that 😉

Shrinking the log for it to only grow again will cause a lot of unnecessary I/O's for the disk where the log file lives and can result in OS level fragmentation of the log files which cannot be undone without taking the DB offline.

SK

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 4

Paresh Prajapati (1/1/2009)5. Monitor TempDB size. If it growth more , then move tempdb to individual disk.

Could you please elaborate on the rationale of the above recommendation?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.