Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Web Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

I have an ASPState database online, and its log file ASPState_log is growing tremendously. The current size is 536gb.

How can I maintain this log file?

I take a daily backups through SQL Agent job. Can I truncate the logs?

What is the best practice to keep the log file size in control?


3 Answers 3


99% of the time, the asp.net session state database does not warrant FULL recovery and transaction log backups. In fact, it usually doesn't warrant any backups at all. I'd favour a script to recreate it over taking backups.

NB: Be wary of folk sneaking persistent objects in to your state database. Lock 'em out.

If your SQL Server isn't clustered, you have the option of targeting tempdb for state storage as an alternative to a persistent database, so it's treated as truly disposable.

Assuming your usage is typical and recovery of the data isn't required either:

Or, if you aren't clustered go the tempdb route and drop your state database, by re-running aspnet_regsql with the option -sstype t.

  • i Have changed the recovery model to Simple. Now will that reduce the Log file size? or should i run a transaction back up and test, if that make any difference in it.
    – HaBo
    Commented Dec 12, 2012 at 16:57
  • @HaBo Use Kimberley Tripp's guide to shrink and size appropriately. Commented Dec 12, 2012 at 17:07

I take a daily backups through SQL Agent job. Can I truncate the logs?

What kind of backups are you taking? It sounds like you're just taking full/differential backups. If you database is in full recovery, then you need to also be backing up your transaction log with log backups.

You have 1 of 2 options provided you are using the full recovery model (or bull-logged):

  1. Regularly backup your transaction log
  2. Switch your database to the simple recovery model (provided this is acceptable in your SLA)

If you need point-in-time recovery, then you should be staying with full recovery. But the way that works is portions of the log aren't able to be reused until they are backed up. It has to do with the log chain and ensure that it is unbroken.

NOTE: Even if you backup your transaction log, you will not notice the file size go down. You will need to explicitly shrink the file if you need to recover that space. Database file shrinking should not be a routine operation.


Your database is set to the FULL recovery model and you're not taking transaction log backups most likely. If you do not need point in time recovery, which means you can only restore up to the point you took a backup on, then run this command to set the DB to simple mode. Simple mode doesn't keep old transactional history and keeps the old invalid sections of the transaction lag marked for reuse. Mark pointed out this DB doesn't need point in time recovery so let's go with this option.

Preferred Method for this DB: No point in time recovery option:

USE [master]

Do note open transactions might get rolled back when you perform this.

Do a DBCC LOGINFO and note how many rows get returned. That is the number of your VLF count. Having too many can cause major performance issues with backup/restores and general log activity. All of your backups will be huge

You then need to resize your transaction log so it's an appropiate size and does not have VLF (internal) fragmentation.

USE [DBName]

This will shrink your transaction log to the smallest size it currently can. Do note this will cause performance issues. Try to not run any of these during important or busy times, preferably during a maintenance window. you can always try to shrink it in smaller chunks if you are a 24/7 shop.

Now read this SQLSkills post on VLFs. Paul Randal recommends letting your transaction log grow for a week, let it go through a reindex, and make that your new size. Grow your log file in accordance to Kimberely's post linked above and you're golden.

Continuing with point in time recovery (again, not needed in your specific scenario):

SQL Server gives you the option to doing something called "Point In Time Recovery" which means it allows you to restore to a specific second in time. In order to do that, SQL Server needs to maintain a 'transaction log' of all the data you will need to recover.

For example let's say someone makes a big mistake at 12:53:12 which requires you to restore from backups. If you take a nightly backup, you then would lose all that additional data that came in after midnight. and you want to make sure that you maintain as much as possible. You can do a full backup restore, then restore the transaction log up to 12:53:11.

In order to do that, you need to start taking regular transaction log backups.
This post has a good script in setting up a quick transaction log backup job. However, if you want to go with the industry recognized and general standard in SQL maintenance scripts, check out Ola Hallegren's backup scripts.

Please let us know if you have more questions.

  • 1
    There really is very little point in backing up this particular database. It would be the equivalent of backing up tempdb. Commented Dec 4, 2012 at 20:31
  • Ah nice, thanks for pointing that out Mark. I'll edit the answer to point that out but leave it up for others if they need to handle a run away log file. Commented Dec 4, 2012 at 20:34
  • Thank you for the nice answer, i am working on this. will post here how it went.
    – HaBo
    Commented Dec 4, 2012 at 22:21
  • I'm happy to hear it was helpful! Please let me know if you have any questions. Commented Dec 5, 2012 at 21:40
  • Thank you for the shrink query. it went down from 520 GB to 1 MB
    – HaBo
    Commented Dec 12, 2012 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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