July 29, 2004 at 11:08 am
A smaller backup size is usually due to using 'compression' when backing up the database.
Are you using third-party software to do your backups?
-SQLBill
July 29, 2004 at 1:55 pm
SQLBill,
SQL doesn't include the allocated, but unused space in the backup. As an example, I just backed up my "DBA" database on a test server.
From EM Taskpad -
data file 31MB , 29MB used
log file 17.8MB, 4MB used
Total 48.8MB, 33MB
Backed up database using EM, backup file is 29.13MB.
Steve
July 30, 2004 at 11:23 pm
Steve,
There is no possibility of appending, each file has a distinct name (db_sat, db_sun, ...)
they're circulated weekly, but prev one is deleted before new one is created (e.g. for saturday backup, previous existing db_sat is deleted first)
Igor,
My recovery model is FULL
My application does not do any bulk-logged operations
SQLBill,
Im not using any third-party software to do my backups
This is becoming more mysterious everyday, but i'm sure we'll find out the reason, only hope it will not be a "silly" one, like overlooking something or such
Thank you all for your efforts, really feeling a bond with all of you there!
EM
July 31, 2004 at 8:54 am
How about DBCC OPENTRAN ?
Check "Long-Running Transactions" topic in BOL.
Igor
August 1, 2004 at 1:29 am
Db does not have any long running transactions, these would have been immediatly detected and rolledback by the application, anyhow checked and found none
The funny thing now is that on some days the DB backup file size is about 3 GB (proper size, this is how it should be), on other days it is 14.7 GB !!!
log backup never exceeds 3 GB
no diff in backup process, command used, time , schedule, system conditions, network, clients, app and users between these days (as far as I know till now)
EM
August 1, 2004 at 7:53 pm
Do you have 'Auto Shrink' option enabled by any chance?
Can you restore (on the test server) your 14.7 GB backup and tell size of data and log files.
Igor
August 2, 2004 at 12:43 am
do u append on your backup File ? this may be the reason of your problem
Also .. try to Shrink your Db ... and everything will be fine after that
I hope this help u.
Alamir Mohamed
Alamir_mohamed@yahoo.com
August 2, 2004 at 12:58 am
Hi,
It looks like some gentlemen when they write replys they base it only on the last post in the forum.
I'd say that for someone to post a reply he should read the thread from the begining to to know all the problem symptoms
This is a quote from a previous reply in this thread:
"There is no possibility of appending, each file has a distinct name (db_sat, db_sun, ...)
they're circulated weekly, but prev one is deleted before new one is created (e.g. for saturday backup, previous existing db_sat is deleted first) "
thank you all
EM
August 2, 2004 at 2:24 am
here's something else to try -
use DBCC cleantable on all of your tables to see if there is any space to free up (after your migration from sybase)
this is wasted space (not free space) and maybe can shrink the data.
of course this won't solve the mystery of your backup size, but can i point out that is bad practice to delete your backup before you've sucessfully creasted your new backup. - what happens if it fails??
MVDBA
August 2, 2004 at 2:30 am
i've just re-read some of the posts - you say that on some days it is the correct size ?
are you doing any overnight batch processing - a reindex or something? make sure your backup is not conflicting with this.
try a manual backup several times and see if the size is consistently right.
if so - bingo - some sort of timing issue
MVDBA
August 2, 2004 at 3:56 am
by the way , i think that reindex DB make backup file after it bigger in size ..
do u do this?
Alamir Mohamed
Alamir_mohamed@yahoo.com
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply