Log file bigger than data file

Question

Log file bigger than data file

Viewing 4 posts - 31 through 33 (of 33 total)

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

Jeffrey Williams SSC Guru Points: 90258 More actions · Answer 1

lisbon (1/28/2010)
Hi,
As noted before, if you always do full backups you can change the recovery model to SIMPLE in database options and the log will shrink after each backup.
Best,
lx

Neither the recovery model nor backups will change the file size. The transaction log will not automatically shrink unless you have auto shrink enabled - which is not something you want to do on a production system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

iBar SSCertifiable Points: 5872 More actions · Answer 2

If it is backed up and still not reducing the size then try to shrink the log file manually upto your required size.

if it works then the problem is in backup plan.

Cheers

Sankar Reddy SSCrazy Points: 2410 More actions · Answer 3

The first thing the OP needs to do is look at the data from the below queries.

select name, log_reuse_wait_desc from sys.databases

GO

USE DBNAME

GO

SELECT name AS 'FileName' , physical_name AS 'PhysicalName', size/128 AS 'TotalSizeinMB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB',

CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB',

(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'

FROM sys.database_files;

The above will tell you why the log [VLF in fact] is NOT truncating and if there is any free space in the log file. Post this info and someone will be able to pickup from there.

[font="Courier New"]Sankar Reddy | http://SankarReddy.com/[/url][/font]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

Bill Gates-870674 (1/28/2010)
If it is backed up and still not reducing the size then try to shrink the log file manually upto your required size.
if it works then the problem is in backup plan.
Cheers

if the main backup file is including the t-log data then shrink of t-log will not help - you actually need to TRUNCATE the log . (either by backup or by truncate log command)

this can be done only if the portion of the log is marked as inactive.

if the log is empty then it will not be included in your backup

MVDBA