shrinking logfile in SQL 2008

Question

shrinking logfile in SQL 2008

Viewing 9 posts - 31 through 38 (of 38 total)

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

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

Please read through this - Managing Transaction Logs[/url]

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

Ivan Mohapatra SSCarpal Tunnel Points: 4452 More actions · Answer 2

the best way is to use this script for log file size shrink

DBCC SHRINKFILE ( file , target_size

[, {NOTRUNCATE | TRUNCATEONLY }] ) [WITH NO_INFOMSGS ]

Arthur Kirchner Ten Centuries Points: 1239 More actions · Answer 3

It its really annoying, how the log shrinking is solved in SQL Server 2008.

Sometimes the is simply a need to quickly shink the log file without being able to kick off all current users etc.

I myself have trouble for days now with this because I need to transfer and delete some lousy 30 mio. entries on an VM.

2005s "backup log XXX with truncate_only" was wonderful, they should have kept it.

sanket kokane Hall of Fame Points: 3465 More actions · Answer 4

Arthur Kirchner (11/6/2012)
2005s "backup log XXX with truncate_only" was wonderful, they should have kept it.

And I'm Glad that they removed this option from 2008 🙂

-----------------------------------------------------------------------------
संकेत कोकणे

george sibbald SSC Guru Points: 104210 More actions · Answer 5

Arthur Kirchner (11/6/2012)
It its really annoying, how the log shrinking is solved in SQL Server 2008.
Sometimes the is simply a need to quickly shink the log file without being able to kick off all current users etc.
I myself have trouble for days now with this because I need to transfer and delete some lousy 30 mio. entries on an VM.
2005s "backup log XXX with truncate_only" was wonderful, they should have kept it.

what problem are you having? it is possible to shrink or truncate a log whilst the database is active

---------------------------------------------------------------------

Arthur Kirchner Ten Centuries Points: 1239 More actions · Answer 6

Hi,

thanks for caring, at first I just wanted to rant, because it's simply annoying:

the ARE situations, databases and data out there, where freeing space quickly is more important than having a secure backup policy. (similar problem: shrinking the tempdb).

But now... please have a look:

CHECKPOINT

GO

BACKUP LOG myDB

TO DISK = 'F:\SQL_Logs\myDB.trn'

GO

DBCC SHRINKFILE (N'myDB_log' , 100)

GO

Error: Cannot shrink log file 2 (myDB_log) because of minimum log space required.

I cannot kick out current users, I can't wait until no user is on the db.

Best Regards

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

If you want to break the log chain (as truncate only used to do), switch to simple recovery. That's the replacement for the truncate only.

You probably have the active portion of the log at the end of the file. If that's the case, you need to wait until it loops around to the beginning of the file (as you would have had to do in SQL 2005 or before with backup log ... truncate only) as there is nothing that can or ever has been able to move log records around the log file (kicking users out will make absolutely no difference here).

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

george sibbald SSC Guru Points: 104210 More actions · Answer 8

run dbcc loginfo(yourdb) to see how many vlfs you have and where the last active one is (status = 2)

you also possibly have quite large vlfs, a log file needs at least one (or very possibly two, not 100% sure) vlfs.

---------------------------------------------------------------------

Arthur Kirchner Ten Centuries Points: 1239 More actions · Answer 9

Thank You very much for your effort,

I am sorry, but we had to restart the server now for another reason (not me ^^),

so I can't check the parameters anymore.

But: "I'll be back"^^

with one of these topics:

* DELETE many data w/o causing large log-files

* shrink tempdb w/o restarting the server

* shrink log file w/o restarting the server

Have a nice evening,

A.