Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

transaction log full

Posted on 2013-06-11
8
Medium Priority
?
662 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 80 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 400 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 720 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
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 720 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

810 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