July 19, 2004 at 2:31 am
Our DB started as Sybase, after 6 years (with average growing factor, means it grows consistently, uniformly, no sudden jumps)
The DB file size was 7 GB, the database size is about 2 GB
The DB was migrated to SQL 2K, SP3, Win 2003 Server with same structure,
Very limited client application code modifications where done where the SQL syntax is different.
This migration was one year ago
The SQL Server DB file size is 10 GB
DB usage pattern did not change; it is used between 8 AM and 4 PM
There is no increase in insertions or updates
So I estimate the database size now to be around 4 GB (I know that I might need to run any of those dbcc commands to report on space used or sp_spaceused) but this estimation should be OK
The entire DB is backed up daily at 10 PM to a file, using
BACKUP DATABASE dbname to disk = ‘db_diskfile’
Trans log is backed up 7 times daily (2 hours interval) to a file using
BACKUP LOG logname to disk = ‘log_diskfile’
These commands are used exactly as typed here, so no room for hints about “are you using “no truncate” or using multiple file groups or differential backup or ……”
The Windows user has all required privileges to the folder containing ‘db_diskfile’ and ‘log_diskfile’ , the SQL user is the DBO
I understand the following (based on syntax used above):
1. Log is automatically truncated after the backup
2. db_diskfile size should be much smaller then 10 GB since it contains only the actual data, so it should be around 4 GB
Log backup is OK, no problems; except the size variation is somehow “weird”
here is a sample for the backup file (log_diskfile) size for one day (log is backed 7 times a day):
33 MB - 3 GB - 450 MB -
The database backup file (db_diskfile) size varies (on different days) between 7 and 9.5 GB !!!!!!!!!!
Question1: Why is the huge variation in log backup size????
Question2: Why a database with size about 4 GB produces a backup of size 7 – 9.5 GB ???
What I’m looking for is a logical explanation, and nothing like “try to run DBCC CHECKDB, or DBCC DBREINDEX, or …..”
Yes I may run them later (this DB is very very critical, so I wouldn’t take a chance during working hours), and maybe they will tell me something, but again what is the explanation of this?
EM
July 19, 2004 at 4:54 am
ok ,
first thing first, in Enterprise manager you can check in the taskpad view of any database what the actual size of the data is in the datafile (there is a blue/grey bar at the bottom of taskpad)
the variation in transaction log file may be due to the types of changes you are making....Are there triggers or procedures that change data in the background (such as auditing triggers).
they may also be related to indexes. if indexes are modified then these are also logged to the T-Log.
do you have any clustered indexes or large indexes ? are there primary keys defined on all tables ? (by default the primary key is created as a clustered index if you use EM)
also has the migration from Sybase gone according to plan? are the data types correct ? I've seen many migrations from Oracle and sybase where there are no Primary keys and the wrong types of indexes are implemented.
also are you using nVarchar and nVar fields - unless you have unicode constraints on your applications then why not convert to Char and varchar (half the storage requirements)
I know it's DBCC but you can also use DBCC cleantable to free up space in the tables where changes from VARCHAR to char have been made (a common change after a migration - e.g. varchar(255) is changed to char(8) )
if you're not using DBCC dbreindex (or indexdefrag) then how are you maintaining your indexes - performance will suffer and something is bound to go wrong!!!!!
MVDBA
July 19, 2004 at 11:03 am
The differences in sizes of your transaction log backups are simply a result of heavier usage. You say that the database is used between 8am and 4pm (8 hours) and that you are taking 7 transaction log backups at a 2 hour interval (14 hours). Unless you have some batch processes occuring outside of the 8am-4pm window, the transaction log backups that are taken during that time should be quite small. I would think that the database would have times of heavy use and times of light use during the 8-4 window (ie, light during lunch, heavier just after lunch). This would also account for variations in the transaction log backup sizes.
The transaction log is only truncated with a transaction log backup, not a database backup. However, the transactions in the database are backed up in a full backup. This would account for the differences in full backup sizes.
As Mike mentioned you can see the database file sizes along with the percentage used in the taskpad view in EM. Your backup sizes are going to be based on the amount of space used, rather than the amount of space allocated.
Steve
July 20, 2004 at 12:00 am
Hi,
1. Actual DB size = 2.3 GB (space allocated = 10 GB)
2. The migration from Sybase to SQL Serv was OK without any problems or "glitches" from all aspects including the data types usage mentioned
3. Of course; each table has one clustered index, some of them have four, five indexes each, but please notice that all these are part of the 2.3 GB
4. There are no batch processing, no triggers in the DB
Now, Variations in log backup I can accept, it make sense to me, but are you trying to tell me it is NORMAL for a DB of actual size = 2.3 GB to produce a backup of size 9 GB????????
It is the DB backup size that worries me much and not the log backup size!
EM
July 20, 2004 at 8:51 am
You've told us that the space allocated for the db is 10GB with 2.3GB used. However, you didn't say anything about the transaction log. If the data size is 2.3GB and the transaction log is holding 6.7GB of transactions, your backup could very well be 9GB.
July 21, 2004 at 5:14 am
just a thought - are you appending to the existing backups?
or is it a new file every day ?
MVDBA
July 25, 2004 at 4:53 am
Sorry for the late reply
It's a new file every day, no appending
Trans log allocated size = 3 GB
DB actual size = 2.3 GB
So if all trans log space was used, the DB backup file should not exceed 3+2.3 = 5.3 GB
But it varies between 7 - 9.5 GB !!
Why???
EM
July 25, 2004 at 6:58 am
What about your system databases? Are you backing those up to the same file?
Does your database only have 2 files? One data, and one log?
Beyond this, I'm fresh out of ideas... unless its simply a difference in how SQL is compressing the data.
Steve
July 25, 2004 at 7:52 am
The backup file contains ONLY a user database, no other databases are backed to the same file
This database contains only 2 files, one data, and one log
EM
July 29, 2004 at 7:43 am
First try to shrink Db (with Log also)
second it is recommended to make backup due to your Insertion and updates time .. for me :
1- i make full backup everyday
2- i make Log backup every 1 hour (or half a hour)
this makes Log file doesn't grow too much
I hope this Help u
Alamir Mohamed
Alamir_mohamed@yahoo.com
July 29, 2004 at 8:01 am
Hi,
That is EXACTLY what I've been doing, daily full backup, log backup every 2h.
The issue is that DB backup is unreasonably growing.
Again, DB allocated size = 10 GB
Log allocated size = 3 GB
DB actual size -> never exceeded 2.5 GB
log actual size -> always around 1 GB
DB backup file (which should contain only actual data) -> varies between 7.5 and 10.5 GB, and growing (TODAY DB BACKUP FILE SIZE IS 12.7 GB !!!!!)
DB performance -> steady, no degradation, response as usual
DB -> does not contain any wiered or complex objects (e.g. triggers, stored procss, ...)
if it continue growing, NOW I'm ending with a DB backup (only) bigger than both ALLOCATED LOG + ALLOCATED DB !!!
EM
July 29, 2004 at 9:02 am
Let's clear something up here.
The backup is not JUST the actual data!
A backup is a file that is used to restore the data back to it's status at the time of the backup.
At the time of your backup, the database consists of:
1. Data
2. Transactions
3. Indexes
4. Unused (unallocated) space
If your data is 2 GB and your database is 10 GB, the backup will be 10 GB. It backs up the unused (unallocated) space since it needs that to restore the database to it's original condition - which included the unused space.
-SQLBill
July 29, 2004 at 9:15 am
Thnak you sir,
but today :
my DB is 10 GB
DB backup is 12.7 GB
based on what u say explain how backup was 7 GB for a DB that is 10 GB
also this contradicts with previous statement in this thread that backup file size should be near to ACTUAL DB SIZE (2.3 GB)
this big variation (7 to 12.7) in one month time, is not due to additional load in DB, DB usage is always the same, STEADY, NORMAL
EM
July 29, 2004 at 9:47 am
EM,
Would you be willing to try something? Would you manually delete your backup file (or move it, or rename it) before your next backup run? Continual growth of a backup file in which the growth rate roughly equals your data size really, really makes it look like the backups are being appended.
Let us know what happens!
Steve
July 29, 2004 at 9:48 am
What is recovery model for your database?
Does your application do any bulk-logged operations?
Another thing, could you try to restore one of your 'big' backups
on the test server and run DBCC OPENTRAN in that database.
Igor
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply