Solved

The transaction log for database ‘xxxx’ is full.

Posted on 2010-09-18
11
707 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
  • 2
  • 2
  • 2
  • +4
11 Comments
 
LVL 32

Expert Comment

by:ewangoya
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 100 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 200 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
 

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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 200 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 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 200 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 69

Accepted Solution

by:
ScottPletcher earned 200 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 22

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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now