Data File shrink

I have a data file in which there is a table containing 5,000,000 rows. I
deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
a clustered primary key index, and the table also has a text column. The
only other tables in the database are small lookup tables.
The datafile size before the delete was 75GB. After shrinking the datafile
(with target size 25000MB), the size decreased to 68GB.
The results of the shrinkfile were the following:
currentsize 8591936
minimumsize 128
usedpages 8206784
estimatedpages 8206784
Is there anything short of recreating a new table using select into, or
bcp, that might reclaim space?
--
Message posted via http://www.sqlmonster.comThe space used by the deleted text cannot be reclaimed in SQL Server 2000.
The (clumsy) solution is to bcp-out then in. In SQL Server 2005 we've fixed
this by adding LOB compaction to the shrink and defrag operations.
Regards
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
> I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||It is most likely due to the text columns. Reindexing and shrinking does
little or nothing to help reduce wasted space in them. BCP out, truncate
and BCP back in is the cleanest method. SQL2005 has features to address
this but not 2000.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:127b65beb25d4ccab668ff9ff8df8322@.SQLMonster.com...
>I have a data file in which there is a table containing 5,000,000 rows. I
> deleted 2,500,000 rows, leaving 2,500,000 rows in the table. The table has
> a clustered primary key index, and the table also has a text column. The
> only other tables in the database are small lookup tables.
> The datafile size before the delete was 75GB. After shrinking the datafile
> (with target size 25000MB), the size decreased to 68GB.
> The results of the shrinkfile were the following:
> currentsize 8591936
> minimumsize 128
> usedpages 8206784
> estimatedpages 8206784
> Is there anything short of recreating a new table using select into, or
> bcp, that might reclaim space?
> --
> Message posted via http://www.sqlmonster.com|||When you say truncate, is that the same as:
delete [tablename]?
--
Message posted via http://www.sqlmonster.com|||Well not exactly. A truncate is much faster than a Delete since it simply
deallocates all the pages associated with the table and it's indexes. There
is minimal logging to the transaction log. A Delete with no where clause
will log each and every row to the log and will be much slower on a larger
system. However there are some caveats to truncate. One is that you can
not issue this on a table with RI unless you drop that first. Two it does
not update the statistics but the idea is you will BCP it right back in
anyway.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:ff665e017d394681a275d526eb8acf92@.SQLMonster.com...
> When you say truncate, is that the same as:
> delete [tablename]?
> --
> Message posted via http://www.sqlmonster.com|||There is an option to reorganize data pages in the maintenance plan wizard.
Does this reclaim any space when I have already performed a shrinkfile
operation?
--
Message posted via http://www.sqlmonster.com|||No - it will take up more space. Under the covers it rebuilds the indexes
using DBCC DBREINDEX. Each index rebuild needs 1.2x the size of the index in
extra space. If you shrink then reorganize, your database will grow again.
If you reorganize then shrink, shrink will fragment the indexes again. Also,
reorganize doesn't touch text pages at all in SQL Server 2000.
There's no way in SQL Server 2000 to get the space back from the deleted
text rows without bcp out/in.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||The Reorganize part of the MP is a DBREINDEX. A DBREINDEX does not reclaim
any space per say. It may free up a few pages based on the fill factor
depending on how full they were before the reindexing. There is a different
option in the wizard to free up space and will essentially do a DBCC
SHRINKFILE. It should be turned off as per the other conversations we had
regarding this subject.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f3800333aebc4d99bcd34685895ed4cd@.SQLMonster.com...
> There is an option to reorganize data pages in the maintenance plan
> wizard.
> Does this reclaim any space when I have already performed a shrinkfile
> operation?
> --
> Message posted via http://www.sqlmonster.com|||So if I started at 5 million records and 75GB, deleted 2.5 million and now
have 68GB, BCP export, truncate the table, BCP import back into the
truncated table, do I then run a shrinkfile again to further reduce the
68GB file? I do not believe that bcp will shrink the file, so do I perform
dbcc shrinkfile, or is there a better way once the import is completed?
--
Message posted via http://www.sqlmonster.com|||You would do it in this order:
BCP out the table
Truncate the table
DBCC SHRINKFILE
BCP back into the table
But you have to be careful that you don't shrink the file too much or it
will simply grow again when you BCP the data back in. Why do you want to
shrink it at all? If there is any chance at all the data will get that
large again it is best to leave it at that size. You also need room for
reindexing.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d29d78ea6b5a4cab9e2566bb93b3bf97@.SQLMonster.com...
> So if I started at 5 million records and 75GB, deleted 2.5 million and now
> have 68GB, BCP export, truncate the table, BCP import back into the
> truncated table, do I then run a shrinkfile again to further reduce the
> 68GB file? I do not believe that bcp will shrink the file, so do I perform
> dbcc shrinkfile, or is there a better way once the import is completed?
> --
> Message posted via http://www.sqlmonster.com|||This is an archive table, that has essentially served its purpose. We are
no longer archiving to it, but instead only deletes will be performed
against it in the future. No further growth anticipated, just the opposite,
a gradual reduction in size.
If I remove the indexes before performing the import, and given that I
allow enough space in the shrunken data file to recreate the indexes, does
this process of recreating the indexes after the import (having a clustered
primary key identity index) cause any fragmentation issues, versus leaving
the indexes in place during the import?
--
Message posted via http://www.sqlmonster.com|||You will need at least 1.2 times the size of the table (in the case of the
clustered index) and for each non-clustered index in free space for this.
If you only have a small amount above that free the chances are the indexes
will not be contiguous in the file but will still be built. The more free
space you have the better chance that the indexes can be placed in a
contiguous area in the file when rebuilt. A little bit of fragmentation
won't hurt.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:fb453ef7c78943ce9b5af4e75cfb224e@.SQLMonster.com...
> This is an archive table, that has essentially served its purpose. We are
> no longer archiving to it, but instead only deletes will be performed
> against it in the future. No further growth anticipated, just the
> opposite,
> a gradual reduction in size.
> If I remove the indexes before performing the import, and given that I
> allow enough space in the shrunken data file to recreate the indexes, does
> this process of recreating the indexes after the import (having a
> clustered
> primary key identity index) cause any fragmentation issues, versus leaving
> the indexes in place during the import?
> --
> Message posted via http://www.sqlmonster.comsql

Data disappear - SQL server 2000 / ASP interface

Hello,
What could possibly cause data in the SQL server database to be
removed, except being deleted manually? We had a couple of situations
where data in certain records disappeared although the records were
still there. The data is entered and editted through the web interface
in ASP. The web interface is accessed by anyone who has an account in
our database.
I am more of a web programmer, not a SQL server administrator, so not
very familiar with SQL Server log or error tracking. If you can
suggest any way to track this kind of events (data disappearing), I
would appreciate it very much.

HB KimHB Kim (haebin@.andrew.cmu.edu) writes:
> What could possibly cause data in the SQL server database to be
> removed, except being deleted manually? We had a couple of situations
> where data in certain records disappeared although the records were
> still there. The data is entered and editted through the web interface
> in ASP. The web interface is accessed by anyone who has an account in
> our database.
> I am more of a web programmer, not a SQL server administrator, so not
> very familiar with SQL Server log or error tracking. If you can
> suggest any way to track this kind of events (data disappearing), I
> would appreciate it very much.

I'm afraid that this description is a bit too vague to work from. Do I
understand you right that you had data for a car with the license
plate ABC123 like make, colour, placement of steering wheel etc. Now
you still have ABC123 in the database and can see it through the web
form, but there is no longer information about the make and colour?

For anyone who does not know your application, it is difficult to tell
whether this indicates an error or not. If you, as you say, users can
open and edit data, someone may have decided to erase some information -
or done so by mistake. If you want to protect yourself against this,
you may need to devise some permission scheme, so that not anyone can
change the data. You may also want to implement some auditing scheme.

There might of course be technical problems that causes the data go
away. Bugs happens in the best families.

The one recommendation I can give is to check out Lumgient Log Explorer
at http://www.lumigent.com/. This is a tool that permits you to
examine the transaction log, to find when and who submitted a certain
statement.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi,

I experienced data disappear. Four tables with a 1-n relationship are
heavily updated and are target of some big queries.

Cenario:
Table A with ~3.000 rows sometimes rollback updates
Table B with ~5.000 rows sometimes rollback updates and inserts
Table C with ~60.000 rows have several rollbacked inserts
Table D with ~2.500.000 rows is havely rollbacked

The workflow:
1) Users inserts a row at table A
2) Users inserts one or more rows at B
3) Users inserts lots of rows at C
4) Users inserts lots of rows at D
5) Users updates D
6) Users updates B and C
7) Users updates A and prints a report with all content from the four
tables concerning with the row from A
8) Minutes or hours later users checks for that report and updates A
9) Days later users print a big report from all month data

Problem:
At steps 8) and 9) users tell some rows from some tables are vanished!

Details:
All update, delete and insert are performed by Stored Procedures.
Some selects are performed by no indexed views.
Some selects use more than 12 joins!
Reports are printed before the data vanished.
Time to data disapear (at users discretion) varies from minutes to days.
Each insert, each update, each delete for EACH ROW, is a explicit
transaction.
Im using MSSQLServer 2000 last sp and Windows Server 2000.
The server and users lies at the same network, the users uses a client
application (not web).
The users app connects with BDE but i coded a newer version with ADO but
this not worked.
All worked fine for sometime but sundenly this problem appears.
Users tell the loss of data is harder now the loss is growing.
The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
number of users is constantly growing).
All server memory is used, some locks take too long some deadlocks errors
arise.
I put a trigger to make sure any insert/update at the problematic tables
inputs a row at a dummy table (since i have the report i can search for
the row) no missing row are found at the dummy table.
Some triggers make a complex validations at some inserts/tables and
roolbacks the transaction, but when tested these triggers works fine.
I run checkdb, rebuild indexes checked realtionships, stored procedures,
application, triggers, etc.

Conclusions:
We need a better server with a lot more memory and a rebuild at the
network. ;)
This problem can be from a deadlock for sources like memory+network+diskIO
and the SQLServer cannot dont handle it.
The locks for sources are handled by windows but maybe the OS failed to
comunicate deadlocks problems to SQLserver.

Workaround:
I activated de server log to write any deadlock, timeout is -1(infinite) a
new server (and switches) are arriving shortly.
I hope this solves the problem. Someone experienced data loss like it?

Jean|||"jean_bulinckx" <jcb@.cin.ufpe.br> wrote in message
news:81d698784dfc937b76d33f9a7d9e5831@.localhost.ta lkaboutdatabases.com...
> Hi,
> I experienced data disappear. Four tables with a 1-n relationship are
> heavily updated and are target of some big queries.
> Cenario:
> Table A with ~3.000 rows sometimes rollback updates
> Table B with ~5.000 rows sometimes rollback updates and inserts
> Table C with ~60.000 rows have several rollbacked inserts
> Table D with ~2.500.000 rows is havely rollbacked
> The workflow:
> 1) Users inserts a row at table A
> 2) Users inserts one or more rows at B
> 3) Users inserts lots of rows at C
> 4) Users inserts lots of rows at D
> 5) Users updates D
> 6) Users updates B and C
> 7) Users updates A and prints a report with all content from the four
> tables concerning with the row from A
> 8) Minutes or hours later users checks for that report and updates A
> 9) Days later users print a big report from all month data
> Problem:
> At steps 8) and 9) users tell some rows from some tables are vanished!
> Details:
> All update, delete and insert are performed by Stored Procedures.
> Some selects are performed by no indexed views.
> Some selects use more than 12 joins!
> Reports are printed before the data vanished.
> Time to data disapear (at users discretion) varies from minutes to days.
> Each insert, each update, each delete for EACH ROW, is a explicit
> transaction.
> Im using MSSQLServer 2000 last sp and Windows Server 2000.
> The server and users lies at the same network, the users uses a client
> application (not web).
> The users app connects with BDE but i coded a newer version with ADO but
> this not worked.
> All worked fine for sometime but sundenly this problem appears.
> Users tell the loss of data is harder now the loss is growing.
> The LAN posses 2 hub is slowdown from 100MB to 10MB all network (the
> number of users is constantly growing).
> All server memory is used, some locks take too long some deadlocks errors
> arise.
> I put a trigger to make sure any insert/update at the problematic tables
> inputs a row at a dummy table (since i have the report i can search for
> the row) no missing row are found at the dummy table.
> Some triggers make a complex validations at some inserts/tables and
> roolbacks the transaction, but when tested these triggers works fine.
> I run checkdb, rebuild indexes checked realtionships, stored procedures,
> application, triggers, etc.
> Conclusions:
> We need a better server with a lot more memory and a rebuild at the
> network. ;)
> This problem can be from a deadlock for sources like memory+network+diskIO
> and the SQLServer cannot dont handle it.
> The locks for sources are handled by windows but maybe the OS failed to
> comunicate deadlocks problems to SQLserver.
> Workaround:
> I activated de server log to write any deadlock, timeout is -1(infinite) a
> new server (and switches) are arriving shortly.
> I hope this solves the problem. Someone experienced data loss like it?
> Jean

So at step 7 the user can see data, but at step 8 it's not there? If so,
when does the transaction COMMIT? Before or after the user checks the data?
If the user views the data, and then the transaction rolls back, that might
explain it.

If not, you could run a Profiler trace filtered to show only DELETE
statements on the problem tables. That should tell you how and when the rows
are deleted.

Simon|||Hi Simon,

Each transaction (each insert, update) is immediately commited (well, at
least at the Stored procedures the code is ok "begin tran if @.@.error
rollback else commit"). The report at step 7) is printed after all job is
done.
Because a several lack of resources (memory and net) im not able to run a
profile at the server. When i try to open the managment folder at the
Enterprise Manager a "timeout" arise. The profiler is a useful tool but
make a overhead the server at this moment canot handle. I put triggers at
the tables just in case a bad user delete or change the rows but it dont
worked for the missing rows.
Im inclined to a timeout or deadlock issue. I activated the server error
log to write about any deadlock. Now i searching for orphaned conections,
maybe a extend rollback is made for a connection broken or the transactin
is waiting for a not longer valid connection.
Ill try to trace all rollbacks.
Remember not a single tran but lots os trans are missing here while
another bunch of trans works fine at the same time and for the same
session.

Jean

P.S.im using readcommited isolation|||[posted and mailed, please reply in news]

jean_bulinckx (jcb@.cin.ufpe.br) writes:
> The workflow:
> 1) Users inserts a row at table A
> 2) Users inserts one or more rows at B
> 3) Users inserts lots of rows at C
> 4) Users inserts lots of rows at D
> 5) Users updates D
> 6) Users updates B and C
> 7) Users updates A and prints a report with all content from the four
> tables concerning with the row from A
> 8) Minutes or hours later users checks for that report and updates A
> 9) Days later users print a big report from all month data
> Problem:
> At steps 8) and 9) users tell some rows from some tables are vanished!

First, if business rules requires all or none of all these rows to be
inserted, all should be packed into one transaction.

There are plenty of possible reasons, and without knowledge of your
application it is difficult to tell. The two main tracks are: a) someone
deleted the rows. b) the rows were never committed.

a) could be because of a badly coded program. I recall a horror story from
our application. We could find that random rows in one table were deleted,
and we had no idea of why. I eventually decided to track down the issue,
and found a function that stored procedure that for deleted rows from this
table. The problem was that id that was passed was declared as int in VB,
which is only 16 bits, so the function deleted the wrong rows. (And there
was not need to call the procedure, because the correct rows were handled
anyway.)

b) can happen if timeouts are not handled correctly. Timeouts are client-
side events of which SQL Server does not know about. When a client gets a
timeout, it cancels the query. But cancelling a query does not rollback
any outstanding transactions, even if the transaction was started by
the procedure that was cancelled. So after a timeout, you should always
issue a rollback. (Or disconnect and reconnect.) Or simply set the
timeout to 0, so you don't get them.

Since you say "some rows from some tables", I'm inclined to believe
that a) is more likely. But if you can deduced that all missing rows
are from the last part of the operation, improper timeout handling is
not an unlikely culprit.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thx guys,

I canot tell too much about the application...

Just the DBA have total control over de database and server. But we are
redesining the server security.

Some more facts...

1) After a major tunning (focus at selects take less time) the number of
missing row decrease a lot!

2) The updates are not packed because i wish to validate rows at
onde-to-one basis and because i wish short transactions. A dozen short
transactions are prefearable than a big transaction.

3) The missing row affects the most frequent updated tables, the most used
and big tables are missind much more rows.

4) The row appear to be "deleted" at random.
if 100 rows are inserted or updated 10 are rollbacked.

I think its a timeout problem.

Jean|||Thx guys,

I canot tell too much about the application...

Just the DBA have total control over de database and server. But we are
redesining the server security.

Some more facts...

1) After a major tunning (focus at selects take less time) the number of
missing row decrease a lot!

2) The updates are not packed because i wish to validate rows at
onde-to-one basis and because i wish short transactions. A dozen short
transactions are prefearable than a big transaction.

3) The missing row affects the most frequent updated tables, the most used
and big tables are missind much more rows.

4) The row appear to be "deleted" at random.
if 100 rows are inserted or updated 10 are rollbacked.

I think its a timeout problem.

Jean|||jean_bulinckx (jcb@.cin.ufpe.br) writes:
> I canot tell too much about the application...
> Just the DBA have total control over de database and server. But we are
> redesining the server security.

It is of course your choice how much information you want to share
about your application. But the less you tell us, the smaller is the
likelyhood that you will get useful advice.

> 2) The updates are not packed because i wish to validate rows at
> onde-to-one basis and because i wish short transactions. A dozen short
> transactions are prefearable than a big transaction.

To avoid contention, maybe. But when determining the transaction length,
the prime focus must be on business rules. If you do:

BEGIN TRANSACTION
INSERT A ...
COMMIT TRANSACTION

BEGIN TRANSACTION
INSERT B ...
COMMIT TRANSACTION

You must be sure that it is permissible that only the first transaction
is carried out. If your application would be inconsistent if this happened,
then you must have in all transaction. If you ignore that, then you should
be surprised if you are missing rows.

If you need to perform validation row-by-row, one option could be to use
temp tables to store the data, and not until you are done insert into
the target table. A variation is to use permanent tables that are
process-keyed. (A process-key can be as simple as @.@.spid, but that does
not work in a disconnected environment.)

> 4) The row appear to be "deleted" at random.
> if 100 rows are inserted or updated 10 are rollbacked.
> I think its a timeout problem.

Actually, to be honest, I think it is a problem with poor application
design.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thx for the replys guys!

These inserts/updates are made by users, One-to-one. Sometimes a user
select a lot of rows to update or insert but at the cliente-side there a
loop to insert ono-to-one (i guess just to reuse the same insert
procedure).

I dont designed the app im just a fireman ;) working at server side.
This thing use ADO and i canot find a timeout handle at code. All i find
is a ordinary "try stored_procedure.execute catch"

Im not sure ADO automatizes any sort of work about timeout but i thing
the app is not handling lock timeouts and deadlocks. The timeout is set to
0 what about close the connection while there are a blocked transaction?

Jean|||jean_bulinckx (jcb@.cin.ufpe.br) writes:
> Im not sure ADO automatizes any sort of work about timeout but i thing
> the app is not handling lock timeouts and deadlocks. The timeout is set to
> 0 what about close the connection while there are a blocked transaction?

If the command timeout is set to 0, then there should not be any timeouts.
But it is important to set the timeout on the command object. The one on
the connection object is not inherited by the command object.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Data deleted from database

Hi,
I have a merge replication set on a database.
day before yesterday I inserted some 10000 records in a table being
replicated.
The records did get insterted successfully but next day morning when I
checked I found that those records are not present in that table.
I checked all possible ways, data could have deleted but I don't find any
thing.
I see that those records are there in the conflic table because they could
not get replicated because of identity calumn. But that should not delete
records from the table.
I 'm confused and need help.
Thanks
SamHi
Are you sure they were commited? You might have inserted them, looked at the
data, and when you closed the connection, the data got rolled back.
Regards
Mike
"Sam" wrote:

> Hi,
> I have a merge replication set on a database.
> day before yesterday I inserted some 10000 records in a table being
> replicated.
> The records did get insterted successfully but next day morning when I
> checked I found that those records are not present in that table.
> I checked all possible ways, data could have deleted but I don't find any
> thing.
> I see that those records are there in the conflic table because they could
> not get replicated because of identity calumn. But that should not delete
> records from the table.
> I 'm confused and need help.
> Thanks
> Sam
>
>|||I can say that they were committed because out of 8 table's data I inserted,
4 tables kept the data and 4 did not.
And those 4 who did not keep the data sill have the same records in their
respective conflict tables because of the identity issue.
I don't understand this behaviour.
Please help
Sam
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CA4AF30-B285-4463-9142-83DE6233515E@.microsoft.com...[vbcol=seagreen]
> Hi
> Are you sure they were commited? You might have inserted them, looked at
> the
> data, and when you closed the connection, the data got rolled back.
> Regards
> Mike
> "Sam" wrote:
>

Data deleted from database

Hi,
I have a merge replication set on a database.
day before yesterday I inserted some 10000 records in a table being
replicated.
The records did get insterted successfully but next day morning when I
checked I found that those records are not present in that table.
I checked all possible ways, data could have deleted but I don't find any
thing.
I see that those records are there in the conflic table because they could
not get replicated because of identity calumn. But that should not delete
records from the table.
I 'm confused and need help.
Thanks
Sam
Hi
Are you sure they were commited? You might have inserted them, looked at the
data, and when you closed the connection, the data got rolled back.
Regards
Mike
"Sam" wrote:

