Solved

transaction log full

Posted on 2013-06-11
8
642 Views
Last Modified: 2013-07-02
hi experts

when i run
[DELETE TOP (50000) FROM Customer where customerId = '966'  and StaffID NOT IN (select ExceptionId from ExceptionDetail ED where ED.ExecutionId = '966')];

 i get

The transaction log for database 'CUSTDEV' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases; nested exception is java.sql.SQLException.

any idea how i can resolve this
when i run the sql
select log_reuse_wait_desc from sys.databases
it says NOTHING

any ideas?

thanks
thanks
0
Comment
Question by:royjayd
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 2

Assisted Solution

by:babuse
babuse earned 20 total points
ID: 39239262
Your transaction log is full as it states.
There are a few things to check.
The best thing to do is go through this article first.
http://msdn.microsoft.com/en-us/library/ms175495.aspx
0
 
LVL 2

Expert Comment

by:babuse
ID: 39239274
I would set my recovery mode in "properties" to simple also.

We can also
1. detach the database,
2. rename the log file
3. re-attach the database without a log file, so it recreates it.
4. delete the old log file
0
 
LVL 10

Accepted Solution

by:
Banthor earned 100 total points
ID: 39239449
NO!!!!

Create a Log Backup to a different Disk
Then the Log will be empty
SELECT @stmt='BACKUP LOG ['+@name+'] TO  DISK = N''' + @Filename + ''' WITH NOFORMAT, NOINIT,  NAME = N''['+@name+']-Transaction Log  Backup'', SKIP, NOREWIND, NOUNLOAD,  COMPRESSION'

THEN EXecute
DBCC SHRINKFILE (N''' + @LogicalFile + ''' , 0, TRUNCATEONLY)" '

Change to Simple only if Yesterday's Backup is good enough for disaster recovery.
Other wise Create a Job that executes a Log backup every 15 minutes.

This will allow for Point in Time Restoration
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 180 total points
ID: 39240029
Do NOT detach the database and delete the log file. It is much harder today to get the log file to recreate, so you should do as @Banthor said and backup the log and then it will truncate it and let you back in.

There are 2 reasons why you will see the transaction log full message.  One is that you have a cap on the size of the TLog file and it has reached the limit (database in Full recovery mode).

Two, you have the database in Full Recovery mode and you actually have run out of disk space on the disk.

You can get past this with backing up the log.  You don't necessarily have to shrink the log file to get it back.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 total points
ID: 39241745
Definitely NEVER detach a db and delete the log: NEVER.

The disk might be full due to the log expanding.  If the log has gotten bloated, you can shrink the log, then reallocate it to less space.

For example, say the log has bloated to 22G; probably 6G is big enough.

So do this:

USE CUSTDEV
--EXEC sp_helpfile
DBCC SHRINKFILE ( 2 )
ALTER DATABASE CUSTDEV
    --NOTE: your logical file name might not be "custdev_log",
    --look at output from sp_helpfile to get actual name if command doesn't run
    MODIFY FILE ( NAME = custdev_log, SIZE = 2GB, FILEGROWTH = 80MB )
ALTER DATABASE CUSTDEV
    --NOTE: your logical file name might not be "custdev_log",
    --look at output from sp_helpfile to get actual name if command doesn't run
    MODIFY FILE ( NAME = custdev_log, SIZE = 6GB )
0
 

Author Comment

by:royjayd
ID: 39249191
thanks all
0
 

Author Comment

by:royjayd
ID: 39252028
Actually i am little confused now because the error message i get now is different. This is what i get now::

"Could not allocate space for object '<temporary system object: 422403532062720>' in database 'tempdb'
because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in
the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the
filegroup.; nested exception is java.sql.SQLException: Could not allocate space for object
'<temporary system object: 422403532062720>' in database 'tempdb' because the 'PRIMARY' filegroup is full.
 Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to
the filegroup, or setting autogrowth on for existing files in the filegroup."


Is this error similar to the original error in question

"The transaction log for database 'CUSTDEV' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases;
nested exception is java.sql.SQLException."

thanks
0
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 180 total points
ID: 39252245
This means that the TEMPDB database is full.  This points me directly to the disk is full that contains the database files or the log files.

You need to check the amount of disk space that is available.

Do this and find the place where the files are.

SELECT * FROM sys.master_files

Then you can see the file locations for tempdb and for the CUSTDEV database and then go check the amount of free space on the disk.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

705 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

12 Experts available now in Live!

Get 1:1 Help Now