I have a database i revocery mode simple (a test-system) where the
transactionlog have grown to much (6 GB and the db size are 300 MB) and I
have problem to shrink it. When I try to shrink it it says "Cannot shrink
log file 2 (DBNAME_log) because all logical log files are in use. What I can
see I'm the only one using it (SQL Srv Man Studio)
If I run DBCC SQLPERF(Logspace) it says 99%
If I run backup log DBNAME with truncate_only it's success but nothing
disappears.
I also have done a full backup ...
What can I do ? It feels like the checkpoint never are run but it must have.
I have tried alter it between recovery mode full and backup to simple
Please help me out
Sincerely
\\Jonas Bjonte@.bson.se wrote:
> I have a database i revocery mode simple (a test-system) where the
> transactionlog have grown to much (6 GB and the db size are 300 MB) and I
> have problem to shrink it. When I try to shrink it it says "Cannot shrink
> log file 2 (DBNAME_log) because all logical log files are in use. What I can
> see I'm the only one using it (SQL Srv Man Studio)
> If I run DBCC SQLPERF(Logspace) it says 99%
> If I run backup log DBNAME with truncate_only it's success but nothing
> disappears.
> I also have done a full backup ...
> What can I do ? It feels like the checkpoint never are run but it must have.
> I have tried alter it between recovery mode full and backup to simple
> Please help me out
> Sincerely
> \\Jonas B
>
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[sp_force_shrink_log]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
drop procedure [dbo].[sp_force_shrink_log]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc sp_force_shrink_log
/*
*************************************************************
Name: sp_force_shrink_log
Description:
Shrink transaction log of the current database in SQL Server 7.0.
Switch context to proper db to execute.
Usage: exec sp_force_shrink_log <target_percent>, <target MB>,
<iterations>, <backup options>
exec pubs..sp_force_shrink_log
Author: Andrew Zanevsky, AZ Databases, Inc., 12/25/1999, v5 - 08/18/2000
zanevsky@.azdatabases.com
Input Params:
--
@.target_percent tinyint. default = 0. Target percentage of remaining
shrinkable
space. Defaults to max possible.
@.target_size_MB int. default = 10. Target size of final log in MB.
@.max_iterations int. default = 1000. Number of loops (max) to run proc
through.
@.backup_log_opt nvarchar(1000). default = 'with truncate_only'. Backup
options.
Output Params:
--
Return:
Results:
--
Locals:
--
@.err Holds error value
Modifications:
--
*************************************************************
*/
@.target_percent tinyint = 0,
@.target_size_MB int = 10,
@.max_iterations int = 1000,
@.backup_log_opt nvarchar(1000) = 'with truncate_only'
as
set nocount on
declare @.db sysname,
@.last_row int,
@.log_size decimal(15,2),
@.unused1 decimal(15,2),
@.unused decimal(15,2),
@.shrinkable decimal(15,2),
@.iteration int,
@.file_max int,
@.file int,
@.fileid varchar(5)
select @.db = db_name(),
@.iteration = 0
create table #loginfo (
id int identity,
FileId int,
FileSize numeric(22,0),
StartOffset numeric(22,0),
FSeqNo int,
Status int,
Parity smallint,
CreateLSN real
)
create unique clustered index loginfo_FSeqNo on #loginfo ( FSeqNo,
StartOffset )
create table #logfiles ( id int identity(1,1), fileid varchar(5) not null )
insert #logfiles ( fileid ) select convert( varchar, fileid ) from
sysfiles where status & 0x40 = 0x40
select @.file_max = @.@.rowcount
if object_id( 'table_to_force_shrink_log' ) is null
exec( 'create table table_to_force_shrink_log ( x nchar(3000) not null )' )
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status,
Parity, CreateLSN) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0 end )
/ 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0
then FileSize else 0 end ) / 1048576.00
from #loginfo
select @.unused1 = @.unused -- save for later
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable * 100
/ @.log_size )
while @.shrinkable * 100 / @.log_size > @.target_percent
and @.shrinkable > @.target_size_MB
and @.iteration < @.max_iterations begin
select @.iteration = @.iteration + 1 -- this is just a precaution
exec( 'insert table_to_force_shrink_log select name from sysobjects
delete table_to_force_shrink_log')
select @.file = 0
while @.file < @.file_max begin
select @.file = @.file + 1
select @.fileid = fileid from #logfiles where id = @.file
exec( 'dbcc shrinkfile( ' + @.fileid + ' )' )
end
exec( 'backup log [' + @.db + '] ' + @.backup_log_opt )
truncate table #loginfo
insert #loginfo ( FileId, FileSize, StartOffset, FSeqNo, Status,
Parity, CreateLSN) exec ( 'dbcc loginfo' )
select @.last_row = @.@.rowcount
select @.log_size = sum( FileSize ) / 1048576.00,
@.unused = sum( case when Status = 0 then FileSize else 0
end ) / 1048576.00,
@.shrinkable = sum( case when id < @.last_row - 1 and Status = 0 then
FileSize else 0 end ) / 1048576.00
from #loginfo
select 'iteration' = @.iteration,
'log size, MB' = @.log_size,
'unused log, MB' = @.unused,
'shrinkable log, MB' = @.shrinkable,
'shrinkable %' = convert( decimal(6,2), @.shrinkable *
100 / @.log_size )
end
if @.unused1 < @.unused
select 'After ' + convert( varchar, @.iteration ) +
' iterations the unused portion of the log has grown from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB.'
union all
select 'Since the remaining unused portion is larger than 10 MB,' where
@.unused > 10
union all
select 'you may try running this procedure again with a higher number of
iterations.' where @.unused > 10
union all
select 'Sometimes the log would not shrink to a size smaller than
several Megabytes.' where @.unused <= 10
else
select 'It took ' + convert( varchar, @.iteration ) +
' iterations to shrink the unused portion of the log from ' +
convert( varchar, @.unused1 ) + ' MB to ' +
convert( varchar, @.unused ) + ' MB'
exec( 'drop table table_to_force_shrink_log' )
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||Hi
If you have FULL recovery model , BACKUP LOG file and then run DBCC
SHRINKFILE command
It seeems you have active transactions with status =2 ( could be seen byb
running DBCC LOGINFO(dbid))
Run demo INSERT to move the transactions (with status =2) on the top of the
list and then run DBCC shrinkfile
"jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:%23Sp$doaYIHA.6044@.TK2MSFTNGP05.phx.gbl...
>I have a database i revocery mode simple (a test-system) where the
>transactionlog have grown to much (6 GB and the db size are 300 MB) and I
>have problem to shrink it. When I try to shrink it it says "Cannot shrink
>log file 2 (DBNAME_log) because all logical log files are in use. What I
>can see I'm the only one using it (SQL Srv Man Studio)
> If I run DBCC SQLPERF(Logspace) it says 99%
> If I run backup log DBNAME with truncate_only it's success but nothing
> disappears.
> I also have done a full backup ...
> What can I do ? It feels like the checkpoint never are run but it must
> have. I have tried alter it between recovery mode full and backup to
> simple
> Please help me out
> Sincerely
> \\Jonas B
>|||My guess is that you have some open or un-replicated transactions in the database. Use DBCC OPENTRAN
(see Books Online).
As for shrinking (after you have resolved your open transaction), check out
http://www.karaszi.com/SQLServer/info_dont_shrink.asp which has some general info about shrinking
log files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
news:%23Sp$doaYIHA.6044@.TK2MSFTNGP05.phx.gbl...
>I have a database i revocery mode simple (a test-system) where the transactionlog have grown to
>much (6 GB and the db size are 300 MB) and I have problem to shrink it. When I try to shrink it it
>says "Cannot shrink log file 2 (DBNAME_log) because all logical log files are in use. What I can
>see I'm the only one using it (SQL Srv Man Studio)
> If I run DBCC SQLPERF(Logspace) it says 99%
> If I run backup log DBNAME with truncate_only it's success but nothing disappears.
> I also have done a full backup ...
> What can I do ? It feels like the checkpoint never are run but it must have. I have tried alter it
> between recovery mode full and backup to simple
> Please help me out
> Sincerely
> \\Jonas B
>|||Yes you are right, the database are replicated, that I didn't know so I have
open transactions. Now I think I can solve it.
Thank's for your help
Sincerely
\\Jonas B
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> skrev i
meddelandet news:e7jiKlbYIHA.4448@.TK2MSFTNGP03.phx.gbl...
> My guess is that you have some open or un-replicated transactions in the
> database. Use DBCC OPENTRAN (see Books Online).
> As for shrinking (after you have resolved your open transaction), check
> out http://www.karaszi.com/SQLServer/info_dont_shrink.asp which has some
> general info about shrinking log files.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "jonte@.bson.se" <jonasberthelsson@.hotmail.com> wrote in message
> news:%23Sp$doaYIHA.6044@.TK2MSFTNGP05.phx.gbl...
>>I have a database i revocery mode simple (a test-system) where the
>>transactionlog have grown to much (6 GB and the db size are 300 MB) and I
>>have problem to shrink it. When I try to shrink it it says "Cannot shrink
>>log file 2 (DBNAME_log) because all logical log files are in use. What I
>>can see I'm the only one using it (SQL Srv Man Studio)
>> If I run DBCC SQLPERF(Logspace) it says 99%
>> If I run backup log DBNAME with truncate_only it's success but nothing
>> disappears.
>> I also have done a full backup ...
>> What can I do ? It feels like the checkpoint never are run but it must
>> have. I have tried alter it between recovery mode full and backup to
>> simple
>> Please help me out
>> Sincerely
>> \\Jonas B
>
Showing posts with label transaction. Show all posts
Showing posts with label transaction. Show all posts
Wednesday, March 28, 2012
Tuesday, March 20, 2012
Problem with ghost locking...
Hi
I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.
After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it...
When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").
Any ideas ?
I'm using SQL-Server y2k + sp3.
Thanks,
Inon.Mode S means that the 2 processes where doing SELECT with default TIL (READ UNCOMMITTED) while you were trying to UPDATE the table (maybe accidentally you hit a key while browsing the table in EM?) UPDATE is not allowed on a table that has shared lock placed on it.
I just now had the strangest error, for some reason a table in my DB was locked by two processes, there is no use with BEGIN TRANSACTION in the queries that execute on the DB and while it suddnely happaned no heavy process was running... I was just browsing the table with the enterprise manager.
After viewing the processes with sp_lock I found the two processes that caused the locking and killed them, this fixed the problem, but I would like to know what caused it...
When viewing the process info with sp_who the status was "sleeping" and in sp_lock in one process the type was "key" and the status was "wait" (mode "s").
Any ideas ?
I'm using SQL-Server y2k + sp3.
Thanks,
Inon.Mode S means that the 2 processes where doing SELECT with default TIL (READ UNCOMMITTED) while you were trying to UPDATE the table (maybe accidentally you hit a key while browsing the table in EM?) UPDATE is not allowed on a table that has shared lock placed on it.
Wednesday, March 7, 2012
Problem with DTC on Win2K3 server and cluster
I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?
I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?
I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Problem with DTC on Win2K3 server and cluster
I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Problem with DTC on Win2K3 server and cluster
I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
problem with distributed transaction via linked server
hi,
i am trying to connect to oracle 9i using sqlserver 2000 linked server.
here the linked server name is intersql. when i put this statement in begin
and end transaction block and try to execute it is giving the following
error
Server: Msg 7391, Level 16, State 1, Procedure
ihmtproc_emp_assmbly_interface, Line 93
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
INSERT INTO eemp_assembly_interface
(
[drawing_file_nm],
[part_number_txt],
[drawing_status_txt],
[drawing_revision_txt],
[model_file_nm],
[model_title_txt],
[parent_part_number_txt],
[release_status_txt],
[emp_desc],
[created_by_user_id],
[created_on_dt]
)
SELECT drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description,
999999999 as created_by_user_id,
GETDATE() as created_on_dt
FROM OPENQUERY(productcenter,
'SELECT
drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description
FROM EMP1 WHERE FLAG=''N'''
)
please help me
- Thanks
ravi sankar gvsIs it because you are specifying [productcenter] instead of [intersql] as
your linked server name?
Raj Moloye
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:uha5aF2aFHA.1456@.TK2MSFTNGP15.phx.gbl...
> hi,
> i am trying to connect to oracle 9i using sqlserver 2000 linked server.
> here the linked server name is intersql. when i put this statement in
> begin
> and end transaction block and try to execute it is giving the following
> error
> Server: Msg 7391, Level 16, State 1, Procedure
> ihmtproc_emp_assmbly_interface, Line 93
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> INSERT INTO eemp_assembly_interface
> (
> [drawing_file_nm],
> [part_number_txt],
> [drawing_status_txt],
> [drawing_revision_txt],
> [model_file_nm],
> [model_title_txt],
> [parent_part_number_txt],
> [release_status_txt],
> [emp_desc],
> [created_by_user_id],
> [created_on_dt]
> )
> SELECT drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description,
> 999999999 as created_by_user_id,
> GETDATE() as created_on_dt
> FROM OPENQUERY(productcenter,
> 'SELECT
> drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description
> FROM EMP1 WHERE FLAG=''N'''
> )
> please help me
> - Thanks
> ravi sankar gvs
>
i am trying to connect to oracle 9i using sqlserver 2000 linked server.
here the linked server name is intersql. when i put this statement in begin
and end transaction block and try to execute it is giving the following
error
Server: Msg 7391, Level 16, State 1, Procedure
ihmtproc_emp_assmbly_interface, Line 93
The operation could not be performed because the OLE DB provider 'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].
INSERT INTO eemp_assembly_interface
(
[drawing_file_nm],
[part_number_txt],
[drawing_status_txt],
[drawing_revision_txt],
[model_file_nm],
[model_title_txt],
[parent_part_number_txt],
[release_status_txt],
[emp_desc],
[created_by_user_id],
[created_on_dt]
)
SELECT drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description,
999999999 as created_by_user_id,
GETDATE() as created_on_dt
FROM OPENQUERY(productcenter,
'SELECT
drawing_filename,
part_number,
drawing_status,
drawing_revision,
model_filename,
model_title ,
parent_part_number,
release_status,
description
FROM EMP1 WHERE FLAG=''N'''
)
please help me
- Thanks
ravi sankar gvsIs it because you are specifying [productcenter] instead of [intersql] as
your linked server name?
Raj Moloye
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:uha5aF2aFHA.1456@.TK2MSFTNGP15.phx.gbl...
> hi,
> i am trying to connect to oracle 9i using sqlserver 2000 linked server.
> here the linked server name is intersql. when i put this statement in
> begin
> and end transaction block and try to execute it is giving the following
> error
> Server: Msg 7391, Level 16, State 1, Procedure
> ihmtproc_emp_assmbly_interface, Line 93
> The operation could not be performed because the OLE DB provider 'MSDAORA'
> was unable to begin a distributed transaction.
> OLE DB error trace [OLE/DB Provider 'MSDAORA'
> ITransactionJoin::JoinTransaction returned 0x8004d01b].
> INSERT INTO eemp_assembly_interface
> (
> [drawing_file_nm],
> [part_number_txt],
> [drawing_status_txt],
> [drawing_revision_txt],
> [model_file_nm],
> [model_title_txt],
> [parent_part_number_txt],
> [release_status_txt],
> [emp_desc],
> [created_by_user_id],
> [created_on_dt]
> )
> SELECT drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description,
> 999999999 as created_by_user_id,
> GETDATE() as created_on_dt
> FROM OPENQUERY(productcenter,
> 'SELECT
> drawing_filename,
> part_number,
> drawing_status,
> drawing_revision,
> model_filename,
> model_title ,
> parent_part_number,
> release_status,
> description
> FROM EMP1 WHERE FLAG=''N'''
> )
> please help me
> - Thanks
> ravi sankar gvs
>
Problem with delete, lock and transaction log
we have problem with SQL Server 2000 SP3 / Windows 2003 EE
we have 10 huge tables, I have a procedure which deletes data from
these tables each day.
1. The deletes are very slow, it takes 20 min to delete 400,000
records. It does use index when it deletes, i have seen the plan
2. i am running the database in simple mode but still the transaction
log grows bigger and bigger during the delete process. I have used
checkpoint after every table delete but it didnt help. is there a way
we can avoid writing to transaction log so that i can solve this
problem and improve the delete performance too
3. If i stop the job then everything gets rolled back that is been done
by procedure. does it not commit table by table within the procedure?
is it waiting for the procedure to finish before it commits?
Can any one please help me ASAP
Thanks
RaghuAnswer for you questions:-
1. Disable the foreign key if you can do the house keeping after shutdown
the application [ See Alter table command]
2. No you can not avoid to log the transaction. The easy way is shrink the
transaction log fter delete [See DBCC SHRINKFILE]
3. That depends up the way you handle the transaction.. If its a single
delete statement inside the procedure and if you kill the job;
then automatically every thing pertaning to that delete will be rolled back
Thanks
Hari
SQL Server MVP
"raghu" <raghu.burju@.gmail.com> wrote in message
news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>|||Thanks hari for the quick response.
I have no foreign keys on these tables. All i have is indexes to of
them one is for delete based on timestamp and other is for select which
includes 3 more columns.
DBCC SHRINKFILE is only after i do the delete, even before the delete
is finished my 100 GB transaction log is getting filled and stopping
all other application to either insert or delete data.
I have 10 delete statements so this mean that everything that it has
tried till that time is rolledback. I can do some kind of commit which
will clear the log as well as prevent the rollback if any problem
occurs. The begin and end transaction locks the table and wont allow to
make selects till it is commited, i want where selects can work when
delete is going on
Raghu
Hari Prasad wrote:[vbcol=seagreen]
> Answer for you questions:-
> 1. Disable the foreign key if you can do the house keeping after shutdown
> the application [ See Alter table command]
> 2. No you can not avoid to log the transaction. The easy way is shrink the
> transaction log fter delete [See DBCC SHRINKFILE]
> 3. That depends up the way you handle the transaction.. If its a single
> delete statement inside the procedure and if you kill the job;
> then automatically every thing pertaning to that delete will be rolled ba
ck
> Thanks
> Hari
> SQL Server MVP
>
>
> "raghu" <raghu.burju@.gmail.com> wrote in message
> news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...|||raghu wrote:
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>
You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
Books Online entry first, there are some caveats to using this vs. DELETE.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy
Truncate table would clean the entire table. I am just trying to clean
old data that is 15 days old and this is a job which does this each day
Raghu
Tracy McKibben wrote:
> raghu wrote:
> You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
> Books Online entry first, there are some caveats to using this vs. DELETE.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||raghu wrote:
> Tracy
> Truncate table would clean the entire table. I am just trying to clean
> old data that is 15 days old and this is a job which does this each day
>
I guess I missed that in your original post...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I have resolved one problem by splitting the job of delete into 2
different procedures where in the transaction log doesn't grow big now.
The performance of delete is still not improved it does take same
amount of time as mentioned. can anyone have any idea FYI
The deletes are too slow. it takes 20 min to delete 400000 records.
Raghu
raghu wrote:[vbcol=seagreen]
> Thanks hari for the quick response.
> I have no foreign keys on these tables. All i have is indexes to of
> them one is for delete based on timestamp and other is for select which
> includes 3 more columns.
> DBCC SHRINKFILE is only after i do the delete, even before the delete
> is finished my 100 GB transaction log is getting filled and stopping
> all other application to either insert or delete data.
> I have 10 delete statements so this mean that everything that it has
> tried till that time is rolledback. I can do some kind of commit which
> will clear the log as well as prevent the rollback if any problem
> occurs. The begin and end transaction locks the table and wont allow to
> make selects till it is commited, i want where selects can work when
> delete is going on
> Raghu
> Hari Prasad wrote:
we have 10 huge tables, I have a procedure which deletes data from
these tables each day.
1. The deletes are very slow, it takes 20 min to delete 400,000
records. It does use index when it deletes, i have seen the plan
2. i am running the database in simple mode but still the transaction
log grows bigger and bigger during the delete process. I have used
checkpoint after every table delete but it didnt help. is there a way
we can avoid writing to transaction log so that i can solve this
problem and improve the delete performance too
3. If i stop the job then everything gets rolled back that is been done
by procedure. does it not commit table by table within the procedure?
is it waiting for the procedure to finish before it commits?
Can any one please help me ASAP
Thanks
RaghuAnswer for you questions:-
1. Disable the foreign key if you can do the house keeping after shutdown
the application [ See Alter table command]
2. No you can not avoid to log the transaction. The easy way is shrink the
transaction log fter delete [See DBCC SHRINKFILE]
3. That depends up the way you handle the transaction.. If its a single
delete statement inside the procedure and if you kill the job;
then automatically every thing pertaning to that delete will be rolled back
Thanks
Hari
SQL Server MVP
"raghu" <raghu.burju@.gmail.com> wrote in message
news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>|||Thanks hari for the quick response.
I have no foreign keys on these tables. All i have is indexes to of
them one is for delete based on timestamp and other is for select which
includes 3 more columns.
DBCC SHRINKFILE is only after i do the delete, even before the delete
is finished my 100 GB transaction log is getting filled and stopping
all other application to either insert or delete data.
I have 10 delete statements so this mean that everything that it has
tried till that time is rolledback. I can do some kind of commit which
will clear the log as well as prevent the rollback if any problem
occurs. The begin and end transaction locks the table and wont allow to
make selects till it is commited, i want where selects can work when
delete is going on
Raghu
Hari Prasad wrote:[vbcol=seagreen]
> Answer for you questions:-
> 1. Disable the foreign key if you can do the house keeping after shutdown
> the application [ See Alter table command]
> 2. No you can not avoid to log the transaction. The easy way is shrink the
> transaction log fter delete [See DBCC SHRINKFILE]
> 3. That depends up the way you handle the transaction.. If its a single
> delete statement inside the procedure and if you kill the job;
> then automatically every thing pertaning to that delete will be rolled ba
ck
> Thanks
> Hari
> SQL Server MVP
>
>
> "raghu" <raghu.burju@.gmail.com> wrote in message
> news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...|||raghu wrote:
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>
You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
Books Online entry first, there are some caveats to using this vs. DELETE.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy
Truncate table would clean the entire table. I am just trying to clean
old data that is 15 days old and this is a job which does this each day
Raghu
Tracy McKibben wrote:
> raghu wrote:
> You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
> Books Online entry first, there are some caveats to using this vs. DELETE.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||raghu wrote:
> Tracy
> Truncate table would clean the entire table. I am just trying to clean
> old data that is 15 days old and this is a job which does this each day
>
I guess I missed that in your original post...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I have resolved one problem by splitting the job of delete into 2
different procedures where in the transaction log doesn't grow big now.
The performance of delete is still not improved it does take same
amount of time as mentioned. can anyone have any idea FYI
The deletes are too slow. it takes 20 min to delete 400000 records.
Raghu
raghu wrote:[vbcol=seagreen]
> Thanks hari for the quick response.
> I have no foreign keys on these tables. All i have is indexes to of
> them one is for delete based on timestamp and other is for select which
> includes 3 more columns.
> DBCC SHRINKFILE is only after i do the delete, even before the delete
> is finished my 100 GB transaction log is getting filled and stopping
> all other application to either insert or delete data.
> I have 10 delete statements so this mean that everything that it has
> tried till that time is rolledback. I can do some kind of commit which
> will clear the log as well as prevent the rollback if any problem
> occurs. The begin and end transaction locks the table and wont allow to
> make selects till it is commited, i want where selects can work when
> delete is going on
> Raghu
> Hari Prasad wrote:
Saturday, February 25, 2012
Problem with delete, lock and transaction log
we have problem with SQL Server 2000 SP3 / Windows 2003 EE
we have 10 huge tables, I have a procedure which deletes data from
these tables each day.
1. The deletes are very slow, it takes 20 min to delete 400,000
records. It does use index when it deletes, i have seen the plan
2. i am running the database in simple mode but still the transaction
log grows bigger and bigger during the delete process. I have used
checkpoint after every table delete but it didnt help. is there a way
we can avoid writing to transaction log so that i can solve this
problem and improve the delete performance too
3. If i stop the job then everything gets rolled back that is been done
by procedure. does it not commit table by table within the procedure?
is it waiting for the procedure to finish before it commits?
Can any one please help me ASAP
Thanks
RaghuAnswer for you questions:-
1. Disable the foreign key if you can do the house keeping after shutdown
the application [ See Alter table command]
2. No you can not avoid to log the transaction. The easy way is shrink the
transaction log fter delete [See DBCC SHRINKFILE]
3. That depends up the way you handle the transaction.. If its a single
delete statement inside the procedure and if you kill the job;
then automatically every thing pertaning to that delete will be rolled back
Thanks
Hari
SQL Server MVP
"raghu" <raghu.burju@.gmail.com> wrote in message
news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>|||Thanks hari for the quick response.
I have no foreign keys on these tables. All i have is indexes to of
them one is for delete based on timestamp and other is for select which
includes 3 more columns.
DBCC SHRINKFILE is only after i do the delete, even before the delete
is finished my 100 GB transaction log is getting filled and stopping
all other application to either insert or delete data.
I have 10 delete statements so this mean that everything that it has
tried till that time is rolledback. I can do some kind of commit which
will clear the log as well as prevent the rollback if any problem
occurs. The begin and end transaction locks the table and wont allow to
make selects till it is commited, i want where selects can work when
delete is going on
Raghu
Hari Prasad wrote:
> Answer for you questions:-
> 1. Disable the foreign key if you can do the house keeping after shutdown
> the application [ See Alter table command]
> 2. No you can not avoid to log the transaction. The easy way is shrink the
> transaction log fter delete [See DBCC SHRINKFILE]
> 3. That depends up the way you handle the transaction.. If its a single
> delete statement inside the procedure and if you kill the job;
> then automatically every thing pertaning to that delete will be rolled back
> Thanks
> Hari
> SQL Server MVP
>
>
> "raghu" <raghu.burju@.gmail.com> wrote in message
> news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > we have 10 huge tables, I have a procedure which deletes data from
> > these tables each day.
> >
> > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > records. It does use index when it deletes, i have seen the plan
> >
> > 2. i am running the database in simple mode but still the transaction
> > log grows bigger and bigger during the delete process. I have used
> > checkpoint after every table delete but it didnt help. is there a way
> > we can avoid writing to transaction log so that i can solve this
> > problem and improve the delete performance too
> >
> > 3. If i stop the job then everything gets rolled back that is been done
> > by procedure. does it not commit table by table within the procedure?
> > is it waiting for the procedure to finish before it commits?
> >
> > Can any one please help me ASAP
> >
> > Thanks
> > Raghu
> >|||raghu wrote:
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>
You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
Books Online entry first, there are some caveats to using this vs. DELETE.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy
Truncate table would clean the entire table. I am just trying to clean
old data that is 15 days old and this is a job which does this each day
Raghu
Tracy McKibben wrote:
> raghu wrote:
> > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > we have 10 huge tables, I have a procedure which deletes data from
> > these tables each day.
> >
> > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > records. It does use index when it deletes, i have seen the plan
> >
> > 2. i am running the database in simple mode but still the transaction
> > log grows bigger and bigger during the delete process. I have used
> > checkpoint after every table delete but it didnt help. is there a way
> > we can avoid writing to transaction log so that i can solve this
> > problem and improve the delete performance too
> >
> > 3. If i stop the job then everything gets rolled back that is been done
> > by procedure. does it not commit table by table within the procedure?
> > is it waiting for the procedure to finish before it commits?
> >
> > Can any one please help me ASAP
> >
> > Thanks
> > Raghu
> >
> You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
> Books Online entry first, there are some caveats to using this vs. DELETE.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||raghu wrote:
> Tracy
> Truncate table would clean the entire table. I am just trying to clean
> old data that is 15 days old and this is a job which does this each day
>
I guess I missed that in your original post...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I have resolved one problem by splitting the job of delete into 2
different procedures where in the transaction log doesn't grow big now.
The performance of delete is still not improved it does take same
amount of time as mentioned. can anyone have any idea FYI
The deletes are too slow. it takes 20 min to delete 400000 records.
Raghu
raghu wrote:
> Thanks hari for the quick response.
> I have no foreign keys on these tables. All i have is indexes to of
> them one is for delete based on timestamp and other is for select which
> includes 3 more columns.
> DBCC SHRINKFILE is only after i do the delete, even before the delete
> is finished my 100 GB transaction log is getting filled and stopping
> all other application to either insert or delete data.
> I have 10 delete statements so this mean that everything that it has
> tried till that time is rolledback. I can do some kind of commit which
> will clear the log as well as prevent the rollback if any problem
> occurs. The begin and end transaction locks the table and wont allow to
> make selects till it is commited, i want where selects can work when
> delete is going on
> Raghu
> Hari Prasad wrote:
> > Answer for you questions:-
> >
> > 1. Disable the foreign key if you can do the house keeping after shutdown
> > the application [ See Alter table command]
> >
> > 2. No you can not avoid to log the transaction. The easy way is shrink the
> > transaction log fter delete [See DBCC SHRINKFILE]
> >
> > 3. That depends up the way you handle the transaction.. If its a single
> > delete statement inside the procedure and if you kill the job;
> > then automatically every thing pertaning to that delete will be rolled back
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> >
> >
> >
> > "raghu" <raghu.burju@.gmail.com> wrote in message
> > news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> > > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > > we have 10 huge tables, I have a procedure which deletes data from
> > > these tables each day.
> > >
> > > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > > records. It does use index when it deletes, i have seen the plan
> > >
> > > 2. i am running the database in simple mode but still the transaction
> > > log grows bigger and bigger during the delete process. I have used
> > > checkpoint after every table delete but it didnt help. is there a way
> > > we can avoid writing to transaction log so that i can solve this
> > > problem and improve the delete performance too
> > >
> > > 3. If i stop the job then everything gets rolled back that is been done
> > > by procedure. does it not commit table by table within the procedure?
> > > is it waiting for the procedure to finish before it commits?
> > >
> > > Can any one please help me ASAP
> > >
> > > Thanks
> > > Raghu
> > >
we have 10 huge tables, I have a procedure which deletes data from
these tables each day.
1. The deletes are very slow, it takes 20 min to delete 400,000
records. It does use index when it deletes, i have seen the plan
2. i am running the database in simple mode but still the transaction
log grows bigger and bigger during the delete process. I have used
checkpoint after every table delete but it didnt help. is there a way
we can avoid writing to transaction log so that i can solve this
problem and improve the delete performance too
3. If i stop the job then everything gets rolled back that is been done
by procedure. does it not commit table by table within the procedure?
is it waiting for the procedure to finish before it commits?
Can any one please help me ASAP
Thanks
RaghuAnswer for you questions:-
1. Disable the foreign key if you can do the house keeping after shutdown
the application [ See Alter table command]
2. No you can not avoid to log the transaction. The easy way is shrink the
transaction log fter delete [See DBCC SHRINKFILE]
3. That depends up the way you handle the transaction.. If its a single
delete statement inside the procedure and if you kill the job;
then automatically every thing pertaning to that delete will be rolled back
Thanks
Hari
SQL Server MVP
"raghu" <raghu.burju@.gmail.com> wrote in message
news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>|||Thanks hari for the quick response.
I have no foreign keys on these tables. All i have is indexes to of
them one is for delete based on timestamp and other is for select which
includes 3 more columns.
DBCC SHRINKFILE is only after i do the delete, even before the delete
is finished my 100 GB transaction log is getting filled and stopping
all other application to either insert or delete data.
I have 10 delete statements so this mean that everything that it has
tried till that time is rolledback. I can do some kind of commit which
will clear the log as well as prevent the rollback if any problem
occurs. The begin and end transaction locks the table and wont allow to
make selects till it is commited, i want where selects can work when
delete is going on
Raghu
Hari Prasad wrote:
> Answer for you questions:-
> 1. Disable the foreign key if you can do the house keeping after shutdown
> the application [ See Alter table command]
> 2. No you can not avoid to log the transaction. The easy way is shrink the
> transaction log fter delete [See DBCC SHRINKFILE]
> 3. That depends up the way you handle the transaction.. If its a single
> delete statement inside the procedure and if you kill the job;
> then automatically every thing pertaning to that delete will be rolled back
> Thanks
> Hari
> SQL Server MVP
>
>
> "raghu" <raghu.burju@.gmail.com> wrote in message
> news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > we have 10 huge tables, I have a procedure which deletes data from
> > these tables each day.
> >
> > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > records. It does use index when it deletes, i have seen the plan
> >
> > 2. i am running the database in simple mode but still the transaction
> > log grows bigger and bigger during the delete process. I have used
> > checkpoint after every table delete but it didnt help. is there a way
> > we can avoid writing to transaction log so that i can solve this
> > problem and improve the delete performance too
> >
> > 3. If i stop the job then everything gets rolled back that is been done
> > by procedure. does it not commit table by table within the procedure?
> > is it waiting for the procedure to finish before it commits?
> >
> > Can any one please help me ASAP
> >
> > Thanks
> > Raghu
> >|||raghu wrote:
> we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> we have 10 huge tables, I have a procedure which deletes data from
> these tables each day.
> 1. The deletes are very slow, it takes 20 min to delete 400,000
> records. It does use index when it deletes, i have seen the plan
> 2. i am running the database in simple mode but still the transaction
> log grows bigger and bigger during the delete process. I have used
> checkpoint after every table delete but it didnt help. is there a way
> we can avoid writing to transaction log so that i can solve this
> problem and improve the delete performance too
> 3. If i stop the job then everything gets rolled back that is been done
> by procedure. does it not commit table by table within the procedure?
> is it waiting for the procedure to finish before it commits?
> Can any one please help me ASAP
> Thanks
> Raghu
>
You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
Books Online entry first, there are some caveats to using this vs. DELETE.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy
Truncate table would clean the entire table. I am just trying to clean
old data that is 15 days old and this is a job which does this each day
Raghu
Tracy McKibben wrote:
> raghu wrote:
> > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > we have 10 huge tables, I have a procedure which deletes data from
> > these tables each day.
> >
> > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > records. It does use index when it deletes, i have seen the plan
> >
> > 2. i am running the database in simple mode but still the transaction
> > log grows bigger and bigger during the delete process. I have used
> > checkpoint after every table delete but it didnt help. is there a way
> > we can avoid writing to transaction log so that i can solve this
> > problem and improve the delete performance too
> >
> > 3. If i stop the job then everything gets rolled back that is been done
> > by procedure. does it not commit table by table within the procedure?
> > is it waiting for the procedure to finish before it commits?
> >
> > Can any one please help me ASAP
> >
> > Thanks
> > Raghu
> >
> You could use TRUNCATE TABLE, which is non-logged. Be sure to read the
> Books Online entry first, there are some caveats to using this vs. DELETE.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||raghu wrote:
> Tracy
> Truncate table would clean the entire table. I am just trying to clean
> old data that is 15 days old and this is a job which does this each day
>
I guess I missed that in your original post...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I have resolved one problem by splitting the job of delete into 2
different procedures where in the transaction log doesn't grow big now.
The performance of delete is still not improved it does take same
amount of time as mentioned. can anyone have any idea FYI
The deletes are too slow. it takes 20 min to delete 400000 records.
Raghu
raghu wrote:
> Thanks hari for the quick response.
> I have no foreign keys on these tables. All i have is indexes to of
> them one is for delete based on timestamp and other is for select which
> includes 3 more columns.
> DBCC SHRINKFILE is only after i do the delete, even before the delete
> is finished my 100 GB transaction log is getting filled and stopping
> all other application to either insert or delete data.
> I have 10 delete statements so this mean that everything that it has
> tried till that time is rolledback. I can do some kind of commit which
> will clear the log as well as prevent the rollback if any problem
> occurs. The begin and end transaction locks the table and wont allow to
> make selects till it is commited, i want where selects can work when
> delete is going on
> Raghu
> Hari Prasad wrote:
> > Answer for you questions:-
> >
> > 1. Disable the foreign key if you can do the house keeping after shutdown
> > the application [ See Alter table command]
> >
> > 2. No you can not avoid to log the transaction. The easy way is shrink the
> > transaction log fter delete [See DBCC SHRINKFILE]
> >
> > 3. That depends up the way you handle the transaction.. If its a single
> > delete statement inside the procedure and if you kill the job;
> > then automatically every thing pertaning to that delete will be rolled back
> >
> > Thanks
> > Hari
> > SQL Server MVP
> >
> >
> >
> >
> > "raghu" <raghu.burju@.gmail.com> wrote in message
> > news:1154641875.483366.120510@.75g2000cwc.googlegroups.com...
> > > we have problem with SQL Server 2000 SP3 / Windows 2003 EE
> > > we have 10 huge tables, I have a procedure which deletes data from
> > > these tables each day.
> > >
> > > 1. The deletes are very slow, it takes 20 min to delete 400,000
> > > records. It does use index when it deletes, i have seen the plan
> > >
> > > 2. i am running the database in simple mode but still the transaction
> > > log grows bigger and bigger during the delete process. I have used
> > > checkpoint after every table delete but it didnt help. is there a way
> > > we can avoid writing to transaction log so that i can solve this
> > > problem and improve the delete performance too
> > >
> > > 3. If i stop the job then everything gets rolled back that is been done
> > > by procedure. does it not commit table by table within the procedure?
> > > is it waiting for the procedure to finish before it commits?
> > >
> > > Can any one please help me ASAP
> > >
> > > Thanks
> > > Raghu
> > >
Problem with dead-locks
We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net applicationlayer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every w
. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Findes din kiosk p nettet? Se http://ekiosk.dkHave you followed the basic recommendations on deadlocks in Books Online?
Start here, if you haven't:
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
Tracing deadlocks might also be much help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
ML|||I have two suggestions. (1) change the mechanism so that the deletes are
committed prior to starting the bulk inserts, or (2) generate a temporary
stored procedure or dynamic SQL to execute the entire job in a single batch,
complete with error handling, etc.
You could save off the rows your deleting into a separate database, and
reinsert them if a failure occurs during the bulk inserts.
My preference is to migrate transaction processing to the data tier whenever
possible. It's a lot easier to modify a stored procedure than to recompile
and redeploy a middle-tier component or God forbid, a client application.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97105AA34EC40stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net applicationlayer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every w. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
> Findes din kiosk p nettet? Se http://ekiosk.dk|||"Brian Selzer" <brian@.selzer-software.com> wrote in
news:epOZG6w6FHA.3416@.TK2MSFTNGP15.phx.gbl:
> I have two suggestions. (1) change the mechanism so that the deletes
> are committed prior to starting the bulk inserts, or (2) generate a
> temporary stored procedure or dynamic SQL to execute the entire job in
> a single batch, complete with error handling, etc.
I will look into this, thanks.
> My preference is to migrate transaction processing to the data tier
> whenever possible. It's a lot easier to modify a stored procedure
> than to recompile and redeploy a middle-tier component or God forbid,
> a client application.
We have since my first post moved the SQL to the database in a stored
procedure that creates some dynamic sql and executes it, so it is no longer
in the application layer. The problem, however, did not go away.
Also, we cannot do it all in the application layer. The entire process is a
ETL-process, where the data is (heavily) transformed before being loaded
into the (other) database. It is not possible to put some of the key parts
in the database-layer - for various purposes we need to do the
transformation in the application layer.
I talked to one of our DBAs, and he suggested that another benefit of
moving the sql to a stored procedure would be that it would complete the
current batch after each "GO"-statement at the end of executing the stored
procedure. We moved the SQL-code and the problem seems to have dissapeared
... for now at least. We are keeping our fingers crossed. If this works, we
are happy ... a bit nervous that we didn't find the cause of the error ...
but happy, none the less.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Sql server 2k from a .Net applicationlayer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every w
. The externalsystem cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Findes din kiosk p nettet? Se http://ekiosk.dkHave you followed the basic recommendations on deadlocks in Books Online?
Start here, if you haven't:
http://msdn.microsoft.com/library/d... />
a_3hdf.asp
Tracing deadlocks might also be much help:
http://msdn.microsoft.com/library/d...
tabse_5xrn.asp
ML|||I have two suggestions. (1) change the mechanism so that the deletes are
committed prior to starting the bulk inserts, or (2) generate a temporary
stored procedure or dynamic SQL to execute the entire job in a single batch,
complete with error handling, etc.
You could save off the rows your deleting into a separate database, and
reinsert them if a failure occurs during the bulk inserts.
My preference is to migrate transaction processing to the data tier whenever
possible. It's a lot easier to modify a stored procedure than to recompile
and redeploy a middle-tier component or God forbid, a client application.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97105AA34EC40stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net applicationlayer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every w
. The external> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
> Findes din kiosk p nettet? Se http://ekiosk.dk|||"Brian Selzer" <brian@.selzer-software.com> wrote in
news:epOZG6w6FHA.3416@.TK2MSFTNGP15.phx.gbl:
> I have two suggestions. (1) change the mechanism so that the deletes
> are committed prior to starting the bulk inserts, or (2) generate a
> temporary stored procedure or dynamic SQL to execute the entire job in
> a single batch, complete with error handling, etc.
I will look into this, thanks.
> My preference is to migrate transaction processing to the data tier
> whenever possible. It's a lot easier to modify a stored procedure
> than to recompile and redeploy a middle-tier component or God forbid,
> a client application.
We have since my first post moved the SQL to the database in a stored
procedure that creates some dynamic sql and executes it, so it is no longer
in the application layer. The problem, however, did not go away.
Also, we cannot do it all in the application layer. The entire process is a
ETL-process, where the data is (heavily) transformed before being loaded
into the (other) database. It is not possible to put some of the key parts
in the database-layer - for various purposes we need to do the
transformation in the application layer.
I talked to one of our DBAs, and he suggested that another benefit of
moving the sql to a stored procedure would be that it would complete the
current batch after each "GO"-statement at the end of executing the stored
procedure. We moved the SQL-code and the problem seems to have dissapeared
... for now at least. We are keeping our fingers crossed. If this works, we
are happy ... a bit nervous that we didn't find the cause of the error ...
but happy, none the less.
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Problem with dead-locks
We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Please turn on TF-1204 and see what is getting deadlocked.
thanks
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_03 06_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = '')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_03 06_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
Please turn on TF-1204 and see what is getting deadlocked.
thanks
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe02005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe02005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe02005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_03 06_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = '')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> Cost
0/0)> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> Cost
0/0)> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_03 06_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>
|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Problem with dead-locks
We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 8
6.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifyi
ng cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt
:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK IN
SERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT T
AB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306
_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar',
FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schem
a-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was f
ound.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0
> Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanC
nt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306_TAB_RENS_TILLADELSE.t
ab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec0x2D8EB570) Value:0x4a2eafe0 Cost0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, s they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, s they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID)
86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 8
6.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe02005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe02005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe02005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifyi
ng cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode:
Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt
:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK IN
SERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT T
AB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306
_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar',
FIRSTROW = 1, KEEPIDENTITY
, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schem
a-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-
Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was f
ound.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> Cost
0/0)> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0> Cost
0/0)> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanC
nt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\000
0023764_0306_TAB_RENS_TILLADELSE.t
ab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec
0x2D8EB570) Value:0x4a2eafe0 Cost0/0)> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
Jesper Stocholm
http://stocholm.dk
Findes din kiosk p nettet? Se http://ekiosk.dk
Problem with dead-locks
We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, så they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, så they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>> Please turn on TF-1204 and see what is getting deadlocked.
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, så they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, så they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>> Please turn on TF-1204 and see what is getting deadlocked.
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk
Monday, February 20, 2012
Problem with date dimension
One of my tables contains the transaction dates. So based on this table
I tried to create a Time dimension (using the first option - I think
it's called single table or something in the Wizard of Analysis
Manager)...
I select everything else correctly and selected the Year, Quarter, Month
option and finally saved he dimension.
However when I browse the dimension, when I do the first click I get a
level with '0' as the year and blank child nodes for quarter and
months...
Any clues?
I'll appreciate help.
Thanks.
Hi,
first of all, my suggestion is do not use AS Wizard for creating Time
dimension.
Read a very good article that Tom Chester wrote about Time dimensions.
http://www.sqljunkies.com/Article/D1...D60951395.scuk
Hope it helps!
Peace,
Andrej
"Learner" <wantnospam@.email.com> wrote in message
news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
> One of my tables contains the transaction dates. So based on this table
> I tried to create a Time dimension (using the first option - I think
> it's called single table or something in the Wizard of Analysis
> Manager)...
>
> I select everything else correctly and selected the Year, Quarter, Month
> option and finally saved he dimension.
> However when I browse the dimension, when I do the first click I get a
> level with '0' as the year and blank child nodes for quarter and
> months...
> Any clues?
> I'll appreciate help.
> Thanks.
|||Great Article!!! Thanks
Regards.
> Hi,
> first of all, my suggestion is do not use AS Wizard for creating Time
> dimension.
> Read a very good article that Tom Chester wrote about Time dimensions.
> http://www.sqljunkies.com/Article/D1...D60951395.scuk
>
> Hope it helps!
> Peace,
> Andrej
>
> "Learner" <wantnospam@.email.com> wrote in message
> news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
>
>
I tried to create a Time dimension (using the first option - I think
it's called single table or something in the Wizard of Analysis
Manager)...
I select everything else correctly and selected the Year, Quarter, Month
option and finally saved he dimension.
However when I browse the dimension, when I do the first click I get a
level with '0' as the year and blank child nodes for quarter and
months...
Any clues?
I'll appreciate help.
Thanks.
Hi,
first of all, my suggestion is do not use AS Wizard for creating Time
dimension.
Read a very good article that Tom Chester wrote about Time dimensions.
http://www.sqljunkies.com/Article/D1...D60951395.scuk
Hope it helps!
Peace,
Andrej
"Learner" <wantnospam@.email.com> wrote in message
news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
> One of my tables contains the transaction dates. So based on this table
> I tried to create a Time dimension (using the first option - I think
> it's called single table or something in the Wizard of Analysis
> Manager)...
>
> I select everything else correctly and selected the Year, Quarter, Month
> option and finally saved he dimension.
> However when I browse the dimension, when I do the first click I get a
> level with '0' as the year and blank child nodes for quarter and
> months...
> Any clues?
> I'll appreciate help.
> Thanks.
|||Great Article!!! Thanks
Regards.
> Hi,
> first of all, my suggestion is do not use AS Wizard for creating Time
> dimension.
> Read a very good article that Tom Chester wrote about Time dimensions.
> http://www.sqljunkies.com/Article/D1...D60951395.scuk
>
> Hope it helps!
> Peace,
> Andrej
>
> "Learner" <wantnospam@.email.com> wrote in message
> news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
>
>
Problem with date dimension
One of my tables contains the transaction dates. So based on this table
I tried to create a Time dimension (using the first option - I think
it's called single table or something in the Wizard of Analysis
Manager)...
I select everything else correctly and selected the Year, Quarter, Month
option and finally saved he dimension.
However when I browse the dimension, when I do the first click I get a
level with '0' as the year and blank child nodes for quarter and
months...
Any clues?
I'll appreciate help.
Thanks.Hi,
first of all, my suggestion is do not use AS Wizard for creating Time
dimension.
Read a very good article that Tom Chester wrote about Time dimensions.
http://www.sqljunkies.com/Article/D...0D60951395.scuk
Hope it helps!
Peace,
Andrej
"Learner" <wantnospam@.email.com> wrote in message
news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
> One of my tables contains the transaction dates. So based on this table
> I tried to create a Time dimension (using the first option - I think
> it's called single table or something in the Wizard of Analysis
> Manager)...
>
> I select everything else correctly and selected the Year, Quarter, Month
> option and finally saved he dimension.
> However when I browse the dimension, when I do the first click I get a
> level with '0' as the year and blank child nodes for quarter and
> months...
> Any clues?
> I'll appreciate help.
> Thanks.|||Great Article!!! Thanks
Regards.
> Hi,
> first of all, my suggestion is do not use AS Wizard for creating Time
> dimension.
> Read a very good article that Tom Chester wrote about Time dimensions.
> [url]http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk[/url
]
>
> Hope it helps!
> Peace,
> Andrej
>
> "Learner" <wantnospam@.email.com> wrote in message
> news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
>
>
I tried to create a Time dimension (using the first option - I think
it's called single table or something in the Wizard of Analysis
Manager)...
I select everything else correctly and selected the Year, Quarter, Month
option and finally saved he dimension.
However when I browse the dimension, when I do the first click I get a
level with '0' as the year and blank child nodes for quarter and
months...
Any clues?
I'll appreciate help.
Thanks.Hi,
first of all, my suggestion is do not use AS Wizard for creating Time
dimension.
Read a very good article that Tom Chester wrote about Time dimensions.
http://www.sqljunkies.com/Article/D...0D60951395.scuk
Hope it helps!
Peace,
Andrej
"Learner" <wantnospam@.email.com> wrote in message
news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
> One of my tables contains the transaction dates. So based on this table
> I tried to create a Time dimension (using the first option - I think
> it's called single table or something in the Wizard of Analysis
> Manager)...
>
> I select everything else correctly and selected the Year, Quarter, Month
> option and finally saved he dimension.
> However when I browse the dimension, when I do the first click I get a
> level with '0' as the year and blank child nodes for quarter and
> months...
> Any clues?
> I'll appreciate help.
> Thanks.|||Great Article!!! Thanks
Regards.
> Hi,
> first of all, my suggestion is do not use AS Wizard for creating Time
> dimension.
> Read a very good article that Tom Chester wrote about Time dimensions.
> [url]http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk[/url
]
>
> Hope it helps!
> Peace,
> Andrej
>
> "Learner" <wantnospam@.email.com> wrote in message
> news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
>
>
Subscribe to:
Posts (Atom)