Shrink Transaction Log

Question

Post reply

Shrink Transaction Log

StefD

SSC Journeyman

Points: 88
More actions
April 20, 2004 at 7:39 am

#82666

I'm managing 22 SQL Servers, and all are set to Simple recovery model with auto shrink option set, and sheduled full backup every day.
I notice that one of them reach transaction log of 32GB!!!
I try every known metod for shrinking (with single user and DBCC SHRINKDATABASE, DBCC SHRINKFILE, backup of the log, ...) end mange the log to 2GB.
Actual size of the log is 1% of 2GB, and I can't shrink it under 2GB.
Please tell me some other way to do this job

Viewing 14 posts - 1 through 13 (of 13 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Have you tried..

BACKUP LOG <DBNAME> WITH NO_LOG

GO

USE <DBNAME>

GO

DBCC SHRINKFILE ('<FILENAME>', 1, TRUNCATEONLY)

GO

Shamless self promotion - read my blog http://sirsql.net

StefD SSC Journeyman Points: 88 More actions · Answer 2

StefD

SSC Journeyman

Points: 88

April 20, 2004 at 7:48 am

#503483

Yes.

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 3

What was the initial size of the log (as you won't be able to shrink the filesize below that)?

Shamless self promotion - read my blog http://sirsql.net

Bircan OZBAS SSC Rookie Points: 32 More actions · Answer 4

use YOURDBNAME

DECLARE @DB VARCHAR(40)

SELECT @DB = 'YOURDBNAME'

CHECKPOINT

EXEC('DBCC SHRINKFILE (YOURDBNAME_log, 1, NOTRUNCATE)')

EXEC('DBCC SHRINKFILE (YOURDBNAME_log, 1, TRUNCATEONLY)')

CREATE TABLE txxxx (char1 char(4000))

DECLARE @i int SELECT @i = 0 WHILE (@i < 300) begin INSERT txxxx values ('a') SELECT @i = @i +1 END DROP TABLE txxxx

EXEC('BACKUP LOG '+@DB+' WITH TRUNCATE_ONLY')

StefD SSC Journeyman Points: 88 More actions · Answer 5

StefD

SSC Journeyman

Points: 88

April 21, 2004 at 12:34 am

#503585

Initial size was 2GB

Bircan OZBAS SSC Rookie Points: 32 More actions · Answer 6

Hi,

you should run the script 5 times.

Your size decrease..

StefD SSC Journeyman Points: 88 More actions · Answer 7

I tried this, but without results...

I get message:

Cannot shrink log file 2 (girafe_Log) because total number of logical log files cannot be fewer than 2.

mohd odili Grasshopper Points: 14 More actions · Answer 8

Hi

I just get red of this problem since 2 days,I had log file of 21GB.

- takeoff your DB

- detach DB - make sure all connections stopped

- rename your log file

- attch DB without log file

your log file will be recreated at the default size

you can delete the old log after you test your db

StefD SSC Journeyman Points: 88 More actions · Answer 9

Thanks for your help.

I manage to shrink the log file

tobias SSC Enthusiast Points: 130 More actions · Answer 10

I've seen this happen (some years ago) when I had multiple long-running transactions. These transactions wove such a tortuous path through the log files that even after stopping and restarting the database, the sweeper could not figure out that the space they used could be cleaned up.

This is consistent with the Detach/Reatach w no log methodology.

The real issue is why do you have these long running transactions. Even if it is something else in this case, this is a symtom of something, and you need to find out what it is a symtom of. In my case, I had a programmer who had botched a stored procedure that (1) touched every record in a large table instead of the record that should have been affected (2) inculded some recursion.

Remve the cause and the symtom (growing unshrinkalbe log files) will fix itself!

dryson Grasshopper Points: 21 More actions · Answer 11

Hi

I realize that one should of course backup both the log and the DB before proceding with the approach you suggest (detach, then attach without log file)

But even so: Won't I risk loosing DB integrity if I simply 'discard' the log file as you suggest ?

Best regards

Martin

SQLBill SSC Guru Points: 51440 More actions · Answer 12

Martin,

The original poster said the database was set to SIMPLE mode. In this mode you can't backup the transaction log - so you can't use it for a restore. The poster must have determined that there isn't a need to be able to restore to a point-in-time and that they could afford to lose a bit of data (they didn't say how often they do a full backup).

So, in your situation....

1. is your database set to SIMPLE Recovery Mode? If so, then dropping the transaction log will make no difference.

2. is your database set to FULL Recovery Mode? If so, then you should be backing up the transaction logs (as well as doing Full backups).

Lastly, you can not switch between the two (not effectively). If you are in Simple mode and you switch to Full mode to backup the transaction log...you won't be able to restore the log, because the last Full backup was done in Simple mode. So if you switch to Full mode, you must immediately do a full backup.

-SQLBill

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 13

Greetings... I know this was about 4 years ago. It would have been helpful to all to identify the solution you ultimately used to resolve your problem.

:doze:

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman