Model Database log file is full

Question

Post reply

Model Database log file is full

rajeshbamane

Grasshopper

Points: 19
More actions
October 8, 2010 at 1:28 am

#97342

HI,
I have the sql server 2000 , and it's model database log file is full ,also we don't have enough space in the hard drive where this log presents.
Now we want to reduce the size of model database log file.
Please guide us to resolve this issue.
Regards

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

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

How big is model database Transaction Log file?

If you have space on the other drive please take the transaction log backup using

BACKUP LOG model TO DISK ='[DriveName]:\Filename

Then issue DBCC SHRINKFILE on the model transaction log file.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Mr. Holio Hall of Fame Points: 3314 More actions · Answer 2

http://msdn.microsoft.com/en-us/library/ms175495.aspx

__________________________
Allzu viel ist ungesund...

Michael L John One Orange Chip Points: 27054 More actions · Answer 3

My question is why is the log file so big in the model database?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

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

Michael L John (11/10/2010)
My question is why is the log file so big in the model database?

Indeed.

There should be no activity in model. There should be nothing happening to fill the log up. So what are you doing in model that's generating transaction log entries?

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

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736160

November 10, 2010 at 9:14 am

#1248971

If you're not sure, check the default trace for activity.

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

Steve Jones - SSC Editor (11/10/2010)
If you're not sure, check the default trace for activity.

Default trace won't have queries in it. Object create/drop yes, but not normal DML.

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

GSquared SSC Guru Points: 260824 More actions · Answer 7

GilaMonster (11/10/2010)
Steve Jones - SSC Editor (11/10/2010)
If you're not sure, check the default trace for activity.
Default trace won't have queries in it. Object create/drop yes, but not normal DML.

Default trace in SQL 2000? I don't remember there being one, but it's been a few years since I used 2k. Am I just misremembering?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 8

Sorry, in 2008 mode. No default trace in SQL 2000.

Look for user objects or data in model. There shouldn't be anything.

Summer90 SSC-Dedicated Points: 32891 More actions · Answer 9

Ya know... it is bizzare... Our SQL2005 and 2008 SQL Servers model log has been growing.... 20mb log backup.... what the heck could be doing this? Not in SQL 2000 though... very odd. I have not had time to setup a trace to see though.

GSquared SSC Guru Points: 260824 More actions · Answer 10

It should only grow through either transactions, or someone deliberately growing it.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon