SQL Server 2008 Transaction Logs - FULL

Morning Experts - Happy New Year.

This morning I have many error messages listing Transaction Logs as being full, when I try it increase the size I get the following error -

The transaction log for database 'xxx' is full. To find out why space in the log cannot be reused, see the log-reuse-wait-desc column in sys.databases (Microsoft SQL Server Error: 9002)

How can i resolve this issue?

Many thanks

Who is Participating?
Lee SavidgeCommented:
backing up the log usually does the trick
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

garethtnashAuthor Commented:
Hi lsavidge, thanks -

It was my C drive - it had run out of space,.. quick question, is it possible to set the transaction log file to be located on the D drive instead?

How would I do this?

Most likely you have the transaction model 'FULL' and you are not running a transaction log backup.

In management studio select your database, right click properties and see what the transaction model is.
Next step is to see the size of your db-files, see at the allocated and allowed space for your db-files. Your transaction log started filling an stopped while maximum allowed allocation is reached and this was to small or it stopped while there was no diskspace to extend.

I couldn't write it better as in link below but just quoting the very crucial statement 'In FULL recovery, the inactive part of the log cannot be truncated until the log records have been captured in a transaction log backup. So, a moot point trying to shrink files without doing the backups, or releasing some of that internal space first.'
Lee SavidgeCommented:
Don't forget that it's probably a default file location in your sever instance, to move with detach/attach
http://www.psoft.net/HSdocumentation/sysadmin/mssqlchangehome.html. Als possible with backup/restor  and the WITH MOVE-option for the files.
Peter KipropCommented:
try the possible ways as in the link below

Scott PletcherSenior DBACommented:
As a quick fix to get you running again, you  probably have one log that has gotten very large.  If so, change that db's recovery model to SIMPLE, then DBCC SHRINKFILE() on that db's log file.  That should free up enough space to get you running again.

Don't recycle or shutdown SQL until you get new space, otherwise you will force rollbacks of all activity that is pending.

If you can't do that, you could add log files on D: to all affected dbs.  Then you could go back and remove the log file on C: from each db.  Again, that can be done without cycling SQL.

>> is it possible to set the transaction log file to be located on the D drive instead? <<

Yes.  The easiest way to do that is around a restart of SQL Server.  If you can't restart SQL Server, you must other methods :-).

I strongly urge you not to detach a db; that is not necessary to move files and will change the security on the file, which may cause problems for you re-attaching it (thanks to a security "enhancement" by MS starting with SQL 2005).
Anthony PerkinsCommented:
You can add additional Log files or you can simply set the database offline, use ALTER DATABASE to change the path for the Transaction Log file, move the file to D: before placing the database online again.  This way you do not have to detach or restart SQL Server.

And as everyone and their mother have already implictly stated, if you cannot make Transaction Log backups, than you should not be using a Full Recovery Model in the first place.  Caveat:  If you do decide to change the Recovery Model from Full to Simple and leave it that way, make sure that everyone understands that you lose the option of point-in-time restores.
garethtnashAuthor Commented:
Thank you All - Sorry to share the points but you were all so helpful - Thank you
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.