December 18, 2011 at 6:37 pm
We are running SQL Server 2005 SP4 on Windows 2003. We have a vendor application which uses one SQL Server User Database. I was checking the size of the user database (3 GBs) and noticed that the Model Database was 35 GBs. The system databases are on the same drive and that drive only has 133 MBs of free space. (The user database is on another drive.) I reported this issue to the vendor and also asked them how could the Model Database become so large. (I know the Model Database is a template database used to create new databases.) I have not heard back from the vendor.
What is the best method to use to reduce the size of the Model Database Log File? Should I do the following:
DBCC SHRINKFILE('modellog', 100);
BACKUP LOG Model WITH TRUNCATE_ONLY
DBCC SHRINKFILE('modellog', 100)
I had heard it was best to do a Shrinkfile on the Log File before and after the Backup Log Truncate_Only, not sure why. Should I use these commands? Do you think there is enough free space (133 MB) on the drive for these commands to execute? I may be able to free up some space by removing some of the vendors installation files. I am still waiting to hear from the vendor.
Thanks in advance, Kevin
December 19, 2011 at 2:25 am
what is the recovery model of your "model" database?
If its full, change it to simple recovery as there is no use of putting "model" database in full-recovery untill you not using it for day-to-day operation.
----------
Ashish
December 19, 2011 at 2:39 am
Did you check if some objects are being created inside the model database ?
"Keep Trying"
December 19, 2011 at 6:35 am
The model database should usually be in Full recovery, so that new databases are in Full recovery when you create them. That's the default for a reason and you usually want to keep it that way.
What I'd check is, is there activity in the model database? Are there user objects in it, and are they being accessed?
After that, switch to Simple, issue a checkpoint, shrink the log file, then switch back to Full Recovery.
If you find user objects in there, confirm they are actually meant to be part of a template, or remove them. If the vendor software is using model for anything, change vendors. (Seriously. It's that stupid if they are.) Do that before the shrink, of course.
- 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
December 19, 2011 at 6:54 am
Here's my emergency ready made script to shrink a db.
Change the correct log size for your needs.
Comment out the backup script start if you don't want it, but I would take a new backup of model once you've cleaned it out.
USE [master]
GO
ALTER DATABASE [model] SET RECOVERY SIMPLE
GO
CHECKPOINT
GO
USE [model]
GO
--Change 3000 to whatever you need
DBCC SHRINKFILE (N'model_Log' , 3000)
GO
USE [master]
GO
ALTER DATABASE [model] SET RECOVERY FULL
GO
EXEC msdb.dbo.sp_start_job @job_name = 'Backup model'
December 19, 2011 at 12:45 pm
You can find big table(s) (http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx ... modify script for system objects also).
December 20, 2011 at 6:04 am
I am keen to know why model Db has huge log file. one thing comes to my mind : that we are putting some default/standard sql objects there which we would need in every future coming database.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 20, 2011 at 6:31 am
Bhuvnesh (12/20/2011)
I am keen to know why model Db has huge log file. one thing comes to my mind : that we are putting some default/standard sql objects there which we would need in every future coming database.
More likely is that some dev/DBA/application is using the model database inappropriately for data processing.
- 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
December 20, 2011 at 8:53 am
I actually prefer my model database to be in Simple mode. If I need full recovery, I change it, when I create a new database, then I change my transaction log backups to handle the new database.
Having said all that, I would check to see why the model database has a transaction log that large.
It could be because new objects, with data, have been added. I have seen the model primed with some data(lookup data, for example), needed for new databases.
Could also be because someone is using it as their primary database(oops).
December 21, 2011 at 8:17 am
lrutkowski (12/20/2011)
I actually prefer my model database to be in Simple mode. If I need full recovery, I change it, when I create a new database, then I change my transaction log backups to handle the new database.Having said all that, I would check to see why the model database has a transaction log that large.
It could be because new objects, with data, have been added. I have seen the model primed with some data(lookup data, for example), needed for new databases.
Could also be because someone is using it as their primary database(oops).
I like this idea, typically if someone knows they need point in time recovery in a newly created database, they'll alter the database, if they don't, they'll probably forget their log backups anyways %^)
Seems like a common thing that folks get hired, and find existing databases at their new jobs have huge logfiles that aren't being backed up because of previous admins simply didn't know better, especially given MS-SQL's frequent use by smaller businesses that may not have a full time dba on board.
December 22, 2011 at 9:12 am
With a transaction log that large, I would agree that something, somewhere is using model db.
But also keep in mind that the transaction log for model will grow if it is in full recovery mode and backups of it are being made, even if there is no other activity in the database.
http://shaunjstuart.com/archive/2011/02/the-mystery-of-the-model-database-transaction-log-growth/[/url]
December 22, 2011 at 10:57 pm
shaun.stuart (12/22/2011)
With a transaction log that large, I would agree that something, somewhere is using model db.But also keep in mind that the transaction log for model will grow if it is in full recovery mode and backups of it are being made, even if there is no other activity in the database.
http://shaunjstuart.com/archive/2011/02/the-mystery-of-the-model-database-transaction-log-growth/[/url]
priceless
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply