sql large inserts: backups

Showing posts with label backups. Show all posts

Friday, March 9, 2012

Large Transaction Log Files - SQL 2000

Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uuYyNXxEHHA.4508@.TK2MSFTNGP02.phx.gbl...
> From your statement that TLog backups impact the website performance, it
> sounds like the SQL Server is running on the same box as the Web Server
> AND it must also be an 'underpowered' server. (I've often seen TLog
> backups on multiGB databases having lots of activity take only seconds
> when done hourly -and the users never realized anything was different.)
> For your current schedule, I would make sure that there is NO TLog backup
> on Sunday -it is most likely is happening before the optimization and FULL
> Backup.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>

Large Transaction Log Files - SQL 2000

The SQL 2000 transaction log backups for our customer's databases are
regularly quite large - often times almost as large as the database itself
despite the fact that we run transaction log backups nightly.
We're wondering if the size of the log backups is related to the SQL
optimization jobs we regularly run. How often is it appropriate to run an
optimization job? When is the best time to do the optimizations? (After a
full db backup? Before a full db backup? After a transaction log
backup?Before a transaction log backup?) Or does it really matter at all?
Our databases vary greatly in size from a few megabytes to a few gigabytes.
Being ecommerce website databases, a large percentage of the activity is
read activity (shoppers browsing the site) but there is write activity when
shoppers register, place an order etc. The number of transactions varies
widely from a transaction once a week to hundreds or thousands of
transactions per day depending on which customers database we are talking
about.
Thanks,
BradIt sounds like a lot of the Transaction Log activity may be related to the
optimization efforts.
Why do you feel the need to exercise optimization efforts out of band with
the backup schedule?
Depending upon write activity, and types of indexes, padding, etc.,
optimization may be occurring much too often.
If you wanted to provide more details about your optimization and backup
schedules, we may be able to give you more directed assistance.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> The SQL 2000 transaction log backups for our customer's databases are
> regularly quite large - often times almost as large as the database itself
> despite the fact that we run transaction log backups nightly.
> We're wondering if the size of the log backups is related to the SQL
> optimization jobs we regularly run. How often is it appropriate to run an
> optimization job? When is the best time to do the optimizations? (After a
> full db backup? Before a full db backup? After a transaction log
> backup?Before a transaction log backup?) Or does it really matter at all?
> Our databases vary greatly in size from a few megabytes to a few
> gigabytes. Being ecommerce website databases, a large percentage of the
> activity is read activity (shoppers browsing the site) but there is write
> activity when shoppers register, place an order etc. The number of
> transactions varies widely from a transaction once a week to hundreds or
> thousands of transactions per day depending on which customers database we
> are talking about.
> Thanks,
> Brad
>|||> We're wondering if the size of the log backups is related to the SQL optimization jobs we
> regularly run.
Likely. It depends on what you actually mean by "optimization". If you mean index rebuilds, then all
data that you "shuffle" will be logged. This can easily be almost same as db size. It is also likely
that you do lots if optimization when you don't have to. Reading
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx is a good start.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad Baker" <brad@.nospam.nospam> wrote in message news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> The SQL 2000 transaction log backups for our customer's databases are regularly quite large -
> often times almost as large as the database itself despite the fact that we run transaction log
> backups nightly.
> We're wondering if the size of the log backups is related to the SQL optimization jobs we
> regularly run. How often is it appropriate to run an optimization job? When is the best time to do
> the optimizations? (After a full db backup? Before a full db backup? After a transaction log
> backup?Before a transaction log backup?) Or does it really matter at all?
> Our databases vary greatly in size from a few megabytes to a few gigabytes. Being ecommerce
> website databases, a large percentage of the activity is read activity (shoppers browsing the
> site) but there is write activity when shoppers register, place an order etc. The number of
> transactions varies widely from a transaction once a week to hundreds or thousands of transactions
> per day depending on which customers database we are talking about.
> Thanks,
> Brad
>|||Thanks for the information. Here is how we have our SQL 2000 Maintenance
Plan Setup:
[Optimization]
[x] Reorganize data and index pages
( ) Reorganize pages with the original amount of free space
(*) Change Free Space per page percentage to: 10%
[ ] Update the statistics used by the query optimizer
Percentage of database to sample: (grayed out)
[ ] Remove unused space from the database files
( ) Shrink database when it grows beyond: (grayed out)
( ) Amount of free space to remain after shrink (grayed out)
Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
[Integrity Check]
[x] Check database integrity
(*) Include indexes
[ ] Attempt to repair any minor problems
( ) Excluded indexes
[ ] Perform these tests before backing up the database or transaction log.
Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
[Complete Backup]
[x] Backup the database as part of the maintenance plan
[x] Verify the integrity of the backup upon completion
( ) Tape (grayed out)
( ) Disk
( ) Use the default backup directory
(*) Use this directory (backup directory)
[x] Create a sub-directory for each database
[x] Remove files older than 2 weeks
Backup extension: BAK
Occurs every 1 week(s) on Sunday, at 2:00:00 AM
[Transaction Log Backup]
[x] Backup the transaction log of the database as part of the maintenance
plan
[x] Verify the integrity of the backup upon completion
( ) Tape (grayed out)
( ) Disk
( ) Use the default backup directory
(*) Use this directory (backup directory)
[x] Create a sub-directory for each database
[x] Remove files older than 2 weeks
Backup extension: TRN
Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59 AM.
The reason for limiting transaction log backups to the night was that we
found they were negatively impacting website performance.
Hopefully that makes sense but please let me know if you have questions.
Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
> It sounds like a lot of the Transaction Log activity may be related to the
> optimization efforts.
> Why do you feel the need to exercise optimization efforts out of band with
> the backup schedule?
> Depending upon write activity, and types of indexes, padding, etc.,
> optimization may be occurring much too often.
> If you wanted to provide more details about your optimization and backup
> schedules, we may be able to give you more directed assistance.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> The SQL 2000 transaction log backups for our customer's databases are
>> regularly quite large - often times almost as large as the database
>> itself despite the fact that we run transaction log backups nightly.
>> We're wondering if the size of the log backups is related to the SQL
>> optimization jobs we regularly run. How often is it appropriate to run an
>> optimization job? When is the best time to do the optimizations? (After a
>> full db backup? Before a full db backup? After a transaction log
>> backup?Before a transaction log backup?) Or does it really matter at all?
>> Our databases vary greatly in size from a few megabytes to a few
>> gigabytes. Being ecommerce website databases, a large percentage of the
>> activity is read activity (shoppers browsing the site) but there is write
>> activity when shoppers register, place an order etc. The number of
>> transactions varies widely from a transaction once a week to hundreds or
>> thousands of transactions per day depending on which customers database
>> we are talking about.
>> Thanks,
>> Brad
>|||From your statement that TLog backups impact the website performance, it
sounds like the SQL Server is running on the same box as the Web Server AND
it must also be an 'underpowered' server. (I've often seen TLog backups on
multiGB databases having lots of activity take only seconds when done
hourly -and the users never realized anything was different.)
For your current schedule, I would make sure that there is NO TLog backup on
Sunday -it is most likely is happening before the optimization and FULL
Backup.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
> Thanks for the information. Here is how we have our SQL 2000 Maintenance
> Plan Setup:
>
> [Optimization]
> [x] Reorganize data and index pages
> ( ) Reorganize pages with the original amount of free space
> (*) Change Free Space per page percentage to: 10%
>
> [ ] Update the statistics used by the query optimizer
> Percentage of database to sample: (grayed out)
>
> [ ] Remove unused space from the database files
> ( ) Shrink database when it grows beyond: (grayed out)
> ( ) Amount of free space to remain after shrink (grayed out)
>
> Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
>
>
>
> [Integrity Check]
> [x] Check database integrity
> (*) Include indexes
> [ ] Attempt to repair any minor problems
> ( ) Excluded indexes
>
> [ ] Perform these tests before backing up the database or transaction log.
>
> Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
>
>
>
> [Complete Backup]
> [x] Backup the database as part of the maintenance plan
> [x] Verify the integrity of the backup upon completion
>
> ( ) Tape (grayed out)
> ( ) Disk
> ( ) Use the default backup directory
> (*) Use this directory (backup directory)
>
> [x] Create a sub-directory for each database
> [x] Remove files older than 2 weeks
>
> Backup extension: BAK
>
> Occurs every 1 week(s) on Sunday, at 2:00:00 AM
>
>
> [Transaction Log Backup]
> [x] Backup the transaction log of the database as part of the maintenance
> plan
> [x] Verify the integrity of the backup upon completion
>
> ( ) Tape (grayed out)
> ( ) Disk
> ( ) Use the default backup directory
> (*) Use this directory (backup directory)
>
> [x] Create a sub-directory for each database
> [x] Remove files older than 2 weeks
>
> Backup extension: TRN
>
> Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59 AM.
>
>
> The reason for limiting transaction log backups to the night was that we
> found they were negatively impacting website performance.
>
> Hopefully that makes sense but please let me know if you have questions.
>
> Thanks,
> Brad
>
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
>> It sounds like a lot of the Transaction Log activity may be related to
>> the optimization efforts.
>> Why do you feel the need to exercise optimization efforts out of band
>> with the backup schedule?
>> Depending upon write activity, and types of indexes, padding, etc.,
>> optimization may be occurring much too often.
>> If you wanted to provide more details about your optimization and backup
>> schedules, we may be able to give you more directed assistance.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Brad Baker" <brad@.nospam.nospam> wrote in message
>> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> The SQL 2000 transaction log backups for our customer's databases are
>> regularly quite large - often times almost as large as the database
>> itself despite the fact that we run transaction log backups nightly.
>> We're wondering if the size of the log backups is related to the SQL
>> optimization jobs we regularly run. How often is it appropriate to run
>> an optimization job? When is the best time to do the optimizations?
>> (After a full db backup? Before a full db backup? After a transaction
>> log backup?Before a transaction log backup?) Or does it really matter at
>> all?
>> Our databases vary greatly in size from a few megabytes to a few
>> gigabytes. Being ecommerce website databases, a large percentage of the
>> activity is read activity (shoppers browsing the site) but there is
>> write activity when shoppers register, place an order etc. The number of
>> transactions varies widely from a transaction once a week to hundreds or
>> thousands of transactions per day depending on which customers database
>> we are talking about.
>> Thanks,
>> Brad
>>
>|||Does you maintenance plan include more than one database?
I ask, because we are having the same problem and our maintenance plan
specifies 3 databases - where the 1st log file looks fine and the 2nd & 3rd
are quite large.
I just made a post on the subject.|||We have 6 maintenance plans - all of which contain about 60 databases each.
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:EF1DFA32-E412-4A9D-850E-68783CF8BDA0@.microsoft.com...
> Does you maintenance plan include more than one database?
> I ask, because we are having the same problem and our maintenance plan
> specifies 3 databases - where the 1st log file looks fine and the 2nd &
> 3rd
> are quite large.
> I just made a post on the subject.|||We have several DB servers all experiencing this problem. They are all
dedicated SQL servers though - no other applications running on them.
The servers are running dual xeon processors with 8GB of RAM. Some of the
servers have a handful of very large databases and other servers have a lot
(100-200) small databases. That's not to say the server isn't underpowered
for the load we are placing on it - sometimes the load is quite high and
other times its nominal.
When the Tlog backups were running we would see ASP errors involving SQL
timeouts as well as short disruptions in service.. so that's why we decided
we probably out to limit tlog backups to night.
I'm going to try disabling transaction log backups on Sunday and see if that
improves things. If you have any other ideas please let me know :)
Thanks,
Brad
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:uuYyNXxEHHA.4508@.TK2MSFTNGP02.phx.gbl...
> From your statement that TLog backups impact the website performance, it
> sounds like the SQL Server is running on the same box as the Web Server
> AND it must also be an 'underpowered' server. (I've often seen TLog
> backups on multiGB databases having lots of activity take only seconds
> when done hourly -and the users never realized anything was different.)
> For your current schedule, I would make sure that there is NO TLog backup
> on Sunday -it is most likely is happening before the optimization and FULL
> Backup.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uhAuaUoEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> Thanks for the information. Here is how we have our SQL 2000 Maintenance
>> Plan Setup:
>>
>> [Optimization]
>> [x] Reorganize data and index pages
>> ( ) Reorganize pages with the original amount of free space
>> (*) Change Free Space per page percentage to: 10%
>>
>> [ ] Update the statistics used by the query optimizer
>> Percentage of database to sample: (grayed out)
>>
>> [ ] Remove unused space from the database files
>> ( ) Shrink database when it grows beyond: (grayed out)
>> ( ) Amount of free space to remain after shrink (grayed out)
>>
>> Occurs every 1 week(s) on Sunday, at 1:00:00 AM.
>>
>>
>>
>> [Integrity Check]
>> [x] Check database integrity
>> (*) Include indexes
>> [ ] Attempt to repair any minor problems
>> ( ) Excluded indexes
>>
>> [ ] Perform these tests before backing up the database or transaction
>> log.
>>
>> Occurs every 1 week(s) on Sunday, at 12:00:00 AM.
>>
>>
>>
>> [Complete Backup]
>> [x] Backup the database as part of the maintenance plan
>> [x] Verify the integrity of the backup upon completion
>>
>> ( ) Tape (grayed out)
>> ( ) Disk
>> ( ) Use the default backup directory
>> (*) Use this directory (backup directory)
>>
>> [x] Create a sub-directory for each database
>> [x] Remove files older than 2 weeks
>>
>> Backup extension: BAK
>>
>> Occurs every 1 week(s) on Sunday, at 2:00:00 AM
>>
>>
>> [Transaction Log Backup]
>> [x] Backup the transaction log of the database as part of the maintenance
>> plan
>> [x] Verify the integrity of the backup upon completion
>>
>> ( ) Tape (grayed out)
>> ( ) Disk
>> ( ) Use the default backup directory
>> (*) Use this directory (backup directory)
>>
>> [x] Create a sub-directory for each database
>> [x] Remove files older than 2 weeks
>>
>> Backup extension: TRN
>>
>> Occurs every 1 day(s), every 4 hour(s) between 12:15:00 AM and 6:59:59
>> AM.
>>
>>
>> The reason for limiting transaction log backups to the night was that we
>> found they were negatively impacting website performance.
>>
>> Hopefully that makes sense but please let me know if you have questions.
>>
>> Thanks,
>> Brad
>>
>>
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:uQNhHHmEHHA.4808@.TK2MSFTNGP03.phx.gbl...
>> It sounds like a lot of the Transaction Log activity may be related to
>> the optimization efforts.
>> Why do you feel the need to exercise optimization efforts out of band
>> with the backup schedule?
>> Depending upon write activity, and types of indexes, padding, etc.,
>> optimization may be occurring much too often.
>> If you wanted to provide more details about your optimization and backup
>> schedules, we may be able to give you more directed assistance.
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to
>> the top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Brad Baker" <brad@.nospam.nospam> wrote in message
>> news:uKE47DmEHHA.4024@.TK2MSFTNGP04.phx.gbl...
>> The SQL 2000 transaction log backups for our customer's databases are
>> regularly quite large - often times almost as large as the database
>> itself despite the fact that we run transaction log backups nightly.
>> We're wondering if the size of the log backups is related to the SQL
>> optimization jobs we regularly run. How often is it appropriate to run
>> an optimization job? When is the best time to do the optimizations?
>> (After a full db backup? Before a full db backup? After a transaction
>> log backup?Before a transaction log backup?) Or does it really matter
>> at all?
>> Our databases vary greatly in size from a few megabytes to a few
>> gigabytes. Being ecommerce website databases, a large percentage of the
>> activity is read activity (shoppers browsing the site) but there is
>> write activity when shoppers register, place an order etc. The number
>> of transactions varies widely from a transaction once a week to
>> hundreds or thousands of transactions per day depending on which
>> customers database we are talking about.
>> Thanks,
>> Brad
>>
>>
>

