Log file for Model too large

Question

Post reply

Log file for Model too large

dwilliscp

SSCrazy Eights

Points: 9270
More actions
July 24, 2012 at 11:57 am

#261521

The log file for Model DB is 43G, 99% unused. I have tried to shrink but to no good.
I went into Server Management.. Database Properties.. and changed the initial size to 2.. from 43756, but when I click ok and go back the initial size is back to 43G.
July 25, 2012 at 4:14 am

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

43gb for model db .... use the aler database statement instead of the ui

Jayanth Kurup[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Before you do anything more, please run the following and post the results:

select name, log_reuse_wait_desc from sys.databases where name = 'model';

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 3

Lynn Pettis (7/24/2012)
Before you do anything more, please run the following and post the results:
select name, log_reuse_wait_desc from sys.databases where name = 'model';

here is the result

Name ----- log_reuse_wait_desc

=======================

model----- NOTHING

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Using SSMS, you should be able to use the ShrinkFile on the log with no problems.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 5

The problem is that the following is not shrinking the log file... it does complete without errors.

USE [model]

GO

DBCC SHRINKFILE (N'modellog' , 0, TRUNCATEONLY)

GO

It returns the following:

32560065664560065664

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Not sure. Try running a t-log backup or two, then try the shrinkfile again.

Steve Jones - SSC Editor SSC Guru Points: 736352 More actions · Answer 7

If you've never run a log backup on model, then you will have active log transactions you cannot shrink. truncate only is for data files and it only removes free space at the end of the file.

I'd run a log backup, then try to shrink it.

And figure out why it's large. Nothing should be performing transactions in model.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

Set the database to simple recovery, shrink it, and set it back to full recovery.

Also, I think you have the wrong format for the DBCC SHRINKFILE command.

use [master]

go

alter database [model] set recovery simple with rollback immediate;

go

use [model]

go

dbcc shrinkfile (modellog ,2)

go

use [master]

go

alter database [model] set recovery full with rollback immediate;

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 9

prettsons (7/25/2012)
First take full backup (with log), then switch the recovery model to simple, then attempt to shrinkfile the log. then switch back to full recovery model.
Take a look at this blog post: http://recoverdatabase.blogspot.in/2012/04/what-to-do-if-sql-server-log-file.html

I know, becouse I checked when I found the size of the log... we are doing full backups and then Log backups. The job did complete, but just to know I was working with.. I did a manual backup of both yesterday. I will do the switching of the recovery model, and see if that fixes the issue.

dwilliscp SSCrazy Eights Points: 9270 More actions · Answer 10

Yep, that did the trick... she is now 6mb! Wahoo. Thanks for the help.