y****9
发帖数: 144 | 1 In terms of the transaction log generated, I did some test. My observation/
conclusion:
----
in terms of the amount of transaction log used, using "INSERT INTO ...
SELECT" with "TABLOCK" hint is essentially same as using "SELECT ... INTO"
regardless of recovery mode. Without the "TABLOCK" hint, regardless of the
recovery mode, using "INSERT INTO ... SELECT" will generate same amount of
transaction log and 4 times more than that in the case of FULL recovery mode
with the hint ( i.e. 125406 KB vs ... 阅读全帖 |
|
y****9
发帖数: 144 | 2 In terms of the transaction log generated, I did some test. My observation/
conclusion:
----
in terms of the amount of transaction log used, using "INSERT INTO ...
SELECT" with "TABLOCK" hint is essentially same as using "SELECT ... INTO"
regardless of recovery mode. Without the "TABLOCK" hint, regardless of the
recovery mode, using "INSERT INTO ... SELECT" will generate same amount of
transaction log and 4 times more than that in the case of FULL recovery mode
with the hint ( i.e. 125406 KB vs ... 阅读全帖 |
|
t****n
发帖数: 263 | 3 This looks like a bad database design to me.
But, anyway, I don't have to maintain this thing.
never used LINQ before. But using SQL, you can do this.
begin tran
select * from TABLE1 with (tablock, updlock, holdlock) where 1=2
then the TABLE1 will be locked until you commit or rollback the transaction. |
|
a9
发帖数: 21638 | 4 begin transaction
select top 1 * from tablea with(tablock,xlock) where a.id=12
if @@rowcount=0 then
insert into tablea (id,status) values(12,0)
end if
commit transaction |
|
n*****y
发帖数: 36 | 5 你的SQL Server 版本是2000，2005还是2008？
另外，如果你的服务器是production server,
似乎tempdb所在的disk容量太小了，为什么不换到
容量大一些的专用硬盘上？
你还可以试试下面两个办法
1. 使用 TABLOCK（代价是low concurency)
2. 使用user-defined snapshots
先为database创建一个snapshot(SQL2005 or 2008),
该snapshot应该建在一个有足够空间的硬盘上，至少
有和该database一样大的空间，然后run dbcc command
on this snapshot
请zenny 和jackrun 大佬指正 |
|
y****9
发帖数: 144 | 6 I checked the BOL:
-- abount SELECT ... INTO clause
The amount of logging for SELECT...INTO depends on the recovery model in
effect for the database. Under the simple recovery model or bulk-logged
recovery model, bulk operations are minimally logged. With minimal logging,
using the SELECT… INTO statement can be more efficient than creating a
table and then populating the table with an INSERT statement.
---
But it seems that we can use minimal logging for isnert into select as well
as long as th... 阅读全帖 |
|
y****9
发帖数: 144 | 7 I checked the BOL:
-- abount SELECT ... INTO clause
The amount of logging for SELECT...INTO depends on the recovery model in
effect for the database. Under the simple recovery model or bulk-logged
recovery model, bulk operations are minimally logged. With minimal logging,
using the SELECT… INTO statement can be more efficient than creating a
table and then populating the table with an INSERT statement.
---
But it seems that we can use minimal logging for isnert into select as well
as long as th... 阅读全帖 |
|
