May 23, 2016 at 3:14 am
Facing an issue with database size, before restart SQL Backup was around 40GB, when we restarted SQL Server, size of full backup reset to 21gb, but after that every day it was growing and today it reached to 48GB.
SQL Server Reset : 20-May-16
DateFull backup size
20-May 21gb
21-May37GB
23-May48GB
Even Differential backup of every 3hrs increased from 14GB to 29GB
We also configured 15min Transaction log backup for Log shipping.
unable to find why the size of DB backup growing so much, We have Index maintenance plan running every day and it was running fine from last 5yrs.
May 23, 2016 at 10:52 am
Check the tablesizes on that database periodically and check is there any tables are growing abnormally.
May 24, 2016 at 2:54 am
You could restore previous backups on to a test box and run this proc to get all table sizes, then compare.
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply