MODEL transaction log growing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » MODEL transaction log growing

MODEL transaction log growing

Rate Topic

Display Mode

Topic Options

Author

Message

stormin_30

Posted Tuesday, February 26, 2008 10:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, May 13, 2013 1:22 PM
Points: 1, Visits: 115

Hello All,

Our support team has recently received 3 calls from our customer stating the transaction log for the MODEL database has grown quite large (in excess of 12GB) with no corresponding data growth. As far as we know nothing is using MODEL, there are no user objects and never any connected sessions to the database.

We know how truncate the log and shrink it back down to size. What we don't know is how to determine what would be using the MODEL DB's transaction log.

If any one has any experience with this issue I'd really appreciate any feedback you have.

Thanks in advance.

Norm

Post #460380

Jack Corbett

Posted Tuesday, February 26, 2008 11:18 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

the only way a tx log can grow is through use. At least as far as I know. I would profile the server to verify that there is no use of the model db.

Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #460416

Sugesh Kumar

Posted Wednesday, February 27, 2008 9:36 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:18 AM
Points: 3,461, Visits: 346

Put the model database in simple recovery model. Also don't create any table in the model database and no transaction there.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Post #461009

Jack Corbett

Posted Wednesday, February 27, 2008 9:55 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 17, 2013 12:22 PM
Points: 10,571, Visits: 11,871

Sugesh Kumar (2/27/2008)

Put the model database in simple recovery model. Also don't create any table in the model database and no transaction there.

Just be aware that by doing this any new databases you create on that server will be in Simple mode as well.

Post #461035

Sugesh Kumar

Posted Wednesday, February 27, 2008 10:21 AM

Hall of Fame

Group: General Forum Members
Last Login: Saturday, November 10, 2012 8:18 AM
Points: 3,461, Visits: 346

Yes model database acts as a template for new databases to be created. so having the model database set to simple has to be done only when needed.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Post #461083

lrjohns

Posted Wednesday, February 06, 2013 1:36 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, February 07, 2013 7:23 AM
Points: 2, Visits: 1

Is there a way to read the transaction log for the model table. I'd like to see what is going on with it. It is up to 80 GB in a month!!!!

Post #1416715

Ratheesh.K.Nair

Posted Thursday, February 07, 2013 2:18 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:03 AM
Points: 2,415, Visits: 3,374

Do you have any tables in Model Db. Check what all process using the database.

Someone might have accidentally pointed to this db instead of user db.

Post #1416898