Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have many databases restored from various other database servers on a shiny new server. I want to unify the log file size limits and allocation percentages. However, I'd like to not do this by hand so we can conform things as time passes.

share|improve this question
    
What database software are you using? MS SQL? MySQL? –  nojak Oct 25 '12 at 17:38

2 Answers 2

You don't want to set size limits on the transaction log files. If you do and a log file fills the users won't be able to use the application any more until to resolve the issue. Set all the transaction logs to grow to what ever size that they need, and don't shrink them unless a rogue transaction comes through and grows the log larger than is normally needed.

If space becomes an issue, buy more drive space.

share|improve this answer
    
Should be rogue, not rouge. ;) –  nojak Oct 25 '12 at 17:42
    
Thanks. Someday I'll learn to spell above a 4th grade level. Apparently today is not that day. –  mrdenny Oct 25 '12 at 17:43
    
Eh, we all have those days. –  nojak Oct 25 '12 at 17:45
    
This may not be the best advice for you. When you have 20 DBs on a server, you don't want one filling up shared drive space for logs and ensuring that more than one DB goes down before you can get in there. If your usage is predictable, you go right ahead and set intelligent limits so that one runaway DB doesn't take the whole server down. –  Mark Oct 25 '12 at 18:32
    
This is a fine answer, and I agree with you, however, they were all set to 20mb, and I have databases failing and I need to bring the limit up for all of them –  DevelopingChris Oct 26 '12 at 18:22

Short:

-- within a DB context
use [DBxxx]
select * from sys.database_files

-- all files on the DB
SELECT * FROM master.sys.master_files

You want to read up on this more before sizing your TLogs. Check out Kim and Paul's articles on SQLSkills.com.

http://www.sqlskills.com/blogs/kimberly/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

You're almost always better off with a growth size that isn't a percentage. And unless you have a dedicated drive for the only important Tlog in your system a limit may save you from a wider outage. More importantly, after killing a rogue process having a bit more TLog space to allow may let you get the rollback started or an emergency TLog backup completed without out having to go down to do it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.