February 13, 2017 at 8:06 am
I hope somebody here can help with this one, it's driving me mad.
Up until 1 week ago, our full daily compressed backup file grew on average 50-100MB a day.
Last week, I noticed the backup file size had jumped and each day was adding roughly 1.5GB
This made no sense as usage had not increased.
On the 5th of this month, our backup file was 19.8GB. As of last night's latest backup, it is 32.2GB
I restored a copy of the DB from 1 week ago and ran the script at the bottom of this post against the copy and the current live DB
When I compared the results, the actual Used Data size had increased only 316MB which is what I would expect based on my knowledge of the system.
So now, I cannot explain why the size difference between daily backups exceeds the actual new data added each day and I'm out of ideas.
It's not a compression issue, as the pre-compressed size reported from msdb..backupset has also grown as show in the listing of backup times and sizes below
2017-01-25 23:23:11.000 112414
2017-01-26 23:38:21.000 112500
2017-01-27 23:23:00.000 112584
2017-01-28 23:20:35.000 112631
2017-01-29 23:17:27.000 112666
2017-01-30 23:26:05.000 112686
2017-01-31 23:19:33.000 112773
2017-02-01 23:29:35.000 112736
2017-02-02 23:23:08.000 112795
2017-02-03 23:31:30.000 112886
2017-02-04 23:30:23.000 112971
2017-02-05 23:20:44.000 113006
2017-02-06 17:39:27.000 113246
2017-02-06 23:41:07.000 118930
2017-02-07 23:36:45.000 136824 <--- What the hell happened here?
2017-02-08 23:34:09.000 140598
2017-02-09 23:26:39.000 144992
2017-02-10 23:32:11.000 147805
2017-02-11 23:28:34.000 152953
2017-02-12 23:22:53.000 153306
The database in in Simple Recovery Mode and 1 daily full backup is made each night. Any advice gratefully received.
SELECT
s.Name + '.' + t.name AS table_name,
(select sum(p2.rows)
from sys.indexes i2 inner join sys.partitions p2 ON i2.object_id = p2.OBJECT_ID AND i2.index_id = p2.index_id
where i2.object_id = t.object_id and i2.object_id > 255 and (i2.index_id = 0 or i2.index_id = 1)
) as total_rows,
SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN a.total_pages * 8 ELSE 0 END) AS data_size_kb,
SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN a.used_pages * 8 ELSE 0 END) AS data_used_kb,
SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN 0 ELSE a.total_pages * 8 END) AS index_size_kb,
SUM(CASE WHEN (i.index_id=0) OR (i.index_id=1) THEN 0 ELSE a.used_pages * 8 END) AS index_used_kb,
SUM(a.total_pages) * 8 AS total_size_kb,
SUM(a.used_pages) * 8 AS total_used_kb
-- SUM(a.used_pages) * 100 / CASE WHEN SUM(a.total_pages) = 0 THEN 1 ELSE SUM(a.total_pages) END AS percent_full
FROM
sys.tables t
INNER JOIN
sys.schemas s ON s.schema_id = t.schema_id
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.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY
t.object_id, t.Name, s.Name
February 13, 2017 at 9:18 am
did you compare the transaction log file sizes?, I suspect it would be log file
February 13, 2017 at 9:37 am
goher2000 - Monday, February 13, 2017 9:18 AMdid you compare the transaction log file sizes?, I suspect it would be log file
Good catch, the log is huge and is all in use and cannot be shrunk. Not sure why. I'm using Snapshot replication but this has completed every day for the last week so why is the log so large?
The other thing I've noticed is that this issue corresponds to the date we applied a schema update to our database. Again, not sure why this would send the log file through the roof.
February 13, 2017 at 9:43 am
Please post the output of this query, replace the DRIVE, PATH and BACKUPFILENAME placeholders as required
restore headeronly from disk =
'DRIVE:\PATH\BACKUPFILENAME.BAK'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 13, 2017 at 9:50 am
I can not tell you what/which transaction caused it, however to shrink log file you have two options
1. you need to take backup of transaction log file and then shrink the log file, repeat this process until transaction log file is shrunk
2) change database recovey model to simple and shrink the transation log file, once complete change recovery mode back to full, be advised using this method will break LSN chain.
February 13, 2017 at 9:50 am
Perry Whittle - Monday, February 13, 2017 9:43 AMPlease post the output of this query, replace the DRIVE, PATH and BACKUPFILENAME placeholders as required
restore headeronly from disk ='DRIVE:\PATH\BACKUPFILENAME.BAK'
BackupName BackupDescription BackupType ExpirationDate Compressed Position DeviceType UserName ServerName DatabaseName DatabaseVersion DatabaseCreationDate BackupSize FirstLSN LastLSN CheckpointLSN DatabaseBackupLSN BackupStartDate BackupFinishDate SortOrder CodePage UnicodeLocaleId UnicodeComparisonStyle CompatibilityLevel SoftwareVendorId SoftwareVersionMajor SoftwareVersionMinor SoftwareVersionBuild MachineName Flags BindingID RecoveryForkID Collation FamilyGUID HasBulkLoggedData IsSnapshot IsReadOnly IsSingleUser HasBackupChecksums IsDamaged BeginsLogChain HasIncompleteMetaData IsForceOffline IsCopyOnly FirstRecoveryForkID ForkPointLSN RecoveryModel DifferentialBaseLSN DifferentialBaseGUID BackupTypeDescription BackupSetGUID CompressedBackupSize Containment
dbname_backup_2017_02_12_232252_6331101 NULL 1 NULL 1 1 2 domain\username server\instance dbname 706 2016-09-17 12:21:03.000 160753084416 282863000012235600001 283510000004611500001 283510000004562500066 283505000002992300138 2017-02-12 23:22:53.000 2017-02-12 23:45:24.000 52 0 1033 196609 110 4608 11 0 2100 servername 512 A32B9514-8E61-4D3F-AF83-0B695A638F39 8CD3D12E-402E-40EA-82A0-E87E4706A7AE SQL_Latin1_General_CP1_CI_AS 72DEE494-4279-43AF-9769-1F143C03DFBA 0 0 0 0 0 0 0 0 0 0 8CD3D12E-402E-40EA-82A0-E87E4706A7AE NULL SIMPLE NULL NULL Database 7408BD23-CA6D-47C0-AE00-42CEA3B38D8B 32998632864 0
February 13, 2017 at 9:54 am
planetmatt - Monday, February 13, 2017 9:37 AMwhy is the log so large?
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'
John
February 13, 2017 at 9:54 am
goher2000 - Monday, February 13, 2017 9:50 AMI can not tell you what/which transaction caused it, however to shrink log file you have two options1. you need to take backup of transaction log file and then shrink the log file, repeat this process until transaction log file is shrunk
2) change database recovey model to simple and shrink the transation log file, once complete change recovery mode back to full, be advised using this method will break LSN chain.
The recovery model IS Simple. Never been full. I'm using Snapshot Replication and DBCC OPENTRAN is showing
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (282863:122356:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
but Replication Monitor is not showing any error so I have no idea why there are transactions still waiting to be replicated. Is there any way of seeing what the command is?
February 13, 2017 at 9:55 am
John Mitchell-245523 - Monday, February 13, 2017 9:54 AMplanetmatt - Monday, February 13, 2017 9:37 AMwhy is the log so large?
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'MyDatabase'John
REPLICATION
February 14, 2017 at 2:09 am
John Mitchell-245523 - Monday, February 13, 2017 9:59 AM
I ran sp_repldone which cleared the transaction log and allowed me to shrink it. That blog post says this is a SQL 2005 bug but I'm running SQL 2012. Is this still a bug in newer versions of SQL Server??
February 14, 2017 at 5:00 am
I had it a few weeks ago on a 2012 instance, so I think so.
February 14, 2017 at 5:03 am
Beatrix Kiddo - Tuesday, February 14, 2017 5:00 AMI had it a few weeks ago on a 2012 instance, so I think so.
Ok Thanks.
Thanks to all who replied.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply