Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
428 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 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 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 11

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 70

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

618 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