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

x
?
Solved

The transaction log for database ‘xxxx’ is full.

Posted on 2010-09-18
11
Medium Priority
?
1,045 Views
Last Modified: 2013-12-01
Hi,
   How do I fix "The transaction log for database ‘xxxx’ is full" if I get it after deleting huge data?

Thanks.

 
0
Comment
Question by:arunbhatt
[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
  • 2
  • 2
  • 2
  • +4
11 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33709931
You ran out of disk space and the log can't grow anymore.
You need more space
0
 
LVL 5

Assisted Solution

by:FredTang
FredTang earned 400 total points
ID: 33709935
Hi,arunbhatt,
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
0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 800 total points
ID: 33709950
If following were true
 - you recovery model already as Simple
 - you can't continue anything with database, always get message said transaction log full

thing you should try
- if the harddisk which have Log File, some file can delete or move, do it
- if nothing can delete or move, you need to add more log file on other harddisk

  USE [master]
  GO
  ALTER DATABASE [databasename]
  ADD LOG FILE ( NAME = 'newlog', FILENAME = 'X:\path\filename.ldf' , SIZE = 2048KB , FILEGROWTH = 1024KB )
  GO

Once your process can continue, then shrink the log and remove the new added logfile.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:arunbhatt
ID: 33713235
Hi,
  Can transaction log file be shrink using recovery model full?

Thanks.
0
 

Author Comment

by:arunbhatt
ID: 33713420
Hi,
  When database is backed up:  the log is backed up and log truncated. Is that true?

Thanks.
0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 800 total points
ID: 33713768
with recovery model "Full", record inside transaction log will be truncated (but still consume same disk space) when you perform one of these
1) full database backup
2) transaction log backup
so, after backup, you still need to shrink log file in order to gain space back.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 33717271
Just to clear up the immediate problem, I suggest you truncate the log, shrink it, then do a full db backup.

USE databasename

BACKUP LOG databasename WITH TRUNCATE_ONLY

--if you have only ONE data file and ONE log file, the command is as below.
--if you have more than one, you will need to run: EXEC sp_helpfile databasename
--    and specify the logical log file name (first column in the output of the sp_helpfile cmd)
DBCC SHRINKFILE ( 2 , 40 )  --shrink the log to 40M
--DBCC SHRINKFILE ( logical_log_file_name, 40 )  --shrink the log to 40M

BACKUP DATABASE databasename
TO DISK = 'x\full\path\to\backup\file\databasename_full_backup.BAK'
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 800 total points
ID: 33717299
For the future, you need to do periodic log backups along with your db backups.  After the log is backed up, SQL can reuse that space in the log so the log will not keep growing.

Db and log backups are *separate*.  A db backup does _NOT_ truncate the log.


Btw, just to be sure, check for an existing transaction on the db:

USE databasename
DBCC OPENTRAN
0
 
LVL 1

Expert Comment

by:ElectricLlama
ID: 38177834
You can also delete and commit smaller chunks of data. Assuming you have some kind of key that ets you selectively delete chunks:

BEGIN TRAN
DELETE YourTable WHERE Key BETWEEN 1 AND 1000
COMMIT TRAN


BEGIN TRAN
DELETE YourTable WHERE Key BETWEEN 1001 AND 2000
COMMIT TRAN
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39687911
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

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