Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Dec 4 '12 at 19:06

This question came from our site for professional and enthusiast programmers.

1  
You need to take regular transaction log backups –  marc_s Dec 4 '12 at 16:44
    
How can i do it? –  HaBo Dec 4 '12 at 17:30
1  
Read on MSDN - SQL Server Books Online - it's the FULL and complete documentation for SQL Server - learn to use it! Also: check out this Simple-Talk article on the topic - also a very good site to go to for SQL Server related questions! –  marc_s Dec 4 '12 at 18:00

3 Answers 3

up vote 6 down vote accepted

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.

share|improve this answer
    
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 Dec 12 '12 at 16:57
    
@HaBo Use Kimberley Tripp's guide to shrink and size appropriately. –  Mark Storey-Smith Dec 12 '12 at 17:07
    
Worked Thank you. –  HaBo Dec 12 '12 at 17:14

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.

share|improve this answer
1  
There really is very little point in backing up this particular database. It would be the equivalent of backing up tempdb. –  Mark Storey-Smith Dec 4 '12 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. –  Ali Razeghi Dec 4 '12 at 20:34
    
Thank you for the nice answer, i am working on this. will post here how it went. –  HaBo Dec 4 '12 at 22:21
    
I'm happy to hear it was helpful! Please let me know if you have any questions. –  Ali Razeghi Dec 5 '12 at 21:40
    
Thank you for the shrink query. it went down from 520 GB to 1 MB –  HaBo Dec 12 '12 at 17:05

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.

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.