DB Size and Backup Size disparity

Question

Post reply

DB Size and Backup Size disparity

tanz

Valued Member

Points: 70
More actions
August 11, 2005 at 5:17 am

#110075

I noticed for several days one of our db size data+log is about 4.2GB. But the backup (full) file size is only 650MB. If I shrink the file it goes down to 650MB however in DBArtisan (I use for monitoring and admin) it shows as -
TotalSpace 650MB freeDBSpace -3600MB (negetive) with a red alert.
Why this disparity?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLHAWK Right there with Babe Points: 716 More actions · Answer 1

What size is the log? The log would not be included in the DB backup.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 2

I've never used DBArtisan but as for your question, when you do a backup it only backs up the actual data in the database, if you allocate 20GB to the data file but only fill 650MB then the backup will only be 650MB in size.

If this hasn't explained why the backup is so small as opposed to the database size then there's either some compression being done (litespeed, sql backup, etc) or it's a problem in DBArtisan.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

To check things out properly you'll need to run the following in QA:

exec your_database..sp_spaceused

go

dbcc sqlperf(loginfo)

go

This will give use space for both the database and the transaction log at that moment. At least this is concrete information you can count on.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 4

good point, although the first one can be a little out of date if you dont use dbcc updateusage first.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

You are correct Mike ... I made an 'assumption' that everyone does the proper maintenance ... including that !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

tanz Valued Member Points: 70 More actions · Answer 6

Sp_spaceused gave result as -

HRS 439.25MB(Database_Size) -3861.59MB(unallocated space)

4351640KB(reserved) 125696KB(data) 4216672KB(index_size) 9272KB(unused)

Log Size with dbcc sqlperf(logspace) is 51.18 used 15.39

tanz Valued Member Points: 70 More actions · Answer 7

sp_spaceused @updateusage = 'true' actually corrected the problem. Now I can see the database size is 439.25MB and unallocated space is 117.34MB

Wondering why the unallocated space grown to the extant of 3861.59MB.

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

I'd attribute it to another 'un-documented feature' (bug) left over from the old Sybase code base ...

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."