Large Transaction Log Files - SQL 2000

Large transaction log backups

I have a maintenance plans set up for a few of my databases where I am
running a full backup on Sundays, followed by transaction log backups each
day of the week. As far as I understand it, each transaction log should
then be backing up transactions made since the last backup (whether that's a
full or transaction).
What I'm actually seeing though is that the first transaction log backup
after each full one is far larger than I'm expecting (in comparison to the
size of the database anyway).
For example, one of those is producing backup files of these sizes:
06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
I'm think I'm misunderstanding the process somewhere. Could anyone clarify?"Rob Oldfield" <blah@.blah.com> wrote in message
news:%23mRHK8EnHHA.3520@.TK2MSFTNGP04.phx.gbl...
>I have a maintenance plans set up for a few of my databases where I am
<Snip>
Please ignore. I've found lots of answers by just Googling. Apologies... I
should have done that first.|||In a Full or Bulk logged recovery model you should plan at regular interval a
clearing of the transaction log, because the full backup in this model
does'nt automatically truncate the log.
The Backup LOG backs up the current consistency of the transaction log
starting from the last successful backup log.
Gilberto Zampatti
"Rob Oldfield" wrote:
> I have a maintenance plans set up for a few of my databases where I am
> running a full backup on Sundays, followed by transaction log backups each
> day of the week. As far as I understand it, each transaction log should
> then be backing up transactions made since the last backup (whether that's a
> full or transaction).
> What I'm actually seeing though is that the first transaction log backup
> after each full one is far larger than I'm expecting (in comparison to the
> size of the database anyway).
> For example, one of those is producing backup files of these sizes:
> 06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
> 07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
> 08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
> 09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
> 10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
> 11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
> 12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
> 13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
> 14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
> 15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
> 16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
> 17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
> 18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
> 19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
> 20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
> 21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
> 22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
> I'm think I'm misunderstanding the process somewhere. Could anyone clarify?
>
>

