April 28, 2015 at 10:41 pm
Hi All,
I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
Really appreciate for the feedback
April 29, 2015 at 2:09 am
1. There is no need to shrink them. If your transaction logs are full, take transaction log backups.
(and how big can the transaction log for the model database be?)
To quote from the Technet article I link to below:
Best practice: We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.
2. Yes, you can set it to simple. You do understand what the consequence is? Every new database will have the recovery model of simple.
More info:
Recovery Models for System Databases
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 29, 2015 at 5:44 pm
Thanks for the reply !
1.OK . I will try to backup the transaction log
2.Hmm Do you mean everytime I create a new database it will automatically set to SIMPLE Recovery ? IF so then I think better just backup trans log because it is not good to have SIMPLE recovery by default in new database ( it is in production anyway)
April 29, 2015 at 7:52 pm
Koen Verbeeck (4/29/2015)
1. There is no need to shrink them. If your transaction logs are full, take transaction log backups.(and how big can the transaction log for the model database be?)
To quote from the Technet article I link to below:
Best practice: We recommend that you create only full database backups of model, as required. Because model is small and rarely changes, backing up the log is unnecessary.
2. Yes, you can set it to simple. You do understand what the consequence is? Every new database will have the recovery model of simple.
More info:
I would actually suggest to not backup model database at all unless you are using it for something or constantly changing the configuration. The log in model database can actually grow fairly large simply due to backups being taken against it. So if you are taking system database backups daily or even more frequently it can grow rather large because each backup against model is a logged event. You can see that in Pinal Dave's post here[/url].
I will almost always set this database to SIMPLE because I would apply my own settings for each database created and not trust the configuration of model database, one main one being the recovery model.
With regards to the master database you are not going to be able to take log backups of that database, nor can you change the recovery model. You just shrink the file and then determine why it is growing.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
April 30, 2015 at 12:51 am
WhiteLotus (4/29/2015)
2.Hmm Do you mean everytime I create a new database it will automatically set to SIMPLE Recovery ? IF so then I think better just backup trans log because it is not good to have SIMPLE recovery by default in new database ( it is in production anyway)
Depends on your requirements of course. Do your database need to be able to be restored up to the minute? In that case, you need full. In all other cases, bulk-logged or simple suffice.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 30, 2015 at 1:06 am
Thanks for the reply 🙂
Actually What I am worried is about someone created new database someday without my knowledge and he doesnt understand about recovery model ... Thats will be a problem
April 30, 2015 at 1:11 am
WhiteLotus (4/30/2015)
Thanks for the reply 🙂Actually What I am worried is about someone created new database someday without my knowledge and he doesnt understand about recovery model ... Thats will be a problem
In my opinion, you'll be better off with a simple recovery model then, because then you have at least no transaction logs that grow out of control.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 30, 2015 at 1:16 am
Hmm let me think about it 🙂
Btw do you know how to reduce data file ? I know how to reduce trans log file ( by doing Backup trans log and shrink log file )
April 30, 2015 at 1:24 am
WhiteLotus (4/30/2015)
Hmm let me think about it 🙂Btw do you know how to reduce data file ? I know how to reduce trans log file ( by doing Backup trans log and shrink log file )
The data file usually has a certain size because at some point it had to contain that much data.
If you are sure that there is now too much space and the database won't need that much space anymore, you can shrink the data file.
Be aware that this can cause index fragmentation.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 30, 2015 at 1:25 am
Also don't shrink your datafile so it's just big enough to contain the data. Leave some room for the database to grow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 30, 2015 at 7:45 pm
Koen Verbeeck (4/30/2015)
WhiteLotus (4/30/2015)
Hmm let me think about it 🙂Btw do you know how to reduce data file ? I know how to reduce trans log file ( by doing Backup trans log and shrink log file )
The data file usually has a certain size because at some point it had to contain that much data.
If you are sure that there is now too much space and the database won't need that much space anymore, you can shrink the data file.
Be aware that this can cause index fragmentation.
thanks for the reply
How do I find the unused space in that database?
May 1, 2015 at 9:08 am
WhiteLotus (4/28/2015)
Hi All,I have some questions :
1.Is it OK to shrink Master, model ? the transaction log for those databases are full .
2.Is it OK to set the Recovery model of Database MODEL as SIMPLE ? at the moment it is in FULL recovery model and the transaction log is Full too
Really appreciate for the feedback
1. Yes, you can shrink any of the system dbs if you want to. Msdb would most often the done, after you're removed large amounts of data from it. When model is in full mode, it can also sometimes need shrunk.
2. You can set model to any recovery model, like other dbs. But keep in mind that new dbs created on that instance inherit their recovery mode from the model db. Thus, if you change it to simple, all new dbs will also be simple by default.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 4, 2015 at 2:30 am
WhiteLotus (4/30/2015)
Koen Verbeeck (4/30/2015)
WhiteLotus (4/30/2015)
Hmm let me think about it 🙂Btw do you know how to reduce data file ? I know how to reduce trans log file ( by doing Backup trans log and shrink log file )
The data file usually has a certain size because at some point it had to contain that much data.
If you are sure that there is now too much space and the database won't need that much space anymore, you can shrink the data file.
Be aware that this can cause index fragmentation.
thanks for the reply
How do I find the unused space in that database?
Right-click database, go to reports > standard reports > Disk Usage.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply