Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

MODEL transaction log growing Expand / Collapse
Author
Message
Posted Tuesday, February 26, 2008 10:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 7, 2013 8:05 AM
Points: 1, Visits: 117
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
Posted Tuesday, February 26, 2008 11:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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
Posted Wednesday, February 27, 2008 9:36 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 28, 2013 8:25 AM
Points: 3,461, Visits: 347
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
Posted Wednesday, February 27, 2008 9:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Thursday, July 24, 2014 9:07 AM
Points: 11,157, Visits: 12,899
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.




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 #461035
Posted Wednesday, February 27, 2008 10:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, June 28, 2013 8:25 AM
Points: 3,461, Visits: 347
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
Posted Wednesday, February 6, 2013 1:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 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
Posted Thursday, February 7, 2013 2:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:06 PM
Points: 2,569, Visits: 3,809
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
Posted Thursday, February 7, 2013 7:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 7, 2013 7:23 AM
Points: 2, Visits: 1
Nothing in there but we think we've narrowed it down to happening upon System Table Backup! The log has autogrown by 7 GB in 1 night.
Post #1417069
Posted Friday, February 8, 2013 3:19 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 321, Visits: 825
Profiler will help you. Look at the Objects and Performance event classes.
Post #1417925
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse