0

My ASPState.LDF file is more than 17GB now, and some of that data is quite old. Is there a way to delete all of the old data?

Still learning SQL database management and I'm new to working with ASPState.

user3499114
  • 15
  • 1
  • 5
  • There are ways to do it correctly and ways to very wrong. This is far more than a forum post can answer. Do some research on how to properly manage log files. – Sean Lange Aug 06 '14 at 18:15
  • Excellent points by @SeanLange. There are entire books on the subject. Maybe this can get you started: [Managing Transaction Logs](http://www.sqlservercentral.com/articles/Administration/64582/) – Dave Mason Aug 06 '14 at 19:23

2 Answers2

1

Consider changing the ASPState database recovery model to SIMPLE. ASPState is only used for temporary ASP.NET session objects that are only valid for the life of the web session. There is no value in using the FULL recovery model for this special purpose database.

Committed transactions are automatically removed from the log in the SIMPLE recovery model. You can reduce the physical log file afterward with DBCC SHRINKFILE.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
0

The LDF file is the log file for the database. If your database is in the Full Recovery Model, the log will not overwrite itself unless it is backed up. So, if you need Full Recovery, which would allow you to recover to a point in time in your database, then you should set up a maintenance job to back up the log file every 15 min or so.

This will stop the log growth, once that is done, you could run a shrinkfile on the log to reduce to a normal size and allow it to grow to a normal size. You do not want to be shrinking it all the time, because autogrowth is costly, but a one time event is fine.

If you do not need point in time recovery, change the db recovery model to simple.

If none of this applies to your situation, start looking at your code. You may have very large transactions that are causing the large log file size. However, if this is a db for a web app, the former probably applies.

Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25