HI,
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
> Need your help ! thx a lot!
> Kennethsql
Showing posts with label growth. Show all posts
Showing posts with label growth. Show all posts
Tuesday, March 27, 2012
Do I need to use "dbcc shrinkfile" command to shrink the transaction log?
HI,
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
quote:
> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
quote:
> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
quote:
> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
quote:
> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
quote:
> Need your help ! thx a lot!
> Kenneth
Saturday, February 25, 2012
Distribution Database Log File Growth
SQL Server 2000 | Transactional Replication
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Any thoughts?
Jeff
I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.
Distribution Database Log File Growth
SQL Server 2000 | Transactional Replication
Suspected Problem: Distribution Database Transaction Log Not Checkpointing
I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.
I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.
SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.
Any thoughts?
Jeff
I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.
Distribution Data File Growth
Recently rebuilt Windows 2003 OS, SQL Server 2000 sp3, 3 publishers of
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
Michelle,
have a look at the msrepl_commands table and see if this is the cause of the
large size. If it is, it could be that you have a subscriber who hasn't
synchronized in a while, or the distribution cleanup agent is disabled, or
you have an anonymous subscriber, so the commands remain until the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||Results from msrepl_commands table:
publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454
Ran distribution clean up agent job (has been running successfully, every 10
minutes):
publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467
The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).
I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.
Thanks,
Michelle
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
various shapes and sizes.
I am almost certain that I re-created the distribution database using the
file properties before everything was moved to the new OS. I ended up having
to drop and recreate replication because I didn't back up the publishers
with the keep_replication switch. So, I dropped the distribution database
and created a new one. But, for some reason the data file seems to be
growing and growing. This behavior is unexpected. How can I determine the
cause of this growth? The subscribers are certainly receiving the
transactions. We have other sql servers with multiple publishers and a
single distribution database but the data file for it stays small.
Michelle,
have a look at the msrepl_commands table and see if this is the cause of the
large size. If it is, it could be that you have a subscriber who hasn't
synchronized in a while, or the distribution cleanup agent is disabled, or
you have an anonymous subscriber, so the commands remain until the retention
period is reached.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||run a dbcc opentran in your distributon database to see what happens.
The keep replication switch was designed to be enable disaster recovery
of your transactional replication solution. You can use it to restore
publications on a server but only so you can script out the
publications or view them. Don't expect to use the keep_replication
swithc on a new server and have everything work. You need to restore
the distribution, master, and msdb databases as well.
|||Thanks. I had all of the pieces (msdb, distribution, master, etc.). All
databases were restored but since I didn't back up the publishers with the
keep_replication switch, I couldn't get replication 'kicked off' again. I
re-marked them after the restore for replication and all of the jobs were
succeeding. However, the log reader was not finding any transactions.
No open transactions in distribution. I'll see where Paul's suggestion leads
me...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:1113509425.813891.157720@.z14g2000cwz.googlegr oups.com...
> run a dbcc opentran in your distributon database to see what happens.
> The keep replication switch was designed to be enable disaster recovery
> of your transactional replication solution. You can use it to restore
> publications on a server but only so you can script out the
> publications or view them. Don't expect to use the keep_replication
> swithc on a new server and have everything work. You need to restore
> the distribution, master, and msdb databases as well.
>
|||Results from msrepl_commands table:
publisher db_id count (xact_seqno)
1 20867
2 1002769
3 159454
Ran distribution clean up agent job (has been running successfully, every 10
minutes):
publisher db_id count (xact_seqno)
1 20866
2 996394
3 158467
The counts are all lower. I added an output file to the job which states:
Removed 74 replicated transactions consisting of 245 statements in 30
seconds (10 rows/sec). Retention max looks to be set at 72 hours (default, I
assume - I don't think that we changed this in the old system).
I'll just keep monitoring this for now. Maybe a 1 GB data file for this
distribution database is not out of line and I no longer have access to the
old system to compare anything.
Thanks,
Michelle
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uWKIq2SQFHA.248@.TK2MSFTNGP15.phx.gbl...
> Michelle,
> have a look at the msrepl_commands table and see if this is the cause of
the
> large size. If it is, it could be that you have a subscriber who hasn't
> synchronized in a while, or the distribution cleanup agent is disabled, or
> you have an anonymous subscriber, so the commands remain until the
retention
> period is reached.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Well over a million records is quite a lot, but I'd be surprised if this
amounts to 1GB. Running, sp_spaceused will give the exact ratio of empty to
used space in the database. If the subscriber(s) have synchronized and are
up to date, you could reduce the retention period and run the cleanup agent
to remove a big chunk of this data but this will only work if anonymous
subscribers aren't enabled.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Subscribe to:
Posts (Atom)