We help IT Professionals succeed at work.

SQL 2008 R2 master.mdf large file size (92GB)

Hi,  
I have a SQL Server 2008 R2 system that is running out of space on the drive.  I have found a master.mdf file that is 92GB in size and I am assuming this is my issue.  I am hoping for some guidance on how we can reduce the file size of this.  I have located under the properties of this an option that is under automatic - Auto Shrink - is set to false.  What impact will this have if I alter this to true and how often and at what times would this run?
Also would there be any other options / command that I can run to reduce the size of this file.
I have limited experience with SQL so would really appreciated the help and guidance in trying to resolve this one.

Cheers,

Jason
Comment
Watch Question

Commented:
Please check if you have created any user objects ( Tables) in Master database. 92 GB is unusual size for Master database.

You can shrink the database, follow the MS link step by step.

https://docs.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-database
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016
Commented:
Are you taking regular backups ? If not, then take backup and after that you can shrink the database.

For backups, Integrity check etc,.use below Ola's script

https://ola.hallengren.com/sql-server-backup.html

You can shrink a DB with 2 ways. Below are the 2 ways given.

-> Shrinking a database by giving a free space percentage
DBCC SHRINKDATABASE (yourDBName, 20)
GO  

Open in new window

-> Truncate a DB
DBCC SHRINKDATABASE (yourDBName, TRUNCATEONLY) 
GO

Open in new window

Vitor MontalvãoIT Engineer
Distinguished Expert 2017
Commented:
Autoshrink is a big NO. Never set that to true in any database.
master database shouldn't grow that much so I will also stand for Deepak's observation. Somebody is using master database as a regular user database. And if it's that the case, shrinking it will not solve your problem. The problem should be solved by migrating the user objects to a new database.
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Recommendation to close this question by accepting the above comments as solution.