Shrinking Log files

Question

Shrinking Log files

Achilies

SSCommitted

Points: 1695
More actions
August 8, 2011 at 9:33 am

#255116

Hi ,
I tried running the commands to shrink log files but i am getting the errors and the file also not shrinking..
The error comes like this:
Cannot shrink log file 2 () because of minimum log space required.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 4214, Level 16, State 1, Line 4
BACKUP LOG cannot be performed because there is no current database backup.
Msg 3013, Level 16, State 1, Line 4
BACKUP LOG is terminating abnormally.
Cannot shrink log file 2 () because of minimum log space required.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Can anybody suggest me what to do ?
Thanks..

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can't shrink that, as it says it's already at a minimum

Why are you trying to shrink anyway? Why are you breaking your transaction log chain? You've left yourself open to data loss if a disaster occurs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 2

First off, I hope you know that shrinking Log file is very bad practice. Is this your production system?

here is a link to an article[/url] Gail Shaw wrote. Please go through it.

What is the minimum size of the Log file that you have set in your DB?

Ooops... Gail, I did not see your reply.

-Roy

Achilies SSCommitted Points: 1695 More actions · Answer 3

I know shrinking is a bad practice...but the log files have grown tooo much..i need to get the space free... I have seen gail's link too...while i am doing it i took backup of this...

Thanks..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Have you checked why the log is full? (if you read my article I assume you've already done all that)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

p.s. There's no way you can be taking log backups after doing what you posted, unless there was a full backup taken that you didn't mention.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

Try shrinking to a larger size, if that makes sense.

Pick a size that is larger your minimum and include that in the shrink.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 7

GilaMonster (8/8/2011)
You can't shrink that, as it says it's already at a minimum
Why are you breaking your transaction log chain?

Does shriniking breaks up log chain ? even if he is not changing the recovery model.

I know the same what you said before but I recently heard the log backup don't breakup if you don't change the recovery model.

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Steve Jones - SSC Editor (8/8/2011)
Try shrinking to a larger size, if that makes sense.
Pick a size that is larger your minimum and include that in the shrink.

I have had this work on occasion.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

@SQLFRNDZ (8/8/2011)
GilaMonster (8/8/2011)
You can't shrink that, as it says it's already at a minimum
Why are you breaking your transaction log chain?
Does shriniking breaks up log chain ? even if he is not changing the recovery model.
I know the same what you said before but I recently heard the log backup don't breakup if you don't change the recovery model.

In this case he is changing the recovery model. The log chain does get broken when you change recovery models.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 10

Beginner_2008 are you making transaction log backups of that database?

My blog

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 11

SQLRNNR (8/8/2011)
@SQLFRNDZ (8/8/2011)
GilaMonster (8/8/2011)
You can't shrink that, as it says it's already at a minimum
Why are you breaking your transaction log chain?
Does shriniking breaks up log chain ? even if he is not changing the recovery model.
I know the same what you said before but I recently heard the log backup don't breakup if you don't change the recovery model.
In this case he is changing the recovery model. The log chain does get broken when you change recovery models.

Thats true.. Thanks

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

@SQLFRNDZ (8/8/2011)
Does shriniking breaks up log chain ?

No.

even if he is not changing the recovery model.

ALTER DATABASE fv_prepaid_ca_test SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(Aprakash_CoreAuth_log, 1,TRUNCATEONLY)
ALTER DATABASE fv_prepaid_ca_test SET RECOVERY FULL WITH NO_WAIT

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Achilies SSCommitted Points: 1695 More actions · Answer 13

Achilies

SSCommitted

Points: 1695

August 8, 2011 at 2:50 pm

#1365536

ya i am taking log backups too..

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

GilaMonster (8/8/2011)
Have you checked why the log is full? (if you read my article I assume you've already done all that)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass