Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Corrupt?? Or what is going on Expand / Collapse
Author
Message
Posted Thursday, May 22, 2014 2:37 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 69, Visits: 350
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?
Post #1573794
Posted Friday, May 23, 2014 2:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1573928
Posted Friday, May 23, 2014 3:25 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061


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."
Post #1573944
Posted Friday, May 23, 2014 6:47 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 69, Visits: 350
It is 27MB of free space.

To be exact:
from the general page:
Size: 33033.63MB
Space Available: 31.95MB
Post #1573989
Posted Friday, May 23, 2014 7:11 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
In that case, did you or someone else enable compression on the backups?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1573997
Posted Friday, May 23, 2014 7:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 69, Visits: 350
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?
Post #1573999
Posted Friday, May 23, 2014 7:46 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 69, Visits: 350
Server properties for backup and restore
show compress backup unchecked
Post #1574019
Posted Friday, May 23, 2014 8:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
If you run RESTORE HEADERONLY on the backup file, what is the result?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1574062
Posted Friday, May 23, 2014 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 69, Visits: 350
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
Post #1574085
Posted Tuesday, May 27, 2014 12:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 69, Visits: 350
I don't see anything obvious. Does anyone else see anything that can shine some light?
Post #1574934
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse