Solved

Transaction log is full for the ASP state db.  How do I shrink or delete the transaction log?

Posted on 2015-02-17
3
349 Views
Last Modified: 2015-05-19
The transaction log for database 'ASPState' is full due to 'ACTIVE_TRANSACTION'. My production website is down because of this error. My database is on SQL server 2014. How do I shrink or clear the transaction log Please help.
0
Comment
Question by:TrialUser
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 40615011
Unless the active transaction is not complete, the transaction log will not be freed up.

The 1st thing will be to figure out which is this long running transaction and either COMMIT or ROLLBACK the same.
Once done, take a log backup and you should be all set (if the database is in a Bulk-logged or FULL recovery mode).
0
 
LVL 10

Expert Comment

by:Tim Edwards
ID: 40615013
You will want to create a maintenance schedule for the database that will do the backup of the database and logs, once you can have this setup and run successfully the transaction logs will truncate

Here is the technet article for backing up SQL 2014, please review in adjust to meet your needs.

https://msdn.microsoft.com/en-us/library/ms179478.aspx
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40615032
Given that your website is down, I would go into emergency mode and first just get it running.  We can, and should and will, worry about other related issues later.

Run these commands, then report the log file total size and the % free.

USE ASPState
ALTER DATABASE ASPState SET RECOVERY SIMPLE;
CHECKPOINT
WAITFOR DELAY '00:01:00'
CHECKPOINT

EXEC sp_helpfile
DBCC SQLPERF ( LOGSPACE )


If the log file is (very) large, run this command too to see how many VLFs you have:
DBCC LOGINFO
0

Featured Post

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question