sql large inserts: log

Showing posts with label log. Show all posts

Monday, March 12, 2012

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server an
d
> by which user. Is it possible to find. (I think there is some log for this
)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. SundarNo, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:
> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Executed SP (Log file)

Hi
I want to know what is the last stored procedure executed in the server and
by which user. Is it possible to find. (I think there is some log for this)
anybody can help me.
Advanced Thanks
B. Sundar
No, there isn't any log for it. You probably need to run Profiler to get
that kind of information.
"Sundar Bramanayagam" wrote:

> Hi
> I want to know what is the last stored procedure executed in the server and
> by which user. Is it possible to find. (I think there is some log for this)
> anybody can help me.
> Advanced Thanks
> B. Sundar

Last Entry in a Log

(I don't post here often, so in case I'm violating long-standing taboos
of this newsgroup, I apologize in advance for calling a relation a
table, using nulls, and other ignorant, destructive, and comtemptible
terminology.)

I have a table that's keeping a sort of running log of different types
of changes to pieces of data. The table has a foreign key of the data
being changed, the foreign key for the type of change occuring, some
information about the change in a couple more columns, and a timestamp
for each entry. So it's:

dataID
eventID
eventInfo
timestamp

What I'd like to do, if at all possible, is a single SQL query that,
given a dataID, returns the most recent eventInfo and timestamp for
each eventID. Is this possible?

Many thanks.
-Eric(eric404@.gmail.com) writes:
> I have a table that's keeping a sort of running log of different types
> of changes to pieces of data. The table has a foreign key of the data
> being changed, the foreign key for the type of change occuring, some
> information about the change in a couple more columns, and a timestamp
> for each entry. So it's:
> dataID
> eventID
> eventInfo
> timestamp
> What I'd like to do, if at all possible, is a single SQL query that,
> given a dataID, returns the most recent eventInfo and timestamp for
> each eventID. Is this possible?

SELECT a.eventID, a.eventInfo, a.timestamp
FROM tbl a
JOIN (SELECT eventID, timestamp = MAX(timestamp)
FROM tbl b
WHERE dataID = @.dataid)
GRUOP BY eventID) AS b ON a.eventID = b.eventID
AND a.timestamp = b.timestamp
WHERE a.dataID = @.dataid

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Is your "timestamp" column actually a DATETIME or SMALLDATETIME column? If
so, try the following query. Don't use the name "timestamp", which refers to
a different datatype in SQL Server. The TIMESTAMP datatype has nothing to do
with date and time so if your column is in fact a TIMESTAMP then you ought
to add a DATETIME column instead.

SELECT eventid, eventinfo, timestamp
FROM YourTable AS T
WHERE timestamp =
(SELECT MAX(timestamp)
FROM YourTable
WHERE eventid = T. eventid
AND dataid = @.dataid)
AND dataid = @.dataid ;

--
David Portas
SQL Server MVP
--|||Thanks for the help, it works great.

Last Checkpoint

Hi:
I am trying to find when last checkpoint was done by sql server. I don't
see this in sql server log. Is there any way to find out when last
checkpoint was done or frequency of checkpoints?
Thanks
I don't know about the last one but you can certainly use perfmon to view
when checkpoints occur.
Andrew J. Kelly SQL MVP
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:84109BB5-9167-49D9-87EF-8DC30FB08105@.microsoft.com...
> Hi:
> I am trying to find when last checkpoint was done by sql server. I don't
> see this in sql server log. Is there any way to find out when last
> checkpoint was done or frequency of checkpoints?
> Thanks

Last Checkpoint

Hi:
I am trying to find when last checkpoint was done by sql server. I don't
see this in sql server log. Is there any way to find out when last
checkpoint was done or frequency of checkpoints?
ThanksI don't know about the last one but you can certainly use perfmon to view
when checkpoints occur.
Andrew J. Kelly SQL MVP
"Sal" <Sal@.discussions.microsoft.com> wrote in message
news:84109BB5-9167-49D9-87EF-8DC30FB08105@.microsoft.com...
> Hi:
> I am trying to find when last checkpoint was done by sql server. I don't
> see this in sql server log. Is there any way to find out when last
> checkpoint was done or frequency of checkpoints?
> Thanks

Friday, March 9, 2012

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may wan
t
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty spac
e
increase and the active space decrease.
"Brandon" wrote:

> I have some databases that are keeping log files (.ldf) that are 3 times t
he
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/defaul...kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/defaul...kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

Large Transaction Logs

I have some databases that are keeping log files (.ldf) that are 3 times the
size of the db and very large. What is the best way to shrink the size of
these .ldf files? I have made backups of the db's and transaction logs
using SQL database maintenance (as some have suggested), but this does not
shrink the .ldf files. What else can I do?
--
Brandon
Presentations Direct - "Document Finishing Solutions"
http://www.presentationsdirect.comBrandon,
Backing up a transaction log will clear it but not shrink it. Use DBCC
SHRINKFILE on the t-log. Tibor has some more info here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
HTH
Jerry
"Brandon" <bsmith@.presentationsdirect.nospam.com> wrote in message
news:eO5MHFOgGHA.1320@.TK2MSFTNGP04.phx.gbl...
>I have some databases that are keeping log files (.ldf) that are 3 times
>the size of the db and very large. What is the best way to shrink the size
>of these .ldf files? I have made backups of the db's and transaction logs
>using SQL database maintenance (as some have suggested), but this does not
>shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>|||You might want to investigate what is or has inflated them.
Reindexing, bulk loads of data, replication, etc, can all grow your
logs and there are ways of addressing each.|||Logs can get "locked" if something is hitting them. Example: I have an
Indexdefrag job that fills up a log. I have to stop the job before I can
shrink it. (I am using Simple recovery mode on this database.) You may want
to use View/Textpad to see if the log is filled with active or not. If
you're in full mode, after you backup the log, you should see the empty space
increase and the active space decrease.
"Brandon" wrote:
> I have some databases that are keeping log files (.ldf) that are 3 times the
> size of the db and very large. What is the best way to shrink the size of
> these .ldf files? I have made backups of the db's and transaction logs
> using SQL database maintenance (as some have suggested), but this does not
> shrink the .ldf files. What else can I do?
> --
> Brandon
> Presentations Direct - "Document Finishing Solutions"
> http://www.presentationsdirect.com
>
>|||Specific to Transaction logs, here are a few articles you might like to
look at:
INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;EN-US;272318
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650
INF: Considerations for Autogrow and Autoshrink Configuration
http://support.microsoft.com/default.aspx?scid=kb;EN-US;315512
INF: Incomplete Transaction May Hold Large Number of Locks and Cause
Blocking
http://support.microsoft.com/default.aspx?scid=kb;EN-US;295108
Taking a backup of the log will not shrink the size of log , also its
important to consider the recovery model of the database. Consider using
DBCC SHRINKFILE.
Hope This Helps
Vishal Gandhi

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 causing write performance hit

Hi,
I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance' We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).
We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.
After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.
Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.
Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!
Thanks,
ChrisThe log could be a problem. Obviously all the uncommitted transactions are
sitting in there, and writing in sequence could be tying up things.
Thinking along those lines, what you need to do is think of a good recovery
model or consider moving the transaction log to a separate disk where there
is nothing on it. I wish I could cite a source better than Transcender
study questions to say that the recommendation I've seen is to put a
transaction log on a mirrored set, but you can find some discussions doing
google searches.
A good recovery model depends on whether you want up to the minute restores
or you can stand to lose everything from the last full /differential backup.
The frequent transaction log backups or truncations will keep the size down
to a manageable one.
If you need up-to-the-minute restores, then consider setting your database
to the full recovery model, and schedule frequent transaction log backups to
disk. Then you can restore additional logs on top of the last full database
restore.
If you can stand to lose everything from the last full backup, then consider
using the simple recovery model (select into/bulkcopy true, trunc log on
chkpt true), which will automatically empty your transaction log at every
checkpoint the database does. Then make sure your full backups and
differential backups are happening at appropriate intervals. Good luck.
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Christian Bouche" <cbouche@.gohealthcast.com> wrote in message
news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
> Hi,
> I really would appreciate any help on this. Can a very
> large transaction log have a negative impact on write
> performance' We were having a problem with VERY slow
> writes (30 seconds each) to a database. Reads were very
> fast (0 ms). The app is running on a Windows 2000/SQL
> 2000 Active/Passive cluster. Each server has 2 G of ram
> and disk space is absolutely not an issue. The only other
> applications that run on the server are Mcaffee (but this
> is disabled from the data/log directories).
> We noticed that the log file for the affected database was
> EXTREMELY large (3G). TONS of leftover disk space for the
> log. There's not a whole lot of updates that occur in
> this database. The database was in Full recovery mode.
> We usually rely on nightly backups due to the sparse
> update/inserts/deletes.
> After encountering this problem, I failed over the node
> (restarted the SQL services, and writes became fast again,
> but this was only temporary (lasted a couple hours), which
> led me to believe that it could be a memory issue.
> Anyway, after that, I made the following changes: 1.
> Backed up the database, then the log, shrank the log
> (now .99MB) and temporarily put set the recovery mode to
> simple. 2. Reduced the max SQL server memory setting to 1
> G.
> Writes are again very fast. I have seen no problems for a
> day and a half under a normal load. It would really help
> to know if the large tlog could have actually been the
> problem? Or the if the memory settings may have done
> the trick and why? Then we can rest assured that the
> issue will not crop
> up again later!
> Thanks,
> Chris
>|||Thank you so much for your help.
Chris
>--Original Message--
>The log could be a problem. Obviously all the
uncommitted transactions are
>sitting in there, and writing in sequence could be tying
up things.
>Thinking along those lines, what you need to do is think
of a good recovery
>model or consider moving the transaction log to a
separate disk where there
>is nothing on it. I wish I could cite a source better
than Transcender
>study questions to say that the recommendation I've seen
is to put a
>transaction log on a mirrored set, but you can find some
discussions doing
>google searches.
>A good recovery model depends on whether you want up to
the minute restores
>or you can stand to lose everything from the last
full /differential backup.
>The frequent transaction log backups or truncations will
keep the size down
>to a manageable one.
>If you need up-to-the-minute restores, then consider
setting your database
>to the full recovery model, and schedule frequent
transaction log backups to
>disk. Then you can restore additional logs on top of the
last full database
>restore.
>If you can stand to lose everything from the last full
backup, then consider
>using the simple recovery model (select into/bulkcopy
true, trunc log on
>chkpt true), which will automatically empty your
transaction log at every
>checkpoint the database does. Then make sure your full
backups and
>differential backups are happening at appropriate
intervals. Good luck.
>--
> ****************************************
******************
*********
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
>Be suspicious of every email attachment, I will never send
>or post anything other than the text of a http:// link nor
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
> ****************************************
******************
*********
>"Christian Bouche" <cbouche@.gohealthcast.com> wrote in
message
>news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
other
this
was
the
again,
which
to 1
for a
help
>
>.
>

Large transaction log causing write performance hit

Hi,
I really would appreciate any help on this. Can a very
large transaction log have a negative impact on write
performance' We were having a problem with VERY slow
writes (30 seconds each) to a database. Reads were very
fast (0 ms). The app is running on a Windows 2000/SQL
2000 Active/Passive cluster. Each server has 2 G of ram
and disk space is absolutely not an issue. The only other
applications that run on the server are Mcaffee (but this
is disabled from the data/log directories).
We noticed that the log file for the affected database was
EXTREMELY large (3G). TONS of leftover disk space for the
log. There's not a whole lot of updates that occur in
this database. The database was in Full recovery mode.
We usually rely on nightly backups due to the sparse
update/inserts/deletes.
After encountering this problem, I failed over the node
(restarted the SQL services, and writes became fast again,
but this was only temporary (lasted a couple hours), which
led me to believe that it could be a memory issue.
Anyway, after that, I made the following changes: 1.
Backed up the database, then the log, shrank the log
(now .99MB) and temporarily put set the recovery mode to
simple. 2. Reduced the max SQL server memory setting to 1
G.
Writes are again very fast. I have seen no problems for a
day and a half under a normal load. It would really help
to know if the large tlog could have actually been the
problem? Or the if the memory settings may have done
the trick and why? Then we can rest assured that the
issue will not crop
up again later!
Thanks,
ChrisThe log could be a problem. Obviously all the uncommitted transactions are
sitting in there, and writing in sequence could be tying up things.
Thinking along those lines, what you need to do is think of a good recovery
model or consider moving the transaction log to a separate disk where there
is nothing on it. I wish I could cite a source better than Transcender
study questions to say that the recommendation I've seen is to put a
transaction log on a mirrored set, but you can find some discussions doing
google searches.
A good recovery model depends on whether you want up to the minute restores
or you can stand to lose everything from the last full /differential backup.
The frequent transaction log backups or truncations will keep the size down
to a manageable one.
If you need up-to-the-minute restores, then consider setting your database
to the full recovery model, and schedule frequent transaction log backups to
disk. Then you can restore additional logs on top of the last full database
restore.
If you can stand to lose everything from the last full backup, then consider
using the simple recovery model (select into/bulkcopy true, trunc log on
chkpt true), which will automatically empty your transaction log at every
checkpoint the database does. Then make sure your full backups and
differential backups are happening at appropriate intervals. Good luck.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Christian Bouche" <cbouche@.gohealthcast.com> wrote in message
news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
> Hi,
> I really would appreciate any help on this. Can a very
> large transaction log have a negative impact on write
> performance' We were having a problem with VERY slow
> writes (30 seconds each) to a database. Reads were very
> fast (0 ms). The app is running on a Windows 2000/SQL
> 2000 Active/Passive cluster. Each server has 2 G of ram
> and disk space is absolutely not an issue. The only other
> applications that run on the server are Mcaffee (but this
> is disabled from the data/log directories).
> We noticed that the log file for the affected database was
> EXTREMELY large (3G). TONS of leftover disk space for the
> log. There's not a whole lot of updates that occur in
> this database. The database was in Full recovery mode.
> We usually rely on nightly backups due to the sparse
> update/inserts/deletes.
> After encountering this problem, I failed over the node
> (restarted the SQL services, and writes became fast again,
> but this was only temporary (lasted a couple hours), which
> led me to believe that it could be a memory issue.
> Anyway, after that, I made the following changes: 1.
> Backed up the database, then the log, shrank the log
> (now .99MB) and temporarily put set the recovery mode to
> simple. 2. Reduced the max SQL server memory setting to 1
> G.
> Writes are again very fast. I have seen no problems for a
> day and a half under a normal load. It would really help
> to know if the large tlog could have actually been the
> problem? Or the if the memory settings may have done
> the trick and why? Then we can rest assured that the
> issue will not crop
> up again later!
> Thanks,
> Chris
>|||Thank you so much for your help.
Chris
>--Original Message--
>The log could be a problem. Obviously all the
uncommitted transactions are
>sitting in there, and writing in sequence could be tying
up things.
>Thinking along those lines, what you need to do is think
of a good recovery
>model or consider moving the transaction log to a
separate disk where there
>is nothing on it. I wish I could cite a source better
than Transcender
>study questions to say that the recommendation I've seen
is to put a
>transaction log on a mirrored set, but you can find some
discussions doing
>google searches.
>A good recovery model depends on whether you want up to
the minute restores
>or you can stand to lose everything from the last
full /differential backup.
>The frequent transaction log backups or truncations will
keep the size down
>to a manageable one.
>If you need up-to-the-minute restores, then consider
setting your database
>to the full recovery model, and schedule frequent
transaction log backups to
>disk. Then you can restore additional logs on top of the
last full database
>restore.
>If you can stand to lose everything from the last full
backup, then consider
>using the simple recovery model (select into/bulkcopy
true, trunc log on
>chkpt true), which will automatically empty your
transaction log at every
>checkpoint the database does. Then make sure your full
backups and
>differential backups are happening at appropriate
intervals. Good luck.
>--
>**********************************************************
*********
>Andy S.
>MCSE NT/2000, MCDBA SQL 7/2000
>andymcdba1@.NOMORESPAM.yahoo.com
>Please remove NOMORESPAM before replying.
>Always keep your antivirus and Microsoft software
>up to date with the latest definitions and product
updates.
>Be suspicious of every email attachment, I will never send
>or post anything other than the text of a http:// link nor
>post the link directly to a file for downloading.
>This posting is provided "as is" with no warranties
>and confers no rights.
>**********************************************************
*********
>"Christian Bouche" <cbouche@.gohealthcast.com> wrote in
message
>news:2a1801c3fc91$500f3fe0$a101280a@.phx.gbl...
>> Hi,
>> I really would appreciate any help on this. Can a very
>> large transaction log have a negative impact on write
>> performance' We were having a problem with VERY slow
>> writes (30 seconds each) to a database. Reads were very
>> fast (0 ms). The app is running on a Windows 2000/SQL
>> 2000 Active/Passive cluster. Each server has 2 G of ram
>> and disk space is absolutely not an issue. The only
other
>> applications that run on the server are Mcaffee (but
this
>> is disabled from the data/log directories).
>> We noticed that the log file for the affected database
was
>> EXTREMELY large (3G). TONS of leftover disk space for
the
>> log. There's not a whole lot of updates that occur in
>> this database. The database was in Full recovery mode.
>> We usually rely on nightly backups due to the sparse
>> update/inserts/deletes.
>> After encountering this problem, I failed over the node
>> (restarted the SQL services, and writes became fast
again,
>> but this was only temporary (lasted a couple hours),
which
>> led me to believe that it could be a memory issue.
>> Anyway, after that, I made the following changes: 1.
>> Backed up the database, then the log, shrank the log
>> (now .99MB) and temporarily put set the recovery mode to
>> simple. 2. Reduced the max SQL server memory setting
to 1
>> G.
>> Writes are again very fast. I have seen no problems
for a
>> day and a half under a normal load. It would really
help
>> to know if the large tlog could have actually been the
>> problem? Or the if the memory settings may have done
>> the trick and why? Then we can rest assured that the
>> issue will not crop
>> up again later!
>> Thanks,
>> Chris
>
>.
>

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?
>
>

Monday, March 12, 2012

Last Executed SP (Log file)

Last Executed SP (Log file)

Last Executed SP (Log file)

Last Entry in a Log

Last Checkpoint

Last Checkpoint

Friday, March 9, 2012

Large Transaction Logs

Large Transaction Logs

Large Transaction Log Files - SQL 2000

Large Transaction Log Files - SQL 2000

Large Transaction Log Files - SQL 2000

Large transaction log causing write performance hit

Large transaction log causing write performance hit

Large transaction log backups

Large transaction log backups

Wednesday, March 7, 2012

Large transaction log backups

sql large inserts

Blog Archive

About Me