Large transaction log backups

Hi everyone,
I'm currently running sql 2k with sp3 on win2k with sp4.
I have a maintenance job set up to back up the transaction
logs every four hours every day. I also have it checked
to retain the files for only one day. Afer some intense
nightly jobs, the morning tran log backup can be over 4GB
in size. The problem is not backing up the log to file,
it's the clean-up that's supposed to happen afterwards.
After the tran backup is complete, sql server is supposed
to delete the older tran log backup file from the previous
day, but it always fails to delete the older tran log
backup when it's over 4GB in size. It marks the job as
failed, but the backup finishes so it's not that big a
deal. Is this a known bug or is there something else I'm
missing?
I know I could back up the tran logs more often to reduce
size, but I'm doing log shipping using a custom script and
I'd rather not have 20+ logs to script and ship.
LeonLeon,
First, you might be able to get the size of the log backup down. If the size
is caused by reindexing, perhaps DBCC INDEXDEFRAG will help? Of perhaps
bulk-logged recovery mode? Things you can play with...
As for backup files not being deleted. Perhaps below might help? It is my
"canned" answered for that topic:
Below KB might help:
http://support.microsoft.com/default.aspx?scid=kb;en-us;303292&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:1320001c3f6f9$e945c820$a601280a@.phx.gbl...
> Hi everyone,
> I'm currently running sql 2k with sp3 on win2k with sp4.
> I have a maintenance job set up to back up the transaction
> logs every four hours every day. I also have it checked
> to retain the files for only one day. Afer some intense
> nightly jobs, the morning tran log backup can be over 4GB
> in size. The problem is not backing up the log to file,
> it's the clean-up that's supposed to happen afterwards.
> After the tran backup is complete, sql server is supposed
> to delete the older tran log backup file from the previous
> day, but it always fails to delete the older tran log
> backup when it's over 4GB in size. It marks the job as
> failed, but the backup finishes so it's not that big a
> deal. Is this a known bug or is there something else I'm
> missing?
> I know I could back up the tran logs more often to reduce
> size, but I'm doing log shipping using a custom script and
> I'd rather not have 20+ logs to script and ship.
> Leon|||Tibor,
Thanks a bunch for the article and the suggestions from
Bill.
Leon
>--Original Message--
>Leon,
>First, you might be able to get the size of the log
backup down. If the size
>is caused by reindexing, perhaps DBCC INDEXDEFRAG will
help? Of perhaps
>bulk-logged recovery mode? Things you can play with...
>As for backup files not being deleted. Perhaps below
might help? It is my
>"canned" answered for that topic:
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292&Product=sql2k
>
>Also, check out below great troubleshooting suggestions
from Bill H at MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>Thanks,
>Bill Hollinshead
>Microsoft, SQL Server
>
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Leon" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1320001c3f6f9$e945c820$a601280a@.phx.gbl...
>> Hi everyone,
>> I'm currently running sql 2k with sp3 on win2k with sp4.
>> I have a maintenance job set up to back up the
transaction
>> logs every four hours every day. I also have it checked
>> to retain the files for only one day. Afer some intense
>> nightly jobs, the morning tran log backup can be over
4GB
>> in size. The problem is not backing up the log to file,
>> it's the clean-up that's supposed to happen afterwards.
>> After the tran backup is complete, sql server is
supposed
>> to delete the older tran log backup file from the
previous
>> day, but it always fails to delete the older tran log
>> backup when it's over 4GB in size. It marks the job as
>> failed, but the backup finishes so it's not that big a
>> deal. Is this a known bug or is there something else
I'm
>> missing?
>> I know I could back up the tran logs more often to
reduce
>> size, but I'm doing log shipping using a custom script
and
>> I'd rather not have 20+ logs to script and ship.
>> Leon
>
>.
>

Wednesday, March 7, 2012

Large transaction log backups

I have a maintenance plans set up for a few of my databases where I am
running a full backup on Sundays, followed by transaction log backups each
day of the week. As far as I understand it, each transaction log should
then be backing up transactions made since the last backup (whether that's a
full or transaction).
What I'm actually seeing though is that the first transaction log backup
after each full one is far larger than I'm expecting (in comparison to the
size of the database anyway).
For example, one of those is producing backup files of these sizes:
06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
I'm think I'm misunderstanding the process somewhere. Could anyone clarify?"Rob Oldfield" <blah@.blah.com> wrote in message
news:%23mRHK8EnHHA.3520@.TK2MSFTNGP04.phx.gbl...
>I have a maintenance plans set up for a few of my databases where I am
<Snip>
Please ignore. I've found lots of answers by just Googling. Apologies... I
should have done that first.|||In a Full or Bulk logged recovery model you should plan at regular interval
a
clearing of the transaction log, because the full backup in this model
does'nt automatically truncate the log.
The Backup LOG backs up the current consistency of the transaction log
starting from the last successful backup log.
Gilberto Zampatti
"Rob Oldfield" wrote:

> I have a maintenance plans set up for a few of my databases where I am
> running a full backup on Sundays, followed by transaction log backups each
> day of the week. As far as I understand it, each transaction log should
> then be backing up transactions made since the last backup (whether that's
a
> full or transaction).
> What I'm actually seeing though is that the first transaction log backup
> after each full one is far larger than I'm expecting (in comparison to the
> size of the database anyway).
> For example, one of those is producing backup files of these sizes:
> 06/05/2007 02:12 857,955,840 ADM_db_200705060200.BAK
> 07/05/2007 00:12 1,313,012,224 ADM_tlog_200705070000.TRN
> 08/05/2007 00:03 611,840 ADM_tlog_200705080001.TRN
> 09/05/2007 00:03 5,133,824 ADM_tlog_200705090001.TRN
> 10/05/2007 00:02 6,510,080 ADM_tlog_200705100000.TRN
> 11/05/2007 00:03 10,966,528 ADM_tlog_200705110001.TRN
> 12/05/2007 00:02 10,376,704 ADM_tlog_200705120000.TRN
> 13/05/2007 02:20 860,951,040 ADM_db_200705130201.BAK
> 14/05/2007 00:06 428,621,312 ADM_tlog_200705140002.TRN
> 15/05/2007 00:02 9,046,528 ADM_tlog_200705150000.TRN
> 16/05/2007 00:02 5,048,832 ADM_tlog_200705160000.TRN
> 17/05/2007 00:03 13,699,584 ADM_tlog_200705170000.TRN
> 18/05/2007 00:04 92,212,736 ADM_tlog_200705180000.TRN
> 19/05/2007 00:03 32,824,832 ADM_tlog_200705190002.TRN
> 20/05/2007 02:06 864,737,792 ADM_db_200705200202.BAK
> 21/05/2007 00:05 393,709,056 ADM_tlog_200705210002.TRN
> 22/05/2007 00:04 17,836,544 ADM_tlog_200705220003.TRN
> I'm think I'm misunderstanding the process somewhere. Could anyone clarif
y?
>
>

Large transaction log backups

Hi everyone,
I'm currently running sql 2k with sp3 on win2k with sp4.
I have a maintenance job set up to back up the transaction
logs every four hours every day. I also have it checked
to retain the files for only one day. Afer some intense
nightly jobs, the morning tran log backup can be over 4GB
in size. The problem is not backing up the log to file,
it's the clean-up that's supposed to happen afterwards.
After the tran backup is complete, sql server is supposed
to delete the older tran log backup file from the previous
day, but it always fails to delete the older tran log
backup when it's over 4GB in size. It marks the job as
failed, but the backup finishes so it's not that big a
deal. Is this a known bug or is there something else I'm
missing?
I know I could back up the tran logs more often to reduce
size, but I'm doing log shipping using a custom script and
I'd rather not have 20+ logs to script and ship.
LeonLeon,
First, you might be able to get the size of the log backup down. If the size
is caused by reindexing, perhaps DBCC INDEXDEFRAG will help? Of perhaps
bulk-logged recovery mode? Things you can play with...
As for backup files not being deleted. Perhaps below might help? It is my
"canned" answered for that topic:
Below KB might help:
http://support.microsoft.com/defaul...2&Product=sql2k
Also, check out below great troubleshooting suggestions from Bill H at MS:
-- Log files don't delete --
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:1320001c3f6f9$e945c820$a601280a@.phx
.gbl...
> Hi everyone,
> I'm currently running sql 2k with sp3 on win2k with sp4.
> I have a maintenance job set up to back up the transaction
> logs every four hours every day. I also have it checked
> to retain the files for only one day. Afer some intense
> nightly jobs, the morning tran log backup can be over 4GB
> in size. The problem is not backing up the log to file,
> it's the clean-up that's supposed to happen afterwards.
> After the tran backup is complete, sql server is supposed
> to delete the older tran log backup file from the previous
> day, but it always fails to delete the older tran log
> backup when it's over 4GB in size. It marks the job as
> failed, but the backup finishes so it's not that big a
> deal. Is this a known bug or is there something else I'm
> missing?
> I know I could back up the tran logs more often to reduce
> size, but I'm doing log shipping using a custom script and
> I'd rather not have 20+ logs to script and ship.
> Leon|||Tibor,
Thanks a bunch for the article and the suggestions from
Bill.
Leon

>--Original Message--
>Leon,
>First, you might be able to get the size of the log
backup down. If the size
>is caused by reindexing, perhaps DBCC INDEXDEFRAG will
help? Of perhaps
>bulk-logged recovery mode? Things you can play with...
>As for backup files not being deleted. Perhaps below
might help? It is my
>"canned" answered for that topic:
>Below KB might help:
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;303292&Product=sql2k
>
>Also, check out below great troubleshooting suggestions
from Bill H at MS:
>
>-- Log files don't delete --
>This is likely to be either a permissions problem or a
sharing violation
>problem. The maintenance plan is run as a job, and jobs
are run by the
>SQLServerAgent service.
>Permissions:
>1. Determine the startup account for the SQLServerAgent
service
>(Start|Programs|Administrative
tools|Services|SQLServerAgent|Startup). This
>account is the security context for jobs, and thus the
maintenance plan.
>2. If SQLServerAgent is started using LocalSystem (as
opposed to a domain
>account) then skip step 3.
>3. On that box, log onto NT as that account. Using
Explorer, attempt to
>delete an expired backup. If that succeeds then go to
Sharing Violation
>section.
>4. Log onto NT with an account that is an administrator
and use Explorer to
>look at the Properties|Security of the folder (where the
backups reside)
>and ensure the SQLServerAgent startup account has Full
Control. If the
>SQLServerAgent startup account is LocalSystem, then the
account to consider
>is SYSTEM.
>5. In NT, if an account is a member of an NT group, and
if that group has
>Access is Denied, then that account will have Access is
Denied, even if
>that account is also a member of the Administrators
group. Thus you may
>need to check group permissions (if the Startup Account
is a member of a
>group).
>6. Keep in mind that permissions (by default) are
inherited from a parent
>folder. Thus, if the backups are stored in C:\bak, and if
someone had
>denied permission to the SQLServerAgent startup account
for C:\, then
>C:\bak will inherit access is denied.
>Sharing violation:
>This is likely to be rooted in a timing issue, with the
most likely cause
>being another scheduled process (such as NT Backup or
Anti-Virus software)
>having the backup file open at the time when the
SQLServerAgent (i.e., the
>maintenance plan job) tried to delete it.
>1. Download filemon and handle from www.sysinternals.com.
>2. I am not sure whether filemon can be scheduled, or you
might be able to
>use NT scheduling services to start filemon just before
the maintenance
>plan job is started, but the filemon log can become very
large, so it would
>be best to start it some short time before the
maintenance plan starts.
>3. Inspect the filemon log for another process that has
that backup file
>open (if your lucky enough to have started filemon before
this other
>process grabs the backup folder), and inspect the log for
the results when
>the SQLServerAgent agent attempts to open that same file.
>4. Schedule the job or that other process to do their
work at different
>times.
>5. You can use the handle utility if you are around at
the time when the
>job is scheduled to run.
>If the backup files are going to a \\share or a mapped
drive (as opposed to
>local drive), then you will need to modify the above
(with respect to where
>the tests and utilities are run).
>Finally, inspection of the maintenance plan's history
report might be
>useful.
>Thanks,
>Bill Hollinshead
>Microsoft, SQL Server
>
>
>--
>Tibor Karaszi, SQL Server MVP
>Archive at:
>http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Leon" <anonymous@.discussions.microsoft.com> wrote in
message
> news:1320001c3f6f9$e945c820$a601280a@.phx
.gbl...
transaction
4GB
supposed
previous
I'm
reduce
and
>
>.
>

