How to reduce the log file size ?

Question

How to reduce the log file size ?

WhiteLotus

SSChampion

Points: 12120
More actions
September 15, 2014 at 6:24 pm

#290727

Hi All,
I need to reduce the log file size to be very small . At the moment , the current size is 14 GB .
I hv run : ALTER DATABASE ExampleDB SET RECOVERY SIMPLE
DBCC SHRINKFILE('ExampleDB_log', 0, TRUNCATEONLY)
but it only free up 1 GB
I also run
DBCC SHRINKFILE('ExampleDB_log', 1)
but it doest give me a result as expected.
What should I do ?
Cheers
September 16, 2014 at 12:20 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

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

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

WhiteLotus (9/15/2014)
I need to reduce the log file size to be very small . At the moment , the current size is 14 GB .

Why?

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

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 2

Why you want to shrink the log file?

Maybe its only shrinking 1 GB because that's all the free space available.

If you still want to shrink, take log backups and then you should shrink.

However, the log will grow back again when it requires more space.

Regards,
SQLisAwe5oMe.

WhiteLotus SSChampion Points: 12120 More actions · Answer 3

SQLisAwE5OmE (9/16/2014)
Why you want to shrink the log file?
Maybe its only shrinking 1 GB because that's all the free space available.
If you still want to shrink, take log backups and then you should shrink.
However, the log will grow back again when it requires more space.

Thanks Guys for the response . I really appreciate it

I hv solved the problem. It turns out there is an open transaction that is still there so i need to kill it first