Solved

SHRINK Master database - is this a good idea

Posted on 2009-03-31
4
1,124 Views
Last Modified: 2012-05-06
We have an old (but very important) production database that has recently started failing. The job history for the backup shows that the problem was with the Master database on that server, which gets backed up at the same time and took 2 days to backup. The Master database is currently 740Mb. We have truncated and dropped a table from the Master database that we know should have been in one of the non system databases. We now think we should shrink the master database. Is there any reason not to do this?  
0
Comment
Question by:Blim2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 125 total points
ID: 24027142
Yes.. You can in case if it occupies more space.
Follow the steps below to shrink your Master database:
1. Right Click Master database in SSMS.
2. Choose Tasks --> Shrink --> Database
3. Specify the target output size of your master db.

Its done. No Issues at all in shrinking a master db.
0
 

Author Comment

by:Blim2
ID: 24027181
Thanks, that's reassuring.
How do we estimate/calculate a target output size? Is it better to overestimate or underestimate?
0
 
LVL 29

Expert Comment

by:QPR
ID: 24027287
it will tell you the minimum size it can be shrunk to.
best to overestimate growth (if disk space allows) as db growth can be resource intensive and can slow down performance at critical times. Do you have any idea of it's growth pattern over time?
Also good to specify a groth in MB rather than percentage.
10% of 250mb today can become 10% of 1GB in the future.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24027299
There is no definite method to calculate the Master database size.
Install your application along with all sets of Server configurations and find out the size of the Master database and have it as a benchmark.

Here after, shrink the Master database to a size which we have obtained with + 25% tolerance to have an optimarl size.

This size varies for each and every application based on their configurations.
0

Featured Post

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question