Besides loopback detection, is there anything else that is needed to prevent the replication command from sending back to the source. Cos i am having problem with the subscriber B sending back the replication to A in the form of B as the publisher and A as the subscriber.
:confused:hi
what is the problem? a error in the sync. Can you writr more information about your problema.
TIA
Abel.|||It gives me an error " violation of primary key constraint..." And when i search the record at the subscriber, it shows that the record is already there. Meaning A send to B...then B send to A...If I'm not wrong in reading about loopback detection, B shouldnt send to A already...correct me if I'm wrong. Tks.|||hi, you have a Transactional replication in a SQL Server? this type of replication not is a "bi-direccional" (by defualt, the data of subscriber be read only, do you can't modified this data), how implement this replication?
In your case use the merge, this type of replication is "bi-directional".
bye.
Abel.
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Sunday, March 11, 2012
Bi-directional Transaction Replication
Two Servers,
1st 2nd
database a ---> after replication a'
b' <--- after replication b
on 1st server a is replicated to 2nd
on 2nd server b is replicated to 1st
I tried with merge replication it is working fine.But if i tried to use
transactional replication it give some error like "access
violation".Using tansactional replication is it possible.plz help me to
slove this problem.
Regards,
Senthil prabu RI have no idea myself, but under "Nonpartitioned, Bidirectional,
Transactional Replication", Books Online says that to implement
bidirectional transactional replication "substantial customization and
programming is usually required."
So you might get a better response in
microsoft.public.sqlserver.replication, as it seems to be something
quite specialized.
Simon
Saturday, February 25, 2012
Better proformance using join or sub-query?
All,
I am using a trigger to keep a "transaction date" column up-to-date with
the datetime the record was lasted inserted/updated. My question is which
SQL statement would provide better performance:
Update tablename set transdate = getdate() where primarykey in (select
primarykey from inserted)
or
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Thanks,
James K.I recommend
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
> with the datetime the record was lasted inserted/updated. My question is
> which SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||I think the second one
Madhivanan|||JKL
I'd go with the first one. The probelm with the second one is that under
some conditions you can get a different ( wrong) output
David Portas has written some script/test about this
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
with
> the datetime the record was lasted inserted/updated. My question is which
> SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||Uri,
The original poster want to update the column the getDate().
I think, As long as you dont depend on the joined table for the
value to update, you are safe.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Of9GdAoGFHA.2420@.TK2MSFTNGP14.phx.gbl...
> JKL
> I'd go with the first one. The probelm with the second one is that under
> some conditions you can get a different ( wrong) output
> David Portas has written some script/test about this
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> You get this from the first SELECT statement:
> countryname capitalcity
> -- --
> UK London
> USA Washington
> and this from the second:
> countryname capitalcity
> -- --
> UK Manchester
> USA Washington
>
>
> "-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
> news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> with
>|||On Fri, 25 Feb 2005 13:00:35 +0530, avnrao wrote:
>Umi, can you please explain the rationale behind it.
Hi Av.,
Mind if I do instead of Uri?
The ANSI syntax and the proprietary UPDATE FROM syntax behave only the
same if for each row in the updated table that satisfies the WHERE
clause, exactly one row in the other table matches the join criteria.
If it's possible that no rows are matched, the ANSI syntax will set the
column to NULL, unless excluded from the update in the WHERE clause. The
UPDATE FROM syntax will not update this row: since it doesn't satisfy
the join criteria, it won't be updated even if it does meet the
requirements of the WHERE clause.
(Note: this difference can be circumvened by using outer join instead of
inner join to make the joined update behave exactly as the ANSI version)
The biggest difference is when one row to be updated can be matched
against more than one row in the source table. This is what's happening
in David's script, as posted by Uri.
In the ANSI version, if the subquery in the SET clause returns more than
one row, you'll get an error message. That ensures that you will revisit
the query and change it so that it will always return exactly one row -
the row that you want it to return.
The UPDATE FROM syntax won't throw an error if a row in the table to be
updated matches against multiple rows from the source. Instead, SQL
Server will just pick one of these rows and use that one to determine
the new values in your table. Or rather, in case you want the full
details, it will update the same row over and over again with each
matching row - and since the result of all but the last change is
overwritten, the net effect is that only the results from the row
processed last will stick.
The above explains why the result of an UPDATE FROM where one row to be
updated matches more than one row from the source is completely
unpredictable: the result will be from the last row processed, but there
is no way to predict the order of evaluation SQL Server will choose for
your query. David's example shows how an index can change the order, but
there are other possibilities: parallellism, available memory, workload,
and the possibility to "piggy-back" another query are just some examples
of how the order of evaluation (and hence the result of the update) may
change between successive execution, even if no schema or data has been
changed!
I hope this helps.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am using a trigger to keep a "transaction date" column up-to-date with
the datetime the record was lasted inserted/updated. My question is which
SQL statement would provide better performance:
Update tablename set transdate = getdate() where primarykey in (select
primarykey from inserted)
or
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Thanks,
James K.I recommend
update tablename set transdate = getdate
from tablename, inserted
where tablename.primarykey = inserted.primarykey
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
> with the datetime the record was lasted inserted/updated. My question is
> which SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||I think the second one
Madhivanan|||JKL
I'd go with the first one. The probelm with the second one is that under
some conditions you can get a different ( wrong) output
David Portas has written some script/test about this
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
"-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> All,
> I am using a trigger to keep a "transaction date" column up-to-date
with
> the datetime the record was lasted inserted/updated. My question is which
> SQL statement would provide better performance:
> Update tablename set transdate = getdate() where primarykey in (select
> primarykey from inserted)
> or
> update tablename set transdate = getdate
> from tablename, inserted
> where tablename.primarykey = inserted.primarykey
> Thanks,
> James K.
>|||Uri,
The original poster want to update the column the getDate().
I think, As long as you dont depend on the joined table for the
value to update, you are safe.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Of9GdAoGFHA.2420@.TK2MSFTNGP14.phx.gbl...
> JKL
> I'd go with the first one. The probelm with the second one is that under
> some conditions you can get a different ( wrong) output
> David Portas has written some script/test about this
> CREATE TABLE Countries
> (countryname VARCHAR(20) NOT NULL PRIMARY KEY,
> capitalcity VARCHAR(20));
> CREATE TABLE Cities
> (cityname VARCHAR(20) NOT NULL,
> countryname VARCHAR(20) NOT NULL
> REFERENCES Countries (countryname),
> CONSTRAINT PK_Cities
> PRIMARY KEY (cityname, countryname));
> INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
> INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
> INSERT INTO Cities VALUES ('Washington', 'USA');
> INSERT INTO Cities VALUES ('London', 'UK');
> INSERT INTO Cities VALUES ('Manchester', 'UK');
> The MS-syntax makes it all too easy for the developer to slip-up by
> writing ambiguous UPDATE...FROM statements where the JOIN criteria is
> not unique on the right side of the join.
> Try these two identical UPDATE statements with a small change to the
> primary key in between.
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
> ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
> cityname);
> UPDATE Countries
> SET capitalcity = cityname
> FROM Countries JOIN Cities /* don't do this! */
> ON Countries.countryname = Cities.countryname;
> SELECT * FROM Countries;
> You get this from the first SELECT statement:
> countryname capitalcity
> -- --
> UK London
> USA Washington
> and this from the second:
> countryname capitalcity
> -- --
> UK Manchester
> USA Washington
>
>
> "-=JLK=-" <jknowlto-nospam@.dtic.mil> wrote in message
> news:eowog0nGFHA.2740@.TK2MSFTNGP12.phx.gbl...
> with
>|||On Fri, 25 Feb 2005 13:00:35 +0530, avnrao wrote:
>Umi, can you please explain the rationale behind it.
Hi Av.,
Mind if I do instead of Uri?
The ANSI syntax and the proprietary UPDATE FROM syntax behave only the
same if for each row in the updated table that satisfies the WHERE
clause, exactly one row in the other table matches the join criteria.
If it's possible that no rows are matched, the ANSI syntax will set the
column to NULL, unless excluded from the update in the WHERE clause. The
UPDATE FROM syntax will not update this row: since it doesn't satisfy
the join criteria, it won't be updated even if it does meet the
requirements of the WHERE clause.
(Note: this difference can be circumvened by using outer join instead of
inner join to make the joined update behave exactly as the ANSI version)
The biggest difference is when one row to be updated can be matched
against more than one row in the source table. This is what's happening
in David's script, as posted by Uri.
In the ANSI version, if the subquery in the SET clause returns more than
one row, you'll get an error message. That ensures that you will revisit
the query and change it so that it will always return exactly one row -
the row that you want it to return.
The UPDATE FROM syntax won't throw an error if a row in the table to be
updated matches against multiple rows from the source. Instead, SQL
Server will just pick one of these rows and use that one to determine
the new values in your table. Or rather, in case you want the full
details, it will update the same row over and over again with each
matching row - and since the result of all but the last change is
overwritten, the net effect is that only the results from the row
processed last will stick.
The above explains why the result of an UPDATE FROM where one row to be
updated matches more than one row from the source is completely
unpredictable: the result will be from the last row processed, but there
is no way to predict the order of evaluation SQL Server will choose for
your query. David's example shows how an index can change the order, but
there are other possibilities: parallellism, available memory, workload,
and the possibility to "piggy-back" another query are just some examples
of how the order of evaluation (and hence the result of the update) may
change between successive execution, even if no schema or data has been
changed!
I hope this helps.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Sunday, February 12, 2012
Best Way to Empty Transaction Log
Hi All,
What is a best way to empty (shrink) a database transaction log. I am
working with huge database - 10GB to 100GB- and basically doesn't care
about transaction log much when data is being deleted by a task. I have a
deletion task consisted of many stored procedures which is running every
night. In some of the client's database, when those stored procedure
deleted data in different tables, the transaction log getting to big and SQL
appears to hang up. Sometimes a deletion task will take a whole weekend.
Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK POINT
trying to reduce the size of the transaction log.
My question is, how can I tell SQL not to log the transactions when my
deletion procedures are running. If there is no way to to tell SQL not to
log the transactions, is the a better way to control the size of the
transaction log file, other than using the CHECK POINT?
Thanks.A CHECKPOINT followed by a DBCC SHRINKFILE, works for us. We shrink our log
before backup everynight, the log itself will hit 100GB(400GB+ DB) over
weekend maintenace. Shrinkfile usually goes quick on the log.
" David N" <dq.ninh@.netiq.com> wrote in message
news:en54Y$qWDHA.2328@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> What is a best way to empty (shrink) a database transaction log. I am
> working with huge database - 10GB to 100GB- and basically doesn't care
> about transaction log much when data is being deleted by a task. I have a
> deletion task consisted of many stored procedures which is running every
> night. In some of the client's database, when those stored procedure
> deleted data in different tables, the transaction log getting to big and
SQL
> appears to hang up. Sometimes a deletion task will take a whole weekend.
> Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK
POINT
> trying to reduce the size of the transaction log.
> My question is, how can I tell SQL not to log the transactions when my
> deletion procedures are running. If there is no way to to tell SQL not to
> log the transactions, is the a better way to control the size of the
> transaction log file, other than using the CHECK POINT?
> Thanks.
>|||Did not read well, sry. All your DELETE FROMs are going to be logged, that
is a good thing. If you are clearing out the entire table, you can use the
TRUNCATE TABLE which is non-logged. If you cannot use TRUNCATE TABLE and
must use DELETE FROM, you can try an optimize it to run more efficiently.
The DELETE might run quicker if you can base the delete off the CLUSTERED
INDEX(use temp tables if needed to achieve) or if this table is index heavy
you might drop some indexes(non-clustered only) and recreate them when the
delete process is finished.
My personal way if it is large delete filling up your log(ie one big logged
transaction). I would suck all the CLUSTERED INDEX values into a temp table
and use a WHILE with TOP functionality in it to loop the delete in smaller
digestible chunks. This will allow the log backups to clear out the
transaction log(make sure these are running in good intervals, ours go every
5mins). We have even added functionality to some of our bigger maint procs
to check logsize during loops and run a log backup to keep it in check.
HTH.
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OsdM$DrWDHA.2032@.TK2MSFTNGP11.phx.gbl...
> A CHECKPOINT followed by a DBCC SHRINKFILE, works for us. We shrink our
log
> before backup everynight, the log itself will hit 100GB(400GB+ DB) over
> weekend maintenace. Shrinkfile usually goes quick on the log.
>
> " David N" <dq.ninh@.netiq.com> wrote in message
> news:en54Y$qWDHA.2328@.TK2MSFTNGP12.phx.gbl...
> > Hi All,
> >
> > What is a best way to empty (shrink) a database transaction log. I am
> > working with huge database - 10GB to 100GB- and basically doesn't care
> > about transaction log much when data is being deleted by a task. I have
a
> > deletion task consisted of many stored procedures which is running every
> > night. In some of the client's database, when those stored procedure
> > deleted data in different tables, the transaction log getting to big and
> SQL
> > appears to hang up. Sometimes a deletion task will take a whole
weekend.
> > Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK
> POINT
> > trying to reduce the size of the transaction log.
> >
> > My question is, how can I tell SQL not to log the transactions when my
> > deletion procedures are running. If there is no way to to tell SQL not
to
> > log the transactions, is the a better way to control the size of the
> > transaction log file, other than using the CHECK POINT?
> >
> > Thanks.
> >
> >
>
What is a best way to empty (shrink) a database transaction log. I am
working with huge database - 10GB to 100GB- and basically doesn't care
about transaction log much when data is being deleted by a task. I have a
deletion task consisted of many stored procedures which is running every
night. In some of the client's database, when those stored procedure
deleted data in different tables, the transaction log getting to big and SQL
appears to hang up. Sometimes a deletion task will take a whole weekend.
Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK POINT
trying to reduce the size of the transaction log.
My question is, how can I tell SQL not to log the transactions when my
deletion procedures are running. If there is no way to to tell SQL not to
log the transactions, is the a better way to control the size of the
transaction log file, other than using the CHECK POINT?
Thanks.A CHECKPOINT followed by a DBCC SHRINKFILE, works for us. We shrink our log
before backup everynight, the log itself will hit 100GB(400GB+ DB) over
weekend maintenace. Shrinkfile usually goes quick on the log.
" David N" <dq.ninh@.netiq.com> wrote in message
news:en54Y$qWDHA.2328@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> What is a best way to empty (shrink) a database transaction log. I am
> working with huge database - 10GB to 100GB- and basically doesn't care
> about transaction log much when data is being deleted by a task. I have a
> deletion task consisted of many stored procedures which is running every
> night. In some of the client's database, when those stored procedure
> deleted data in different tables, the transaction log getting to big and
SQL
> appears to hang up. Sometimes a deletion task will take a whole weekend.
> Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK
POINT
> trying to reduce the size of the transaction log.
> My question is, how can I tell SQL not to log the transactions when my
> deletion procedures are running. If there is no way to to tell SQL not to
> log the transactions, is the a better way to control the size of the
> transaction log file, other than using the CHECK POINT?
> Thanks.
>|||Did not read well, sry. All your DELETE FROMs are going to be logged, that
is a good thing. If you are clearing out the entire table, you can use the
TRUNCATE TABLE which is non-logged. If you cannot use TRUNCATE TABLE and
must use DELETE FROM, you can try an optimize it to run more efficiently.
The DELETE might run quicker if you can base the delete off the CLUSTERED
INDEX(use temp tables if needed to achieve) or if this table is index heavy
you might drop some indexes(non-clustered only) and recreate them when the
delete process is finished.
My personal way if it is large delete filling up your log(ie one big logged
transaction). I would suck all the CLUSTERED INDEX values into a temp table
and use a WHILE with TOP functionality in it to loop the delete in smaller
digestible chunks. This will allow the log backups to clear out the
transaction log(make sure these are running in good intervals, ours go every
5mins). We have even added functionality to some of our bigger maint procs
to check logsize during loops and run a log backup to keep it in check.
HTH.
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OsdM$DrWDHA.2032@.TK2MSFTNGP11.phx.gbl...
> A CHECKPOINT followed by a DBCC SHRINKFILE, works for us. We shrink our
log
> before backup everynight, the log itself will hit 100GB(400GB+ DB) over
> weekend maintenace. Shrinkfile usually goes quick on the log.
>
> " David N" <dq.ninh@.netiq.com> wrote in message
> news:en54Y$qWDHA.2328@.TK2MSFTNGP12.phx.gbl...
> > Hi All,
> >
> > What is a best way to empty (shrink) a database transaction log. I am
> > working with huge database - 10GB to 100GB- and basically doesn't care
> > about transaction log much when data is being deleted by a task. I have
a
> > deletion task consisted of many stored procedures which is running every
> > night. In some of the client's database, when those stored procedure
> > deleted data in different tables, the transaction log getting to big and
> SQL
> > appears to hang up. Sometimes a deletion task will take a whole
weekend.
> > Note that the stored procedures do use TRUNCATE TRANSACTION and CHECK
> POINT
> > trying to reduce the size of the transaction log.
> >
> > My question is, how can I tell SQL not to log the transactions when my
> > deletion procedures are running. If there is no way to to tell SQL not
to
> > log the transactions, is the a better way to control the size of the
> > transaction log file, other than using the CHECK POINT?
> >
> > Thanks.
> >
> >
>
Subscribe to:
Posts (Atom)