transaction log full

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
royjaydAsked:
Who is Participating?
 
BanthorConnect With a Mentor Commented:
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
 
babuseConnect With a Mentor Commented:
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
 
babuseCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
royjaydAuthor Commented:
thanks all
0
 
royjaydAuthor Commented:
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
 
DBAduck - Ben MillerConnect With a Mentor Principal ConsultantCommented:
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
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.