The transaction log for database ‘xxxx’ is full.

Hi,
   How do I fix "The transaction log for database ‘xxxx’ is full" if I get it after deleting huge data?

Thanks.

 
arunbhattAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
Ephraim WangoyaCommented:
You ran out of disk space and the log can't grow anymore.
You need more space
0
 
FredTangConnect With a Mentor Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
JoeNuvoConnect With a Mentor Commented:
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
 
arunbhattAuthor Commented:
Hi,
  Can transaction log file be shrink using recovery model full?

Thanks.
0
 
arunbhattAuthor Commented:
Hi,
  When database is backed up:  the log is backed up and log truncated. Is that true?

Thanks.
0
 
JoeNuvoConnect With a Mentor Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
ElectricLlamaCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.