Solved

Cannot shrink SQL 2005 Logfile, continues to grow

Posted on 2012-12-26
15
485 Views
Last Modified: 2013-04-03
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!
0
Comment
Question by:IT Gal
  • 5
  • 3
  • 2
  • +3
15 Comments
 

Author Comment

by:IT Gal
ID: 38721245
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?
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
ID: 38721306
Have you verified the database is or ever was involved in Replication?
0
 

Author Comment

by:IT Gal
ID: 38721315
"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.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38721353
Then I am afraid I am out of ideas.
0
 

Author Comment

by:IT Gal
ID: 38721355
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38721367
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.
0
 

Author Comment

by:IT Gal
ID: 38721384
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?
0
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 100 total points
ID: 38721722
If nothing helps, I suggest to rename the DB, back it up and recreate the original DB from backup. Make sure you also rename the "old" DB files, so there will be no filename conflicts with the restored one.

Are you certain the DB shows to have free log space in that 27 GB?
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 38722172
Woah!  Don't do anything outside the normal SQL realm yet!

First let's see what, if anything, SQL says is preventing it from truncating log records:


SELECT
    log_reuse_wait_desc,
    state_desc,
    recovery_model_desc,
    name --for verification that we're looking at the right db
FROM sys.databases
WHERE
    name = N'<your_db_name_here>'
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38722176
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 )
0
 
LVL 5

Assisted Solution

by:Kernel_Recovery_Tools
Kernel_Recovery_Tools earned 100 total points
ID: 38722893
0
 
LVL 2

Assisted Solution

by:RRobinho
RRobinho earned 100 total points
ID: 38722923
Perform the bellow operation:

•      Take a full backup of your database
•      Take transaction-log backup of your database frequently like in every Half an hour so that log file will not continuously grow
•      Shrink if you do not have any free space. You can perform this operation manually if required.

NOTE :  Most of DBAs suggest to avoid shrinking database because when you shrink the database to gain space keeping in mind it will help to increase in performance, but in fact it leads to increase in fragmentation which reduces the performance.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 38723025
RRobinho,

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

Author Comment

by:IT Gal
ID: 38723472
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.
0
 
LVL 5

Expert Comment

by:Kernel_Recovery_Tools
ID: 38723522
Thanks pir8matt for choosing us.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Connect to an SQL server ActiveX 10 25
Query for timesheet application 3 17
What is this datetime? 1 19
MS SQL SERVER and ADODB.commands 8 20
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

856 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