Home / server / Questions / 442406
In Process
DevelopingChris
Asked: 2012-10-26 09:18:31 +0800 CST

Where is the file size limit stored in the system tables in sql server

  • 772

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.

sql-server

2 Answers

  1. 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.

    • 1

  2. 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.

    • 1