Hi all,

I am using SQL Server to store the session variable in my ASP application.  In order to do it, I executed the below command to create 'ASPState' DB in my SQLServer. 

[drive:]\%windir%\Microsoft.NET\Framework\[version folder]\Aspnet_regsql.exe-S[Server]-U[Username]-P[Password] -ssadd -sstype p

The application server is running NLB and the system is running well until one day, the below error promoted:

The transaction log for database 'ASPState' is full.  To find out why space in the log cannot be reused, see the log_reuse_wait _desc column in sys.databases

I have checked the file size of the transaction file and it becomes 2GB which hits the limit of DB growth.

Anybody know what 's that problem and how can I fix it?

Many Thanks,

Keith

The problem is your transaction log has filled up the entire space allocated to it.
The transaction log is the part of the database to which SQL Server writes all transactions before writing them to the database.

You will now need to Truncate it and can try the following (Truncation of the transaction logs can be done using the following command via SQL Query Analyzer)
 

BACKUP LOG db_name WITH TRUNCATE_ONLY 

Thanks addie,

However, the application is deployed just a month.  Only 4-5 users with little transactions.  Therefore, it is strange that the trasaction log grown up too quickly.  I just wonder is it a common problem for using 'ASPState' DB?

I guess you are not in the Simple Recovery Mode?

Hi addie,

You are right.  I am using Full Recovery Mode.  However, this is default value when I using "Aspnet_regsql" to create "ASPState" DB.  Should I need to change it to "Simple"?

I am not sure about its implications on your app, but Yes that certainly is an option

Hi Keith,

The root cause of error “The transaction log for database 'ASPState' is full” is you didn’t backup or truncate log. SQL Server doesn’t automatically truncate log for the purpose of restoring database when it is damaged. Hence, with using that database, the .ldf file becomes larger and larger until fill with the hard disk.

To resolve this issue, you should backup and truncate log every some time. The simplest way is creating a job in SQL Server to backup log and set this job to run every some time (for example: every two weeks).

Since the .ldf file has grown to 2 GB, I recommend you first shrink the ldf file. To do so, please refer to the following link:

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/kb/272318

By the way, set the Recovery Model from full to Simple can decrease the .ldf file growth speed. Just for your reference, here are some helpful articles. You can read them if you interest in.

Selecting a Recovery Model
http://msdn2.microsoft.com/en-us/library/aa173531(SQL.80).aspx

How to stop the transaction log of a SQL Server database from growing unexpectedly
http://support.microsoft.com/kb/873235

INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/kb/110139