Sql-server – ASPState Log file growing tremendously – SQL Server 2008

sql serversql-server-2008transaction-log

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?

Best Answer

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.

Related Solutions

Sql-server – Why can’t I read the transaction log backup file using fn_dump_dblog

@Alan Please check once by disabling the audit.

That server has an AUDIT on a database. If the audit is enabled, you will get that error. If you disable the audit,query will run fine.

Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

If the rebuild is not specified as ONLINE (which you can't do except on Enterprise Edition), the SELECT is blocked because of LCK_M_SCH_S, and if you execute sp_lock you will see it is an exclusive lock. Even NOLOCK can't penetrate that. You can simulate this by:

In one window, start a transaction which rebuilds offline (this is so that you can investigate beyond the rebuild instead of trying to force a really long rebuild). Make note of the spid:
```
USE AdventureWorks2012;
SELECT @@SPID;
BEGIN TRANSACTION;
ALTER TABLE Sales.SalesOrderHeader REBUILD WITH (ONLINE = OFF);
```
In a second window, execute your NOLOCK query (again, make note of the spid):
```
SELECT @@SPID;
SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK);
```

In a third window, check:

SELECT session_id, blocking_session_id, last_wait_type 
  FROM sys.dm_exec_requests 
  WHERE blocking_session_id IN (two spids above)
     OR session_id IN (two spids above);

EXEC sp_lock;

Scan the second resultset for all of the rows involved with the spid that is the main blocker.

Don't forget to rollback or commit...

Best Answer

Related Solutions

Sql-server – Why can’t I read the transaction log backup file using fn_dump_dblog

Sql-server – blocking ‘SELECT… WITH(NOLOCK)’

Related Question