We have 30 remote subscribers and a publisher.We have
experienced in past that the conflict tables grow to the
limit and for some reason after that we start having
replication issues.(It will take anywhere from 3 hr to 6
hrs compare to 10 mins)
becos of the timings the data gets replicated to all the
sites but it doesn't clear them up from the conflicts
tables. How do we manually purge the conflict records so
that it shouldn't cause us problems in long run.
thanks
delete table
where....
Conflict tables are just that tables. There is nothing special about them,
so you can freely insert/update/delete from them. (They are required for
merge so just playing with data isn't recommended.) But, you won't get a
failure on a transaction.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||HI should I use delte table name or I should use truncate
table name statement.
thanks
>--Original Message--
>delete table
>where....
>Conflict tables are just that tables. There is nothing
special about them,
>so you can freely insert/update/delete from them. (They
are required for
>merge so just playing with data isn't recommended.) But,
you won't get a
>failure on a transaction.
>--
>Mike
>Principal Mentor
>Solid Quality Learning
>"More than just Training"
>SQL Server MVP
>http://www.solidqualitylearning.com
>http://www.mssqlserver.com
>
>.
>
sqlsql
Showing posts with label grow. Show all posts
Showing posts with label grow. Show all posts
Sunday, March 25, 2012
Tuesday, February 14, 2012
CHECKDB causes tempdb to grow
We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan Jones
DBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan Jones
DBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
CHECKDB causes tempdb to grow
We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan JonesDBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
--
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan JonesDBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
--
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
CHECKDB causes tempdb to grow
We run a DBCC CHECKDB statement against each of our
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan JonesDBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
databases once a week (during off-hours). It runs
successfully, but each morning after it runs I end up
having to shrink tempdb's data and log files.
I know that CHECKDB uses alot of tempdb, but why doesn't
it shrink back down on its own when CHECKDB is finished?
Thank you!
Susan JonesDBCC checkdb ususally doesn't use a lot of space like DBCC DBREINDEX. But if
tempdb does not shrink automatically, check open transactions using DBCC
OPENTRAN.
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||Unless autoshrink is turned on, no database will automatically shrink...
This uses lots of resources, and fragments the databases ( an issue for user
databases.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.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
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones|||IN addition to the other comments why would you want to shrink the files
when it obviously needs to be that large to operate properly. By shrinking
the files you incur overhead in both the grow and shrink phases which is
unnecessary if you just leave it alone.
Andrew J. Kelly SQL MVP
"Susan" <sjones@.sartox.com> wrote in message
news:7dea01c48493$ad643120$a401280a@.phx.gbl...
> We run a DBCC CHECKDB statement against each of our
> databases once a week (during off-hours). It runs
> successfully, but each morning after it runs I end up
> having to shrink tempdb's data and log files.
> I know that CHECKDB uses alot of tempdb, but why doesn't
> it shrink back down on its own when CHECKDB is finished?
> Thank you!
> Susan Jones
Subscribe to:
Posts (Atom)