ASPState Log file growing tremendously - SQL Server 2008

Question

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?

Answer 1

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:

• Switch to SIMPLE recovery model.
• Follow Kimberly Tripp's suggested route for shrinking the log.

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.

Answer 2

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.

Answer 3

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]
GO
ALTER DATABASE [DBName] SET RECOVERY SIMPLE WITH NO_WAIT
GO

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]
GO
DBCC SHRINKFILE (N'DBName_log' , 1)
GO

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.