size of differential backups to big

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

size of differential backups to big

Author

Topic

wibni
Starting Member

31 Posts

Posted - 2010-10-26 : 03:37:20

Hello,

I'm working on SQL Server Standard 2005.
My database is 4.5GB. I do a full backup which comes to a backup file of 2GB. If I do a differential backup immediately after my full backup, my differential backup file is still 1.5GB.
I would expect the differential backup file to only be a couple of MB since nothing has changed. Nothing ran on the database in between and I can repeat this scenario as often as I want. The differantial backup does not go down in size.
What can I do to reduce the size of my differential backup file?
My recovery model is set to full. I tried to back up transaction logs or changing it to simple. Nothing seems to work.

Any help much appreciated.

vna_mhars
Starting Member

37 Posts

Posted - 2010-10-27 : 03:18:19

Hi,

If you do backup often after a full backup, why don't you use Transactional backup?

Regards,

vamodente

Kristen
Test

22859 Posts

Posted - 2010-10-27 : 03:26:32

I think the point is that making a Differential Backup immediately after a Full Backup the O/P expects the Differential backup to be Empty / Tiny (as do I ...)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-27 : 04:37:48

You're not perhaps using COPY_ONLY when you do that full backup?

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-10-27 : 21:01:18

No, I'm not using copy_only.
My full backups are done like this:

BACKUP DATABASE [sfbdat] TO  DISK = N'C:\temp\Backup\full.bak' WITH NOFORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10

And my differential backups lke this:

BACKUP DATABASE [sfbdat] TO  DISK = N'C:\temp\Backup\full.bak' WITH  DIFFERENTIAL , NOFORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10

What else could it be? It's only this one database having this issue. Differential backups on other databases are working fine.

Kristen
Test

22859 Posts

Posted - 2010-10-28 : 03:06:53

You are using the same filename - don't know if that is important (i.e. headers / part of file may be preserved / reused) but it will prevent you restoring once the file is overwritten of course. Might just be a typo in making content for this post?

Your use of Tape commands is potentially ambiguous - INIT will clear the file, except the headers, SKIP will override that, NOFORMAT will preserve the headers ... I have no idea what the upshot of that ambiguity is!

NOREWIND / NOUNLOAD are not relevant to disk files either.

I never quite understand how Tape commands come into use on Disk files, but maybe they have a purpose? (Not getting at you, I see this quite often, and it always makes me wonder why)

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-28 : 05:08:16

quote:
Originally posted by Kristen

I never quite understand how Tape commands come into use on Disk files, but maybe they have a purpose? (Not getting at you, I see this quite often, and it always makes me wonder why)

No use. As for why, that's easy. Management studio generates them when it scripts a backup. Most people I've asked don't know what the commands do, they just leave them there.

Init is relevant to data files too, it controls the append/overwrite option, based on the expiration date.
Skip disables the check the expiration date of the backup, so any old backups in the media set won't be overwritten. Set this with init and old backups WILL be overwritten.
Noformat uses the existing media headers.
So that combo will use the existing media headers (if they exist) but overwrite any backup, regardless of expiration date.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-28 : 05:11:14

How long do the backups take? Is there a lot of activity during the backup?

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-10-28 : 22:23:56

Thanks for your replies.

The file name in my differential backup is just a typo actually.
Tried it with fewer options in the script but still no win.

Full backups:

BACKUP DATABASE [sfbdat] TO  DISK = N'C:\temp\Backup\full.bak' WITH NOFORMAT, INIT, SKIP, STATS = 10

Processed 165714 pages for database 'sfbdat', file 'SFBDAT_log' on file 1.
BACKUP DATABASE successfully processed 233746 pages in 85.394 seconds (22.423 MB/sec).

Diff Backups:

BACKUP DATABASE [sfbdat] TO  DISK = N'C:\temp\Backup\diff.bak' WITH  DIFFERENTIAL , NOFORMAT, INIT, SKIP, STATS = 10

Processed 165715 pages for database 'sfbdat', file 'SFBDAT_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 165755 pages in 58.964 seconds (23.028 MB/sec).

The log file gives me this for my full backup:
Date 29/10/2010 10:07:21 AM
Log SQL Server (Current - 28/10/2010 5:40:00 PM)
Source Backup
Message
Database backed up. Database: sfbdat, creation date(time): 2010/03/14(14:34:38), pages dumped: 234198, first LSN: 4182:2676:1, last LSN: 4447:32685:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\temp\Backup\full.bak'}). This is an informational message only. No user action is required.

And my diff backup:
Date 29/10/2010 10:11:24 AM
Log SQL Server (Current - 28/10/2010 5:40:00 PM)
Source Backup
Message
Database differential changes were backed up. Database: sfbdat, creation date(time): 2010/03/14(14:34:38), pages dumped: 166230, first LSN: 4182:2676:1, last LSN: 4447:32705:1, full backup LSN: 4447:32669:37, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\temp\Backup\diff.bak'}). This is an informational message. No user action is required.

There is nothing happening in between my full and differential backup.

Kristen
Test

22859 Posts

Posted - 2010-10-29 : 00:59:34

Processed 165714 pages for database 'sfbdat', file 'SFBDAT_log' on file 1.
BACKUP DATABASE successfully processed 233746 pages in 85.394 seconds (22.423 MB/sec).

That doesn't look right; don't you get another line showing the number of pages for the "file 'SFBDAT_data'" data part of the file? (name may not be quite like that, but the name you are showing looks to be for the LOG).

Looks to me like the TLog has not been backed up / has open or blocked transaction, and the TLog file is making up most of the Full backup, and is included in the DIFF backup too.

Here's what your commands look like on a database here (SQL2008 though ...)

BACKUP DATABASE [MyDatabase] TO DISK = N'C:\MyPath\full.bak' WITH NOFORMAT, INIT, SKIP, STATS = 10

Processed 12568 pages for database 'MyDatabase', file 'MyDatabase_data' on file 1.
Processed 1 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE successfully processed 12569 pages in 1.025 seconds (95.800 MB/sec).

BACKUP DATABASE [MyDatabase] TO DISK = N'E:\KBM_TEMP\diff.bak' WITH DIFFERENTIAL , NOFORMAT, INIT, SKIP, STATS = 10

Processed 40 pages for database 'MyDatabase', file 'MyDatabase_data' on file 1.
Processed 1 pages for database 'MyDatabase', file 'MyDatabase_log' on file 1.
BACKUP DATABASE WITH DIFFERENTIAL successfully processed 41 pages in 0.047 seconds (6.804 MB/sec).

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 01:39:23

Agreed. A very large portion of the log is active. (uncommitted transactions most likely) and at minimum the active portion of the log must be included in a full or diff backup so that the database restores consistently.

The LSNs also show that. There's a massive range between the first LSN and the last LSN showing that a lot of the log is getting included in both backups.

What does DBCC OpenTran return?

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-10-29 : 03:29:38

I do not get the "SFBDAT_data" message or a similiar message. Only the message for the log file.

DBCC OpenTran returns the below:

Transaction information for database 'sfbdat'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4182:2676:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 04:40:26

Is this database supposed to have transactional replication configured?

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-10-29 : 05:54:48

Hi Gail,

No, this might just be an old left over.
It won't let me delete it though. Looks like it's having an issue with teh server name. (Server was renamed a while back)
Found this script which should fix it: http://www.julian-kuiters.id.au/article.php/sqlserver2005-rename-server

Is it just a harmless procedure to change the SQL server name? Will it affect applications using this SQL Server?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 06:11:19

You do need to fix the server name, it may impact stuff other than replication to have the computer name different to what SQL thinks it is.

Is there a current transactional replication publication?

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-10-29 : 11:58:41

Yes, there seem to be a publication set up. But I don't know whether this is still being used.
I only see a publication, should there not also be a subscription?

Is there a way to test whether the replication is still being used?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-10-29 : 16:18:16

Well the log reader's not running, which is the cause (I suspect) of this and is also likely to be causing a transaction log that just keeps growing.

Either start the log reader or drop the publication. I can't tell if it's necessary any longer, you'll have to investigate what was using it (if anything). With no subscription and the log reader stopped, there's no way this publication is currently in use. Whether it should be is another matter that you'll have to look into.

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-11-01 : 05:13:22

Thanks for your help Gail.
Looks like I'm stuck here. Can't drop the publication. Only get an Error 20029. The Distributer has not been installed correctly. Could not disable database for publishing.
If I right-click on Replication in the left tree and select 'Disable Publishing and Distribution' I get Error 2812. An exception occurred while executing a transact-SQL statement or batch.

Should there be an entry under Proxies > Replication Transaction-Log Reader in the left pane?
Is this what you mean by 'start the log reader'?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-11-01 : 05:45:14

No. Proxies are security-related objects. The log reader will be a job.

There may be a workaround. Try creating another publication (transactional) and publishing a single table. This should require the distributor to be recreated (or it may just fail). If you can do that, then see if you can drop both publications.

--
Gail Shaw
SQL Server MVP

wibni
Starting Member

31 Posts

Posted - 2010-11-02 : 02:18:40

Unfortunately it won't let me create another publication. I get the following error:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find stored procedure ''.
Could not find stored procedure ''.
Changed database context to 'my_database'. (Microsoft SQL Server, Error: 2812)

Why would it not be able to find this stored procedure? Is there something that has to be installed additionally to run replication? I double checked and all necessary options seem installed.
Or could it be that it still thinks the server name is different somehow and can therefore not find the stored procedure?

The code below however returns identical names.

 SELECT @@SERVERNAME As [@@SERVERNAME],
CAST(SERVERPROPERTY('MACHINENAME') AS VARCHAR(128)) + COALESCE('' +
CAST(SERVERPROPERTY('INSTANCENAME') AS VARCHAR(128)), '') As RealInstanceName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources