May 22, 2014 at 2:37 pm
So Saturday the drive that holds our DB's ran out of space and on Monday I fixed the space issue and moved my backups to a new drive to reduce space. I ran a successful DB Integrity check after and the DB has been functioning without any complaints since but...
I noticed my full backups went from 28GB down to 4GB. The db properties under General, Database says the size is 33033.63MB with 27.36MB free space.
Can someone explain what is going on all of a sudden after this event or help me out?
May 23, 2014 at 2:27 am
Sounds about right. 32GB with 27GB free space is roughly 5GB of data. Backjups only back up the data, not free space, so the backup being around 4GB (allowing for rounding and different conversions of MB to GB, 1000 vs 1024), 4GB backup sounds correct.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2014 at 3:25 am
I noticed my full backups went from 28GB down to 4GB. The db properties under General, Database says the size is 33033.63MB with 27.36MB free space.
It's 27.36 MB or GB free space?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 23, 2014 at 6:47 am
It is 27MB of free space.
To be exact:
from the general page:
Size: 33033.63MB
Space Available: 31.95MB
May 23, 2014 at 7:11 am
In that case, did you or someone else enable compression on the backups?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2014 at 7:13 am
It is set to use the Server Default setting and has always been set this way. How can I check what the system default is as I have never modified this?
May 23, 2014 at 7:46 am
Server properties for backup and restore
show compress backup unchecked
May 23, 2014 at 8:30 am
If you run RESTORE HEADERONLY on the backup file, what is the result?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 23, 2014 at 9:05 am
RESTORE HEADERONLY
FROM DISK = N'H:\Backup\PROD\File.bak'
Ok here is the run against a DB that was normal back from 5/10 (23GB).
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSize
DCCS_Berea_Prod_backup_2014_05_10_120543_8628395NULL1NULL012NT AUTHORITY\SYSTEMSTM5SQL201DCCS_Berea_Prod6612013-03-07 14:12:04.000235295825929780000067134000011414000006225600001141400000619070023414130000220105002772014-05-10 12:05:43.0002014-05-10 12:08:22.0005201033196609100460810504000STM5SQL201512C741A600-E7A4-465E-94DB-6AA2D0D1DDCC480D4267-C73C-412E-8666-3158C7D089E3SQL_Latin1_General_CP1_CI_AS5BBDC064-4247-4123-818D-1FAEE6A366430000000000480D4267-C73C-412E-8666-3158C7D089E3NULLFULLNULLNULLDatabase3D1279EB-D33C-4854-A413-C3C7183770A923529582592
Here is the result from the 4GB db backup:
BackupNameBackupDescriptionBackupTypeExpirationDateCompressedPositionDeviceTypeUserNameServerNameDatabaseNameDatabaseVersionDatabaseCreationDateBackupSizeFirstLSNLastLSNCheckpointLSNDatabaseBackupLSNBackupStartDateBackupFinishDateSortOrderCodePageUnicodeLocaleIdUnicodeComparisonStyleCompatibilityLevelSoftwareVendorIdSoftwareVersionMajorSoftwareVersionMinorSoftwareVersionBuildMachineNameFlagsBindingIDRecoveryForkIDCollationFamilyGUIDHasBulkLoggedDataIsSnapshotIsReadOnlyIsSingleUserHasBackupChecksumsIsDamagedBeginsLogChainHasIncompleteMetaDataIsForceOfflineIsCopyOnlyFirstRecoveryForkIDForkPointLSNRecoveryModelDifferentialBaseLSNDifferentialBaseGUIDBackupTypeDescriptionBackupSetGUIDCompressedBackupSize
DCCS_Berea_Prod_backup_2014_05_22_162655_2486705NULL1NULL012NT AUTHORITY\SYSTEMSTM5SQL201DCCS_Berea_Prod6612013-03-07 14:12:04.000427264409614730000144111000011489000019916000001148900001990630022714880000221208001982014-05-22 16:26:55.0002014-05-22 16:27:19.0005201033196609100460810504000STM5SQL201512C741A600-E7A4-465E-94DB-6AA2D0D1DDCC480D4267-C73C-412E-8666-3158C7D089E3SQL_Latin1_General_CP1_CI_AS5BBDC064-4247-4123-818D-1FAEE6A366430000000000480D4267-C73C-412E-8666-3158C7D089E3NULLFULLNULLNULLDatabase1111D083-B52D-4AF5-BA16-A9B09C33D4B24272644096
May 27, 2014 at 12:55 pm
I don't see anything obvious. Does anyone else see anything that can shine some light?
May 27, 2014 at 1:07 pm
Can you run DBCC UpdateUsage and then check free space again?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 27, 2014 at 2:24 pm
I ran DBCC UPDATEUSAGE (DCCS_Berea_Prod)
The Messages say:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DB size when still going into properties shows 33033.63 MB with space available of 82.99 MB
Now if I got in Shrink by file I get some strange data saying currently allocated space of 1300 MB and 82.81 MB free.
Ok if I go into Shrink for the log file it shows a 31733.63 MB currently allocated and 26354.71 MB free.
I also had a strange thing happen where my weekly plan this week executed on Saturday and was still running this morning. I stopped the job there is no reference that it did anything in the logs.
May 27, 2014 at 3:13 pm
lawson2305 (5/27/2014)
Now if I got in Shrink by file I get some strange data saying currently allocated space of 1300 MB and 82.81 MB free.Ok if I go into Shrink for the log file it shows a 31733.63 MB currently allocated and 26354.71 MB free.
Ah, now things make sense. You've got a very small amount of data (1.3GB) but a massive log file. Backups don't contain the entire transaction log. They just need enough of the log to recover the database once restored. That large backup must have occurred when there was a huge uncommitted transaction, or lots of unreplicated transactions and hence it needed to backup most of the log. The more recent backup didn't.
You might want to investigate and see why a 1.3 GB database has 5 GB of log in use. Large transactions, replication falling behind, lack of log maintenance?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 28, 2014 at 7:25 am
Ok a strange thing regarding the log.
1. This backup has been about 30GB for quite sometime and we do full daily log backups and hourly append log backups from a maintenance plan. Is there a setting in the backup that is not committing the transactions in the backup?? What is strange is right after the free space loss is when the backup all of a sudden changed.
Basically it has been a steady growth of space up to the 30GB and held there since around 5/14.
May 28, 2014 at 7:43 am
Backups don't commit transactions, so not sure what you want there.
I don't know what was done when the DB ran out of space, so can't comment on whether it's strange or not.
It sounds like you possibly had a very long running transaction (someone ran begin tran and left the connection open), that would mean that the backup would have to include all the log back to that begin tran and nothing would commit that transaction until the connection was explicitly closed or the service restarted. Other possibility is unreplicated transactions from transactional replication, that would also require the full backup to include all the log back to the first unreplicated transaction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply