Transaction log problems
From: Robert (Robert_at_discussions.microsoft.com)
Date: 09/29/04
- Next message: CurtM: "Referencing tables on a remote server"
- Previous message: Aaron [SQL Server MVP]: "Re: Autoshrink and Autogrow"
- Next in thread: Peter The Spate: "Transaction log problems"
- Reply: Peter The Spate: "Transaction log problems"
- Reply: Keith Kratochvil: "Re: Transaction log problems"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 29 Sep 2004 08:35:29 -0700
We are using SQL Server 2000 on Windows Server 2003 Standard Edition, all
updates applied.
We use this server to host schema databases for a third party business
intelligence platform.
One of the databases has recently begun showing large growth of the
transaction log and it is causing us some problems. NOTHING has changed
which would cause this problem. The data loads are consistent and have not
been changed at all.
Because the database is used by the third party software, one of the edicts
is that the database must be set to FULL recovery mode so I can't change it
to SIMPLE. The database is set to autogrow. The transaction log is also set
to autogrow and restricted to 1 GB. I do NOT have autoshrink turned on
because so many of the MVP's say it causes too much overhead and
fragmentation.
We used the database maintenance wizard to create a backup plan that backed
up the database and the transaction log once daily--and that had worked
successfully up until 2 weeks ago. At that point the transaction log began
filling up rapidly forcing us to use the command BACKUP LOG <db> WITH
TRUNCATE_ONLY and subsequently command DBCC SHRINKFILE(<db_log>, 20) in order
for the backup to run successfully. Sometimes, this has to be done several
times per day.
So, today, I changed the database maintenance plan to backup the transaction
log every hour thinking that it would automatically truncate and shrink the
transaction log file and I would not have to monitor it so closely. It runs
successfully but immediately after it finishes, I check the size of the file
and it has grown(!) and not shrunk at all.
I have run DBCC OPENTRAN and no open transactions are reported.
I have run DBCC LOGFILE and received 167 rows of data that I do not
understand.
sp_helpfile shows only one data file and one log file.
I want this to stop and go back to the way it was 2 weeks ago when
everything worked and I didn't have to monitor it all day long.
I should mention that I have no training in SQL SERVER just what I have
learned as I go along. And there will be no training forthcoming. I am
familiar with the term stored procedure and think that I could write one but
I have no idea WHERE to store it or how to invoke it and/or schedule it. If
I did, I would be happy to schedule the TRUNCATE+SHRINK commands to run every
5 minutes.
Can anyone shed some light on this situation for me or point me toward a
source for some information? The Books On Line just really suck IMHO and I
have difficulty learning anything from them. I have already searched thru
the other posts concerning the transaction logs and viewed the web sites that
people recommended but they just don't make sense to me (they assume that the
DBA's are fully Microsoft certified--not the case here). Can you sense the
frustration level?
Any information and assistance is greatly appreciated.
Regards,
Robert
- Next message: CurtM: "Referencing tables on a remote server"
- Previous message: Aaron [SQL Server MVP]: "Re: Autoshrink and Autogrow"
- Next in thread: Peter The Spate: "Transaction log problems"
- Reply: Peter The Spate: "Transaction log problems"
- Reply: Keith Kratochvil: "Re: Transaction log problems"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
