Solved

transaction log full

Posted on 2013-06-11
8
651 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
[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
  • +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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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
 
LVL 69

Assisted Solution

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

726 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