Solved

The transaction log for database ‘xxxx’ is full.

Posted on 2010-09-18
11
877 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: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 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
Technology Partners: 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!

 

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 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:Scott Pletcher
Scott Pletcher 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:
Scott Pletcher 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

685 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