- SQL
- Microsoft SQL Server 2008
- Microsoft SQL Server
- Query Syntax
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
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
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
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
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
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
DBCC SHRINKDATABASE (yourDBName, TRUNCATEONLY)
GO
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial