Huge transaction log

[aquila125]

Hi all,

We've got a database of about 8GB that is used by a web application (written by a third party). Recently I noticed that the transaction log file was about 27GB. I tried to truncate (backup log files, shrinkfile) it but the minimum required size is 25GB.

I used DBCC OPENTRAN to see if there were open transaction but there were none.

Any ideas on why the transaction log is that big?

thanks!

 

[icemel]

If the required size is 25 GB, then it seems that the log was created this size, since you can't shrink smaller than the size intially specified when the log was created.

Perhaps the size can be altered (never done this), check BOL, "alter database" command. Someone correct me if I am wrong!

ALTER DATABASE database
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ]
| ADD LOG FILE < filespec > [ ,...n ]
| REMOVE FILE logical_file_name
| ADD FILEGROUP filegroup_name
| REMOVE FILEGROUP filegroup_name
| MODIFY FILE < filespec >
| MODIFY NAME = new_dbname
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ]
| COLLATE < collation_name >
}

 

[SQLBI]

You could always delete the offending log file having created another one with a more suitable original size...

Enterptise Manager > Right Click DB > Properties > Transaction Log.

Never one it before but can't think why it would cause any problems.


Cheers,
Leigh

You're only as good as your last backup!

 

[mrdenny]

When you run DBCC SHRINKFILE was is the output that it says?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[grimwyre]

Has the Transaction Log ever been backed up? We arrange to backup all TLogs regularly as some were growing. Actions like re-index also caused our TLogs to grow, which caused the next TLog backup to be large, but reduced the log usage.

 

[aquila125]

Thanks for all the replies.


I tried BACKUP LOG dbname TRUNCATE_ONLY.. that helped!
DBCC SHRINKFILE made me shrink the file..


Thanks!!