Solved

SHRINK Master database - is this a good idea

Posted on 2009-03-31
4
982 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
  • 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trigger for audit 26 71
Insert statement is inserting duplicate records 15 58
SQL Select * from 6 36
SSIS how to COMPARE a data column from different servers? 6 89
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

910 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now