月亮女神在不？？

澳贼

啥 sql statement to check who and when the rows of a table were deleted?

bffbffbff

, 女神
你用的什么版本数据库？ 2008或者更新的不知道，反正2005或者更老的好像没法查，除非你们有 自己的audit table记录table的更新历史，不过那些table要带trigger。

乱码

there's no such a thing,

http://www.sqldev.org/getting-st ... d-rows-106233.shtml

http://www.dbforums.com/microsof ... o-deleted-rows.html

月亮

没法查，除非之前写过trigger

findcaiyzh

transaction log里应该有吧。
不过从transaction log里恢复删除时的语句比较麻烦，反正我是我不会。

我用过Apex SQL log的试用版(买的话要一千美元左右)，是可以查到的，当然前提是transaction log没有被truncated.

http://www.apexsql.com/sql_tools_log.aspx

北风

如果想恢复的话有办法，不过你得知道什么时候它还没被删除
光想知道什么时候谁改的，好像没办法

kr2000

澳贼，谁让你背黑锅了？

bffbffbff

原帖由 kr2000 于 2011-2-2 10:06 发表
澳贼，谁让你背黑锅了？

ms他的数据被黑了

典

We are using SQL audit (2008)
We are able to track user / time / action (delete / update / insert)

To do further you can use CDC (not realy sure)

典

But for delete, we do not allow anybody to delete production data, even the admin account.

uowzd01

prodution的data不应当删除，mark一下就可以了

澳贼

先delete 了 一个表里的全部数据，然后load data from files, then fill in all tables.

所以会出现暂时无数据的情况。

各位高手都是怎么解决这种情况的？？是不是该先把新数据load到一个tempTable,然后再delete * from tableA,  然后再
insert into tableA select *
   from tempTable

澳贼

原帖由 典 于 2011-2-2 10:17 发表
We are using SQL audit (2008)
We are able to track user / time / action (delete / update / insert)

To do further you can use CDC (not realy sure)

2008 以前没有 auditing 吧？

月亮

如果只是copy大量数据，SSIS pkg一般都先delete然后再load，因为没必要多一道temp table，如果fail了，系统会自动发邮件给DBA或者其他负责的人（一般都会这么设定），只要手动再运行一次就可以了。

澳贼

原帖由 月亮 于 2011-2-2 12:23 发表
如果只是copy大量数据，SSIS pkg一般都先delete然后再load，因为没必要多一道temp table，如果fail了，系统会自动发邮件给DBA或者其他负责的人（一般都会这么设定），只要手动再运行一次就可以了。

那就会有这问题。loading的时候，table 是空的。 poor design

典

Should run SSIS package overnight only?  (or make sure nobody accessing?)

月亮

原帖由 典 于 2011-2-2 12:42 发表
Should run SSIS package overnight only?  (or make sure nobody accessing?)

应该都是midnight的时候运行的，可能是fail了，所以第二天table里面什么都没有，以为被人删了

澳贼

原帖由 典 于 2011-2-2 12:42 发表
Should run SSIS package overnight only?  (or make sure nobody accessing?)

问题是有的程序24小时都有人用。

典

To avoid "empty data to the user", I think you may change the isolation level of the table( or add hints to the delete command)
When the SSIS starts to truncate (or delete) the table, the table is locked, then no users are allowed to access the table,

澳贼

原帖由 典 于 2011-2-2 12:55 发表
To avoid "empty data to the user", I think you may change the isolation level of the table( or add hints to the delete command)
When the SSIS starts to truncate (or delete) the table, the table is loc ...

‘no users are allowed to access the table’ ？？ 这个设计也太野蛮了吧？？

典

原帖由 澳贼 于 2011-2-2 12:12 发表


‘no users are allowed to access the table’ ？？ 这个设计也太野蛮了吧？？

Actually it's waiting.
When the table is locked by your hints, other users need to wait for the lock to be released. It may be only few seconds waiting(Assume your SSIS is fast enough)

澳贼

原帖由 典 于 2011-2-2 13:25 发表


Actually it's waiting.
When the table is locked by your hints, other users need to wait for the lock to be released. It may be only few seconds waiting(Assume your SSIS is fast enough)

要是几秒钟，我能发现问题么？？　因为这个ＳＳＩＳ太复杂了。ｌｏａｄ的时候，要ｖａｌｉｄａｔｅ很多数据。每次运行５－１０分钟。

典

For this case,
The lock only happens after you issued the "delete" command, nothing to do with validation or calculation.

I am not trying to convince you, just some sort of silly ideas

澳贼

原帖由 典 于 2011-2-2 14:34 发表
For this case,
The lock only happens after you issued the "delete" command, nothing to do with validation or calculation.

I am not trying to convince you, just some sort of silly ideas

"after you issued the "delete" command," 才会ｌｏａｄ．一些不合格的ｄａｔａ要在后面过滤掉，你的明白？
？

典

原帖由 澳贼 于 2011-2-2 13:53 发表


"after you issued the "delete" command," 才会ｌｏａｄ．一些不合格的ｄａｔａ要在后面过滤掉，你的明白？
？

Now 明白
your original thought of  temp-table may be better.
Anyway, it's a bad solution to run SSIS to the production DB withouth lock_out users.

澳贼

原帖由 典 于 2011-2-2 15:33 发表

Now 明白
your original thought of  temp-table may be better.
Anyway, it's a bad solution to run SSIS to the production DB withouth lock_out users.

谢谢．经验之谈，我以后多注意．今天分都光了．改日补上

澳贼

十年了

stoneage

女神还没老！

swe

金瓶梅读完了吗

澳贼

stoneage 发表于 2021-7-20 17:17
女神还没老！

不能老