Avatar of IT Gal
IT Gal
 asked on

Cannot shrink SQL 2005 Logfile, continues to grow

HELP! I have a SQL 2005 database with a logfile that is 27gb and growing.

I have tried using the GUI to shrink, but it just says that it 'completes successfully' and never does anything to the file.

The database recovery mode is already set to simple. I have tried executing the DBCC ShrinkFile query and while it says that it is completing successfully, it's not. The file not only remains the same size, it continues to grow for some reason.

I'm afraid of detaching the database and deleting this file because I'm worried about uncommitted transactions.

What can I do? I need help!
Microsoft ApplicationsMicrosoft SQL ServerMicrosoft Server Apps

Avatar of undefined
Last Comment
Kernel_Recovery_Tools

8/22/2022 - Mon
IT Gal

ASKER
By the way, the message I'm getting is:

"Cannot shrink log file 2 (xxxxxxxxxx_Log) because all logical log files are in use."

How do I get around this?
ASKER CERTIFIED SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
IT Gal

ASKER
"Have you verified the database is or ever was involved in Replication?"

It's the only running SQL server we have, so I don't believe its involved in any replication.
Anthony Perkins

Then I am afraid I am out of ideas.
Your help has saved me hundreds of hours of internet surfing.
fblack61
IT Gal

ASKER
Ok, well how about just backing the logfile up somewhere else, detaching the DB and then deleting the logfile and reattach it?

I've had to stop SQL server for the interim because the file just continues growing and I'm out of disk space. I have no idea whats happening.
Anthony Perkins

Ok, well how about just backing the logfile up somewhere else, detaching the DB and then deleting the logfile and reattach it?
If in fact you are using the Simple Recovery Model, then you will not be able to backup the Transaction Log.  Secondly the Transaction Log is an integral part of the database so deleting  the log file is reckless to say the least.  Whatever you do, please please do a full backup of your database before you make matters worse.
IT Gal

ASKER
The database is backed up daily using MozyPro, so I'm not worried about that.

I agree that its potentially reckless, but what other alternative do I have? I can't shrink it, can't delete it, wtf am I supposed to do?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Qlemo

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Scott Pletcher

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Scott Pletcher

If you're getting into emergency territory, and you have additional disk space on another drive, add a log file to the db to keep your current drive from filling and causing all kinds of problems.


ALTER DATABASE <your_db_name>
ADD LOG FILE ( NAME = your_db_name_log2, FILENAME = 'd:\full\path\on\new\drive\your_db_name_log2.ldf', SIZE = 2GB, FILEGROWTH = 50MB )
SOLUTION
Kernel_Recovery_Tools

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
RRobinho

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Qlemo

RRobinho,

The Asker already told us (in the original question) that the DB is in Simple recovery mode - no Tranaction Log backup hence.
IT Gal

ASKER
Thanks everyone. It turned out that replication was, in fact, turned on. Something weird must have happened with the replication trying to catch up or generating bogus logs, because its been that way for a long time and this issue never occurred before.

Anyway, a normal shrink was possible after just stopping replication. I'll just split points evenly for everyone that offered a possible solution. Thanks.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Kernel_Recovery_Tools

Thanks pir8matt for choosing us.