SQL Server 2000 Log Backup Size? 
Author Message
 SQL Server 2000 Log Backup Size?

I'm trying to determine if there is anything weird in my SQL Server
2000 transaction log backup size:
DB size = 22.2gb
Log size = 2.8gb
Bulk-logging recovery model



I do subsequent Log backps every 4 hours through 10pm (with NOINIT)

Under normal circumstances, nothing takes place between the 2am Full
DB backup and the 6am Log backup.  I would expect the first Log backup
to be very small since little/no activity has taken place since the
Full DB backup. The first Log backup is continually 1.4gb - is there
an explanation for this??

I've check DBCC OPENTRAN and there are no active open transactions.

Subsequent Log backups are 100-500mb in size and appear proportional
to the amount of activity that has taken place between Log backups.

Finally DBCC LOGINFO shows 331 rows with sizes increasing from 253,952
to 33,816,576.  Row 327 has a status of 2 (all the rest are 0) and a
size of 33,423,360.

Is the backup having to record everything up to VLF 327??  Is there a
way to get around this??

Thanks!
Wyatt



Tue, 27 Sep 2005 16:24:41 GMT
 SQL Server 2000 Log Backup Size?

My guess is that there is some maintenance work going on between 2 AM and 6
AM, like reindexing. Check the jobs that you are running after 2 AM to check
this.
Bulk logging does not mean that activities like reindexing are not logged,
just that the logging happens at the level of datapages, not at row level.
In most cases this will cause less logging and be quicker.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:
> I'm trying to determine if there is anything weird in my SQL Server
> 2000 transaction log backup size:
> DB size = 22.2gb
> Log size = 2.8gb
> Bulk-logging recovery model



> I do subsequent Log backps every 4 hours through 10pm (with NOINIT)

> Under normal circumstances, nothing takes place between the 2am Full
> DB backup and the 6am Log backup.  I would expect the first Log backup
> to be very small since little/no activity has taken place since the
> Full DB backup. The first Log backup is continually 1.4gb - is there
> an explanation for this??

> I've check DBCC OPENTRAN and there are no active open transactions.

> Subsequent Log backups are 100-500mb in size and appear proportional
> to the amount of activity that has taken place between Log backups.

> Finally DBCC LOGINFO shows 331 rows with sizes increasing from 253,952
> to 33,816,576.  Row 327 has a status of 2 (all the rest are 0) and a
> size of 33,423,360.

> Is the backup having to record everything up to VLF 327??  Is there a
> way to get around this??

> Thanks!
> Wyatt



Tue, 27 Sep 2005 16:57:02 GMT
 SQL Server 2000 Log Backup Size?

Thanks Jacco,

This is on a data warehouse and most updates are weekly - most
optimizations occur over the weekend.  I moved the one job that runs
daily to 11pm so that it would occur after the last Log backup and
before the Full DB backup.

So all there is on weekdays between 2am and 6pm is an idle SQL
server....

Wyatt...perplexed...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 27 Sep 2005 18:31:27 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Can Windows 2000 NT backup, perform a backup on SQL Server 2000?, pcumming

2. Can Windows 2000 NT backup, perform a backup on SQL Server 7/2000?, pcumming

3. SQL Server 2000 Transaction Log Backup

4. SQL Server 2000: Transaction Logs and Offline Backups ?

5. how to backup a sql server 65 server from a sql server 2000 server

6. How to restore SQL server 6.5 backup to SQL server 2000

7. Restoring msdb database from SQL Server 7.0 backup to SQL Server 2000

8. Help - restore backup file from SQL server 2000 on SQL server 7.0 failed

9. APIs for accessing SQL Server 7.x and SQL Server 2000 transaction log

10. SQL 2000 Transaction Log Size.

11. Size of LOG in SQL 2000

12. Log Backup information is not being written to Sql Server Logs


 
Powered by phpBB® Forum Software