Large Tran log backups

Can someone explain me this? I am doing some research and testing and
cannot understand the behaviour I am noticing.
I have a database data file = 1.6 gig and I have the tran log for
testing purposes set to 101.06MB set to auto growth by 10% unrestricted
file size. I also have an alert set up to take the transaction log back
up when it is over 70% full.
This morning I noticed that tran log back up job was fired twice
yesterday by the alert and the size of the tran log back ups were 3.42G
and 3.48G. When I look at the size of the tran log file it is 101.06MB.
I do not have the Auto_shrink option turned on.
Can someone explain how is it possible to have such a large tran log
backup file when the file itself is only 100MB?
Thanksshub wrote:
> Can someone explain me this? I am doing some research and testing and
> cannot understand the behaviour I am noticing.
> I have a database data file = 1.6 gig and I have the tran log for
> testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> file size. I also have an alert set up to take the transaction log back
> up when it is over 70% full.
> This morning I noticed that tran log back up job was fired twice
> yesterday by the alert and the size of the tran log back ups were 3.42G
> and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> I do not have the Auto_shrink option turned on.
> Can someone explain how is it possible to have such a large tran log
> backup file when the file itself is only 100MB?
> Thanks
>
Are you writing each backup to a new file, or appending to the same file
each time?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I am writing them to a new file. But I am still not understanding how
the backup file of the tran log is larger than the actual size of the
tan log file.
Thanks
Tracy McKibben wrote:
> shub wrote:
> > Can someone explain me this? I am doing some research and testing and
> > cannot understand the behaviour I am noticing.
> >
> > I have a database data file = 1.6 gig and I have the tran log for
> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> > file size. I also have an alert set up to take the transaction log back
> > up when it is over 70% full.
> >
> > This morning I noticed that tran log back up job was fired twice
> > yesterday by the alert and the size of the tran log back ups were 3.42G
> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> > I do not have the Auto_shrink option turned on.
> >
> > Can someone explain how is it possible to have such a large tran log
> > backup file when the file itself is only 100MB?
> >
> > Thanks
> >
> Are you writing each backup to a new file, or appending to the same file
> each time?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||What do you get back when you run these:
RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
Roy Harvey
Beacon Falls, CT
On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
>I am writing them to a new file. But I am still not understanding how
>the backup file of the tran log is larger than the actual size of the
>tan log file.
>Thanks
>Tracy McKibben wrote:
>> shub wrote:
>> > Can someone explain me this? I am doing some research and testing and
>> > cannot understand the behaviour I am noticing.
>> >
>> > I have a database data file = 1.6 gig and I have the tran log for
>> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
>> > file size. I also have an alert set up to take the transaction log back
>> > up when it is over 70% full.
>> >
>> > This morning I noticed that tran log back up job was fired twice
>> > yesterday by the alert and the size of the tran log back ups were 3.42G
>> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
>> > I do not have the Auto_shrink option turned on.
>> >
>> > Can someone explain how is it possible to have such a large tran log
>> > backup file when the file itself is only 100MB?
>> >
>> > Thanks
>> >
>> Are you writing each backup to a new file, or appending to the same file
>> each time?
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||When I ran the first command here is what I got ....
Data file size = 1751777280
Tran log size = 5437521920
Please help me understand how could the tran log get that big when I
have an alert setup and also how is it that it is only 100MB now. I do
not have the auto shrink option turned on.
On the second command, here is what I got, providing you the info that
you would care
BackupSize FirstLsn LastLsn
CheckpointLsn
3754968576 546000002908400001 661000001920700001
648000000145600034
And Backuptype and Devicetype were both = 2.
Thanks
Roy Harvey wrote:
> What do you get back when you run these:
> RESTORE FILELISTONLY FROM DISK='path & name of tran log backup file'
> RESTORE HEADERONLY FROM DISK='path & name of tran log backup file'
> Roy Harvey
> Beacon Falls, CT
> On 4 Aug 2006 09:30:35 -0700, "shub" <shubtech@.gmail.com> wrote:
> >I am writing them to a new file. But I am still not understanding how
> >the backup file of the tran log is larger than the actual size of the
> >tan log file.
> >Thanks
> >Tracy McKibben wrote:
> >> shub wrote:
> >> > Can someone explain me this? I am doing some research and testing and
> >> > cannot understand the behaviour I am noticing.
> >> >
> >> > I have a database data file = 1.6 gig and I have the tran log for
> >> > testing purposes set to 101.06MB set to auto growth by 10% unrestricted
> >> > file size. I also have an alert set up to take the transaction log back
> >> > up when it is over 70% full.
> >> >
> >> > This morning I noticed that tran log back up job was fired twice
> >> > yesterday by the alert and the size of the tran log back ups were 3.42G
> >> > and 3.48G. When I look at the size of the tran log file it is 101.06MB.
> >> > I do not have the Auto_shrink option turned on.
> >> >
> >> > Can someone explain how is it possible to have such a large tran log
> >> > backup file when the file itself is only 100MB?
> >> >
> >> > Thanks
> >> >
> >>
> >> Are you writing each backup to a new file, or appending to the same file
> >> each time?
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I really can't explain it any more than you can. I can only assume
that Something Is Not As It Appears. The question is, what?
Whatever it is, expect more questions (as you have had from Tracy and
me) before you get any answers.
I am not suggesting any of the following, but they all have to be
tested and dismissed. I am sure I am missing some others.
- The log is really not 110MB, but multiple GB.
- The file is from backing up some other database.
- The file is from backup up this database at some time in the past.
I hope some else has more specific advice that this!
Roy
On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>When I ran the first command here is what I got ....
>Data file size = 1751777280
>Tran log size = 5437521920
>Please help me understand how could the tran log get that big when I
>have an alert setup and also how is it that it is only 100MB now. I do
>not have the auto shrink option turned on.
>On the second command, here is what I got, providing you the info that
>you would care
>BackupSize FirstLsn LastLsn
> CheckpointLsn
>3754968576 546000002908400001 661000001920700001
>648000000145600034
>And Backuptype and Devicetype were both = 2.
>Thanks
>|||Here is a question for you, Is there a way to find out if the
transaction log file size was shrunk manually after the transaction
log backups?
Roy Harvey wrote:
> I really can't explain it any more than you can. I can only assume
> that Something Is Not As It Appears. The question is, what?
> Whatever it is, expect more questions (as you have had from Tracy and
> me) before you get any answers.
> I am not suggesting any of the following, but they all have to be
> tested and dismissed. I am sure I am missing some others.
> - The log is really not 110MB, but multiple GB.
> - The file is from backing up some other database.
> - The file is from backup up this database at some time in the past.
> I hope some else has more specific advice that this!
> Roy
> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
> >When I ran the first command here is what I got ....
> >Data file size = 1751777280
> >Tran log size = 5437521920
> >
> >Please help me understand how could the tran log get that big when I
> >have an alert setup and also how is it that it is only 100MB now. I do
> >not have the auto shrink option turned on.
> >
> >On the second command, here is what I got, providing you the info that
> >you would care
> >BackupSize FirstLsn LastLsn
> > CheckpointLsn
> >3754968576 546000002908400001 661000001920700001
> >648000000145600034
> >
> >And Backuptype and Devicetype were both = 2.
> >
> >Thanks
> >|||Not that I know of, but there is a LOT I don't know. I do know that
shrinking the transaction log doesn't usually take effect immediately,
as it only happens after the logging reaches the end and has to go
back to the beginning.
Roy
On 4 Aug 2006 13:26:46 -0700, "shub" <shubtech@.gmail.com> wrote:
>Here is a question for you, Is there a way to find out if the
>transaction log file size was shrunk manually after the transaction
>log backups?
>Roy Harvey wrote:
>> I really can't explain it any more than you can. I can only assume
>> that Something Is Not As It Appears. The question is, what?
>> Whatever it is, expect more questions (as you have had from Tracy and
>> me) before you get any answers.
>> I am not suggesting any of the following, but they all have to be
>> tested and dismissed. I am sure I am missing some others.
>> - The log is really not 110MB, but multiple GB.
>> - The file is from backing up some other database.
>> - The file is from backup up this database at some time in the past.
>> I hope some else has more specific advice that this!
>> Roy
>> On 4 Aug 2006 11:14:03 -0700, "shub" <shubtech@.gmail.com> wrote:
>> >When I ran the first command here is what I got ....
>> >Data file size = 1751777280
>> >Tran log size = 5437521920
>> >
>> >Please help me understand how could the tran log get that big when I
>> >have an alert setup and also how is it that it is only 100MB now. I do
>> >not have the auto shrink option turned on.
>> >
>> >On the second command, here is what I got, providing you the info that
>> >you would care
>> >BackupSize FirstLsn LastLsn
>> > CheckpointLsn
>> >3754968576 546000002908400001 661000001920700001
>> >648000000145600034
>> >
>> >And Backuptype and Devicetype were both = 2.
>> >
>> >Thanks
>> >|||Another thing that needs checking: is the log on multiple files.
Roy

Friday, March 9, 2012

Large Transaction Log Files - SQL 2000

Large Transaction Log Files - SQL 2000

Large Transaction Log Files - SQL 2000

Large transaction log backups

Large transaction log backups

Wednesday, March 7, 2012

Large transaction log backups

Large transaction log backups

Large Tran log backups

Large Tran log backups

sql large inserts

Blog Archive

About Me