?
Solved

The transaction log for database ‘xxxx’ is full.

Posted on 2010-09-18
11
Medium Priority
?
995 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 69

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 69

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 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 real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

770 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