SQL Server log file over 5GB in size

Question

I notice that my database size is over 5GB in size around 98% of this is taken by log file while .mdf file size is around 10MB.

I tried shrinking database that reduced space by 2MB
Then I tried shrinking data file both .mbf & .ldf which hardly reduced space by 10-15mb.

When I take full backup my .bak file size is around is 18MB why so and how can I reduce the log file so that I don't have to worry about disk space?

I am running SQL Server 2008 R2

This may help: http://stackoverflow.com/a/18292136/2203084 – Felix Pamittan Apr 06 '15 at 05:39 — Felix Pamittan, Apr 06 '15 at 05:39

score 2 · Accepted Answer · answered Apr 06 '15 at 05:35

2

You can try using this SQL

ALTER DATABASE yourdatabase SET RECOVERY SIMPLE
 DBCC SHRINKFILE (yourdatabase_Log, 1)

i.e, you first make your recovery model to Simple(which I guess is set to Full presently)

However if you want to truncate the log file you can use this command

BACKUP LOG  yourdatabase WITH TRUNCATE_ONLY
DBCC SHRINKFILE (yourdatabase_Log, 1)

Check the MSDN

answered Apr 06 '15 at 05:35

Rahul Tripathi

168,305
31
280
331

@KnowledgeSeeker:- So you need to set it as Simple! – Rahul Tripathi Apr 06 '15 at 05:41

SQL Server log file over 5GB in size

1 Answers1