7

I have a problem that is my log file in SQL Server 2008 is grown to 40Gb and I wanted to shrink it. The query I am using is 

    ALTER DATABASE DatabaseName SET RECOVERY SIMPLE
use DatabaseName
GO
CHECKPOINT
GO
DBCC SHRINKFILE(transactionloglogicalfilename,TRUNCATEONLY)
GO
ALTER DATABASE DatabaseName SET RECOVERY FULL

is not shrinking the log file at more level and also the shrink log file is expanding immediately in short time only.

So where is the problem ?

I wanted to shrink the log file upto 1GB and my database size is 16GB

I have SQL Server 2008 R2

Please someone help me

2 Answers2

10

Go into Management studio, right click the database, Tasks > Shrink > Files.

Under File Type, pick the log file. Make sure Release unused space is selected as the shrink action, click OK.

As for the shrunken log file growing quickly again, it is probably because you are set to a Full recovery model. Setting it to simple will commit changes immediately and not save them in the log. Simple is only good if you are performing full backups on a regular basis.

DanBig
  • 11,393
  • 1
  • 28
  • 53
5

If you want to prevent further growing you need to set your database logging to simple (but only if you do Full Backups of your database!).

  1. Right click on your database
  2. Choose Properties
  3. Choose Options
  4. Set Recovery mode to simple

This will work and is best if your backup schedule is Full Backup every day. Because in such scenario your log won't be trimmed and it will skyrocket.

If you would be using Grandfather&Father&Son backup technique. Which means Monthly Full backup, Weekly Full backup, and then every day incremental backup. Then for that you need Full Recovery Mode. So considering that you set your database to Full Backup you need to either stop doing that and switch backup to Simple Recovery Mode or start doing backups correctly and your log will be trimmed by backups.

In your case where you switch it to simple and then switch it to full your logs will grow again.

Combine this with as DanBig suggestion to Shrink the database by doing following steps:

  1. Open up Sql Management Studio
  2. Right click the database, Tasks > Shrink > Files.
  3. Under File Type, choose the the log file. Be sure to mark option Release unused space as the shrink action, click OK.
MadBoy
  • 3,703
  • 13
  • 61
  • 93
  • Thanks. Had the same problem. Changing Recovery Model to simple then shrinking works like a charm. –  Aug 10 '11 at 08:37