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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.