How to shrink log in 2008 r2? URGENT!!!!!!

Question

How to shrink log in 2008 r2? URGENT!!!!!!

Viewing 7 posts - 16 through 21 (of 21 total)

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

psamyn SSC Journeyman Points: 79 More actions · Answer 1

Strange, why does ssms then suggest this line when you apply this on a lig file when you choose for the option shrink file.

I checked it on my test system and the size didn't change.

Thanks for pointing out though because I have a bug in a script of mine.

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

psamyn (3/4/2014)
Strange, why does ssms then suggest this line when you apply this on a lig file when you choose for the option shrink file.

Because SSMS is a poorly written mess of an app?

When you script a backup to disk, you get about 6 options which are only applicable to backups to tape.

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

psamyn (3/4/2014)
This should do it also.
DBCC SHRINKFILE (N'<DATABASE_LOG_FILE_NAME>' , 0, TRUNCATEONLY)

To backup what Gail has said, the only time you want to shrink the log file to zero is if you're trying to clear a bazillion VLFs and you follow the shrink-to-zero with an immediate regrow to the correct size, which is frequently the same size that you shrunk it from except for "blowouts" caused by "runaway" queries.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

psamyn (3/4/2014)
Strange, why does ssms then suggest this line when you apply this on a lig file when you choose for the option shrink file.
I checked it on my test system and the size didn't change.
Thanks for pointing out though because I have a bug in a script of mine.

Almost forgot. The log file won't shrink past the last VLF currently in use. It is sometimes necessary to first take a backup of the database and then shift to the SIMPLE recovery model to get the log file to shrink. That must be immediately followed by a shift back to the FULL or BULK Logged recovery model and at least a DIF backup must be taken to re-establish the log chain. Of course, if your database was originally in the SIMPLE recovery model, then you wouldn't need to do all of that. Instead, you should start sweating bullets about how much data you could lose if something ever goes wrong. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (3/4/2014)
Almost forgot. The log file won't shrink past the last VLF currently in use. It is sometimes necessary to first take a backup of the database and then shift to the SIMPLE recovery model to get the log file to shrink.

Switching to simple won't change which the last VLF in use is. If the DB's log won't shrink because the active VLF is at the end of the file, switching it to simple will result in a DB in simple recovery with the active VLF at the end of the file.

A combo of shrink, backup log, shrink (possibly with another log backup) is a pattern that SQL is programmed to notice, while it won't move log records, that combo will make SQL start writing into the earliest open VLF (according to Paul R), which means that the last VLF of the file can be made inactive by another log backup (providing nothing else using it) and allow for more shrinking.

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

GilaMonster (3/5/2014)
Jeff Moden (3/4/2014)
Almost forgot. The log file won't shrink past the last VLF currently in use. It is sometimes necessary to first take a backup of the database and then shift to the SIMPLE recovery model to get the log file to shrink.
Switching to simple won't change which the last VLF in use is. If the DB's log won't shrink because the active VLF is at the end of the file, switching it to simple will result in a DB in simple recovery with the active VLF at the end of the file.
A combo of shrink, backup log, shrink (possibly with another log backup) is a pattern that SQL is programmed to notice, while it won't move log records, that combo will make SQL start writing into the earliest open VLF (according to Paul R), which means that the last VLF of the file can be made inactive by another log backup (providing nothing else using it) and allow for more shrinking.

That seems contrary to what I've seen happen. If what you say is true, it seems to me that shrinking it to 0 wouldn't work everytime (at least for me, it has) in the SIMPLE recovery model.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Jeff Moden (3/5/2014)
If what you say is true, it seems to me that shrinking it to 0 wouldn't work everytime (at least for me, it has) in the SIMPLE recovery model.

I didn't say it won't, I said it's not necessary to switch to simple because you can do the shrink/backup/shrink/backup combo in full. Switching to simple doesn't automatically make SQL switch the active portion of the log to the beginning of the file, but since checkpoints are automatic, you can end up doing a shrink/checkpoint/shrink without realising it.

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