Shrink Log

Question

Shrink Log

VastSQL

SSC-Dedicated

Points: 36097
More actions
March 17, 2008 at 8:42 am

#204309

Hi Experts,
Am not able to shrink the log files of a database the log file size is 15gb .I tried dbcc shrinkfile but no use and before that i took a full backup
Please help in solve this issue.
TIA

Viewing 11 posts - 1 through 10 (of 10 total)

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

SanjayAttray SSChampion Points: 13157 More actions · Answer 1

Instead of taking full backup, take log file backup and try to shrink it. 99.99% of times you would be able to shrink log file after taking log file backup.

SQL DBA.

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 2

If not search for "forcibly shrink" here for a script that can help.

you should rarely shrink anything. Usually this is because something hasn't been done correctly in the past or you had a one-time event. Do not use shrink on a regular basis. You should have space allocated (data and log) to handle events for the daily load (log) and the next months (data).

vaidhyanathan1 SSC Journeyman Points: 80 More actions · Answer 3

Have you changed the database recovery option to simple before shrinking the database. After shrinking, revert the recovery mode to Normal.

Cliff Jones SSChampion Points: 10547 More actions · Answer 4

If your database is set up for replication, transactions marked as not replicated can prevent the log from being shrunk. You may need the run the sp_repldone stored procedure to mark any such transaaction as having been replicated before shrinking the log. This may have been the cause of your log growing unbounded in the first place.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

I tried the dbcc shrinkfile after taking a log backup but still the log file is not shrinking ...Is there any option to truncate log???

Cliff Jones SSChampion Points: 10547 More actions · Answer 6

Cliff Jones

SSChampion

Points: 10547

March 20, 2008 at 8:07 am

#792017

You might try:

dump transaction with no_log

Cliff Jones SSChampion Points: 10547 More actions · Answer 7

Sorry, that should be:

dump transaction databasename with no_log

Stelvio Gori Grasshopper Points: 17 More actions · Answer 8

Hi,

try to run

backup log log_logical_name with truncate_only

before running the shrink command.

It normally works for me,

regards,

SG

Stelvio Gori Grasshopper Points: 17 More actions · Answer 9

To be more exact:

backup log DATABASE_NAME with truncate_only

dbcc shrinkfile ([DATABASE_LOG_NAME],2)

where "2" is the desireded size of the log file after shrinking.

Please keep in mind, however, that (quoting SQLBill at http://www.sqlservercentral.com/Forums/Topic266619-5-1.aspx)

"If you use truncate (with DBCC SHRINKFILE or with BACKUP LOG dbname WITH TRUNCATE_ONLY), you 'break' the backup chain and must start a new chain. That means that you must do a Full backup after you issue the truncate command. Truncate means that log and any after it cannot be applied to the FULL backup done before the truncate.

I have used truncate at times.....but I always do a full backup right after it."

Regards,

SG

chenjing957 Grasshopper Points: 15 More actions · Answer 10

chenjing957

Grasshopper

Points: 15

March 21, 2008 at 5:55 am

#792395

dump log with no_log or backup log,you can try it