SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Corrupt?? Or what is going on


Corrupt?? Or what is going on

Author
Message
lawson2305
lawson2305
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 429
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228317 Visits: 46341
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7589 Visits: 2250


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."
lawson2305
lawson2305
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 429
It is 27MB of free space.

To be exact:
from the general page:
Size: 33033.63MB
Space Available: 31.95MB
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228317 Visits: 46341
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


lawson2305
lawson2305
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 429
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?
lawson2305
lawson2305
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 429
Server properties for backup and restore
show compress backup unchecked
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228317 Visits: 46341
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


lawson2305
lawson2305
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 429
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).
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
DCCS_Berea_Prod_backup_2014_05_10_120543_8628395 NULL 1 NULL 0 1 2 NT AUTHORITY\SYSTEM STM5SQL201 DCCS_Berea_Prod 661 2013-03-07 14:12:04.000 23529582592 978000006713400001 1414000006225600001 1414000006190700234 1413000022010500277 2014-05-10 12:05:43.000 2014-05-10 12:08:22.000 52 0 1033 196609 100 4608 10 50 4000 STM5SQL201 512 C741A600-E7A4-465E-94DB-6AA2D0D1DDCC 480D4267-C73C-412E-8666-3158C7D089E3 SQL_Latin1_General_CP1_CI_AS 5BBDC064-4247-4123-818D-1FAEE6A36643 0 0 0 0 0 0 0 0 0 0 480D4267-C73C-412E-8666-3158C7D089E3 NULL FULL NULL NULL Database 3D1279EB-D33C-4854-A413-C3C7183770A9 23529582592


Here is the result from the 4GB db backup:
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
DCCS_Berea_Prod_backup_2014_05_22_162655_2486705 NULL 1 NULL 0 1 2 NT AUTHORITY\SYSTEM STM5SQL201 DCCS_Berea_Prod 661 2013-03-07 14:12:04.000 4272644096 1473000014411100001 1489000019916000001 1489000019906300227 1488000022120800198 2014-05-22 16:26:55.000 2014-05-22 16:27:19.000 52 0 1033 196609 100 4608 10 50 4000 STM5SQL201 512 C741A600-E7A4-465E-94DB-6AA2D0D1DDCC 480D4267-C73C-412E-8666-3158C7D089E3 SQL_Latin1_General_CP1_CI_AS 5BBDC064-4247-4123-818D-1FAEE6A36643 0 0 0 0 0 0 0 0 0 0 480D4267-C73C-412E-8666-3158C7D089E3 NULL FULL NULL NULL Database 1111D083-B52D-4AF5-BA16-A9B09C33D4B2 4272644096

lawson2305
lawson2305
SSC Veteran
SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)SSC Veteran (268 reputation)

Group: General Forum Members
Points: 268 Visits: 429
I don't see anything obvious. Does anyone else see anything that can shine some light?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search