none
How to limit log file size to keep 10 days log information?

    問題

  • How to limit the log file size to keep only log information of 10 days?

    Or else, is there any way to limit the size to 10%?

    In the latter case, does it automatically truncate old log and keep filling the new?

    2012年3月3日 上午 08:44

解答

  • Hello,

    There is a good deal of different things happening here. First, you'll want to take log backups and keep those. Logs backups can only be applied if their sequence is unbroken and the restore must start with a full backup. If you want to be able to restore to a point in time you must keep all log backups since the last full backup was taken, otherwise it's worthless. If you only need to keep 10 days worth, you'll have to come up witha  recovery strategy to make sure you can recover to any point in those 10 days if that's what your requirements are.

    What you are asking also depends on your recovery model of the database in question. Simple recovery model will truncate if possible on checkpoint commands (which happen every so often or can be manually run, we're not going to get into that here). Bulk logged and full recovery model allow for transaction log backups which will also truncate if possible when a BACKUP LOG command is given. Therefor to do what you want you must either be in full or bulk logged recovery for that database.

    You can set a hard limit to the size of the log, but once that is reached any insert/delete/update/alter statements will fail and let you know your log is full. you can do this by an alter file statement for the log or gui based by using ssms to open up the properties of that database and choosing the files pane.

    I'm not going to go over the internals of the log file again, you can read a great post about it here: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

    -Sean

    2012年3月3日 下午 02:45

所有回覆

  • Hello,

    There is a good deal of different things happening here. First, you'll want to take log backups and keep those. Logs backups can only be applied if their sequence is unbroken and the restore must start with a full backup. If you want to be able to restore to a point in time you must keep all log backups since the last full backup was taken, otherwise it's worthless. If you only need to keep 10 days worth, you'll have to come up witha  recovery strategy to make sure you can recover to any point in those 10 days if that's what your requirements are.

    What you are asking also depends on your recovery model of the database in question. Simple recovery model will truncate if possible on checkpoint commands (which happen every so often or can be manually run, we're not going to get into that here). Bulk logged and full recovery model allow for transaction log backups which will also truncate if possible when a BACKUP LOG command is given. Therefor to do what you want you must either be in full or bulk logged recovery for that database.

    You can set a hard limit to the size of the log, but once that is reached any insert/delete/update/alter statements will fail and let you know your log is full. you can do this by an alter file statement for the log or gui based by using ssms to open up the properties of that database and choosing the files pane.

    I'm not going to go over the internals of the log file again, you can read a great post about it here: http://technet.microsoft.com/en-us/magazine/2009.02.logging.aspx

    -Sean

    2012年3月3日 下午 02:45
  • Which log are yoiu talking about?
    SQL Server Error Log?
    SQL Agent Error Log?
    Database Transaction Log?

    There are system sp's called sp_cycle_errorlog and sp_cycle_agent_errorlog which will create a new errorlog on demand. You could create a job to run this daily and then change the configuration to keep 10 days worth of data.

    Not sure why you would want to keep 10 days worth of data in a transaction log, that wouldn't lead to great recovery times. If you wanted to keep the transaction log to just 10% full, then you could create an alert based upon the size of the log, once it hits a certain value then it can be set to trigger a job, which is this case would be a log backup.

    10% could prove to be very small though.


    If you find this helpful, please mark the post as helpful,
    If you think this solves the problem, please propose or mark it an an answer.

    Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

    Richard Douglas
    My Blog: Http://SQL.RichardDouglas.co.uk
    Twitter: @SQLRich

    2012年3月5日 上午 11:14