SQL Server 2012 Database is growing fast and not enough disk space

Hi
We have a SQL Server 2012 database  that is growing fast and we are losing disk space. The backups have been running  and within the SQL database we have multiple database instances, how can find which database is growing and why. I would greatly appreciate your assistance
LVL 1
Phil MapfumoInfrastructure EngineerAsked:
Who is Participating?
 
Dustin SaundersDirector of OperationsCommented:
The simplest answer is to go to where your data files (mdf and ldf) are saved and look at which ones are so large.  If you have insane growth that is unexpected you might have one of two things going on:

1. Something in an insert loop or some bad trigger coding that is just generating tons and tons of data.
2. Tons of transactions and Full recovery model on your files.

The .mdf is the database file and the .ldf is the log file, so start by looking at those sizes and you can get an idea of what is growing so fast.

in SSMS you can right click on the database and go to Shrink > Files and see how much space is used.  Sometimes if you are doing restores or database upgrades, they may 'bloat' and you could reclaim some of that space, but before going down that route be advised that shrink operations have various different effects on the database and indexes.
ee_shrink.png
You could also check your backup location and make sure your backups are behaving as expected and cleaning themselves up at expiration or not endlessly appending.

If you want to view the file locations for an instance, you can run the query:
SELECT name, physical_name AS current_file_location
FROM sys.master_files

Open in new window

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You will need to check your backup history to see when it started to grow (large backup files).
Follow this article to see how you can query for the backup history.
0
 
Phil MapfumoInfrastructure EngineerAuthor Commented:
Many thanks to all
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.