SQL database grown huge after backups

An external provider has setup a load of SQL agent backup jobs on a 2008 database. Since this the MDF file seems to have grown from around 50GB to 120GB.

When i run a script to check the table usage, the total table usage is only 37GB. The bakups created by the agent were manually deleted from the drive as it had consumed 100% disk space.

Will anything have been left in the mdf file to cause it to grow so much and is there a procedure I can run to reduce its size.

Code I ran to check table size is as follows.

SELECT 
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages, 
 SUM(a.used_pages) AS UsedPages, 
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 
ORDER BY 
 OBJECT_NAME(i.object_id) 

Open in new window

LVL 1
wint100Asked:
Who is Participating?
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.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> An external provider has setup a load of SQL agent backup jobs on a 2008 database. Since this the MDF file seems to have grown from around 50GB to 120GB.

There are few scenarios where this can happen. Probably,
1. They might have loaded some staging tables and then loaded to your Actual tables. This extension in space of MDF file can't be reduced without a Shrink process
2. Rebuild of indexes across all tables which can increase the size of MDF files if SORT in TEMPDB option is set to OFF.

If you have taken a Full backup of your database, then you can safely shrink your MDF file to release all the unused space.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
try to avoid shrinking MDF file..
check if your DB in simple recovery
if it is in Full -- make sure to set trans log backup

check with vendor if it is normal what you have :
how big can this DB be ?
and order extra drives  (DB files ,backups) space accordingly
it looks like an expected -- and order
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
try to avoid shrinking MDF file..

Yes, that is recommended one but for this scenario MDF file is sized 150 GB with actual data occupying only 37 GB. Its more than 65% free space and hence recommended to shrink the MDF file once..
And taking Transactional Log Backup will not help control the size of MDF file as it isn't related at all.

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
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Raja:  in this case, only the vendor ("An external provider")  has all answers ... when, what , why and how
wint100Author Commented:
thanks for the help. I've used Shrink database to get it down to 70GB. Also I changed recovery mode to simple and shrunk the log file from 35GB to 500MB.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> in this case, only the vendor ("An external provider")  has all answers ... when, what , why and how

yes, that's correct and that's why I suggested they might have did operations like this which would have caused the MDF file to grow huge..

>>  I've used Shrink database to get it down to 70GB. Also I changed recovery mode to simple and shrunk the log file from 35GB to 500MB.

Few things to do now..
1. Rebuild all required indexes on the database as Shrinking data file would have caused fragmentation in indexes.
2. Change your Recovery Model to FULL and take a Full backup.
3. Set the Initial size of your Log File to 2 or 4 GB and autogrowth to 512 MB. For a database with data sized 37 GB, log file of 500 MB seems very less.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
wint100: did you contact your vendor who set "all " jobs there and maybe still doing some work there?
OR
What you did you may need to do again and again.. the communication is the key to see if they done and you can set All DBAs jobs tasks and monitoring...

check with them what  the data growth forecast is ..etc
their product docs , sql setup info, etc ..
does their software mange all DBA tasks- reindex,backups Or you can set standard sqlagent DBA jobs?

maybe you need to have FULL recovery, maybe you do not ... . it depends on  the software and businesses needs for  RPO RTO.. etc
and much more...

when you'll get answers  you can plan you plan
wint100Author Commented:
Hi,

Yes I've put this to them and asked them to manage this accordingly, so hopefully they'll get to the bottom of it and set the recovery type accordingly.

Thanks for all your help, I'll distribute points evenly.
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Thanks for all your help, I'll distribute points evenly.

Welcome and glad to assist..
It appears like you have assigned points to me alone but you have mentioned that you wish to distribute.
Kindly let me know how you wish to distribute so that we can modify it accordingly..
wint100Author Commented:
Yes I tried to also assign to Eugene Z, can this be ammended?
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
Query Syntax

From novice to tech pro — start learning today.