asked on

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

Raja Jegan R

>> 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

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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EugeneZ

Raja: in this case, only the vendor ("An external provider") has all answers ... when, what , why and how

wint100

ASKER

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 R

>> 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

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

wint100

ASKER

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 R

>> 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..

wint100

ASKER

Yes I tried to also assign to Eugene Z, can this be ammended?