?
Solved

transaction log full

Posted on 2013-06-11
8
Medium Priority
?
654 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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 69

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

771 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