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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee SavidgeCommented:
backing up the log usually does the trick
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.