> Hi,
> I have a merge replication set on a database.
> day before yesterday I inserted some 10000 records in a table being
> replicated.
> The records did get insterted successfully but next day morning when I
> checked I found that those records are not present in that table.
> I checked all possible ways, data could have deleted but I don't find any
> thing.
> I see that those records are there in the conflic table because they could
> not get replicated because of identity calumn. But that should not delete
> records from the table.
> I 'm confused and need help.
> Thanks
> Sam
>
>
|||I can say that they were committed because out of 8 table's data I inserted,
4 tables kept the data and 4 did not.
And those 4 who did not keep the data sill have the same records in their
respective conflict tables because of the identity issue.
I don't understand this behaviour.
Please help
Sam
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CA4AF30-B285-4463-9142-83DE6233515E@.microsoft.com...[vbcol=seagreen]
> Hi
> Are you sure they were commited? You might have inserted them, looked at
> the
> data, and when you closed the connection, the data got rolled back.
> Regards
> Mike
> "Sam" wrote:

Data deleted from database

Hi,
I have a merge replication set on a database.
day before yesterday I inserted some 10000 records in a table being
replicated.
The records did get insterted successfully but next day morning when I
checked I found that those records are not present in that table.
I checked all possible ways, data could have deleted but I don't find any
thing.
I see that those records are there in the conflic table because they could
not get replicated because of identity calumn. But that should not delete
records from the table.
I 'm confused and need help.
Thanks
SamHi
Are you sure they were commited? You might have inserted them, looked at the
data, and when you closed the connection, the data got rolled back.
Regards
Mike
"Sam" wrote:
> Hi,
> I have a merge replication set on a database.
> day before yesterday I inserted some 10000 records in a table being
> replicated.
> The records did get insterted successfully but next day morning when I
> checked I found that those records are not present in that table.
> I checked all possible ways, data could have deleted but I don't find any
> thing.
> I see that those records are there in the conflic table because they could
> not get replicated because of identity calumn. But that should not delete
> records from the table.
> I 'm confused and need help.
> Thanks
> Sam
>
>|||I can say that they were committed because out of 8 table's data I inserted,
4 tables kept the data and 4 did not.
And those 4 who did not keep the data sill have the same records in their
respective conflict tables because of the identity issue.
I don't understand this behaviour.
Please help
Sam
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:7CA4AF30-B285-4463-9142-83DE6233515E@.microsoft.com...
> Hi
> Are you sure they were commited? You might have inserted them, looked at
> the
> data, and when you closed the connection, the data got rolled back.
> Regards
> Mike
> "Sam" wrote:
>> Hi,
>> I have a merge replication set on a database.
>> day before yesterday I inserted some 10000 records in a table being
>> replicated.
>> The records did get insterted successfully but next day morning when I
>> checked I found that those records are not present in that table.
>> I checked all possible ways, data could have deleted but I don't find any
>> thing.
>> I see that those records are there in the conflic table because they
>> could
>> not get replicated because of identity calumn. But that should not delete
>> records from the table.
>> I 'm confused and need help.
>> Thanks
>> Sam
>>

Data comparison and update

Hello All,

I have two tables T1 and T2 with the same data structure. I need to compare T1 with T2 for all columns and update T2 for deleted, inserted and updated rows. How can I do this?

Are you duplicating the T1 data into T2? If so, why not simply delete all T2 rows and insert all T1 rows into T2 (or drop T2 and then recreate it from T1, including data)?|||

Hello,

Thanks you very much for the reply. T1 is big and is changing constantly and I am trying to find the discrepancy between T1 and T2 and update T2 based on the discrepancies. SO trying to realize synchronization on a single table. Any idea?

|||I haven't used Triggers in a long time, but it may be a good solution to your situation. Set up the Triggers for UPDATE, INSERT and DELETE operations and have it synchronize your T2 table accordingly. Once the Triggers are defined (and tested), you don't even have to worry about it. Be sure that performance isn't hit by doing this, though.|||

Sorry Jim, wish I could help you, but I'm currently bound by some confidentiality agreements that prohibit me from discussing this in much detail. But here are some choices:

Use a trigger to update t1 whenever t2 is updated (If you need them to be synchronized in realtime, including transaction consistancy). But since they are the same structure, there is usually little reason for implementing it this way.

Replication. You can use this to replicate data from server to server, and probably from table to table as well. There are so many options on how this can be done, it'll take you a while to research and test the possibilities.

Diff-gramming. Write queries to automatically insert, update, and delete those rows which differ from table1 to table2.