Commit, select or update duration time vary from short to long

Hi
I have a statement that normally takes a short time but sometimes
takes long. I think I have isolated the problem to being variations in
the time it takes to commit.
I have constructed a setup that somehow show my problem.
First execute the following script (45 lines):
use pubs
/****** Object: Stored Procedure dbo.spTestCommit Script Date:
16-05-2005 08:20:27 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTestCommit]
GO
/****** Object: StoredProcedure [dbo].[spTestCommit] Script Date
:
05/16/2007 08:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTestCommit]
@.deltaValue int,
@.titleId varchar(50)
AS
declare @.tranCount int
select @.tranCount = @.@.trancount
if (@.tranCount = 0) begin
tran spTran
else begin
save tran spTran
end
UPDATE titles
SET ytd_sales = ytd_sales + @.deltaValue
WHERE title_id = @.titleId
if (@.tranCount = 0)
begin
commit tran
end
return 0
errorHandler:
rollback tran spTran
return 1
GO
Then start a trace enabling Execution plan and execute the statement.
I use the following:
exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
(This will decrease and then increase some int value in the
pubs..titles table for a specific record)
Now my point is that when I execute those 2 lines, then they have a
duration of 10 or 20 ms most of the time. But all of the sudden I see
an entry with a duration of 210 ms.
Why does it take so much longer when that is the only thing I execute
on that entire database?!?
The output from my trace for the 210 ms run is:
Execution Plan Execution Tree
--
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]),
DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[
;titles].
[title_id]=[@.titleId]))
SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
Execution Plan Execution Tree
--
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]),
DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[
;titles].
[title_id]=[@.titleId]))
SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2
007-05-16
13:11:23.553 2007-05-16 13:11:23.763
In my real world setup I have a stored procedure that normally takes
0-20 ms but have been seen taking 30000 ms or even more... I would
very much apreciate some hints as to why it vary so much...
Thanks
Resist> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
SQL Server must wait until the COMMIT log write is physically complete
before completing the statement. If a CHECKPOINT is writing lots of data
and data and log files are on the same physical disk, this can prolong the
COMMIT statement duration. This is one reason why it's a Best Practice to
place data and log on different disks. You can monitor checkpoints in
perfmon to see the correlation.

> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
30+ seconds is excessive and probably due to a different reason, assuming
your I/O subsystem is adequately sized for your workload. A more likely
cause is blocking. Run sp_who at the time of the problem to see if that's
the case. An occasional long-running query or transaction may be the
culprit.
Hope this helps.
Dan Guzman
SQL Server MVP
"resist" <dba@.godhund.dk> wrote in message
news:1179315894.009631.298820@.y80g2000hsf.googlegroups.com...
> Hi
> I have a statement that normally takes a short time but sometimes
> takes long. I think I have isolated the problem to being variations in
> the time it takes to commit.
> I have constructed a setup that somehow show my problem.
> First execute the following script (45 lines):
> use pubs
> /****** Object: Stored Procedure dbo.spTestCommit Script Date:
> 16-05-2005 08:20:27 ******/
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
> [spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[spTestCommit]
> GO
> /****** Object: StoredProcedure [dbo].[spTestCommit] Script Da
te:
> 05/16/2007 08:32:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[spTestCommit]
> @.deltaValue int,
> @.titleId varchar(50)
>
> AS
> declare @.tranCount int
> select @.tranCount = @.@.trancount
> if (@.tranCount = 0) begin
> tran spTran
> else begin
> save tran spTran
> end
> UPDATE titles
> SET ytd_sales = ytd_sales + @.deltaValue
> WHERE title_id = @.titleId
>
> if (@.tranCount = 0)
> begin
> commit tran
> end
> return 0
> errorHandler:
> rollback tran spTran
> return 1
> GO
>
>
> Then start a trace enabling Execution plan and execute the statement.
> I use the following:
> exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> (This will decrease and then increase some int value in the
> pubs..titles table for a specific record)
>
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
> The output from my trace for the 210 ms run is:
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004])
, DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE&#
91;titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004])
, DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE&#
91;titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
> SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2007-05-16
> 13:11:23.553 2007-05-16 13:11:23.763
>
> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
> Thanks
> Resist
>

Commit, select or update duration time vary from short to long

Hi
I have a statement that normally takes a short time but sometimes
takes long. I think I have isolated the problem to being variations in
the time it takes to commit.
I have constructed a setup that somehow show my problem.
First execute the following script (45 lines):
use pubs
/****** Object: Stored Procedure dbo.spTestCommit Script Date:
16-05-2005 08:20:27 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTestCommit]
GO
/****** Object: StoredProcedure [dbo].[spTestCommit] Script Date:
05/16/2007 08:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTestCommit]
@.deltaValue int,
@.titleId varchar(50)
AS
declare @.tranCount int
select @.tranCount = @.@.trancount
if (@.tranCount = 0) begin
tran spTran
else begin
save tran spTran
end
UPDATE titles
SET ytd_sales = ytd_sales + @.deltaValue
WHERE title_id = @.titleId
if (@.tranCount = 0)
begin
commit tran
end
return 0
errorHandler:
rollback tran spTran
return 1
GO
Then start a trace enabling Execution plan and execute the statement.
I use the following:
exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
(This will decrease and then increase some int value in the
pubs..titles table for a specific record)
Now my point is that when I execute those 2 lines, then they have a
duration of 10 or 20 ms most of the time. But all of the sudden I see
an entry with a duration of 210 ms.
Why does it take so much longer when that is the only thing I execute
on that entire database?!?
The output from my trace for the 210 ms run is:
Execution PlanExecution Tree
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
[title_id]=[@.titleId]))
SQL Query AnalyzerusrPC\usr2756552007-05-16 13:11:23.553
Execution PlanExecution Tree
Clustered Index Update(OBJECT[pubs].[dbo].[titles].
[UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
[title_id]=[@.titleId]))
SQL Query AnalyzerusrPC\usr2756552007-05-16 13:11:23.703
SQL:BatchCompletedexec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
SQL Query AnalyzerusrPC\usr01802102756552007-05-16
13:11:23.5532007-05-16 13:11:23.763
In my real world setup I have a stored procedure that normally takes
0-20 ms but have been seen taking 30000 ms or even more... I would
very much apreciate some hints as to why it vary so much...
Thanks
Resist
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
SQL Server must wait until the COMMIT log write is physically complete
before completing the statement. If a CHECKPOINT is writing lots of data
and data and log files are on the same physical disk, this can prolong the
COMMIT statement duration. This is one reason why it's a Best Practice to
place data and log on different disks. You can monitor checkpoints in
perfmon to see the correlation.

> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
30+ seconds is excessive and probably due to a different reason, assuming
your I/O subsystem is adequately sized for your workload. A more likely
cause is blocking. Run sp_who at the time of the problem to see if that's
the case. An occasional long-running query or transaction may be the
culprit.
Hope this helps.
Dan Guzman
SQL Server MVP
"resist" <dba@.godhund.dk> wrote in message
news:1179315894.009631.298820@.y80g2000hsf.googlegr oups.com...
> Hi
> I have a statement that normally takes a short time but sometimes
> takes long. I think I have isolated the problem to being variations in
> the time it takes to commit.
> I have constructed a setup that somehow show my problem.
> First execute the following script (45 lines):
> use pubs
> /****** Object: Stored Procedure dbo.spTestCommit Script Date:
> 16-05-2005 08:20:27 ******/
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
> [spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[spTestCommit]
> GO
> /****** Object: StoredProcedure [dbo].[spTestCommit] Script Date:
> 05/16/2007 08:32:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[spTestCommit]
> @.deltaValue int,
> @.titleId varchar(50)
>
> AS
> declare @.tranCount int
> select @.tranCount = @.@.trancount
> if (@.tranCount = 0) begin
> tran spTran
> else begin
> save tran spTran
> end
> UPDATE titles
> SET ytd_sales = ytd_sales + @.deltaValue
> WHERE title_id = @.titleId
>
> if (@.tranCount = 0)
> begin
> commit tran
> end
> return 0
> errorHandler:
> rollback tran spTran
> return 1
> GO
>
>
> Then start a trace enabling Execution plan and execute the statement.
> I use the following:
> exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> (This will decrease and then increase some int value in the
> pubs..titles table for a specific record)
>
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
> The output from my trace for the 210 ms run is:
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT[pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET[titles].[ytd_sales]=[Expr1004]), DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE[titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
> SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2007-05-16
> 13:11:23.553 2007-05-16 13:11:23.763
>
> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
> Thanks
> Resist
>

Commit, select or update duration time vary from short to long

Hi
I have a statement that normally takes a short time but sometimes
takes long. I think I have isolated the problem to being variations in
the time it takes to commit.
I have constructed a setup that somehow show my problem.
First execute the following script (45 lines):
use pubs
/****** Object: Stored Procedure dbo.spTestCommit Script Date:
16-05-2005 08:20:27 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spTestCommit]
GO
/****** Object: StoredProcedure [dbo].[spTestCommit] Script Date:
05/16/2007 08:32:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spTestCommit]
@.deltaValue int,
@.titleId varchar(50)
AS
declare @.tranCount int
select @.tranCount = @.@.trancount
if (@.tranCount = 0) begin
tran spTran
else begin
save tran spTran
end
UPDATE titles
SET ytd_sales = ytd_sales + @.deltaValue
WHERE title_id = @.titleId
if (@.tranCount = 0)
begin
commit tran
end
return 0
errorHandler:
rollback tran spTran
return 1
GO
Then start a trace enabling Execution plan and execute the statement.
I use the following:
exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
(This will decrease and then increase some int value in the
pubs..titles table for a specific record)
Now my point is that when I execute those 2 lines, then they have a
duration of 10 or 20 ms most of the time. But all of the sudden I see
an entry with a duration of 210 ms.
Why does it take so much longer when that is the only thing I execute
on that entire database?!?
The output from my trace for the 210 ms run is:
Execution Plan Execution Tree
--
Clustered Index Update(OBJECT:([pubs].[dbo].[titles].
[UPKCL_titleidind]), SET:([titles].[ytd_sales]=[Expr1004]), DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE:([titles].
[title_id]=[@.titleId]))
SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
Execution Plan Execution Tree
--
Clustered Index Update(OBJECT:([pubs].[dbo].[titles].
[UPKCL_titleidind]), SET:([titles].[ytd_sales]=[Expr1004]), DEFINE:
([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE:([titles].
[title_id]=[@.titleId]))
SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
exec spTestCommit -1, 'BU1032'
SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2007-05-16
13:11:23.553 2007-05-16 13:11:23.763
In my real world setup I have a stored procedure that normally takes
0-20 ms but have been seen taking 30000 ms or even more... I would
very much apreciate some hints as to why it vary so much...
Thanks
Resist> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
SQL Server must wait until the COMMIT log write is physically complete
before completing the statement. If a CHECKPOINT is writing lots of data
and data and log files are on the same physical disk, this can prolong the
COMMIT statement duration. This is one reason why it's a Best Practice to
place data and log on different disks. You can monitor checkpoints in
perfmon to see the correlation.
> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
30+ seconds is excessive and probably due to a different reason, assuming
your I/O subsystem is adequately sized for your workload. A more likely
cause is blocking. Run sp_who at the time of the problem to see if that's
the case. An occasional long-running query or transaction may be the
culprit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"resist" <dba@.godhund.dk> wrote in message
news:1179315894.009631.298820@.y80g2000hsf.googlegroups.com...
> Hi
> I have a statement that normally takes a short time but sometimes
> takes long. I think I have isolated the problem to being variations in
> the time it takes to commit.
> I have constructed a setup that somehow show my problem.
> First execute the following script (45 lines):
> use pubs
> /****** Object: Stored Procedure dbo.spTestCommit Script Date:
> 16-05-2005 08:20:27 ******/
> if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
> [spTestCommit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[spTestCommit]
> GO
> /****** Object: StoredProcedure [dbo].[spTestCommit] Script Date:
> 05/16/2007 08:32:43 ******/
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[spTestCommit]
> @.deltaValue int,
> @.titleId varchar(50)
>
> AS
> declare @.tranCount int
> select @.tranCount = @.@.trancount
> if (@.tranCount = 0) begin
> tran spTran
> else begin
> save tran spTran
> end
> UPDATE titles
> SET ytd_sales = ytd_sales + @.deltaValue
> WHERE title_id = @.titleId
>
> if (@.tranCount = 0)
> begin
> commit tran
> end
> return 0
> errorHandler:
> rollback tran spTran
> return 1
> GO
>
>
> Then start a trace enabling Execution plan and execute the statement.
> I use the following:
> exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> (This will decrease and then increase some int value in the
> pubs..titles table for a specific record)
>
> Now my point is that when I execute those 2 lines, then they have a
> duration of 10 or 20 ms most of the time. But all of the sudden I see
> an entry with a duration of 210 ms.
> Why does it take so much longer when that is the only thing I execute
> on that entire database?!?
> The output from my trace for the 210 ms run is:
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT:([pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET:([titles].[ytd_sales]=[Expr1004]), DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE:([titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.553
> Execution Plan Execution Tree
> --
> Clustered Index Update(OBJECT:([pubs].[dbo].[titles].
> [UPKCL_titleidind]), SET:([titles].[ytd_sales]=[Expr1004]), DEFINE:
> ([Expr1004]=[titles].[ytd_sales]+[@.deltaValue]), WHERE:([titles].
> [title_id]=[@.titleId]))
> SQL Query Analyzer usr PC\usr 2756 55 2007-05-16 13:11:23.703
> SQL:BatchCompleted exec spTestCommit 1, 'BU1032'
> exec spTestCommit -1, 'BU1032'
> SQL Query Analyzer usr PC\usr 0 18 0 210 2756 55 2007-05-16
> 13:11:23.553 2007-05-16 13:11:23.763
>
> In my real world setup I have a stored procedure that normally takes
> 0-20 ms but have been seen taking 30000 ms or even more... I would
> very much apreciate some hints as to why it vary so much...
> Thanks
> Resist
>

Commit, rollback...

Hello!
I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...
How do I disable rollback feature one time on my deletequery?
Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.
And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.
T
To minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.
MC
"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
|||"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.
Rick Sawtell
MCT, MCSD, MCDBA
|||Ooops.
I forgot to stick a COMMIT in there.
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
|||I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)
Cannot perform a backup or restore operation within a transaction.
And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour...
"Rick Sawtell" <Quickening@.msn.com> kirjoitti
viestiss:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Ooops.
> I forgot to stick a COMMIT in there.
> DECLARE @.rowid int
> SET @.rowid = 10000
> WHILE @.rowid < 28449928
> BEGIN
> BEGIN TRAN
> DELETE tablename
> WHERE id <= @.rowid
> IF @.@.Error <> 0
> ROLLBACK
> ELSE
> COMMIT
> BACKUP LOG <databasename> WITH TRUNCATE_ONLY
> SET @.rowid = @.rowid + 10000
> END -- Loop
>
|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
Steve L
|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
BEGIN TRAN
DELETE dbo.stat
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG stat WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.
"Steve L" <steve_lawrenson@.tiscali.co.uk> kirjoitti
viestiss:1138105823.735961.52530@.g44g2000cwa.goog legroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> Steve L
>
|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
WHILE @.ROWCOUNT < 10001
BEGIN
DELETE dbo.stat
WHERE id = @.rowid
SELECT @.rowid = @.rowid+1, @.rowcount=@.rowcount+1
END
Select @.rowcount = 0
BACKUP LOG stat WITH TRUNCATE_ONLY
--SET @.rowid = @.rowid + 10000
END -- Loop
Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

Commit, rollback...

Hello!
I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...
How do I disable rollback feature one time on my deletequery?
Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.
And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.
TTo minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.
MC
"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>|||"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.
Rick Sawtell
MCT, MCSD, MCDBA|||Ooops.
I forgot to stick a COMMIT in there.
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop|||I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)
Cannot perform a backup or restore operation within a transaction.
And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour...
"Rick Sawtell" <Quickening@.msn.com> kirjoitti
viestissä:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Ooops.
> I forgot to stick a COMMIT in there.
> DECLARE @.rowid int
> SET @.rowid = 10000
> WHILE @.rowid < 28449928
> BEGIN
> BEGIN TRAN
> DELETE tablename
> WHERE id <= @.rowid
> IF @.@.Error <> 0
> ROLLBACK
> ELSE
> COMMIT
> BACKUP LOG <databasename> WITH TRUNCATE_ONLY
> SET @.rowid = @.rowid + 10000
> END -- Loop
>|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
Steve L|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
BEGIN TRAN
DELETE dbo.stat
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG stat WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.
"Steve L" <steve_lawrenson@.tiscali.co.uk> kirjoitti
viestissä:1138105823.735961.52530@.g44g2000cwa.googlegroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> Steve L
>|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
WHILE @.ROWCOUNT < 10001
BEGIN
DELETE dbo.stat
WHERE id = @.rowid
SELECT @.rowid = @.rowid+1, @.rowcount=@.rowcount+1
END
Select @.rowcount = 0
BACKUP LOG stat WITH TRUNCATE_ONLY
--SET @.rowid = @.rowid + 10000
END -- Loop
Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

Commit, rollback...

Hello!
I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
server. We have table with over 20 000 000 records and now A'am cleaning
it. I tryed to delete most of it with simple detele statement. (DELETE FROM
[Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun it, but
it stopts
becouse transactiong log becoms full... We do not have much of fre space for
transaction logs...
How do I disable rollback feature one time on my deletequery?
Or how do I disable it from hole database? Databese get's every day 200 000
new records and it has many transactions over the day. But data that is
strored is not critical so we would need more speed to our queries instead
of failssafe.
And another question. How do I speed up database table that has over 4 000
000 records? Mainly all queries are like queries or quesries against dates.
TTo minimize the log you can switch to the simple recovery mode.
For the delete operation, you could delete data in steps, perhaps using
rowvcount setting. That way, you can loop deletion of lets say 10000 records
until you dont have anything left to delete.
MC
"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun
it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>|||"TLehtinen" <timo@.raa.to> wrote in message
news:43d4d775$0$7478$39db0f71@.news.song.fi...
> Hello!
> I have problmes wtih my litle(single prosessor raid-5, standard sql) sql
> server. We have table with over 20 000 000 records and now A'am cleaning
> it. I tryed to delete most of it with simple detele statement. (DELETE
> FROM [Stat].[dbo].[Stat] WHERE id < 28449928) It tryes to tun
it, but it
> stopts becouse transactiong log becoms full... We do not have much of fre
> space for transaction logs...
> How do I disable rollback feature one time on my deletequery?
> Or how do I disable it from hole database? Databese get's every day 200
> 000 new records and it has many transactions over the day. But data that
> is strored is not critical so we would need more speed to our queries
> instead of failssafe.
> And another question. How do I speed up database table that has over 4 000
> 000 records? Mainly all queries are like queries or quesries against
> dates.
>
> T
>
Step 1.
To delete those rows, I would suggest that you do it in chunks so as not to
overload the transaction log.
For example:
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
Step 2
To speed up your queries...
Indexes, indexes, indexes...
Take the most often used queries that you are running, store them in a .sql
file and then use the Index Tuning Wizard to see what it suggests.
Rick Sawtell
MCT, MCSD, MCDBA|||Ooops.
I forgot to stick a COMMIT in there.
DECLARE @.rowid int
SET @.rowid = 10000
WHILE @.rowid < 28449928
BEGIN
BEGIN TRAN
DELETE tablename
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG <databasename> WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop|||I get this error from line BACKUP LOG stat WITH TRUNCATE_ONLY (stat is my
database)
Cannot perform a backup or restore operation within a transaction.
And transaction log is beeing filled up... And still this is very slow
delete procedure. Servers deletes about 1 000 000 recors in half hour...
"Rick Sawtell" <Quickening@.msn.com> kirjoitti
viestiss:O0cr8uCIGHA.3460@.TK2MSFTNGP12.phx.gbl...
> Ooops.
> I forgot to stick a COMMIT in there.
> DECLARE @.rowid int
> SET @.rowid = 10000
> WHILE @.rowid < 28449928
> BEGIN
> BEGIN TRAN
> DELETE tablename
> WHERE id <= @.rowid
> IF @.@.Error <> 0
> ROLLBACK
> ELSE
> COMMIT
> BACKUP LOG <databasename> WITH TRUNCATE_ONLY
> SET @.rowid = @.rowid + 10000
> END -- Loop
>|||See posting 4 - the script was updated as Rick missed out a commit
Cheers
Steve L|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
BEGIN TRAN
DELETE dbo.stat
WHERE id <= @.rowid
IF @.@.Error <> 0
ROLLBACK
ELSE
COMMIT
BACKUP LOG stat WITH TRUNCATE_ONLY
SET @.rowid = @.rowid + 10000
END -- Loop
And still Transaction log is filled up and I get these error messages...
Cannot perform a backup or restore operation within a transaction.
"Steve L" <steve_lawrenson@.tiscali.co.uk> kirjoitti
viestiss:1138105823.735961.52530@.g44g2000cwa.googlegroups.com...
> See posting 4 - the script was updated as Rick missed out a commit
> Cheers
> Steve L
>|||Sill not working My scipt is here (17 000 000 is the start of my id):
DECLARE @.rowid int
SET @.rowid = 17000000
WHILE @.rowid < 28000000
BEGIN
WHILE @.ROWCOUNT < 10001
BEGIN
DELETE dbo.stat
WHERE id = @.rowid
SELECT @.rowid = @.rowid+1, @.rowcount=@.rowcount+1
END
Select @.rowcount = 0
BACKUP LOG stat WITH TRUNCATE_ONLY
--SET @.rowid = @.rowid + 10000
END -- Loop
Using two loops - the inner one deletes the rows - one at a time though
- see how you get on - also test your backup log statement

Commit Update to SQL Database Table

The following code will not update and commit the update to a SQL Database Table. Now my where statement is looking for a Date field. Could this be the problem?

Dim DBConnAs SqlConnection

Dim DBAddAsNew SqlCommand

Dim strConnectAsString = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString

DBConn =New SqlConnection(strConnect)

'Update a existing row in the table

DBAdd.CommandText ="UPDATE [D12_MIS] SET [CSJ] = @.CSJ, [EST_DATE] = @.EST_DATE, [RECORD_LOCK_FLAG] = @.RECORD_LOCK_FLAG, [EST_CREATE_BY_NAME] = @.EST_CREATE_BY_NAME, [EST_REVIEW_BY_NAME] = @.EST_REVIEW_BY_NAME, [m2_1] = @.m2_1, [m2_2_date] = @.m2_2_date, [m2_3_date] = @.m2_3_date, [m2_4_date] = @.m2_4_date, [m2_5] = @.m2_5, [m3_1a] = @.m3_1a, [m3_1b] = @.m3_1b, [m3_2a] = @.m3_2a, [m3_2b] = @.m3_2b, [m3_3a] = @.m3_3a, [m3_3b] = @.m3_3b WHERE [EST_DATE] = " & EstDateText

With DBAdd.Parameters

.AddWithValue("@.CSJ", pvCSJ.Text)

.AddWithValue("@.EST_DATE", tmp1Date)

.AddWithValue("@.RECORD_LOCK_FLAG", tmpRecordLock)

.AddWithValue("@.EST_CREATE_BY_NAME", CheckedCreator)

.AddWithValue("@.EST_REVIEW_BY_NAME", CheckedReviewer)

.AddWithValue("@.m2_1", vb2_1)

.AddWithValue("@.m2_2_date", tmp2Date)

.AddWithValue("@.m2_3_date", tmp3Date)

.AddWithValue("@.m2_4_date", tmp4Date)

.AddWithValue("@.m2_5", vb2_5)

.AddWithValue("@.m3_1a", vb3_1a)

.AddWithValue("@.m3_1b", vb3_1b)

.AddWithValue("@.m3_2a", vb3_2a)

.AddWithValue("@.m3_2b", vb3_2b)

.AddWithValue("@.m3_3a", vb3_3a)

.AddWithValue("@.m3_3b", vb3_3b)

EndWith

DBAdd.Connection = DBConn

DBAdd.Connection.Open()

Dim rowsAffectedAsInteger = 0

Try

rowsAffected = DBAdd.ExecuteNonQuery

Catch exAs Exception

tb2_2.Text = ex.ToString()

Finally

DBAdd.Connection.Close()

EndTry

tb2_1.Text = rowsAffected

Yes. Since a date column (datetime) consists of both date and time portion, you WHERE clause will attempt to match both the date and the time values. Chances are, you don't have the time portion specified (or a different one), so nothing will be matched. Instead, try to use the format:

WHERE [EST_DATE] >= '02/18/2006 12:00:00 AM' AND [EST_DATE] <= '02/18/2006 11:59:59 PM'

or

WHERE [EST_DATE] >= '02/18/2006' AND [EST_DATE]< '02/19/2006'

Notice that when yo specify only the date portion, the time portion defaults to 12:00:00 AM, so the second method uses less-than (<) the following date of the date you're trying to filter.|||

WHERE [EST_DATE] = " & EstDateText .

I think the problem is that you have not enclosed the date with single quotes. Try the following one

WHERE [EST_DATE] = '" & EstDateText & "'"

|||That was the problem of missing quotes, thanks!|||Change the date to a parameter, and you wouldn't have had that problem.

Commit Transaction Gets Deleted - Unable to save SP

I've re-written a stored procedure and when I post the following code
into the existing SP in EM, is saves OK. However, when I re-edit the
SP, the last line 'Commit Transaction' has been removed.

I cannot save the remainder of the SP as it throws error 208 (Invalid
Object name #Max) about two of the temp tables I use when I post the
entire script. It shows in a message box with the header : 'Microsoft
SQL-DMO(ODBC SQLState:42S02)

I haven't posted the full SP nor the structure as it's quite large
(2000 lines), so hopefully I have given enough detail, but my questions
are :

Why does it now have problems with (temp) #Tables ? The use of these
has not changed. All I have done is wrap the script into various
transactions as this helps a lot for performance and tweaked a few
parts later in the SP again for performance.

Also, why does the line get removed once I save the SP ?

If I run this in QA, I get the same errors, so I suspect it's my
script, but don't know where I'm going wrong.

SQL2000 (Need to upgrade the service pack as recently installed on my
PC, so this may help)

Thanks in advance

Ryan

CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
@.ExtractMonth INTEGER) AS

BEGIN TRANSACTION

SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max

UPDATE #Max SET MaxYear = @.ExtractYear
UPDATE #Max SET MaxMonth = @.ExtractMonth

PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
CREATE TABLE #Extract (
[DEALER_SOURCE_DATA_ID] INT,
[DSD_YEAR] INT NULL,
[DSD_MONTH] INT NULL,
[DEALER_CODE] VarChar(20),
[FranDealerCode] VarChar(20) NULL,
[Line_No] VarChar(75),
[Current] [numeric](15, 5) NULL,
[YTD] [numeric](15, 5) NULL,
[12Months] [numeric](15, 5) NULL,
[24Months] [numeric](15, 5) NULL,
[Average_YTD] [numeric](15, 5) NULL,
[Average12Months] [numeric](15, 5) NULL,
[Average24Months] [numeric](15, 5) NULL,
[Last_YTD] [numeric](15, 5) NULL,
[Current_STATUS] INT,
[PD1] [numeric](15, 5) NULL,
[PD2] [numeric](15, 5) NULL,
[PD3] [numeric](15, 5) NULL,
[PD4] [numeric](15, 5) NULL,
[PD5] [numeric](15, 5) NULL,
[PD6] [numeric](15, 5) NULL,
[PD7] [numeric](15, 5) NULL,
[PD8] [numeric](15, 5) NULL,
[PD9] [numeric](15, 5) NULL,
[PD10] [numeric](15, 5) NULL,
[PD11] [numeric](15, 5) NULL,
[PD12] [numeric](15, 5) NULL,
[PD13] [numeric](15, 5) NULL,
[PD14] [numeric](15, 5) NULL,
[PD15] [numeric](15, 5) NULL,
[PD16] [numeric](15, 5) NULL,
[PD17] [numeric](15, 5) NULL,
[PD18] [numeric](15, 5) NULL,
[PD19] [numeric](15, 5) NULL,
[PD20] [numeric](15, 5) NULL,
[PD21] [numeric](15, 5) NULL,
[PD22] [numeric](15, 5) NULL,
[PD23] [numeric](15, 5) NULL,
[PD24] [numeric](15, 5) NULL,
[PD25] [numeric](15, 5) NULL,
[PD26] [numeric](15, 5) NULL,
[PD27] [numeric](15, 5) NULL,
[PD28] [numeric](15, 5) NULL,
[PD29] [numeric](15, 5) NULL,
[PD30] [numeric](15, 5) NULL,
[PD31] [numeric](15, 5) NULL,
[PD32] [numeric](15, 5) NULL,
[PD33] [numeric](15, 5) NULL,
[PD34] [numeric](15, 5) NULL,
[PD35] [numeric](15, 5) NULL,
[PD36] [numeric](15, 5) NULL)

INSERT INTO #Extract

SELECT DISTINCT
SD.DEALER_SOURCE_DATA_ID,
SD.DSD_YEAR,
SD.DSD_MONTH,
DN.DEALER_CODE,
DN.FRAN_DEALER_CODE,
DV.FIELD_CODE,
0,
0,
0,
0,
0,
0,
0,
0,
SD.STATUS,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0

FROM
DEALER_NAW DN WITH (NOLOCK)
INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
ON DN.DEALER_CODE = SD.DEALER_CODE
INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
113,114,115,130,131,132,133,134,135,136,137)
GO
COMMIT TRANSACTIONYou need to remove 'GO' keyword.
Its a batch separator so you basically have two independent parts. Second
part is : COMMIT TRANSACTION.

MC

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1138113402.871088.270810@.g43g2000cwa.googlegr oups.com...
> I've re-written a stored procedure and when I post the following code
> into the existing SP in EM, is saves OK. However, when I re-edit the
> SP, the last line 'Commit Transaction' has been removed.
> I cannot save the remainder of the SP as it throws error 208 (Invalid
> Object name #Max) about two of the temp tables I use when I post the
> entire script. It shows in a message box with the header : 'Microsoft
> SQL-DMO(ODBC SQLState:42S02)
> I haven't posted the full SP nor the structure as it's quite large
> (2000 lines), so hopefully I have given enough detail, but my questions
> are :
> Why does it now have problems with (temp) #Tables ? The use of these
> has not changed. All I have done is wrap the script into various
> transactions as this helps a lot for performance and tweaked a few
> parts later in the SP again for performance.
> Also, why does the line get removed once I save the SP ?
> If I run this in QA, I get the same errors, so I suspect it's my
> script, but don't know where I'm going wrong.
> SQL2000 (Need to upgrade the service pack as recently installed on my
> PC, so this may help)
> Thanks in advance
>
> Ryan
> CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
> @.ExtractMonth INTEGER) AS
> BEGIN TRANSACTION
> SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max
> UPDATE #Max SET MaxYear = @.ExtractYear
> UPDATE #Max SET MaxMonth = @.ExtractMonth
> PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
> CREATE TABLE #Extract (
> [DEALER_SOURCE_DATA_ID] INT,
> [DSD_YEAR] INT NULL,
> [DSD_MONTH] INT NULL,
> [DEALER_CODE] VarChar(20),
> [FranDealerCode] VarChar(20) NULL,
> [Line_No] VarChar(75),
> [Current] [numeric](15, 5) NULL,
> [YTD] [numeric](15, 5) NULL,
> [12Months] [numeric](15, 5) NULL,
> [24Months] [numeric](15, 5) NULL,
> [Average_YTD] [numeric](15, 5) NULL,
> [Average12Months] [numeric](15, 5) NULL,
> [Average24Months] [numeric](15, 5) NULL,
> [Last_YTD] [numeric](15, 5) NULL,
> [Current_STATUS] INT,
> [PD1] [numeric](15, 5) NULL,
> [PD2] [numeric](15, 5) NULL,
> [PD3] [numeric](15, 5) NULL,
> [PD4] [numeric](15, 5) NULL,
> [PD5] [numeric](15, 5) NULL,
> [PD6] [numeric](15, 5) NULL,
> [PD7] [numeric](15, 5) NULL,
> [PD8] [numeric](15, 5) NULL,
> [PD9] [numeric](15, 5) NULL,
> [PD10] [numeric](15, 5) NULL,
> [PD11] [numeric](15, 5) NULL,
> [PD12] [numeric](15, 5) NULL,
> [PD13] [numeric](15, 5) NULL,
> [PD14] [numeric](15, 5) NULL,
> [PD15] [numeric](15, 5) NULL,
> [PD16] [numeric](15, 5) NULL,
> [PD17] [numeric](15, 5) NULL,
> [PD18] [numeric](15, 5) NULL,
> [PD19] [numeric](15, 5) NULL,
> [PD20] [numeric](15, 5) NULL,
> [PD21] [numeric](15, 5) NULL,
> [PD22] [numeric](15, 5) NULL,
> [PD23] [numeric](15, 5) NULL,
> [PD24] [numeric](15, 5) NULL,
> [PD25] [numeric](15, 5) NULL,
> [PD26] [numeric](15, 5) NULL,
> [PD27] [numeric](15, 5) NULL,
> [PD28] [numeric](15, 5) NULL,
> [PD29] [numeric](15, 5) NULL,
> [PD30] [numeric](15, 5) NULL,
> [PD31] [numeric](15, 5) NULL,
> [PD32] [numeric](15, 5) NULL,
> [PD33] [numeric](15, 5) NULL,
> [PD34] [numeric](15, 5) NULL,
> [PD35] [numeric](15, 5) NULL,
> [PD36] [numeric](15, 5) NULL)
> INSERT INTO #Extract
> SELECT DISTINCT
> SD.DEALER_SOURCE_DATA_ID,
> SD.DSD_YEAR,
> SD.DSD_MONTH,
> DN.DEALER_CODE,
> DN.FRAN_DEALER_CODE,
> DV.FIELD_CODE,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> SD.STATUS,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0
> FROM
> DEALER_NAW DN WITH (NOLOCK)
> INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
> ON DN.DEALER_CODE = SD.DEALER_CODE
> INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
> ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
> AND SD.STATUS < 4096
> INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
> ON DN.DEALER_CODE = GR.DEALER_CODE
> AND GR.FIXED_GROUP_ID IN
> (11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
> 51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
> 113,114,115,130,131,132,133,134,135,136,137)
> GO
> COMMIT TRANSACTION|||Sorted. Thanks for the pointer. I should have spotted that earlier.

Ryan

Commit timestamp on publisher

Hi,
I'm using SQL2000 transaction replication. I'd like to find out
exactly when individual transactions are committed on the publisher
side, before they are actually replicated. Is there a system table
that holds this info? I've looked at MSDistribution_History and
MSqreader_History but they don't have what I'm looking for. Is there a
system variable that I can tap into that may have this (like DB2
has)?
Any thoughts?
Thanks,
Glenn
Glenn,
you can use sp_browsereplcmds which queries the MSrepl_commands table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)
You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)
|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:

> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>
|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:

>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:

Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transaction
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. The
action of that call will be rolled back right? Or is just if the action is a
statement in the same procedure?
"Andrew J. Kelly" wrote:
> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you issue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> > How do I commit a nested transaction while rolling back the outer
> > transaction
> >
> > BEGIN MASTER TRANSACTION
> >
> > Action 1
> >
> > BEGIN NESTED TRANSACTION
> > Action2
> > COMMIT NESTED TRANSACTION
> >
> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> > action
> > 2 committed.)
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:
>What about if one of the action's is a call to another stored procedure. The
>action of that call will be rolled back right? Or is just if the action is a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
>>A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
>>Atomicity
>>A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
--
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>> You can't. If you roll back an outer transaction all the nested ones
>> roll
>> back as well. If you used a SAVE TRAN you can roll it back but any work
>> done inside that (even nested trans ) are rolled back as well. If you
>> issue
>> a plain ROLLBACK (not associated with a nested tran) then everything is
>> rolled back.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Scott" <Scott@.discussions.microsoft.com> wrote in message
>> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>> > How do I commit a nested transaction while rolling back the outer
>> > transaction
>> >
>> > BEGIN MASTER TRANSACTION
>> >
>> > Action 1
>> >
>> > BEGIN NESTED TRANSACTION
>> > Action2
>> > COMMIT NESTED TRANSACTION
>> >
>> > ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
>> > action
>> > 2 committed.)
>>

Commit Nested Transaction

How do I commit a nested transaction while rolling back the outer transactio
n
BEGIN MASTER TRANSACTION
Action 1
BEGIN NESTED TRANSACTION
Action2
COMMIT NESTED TRANSACTION
ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave action
2 committed.)You can't. If you roll back an outer transaction all the nested ones roll
back as well. If you used a SAVE TRAN you can roll it back but any work
done inside that (even nested trans ) are rolled back as well. If you issue
a plain ROLLBACK (not associated with a nested tran) then everything is
rolled back.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
> How do I commit a nested transaction while rolling back the outer
> transaction
> BEGIN MASTER TRANSACTION
> Action 1
> BEGIN NESTED TRANSACTION
> Action2
> COMMIT NESTED TRANSACTION
> ROLLBACK MASTER TRANSACTION (This should rollback action 1 but leave
> action
> 2 committed.)|||What about if one of the action's is a call to another stored procedure. Th
e
action of that call will be rolled back right? Or is just if the action is
a
statement in the same procedure?
"Andrew J. Kelly" wrote:

> You can't. If you roll back an outer transaction all the nested ones roll
> back as well. If you used a SAVE TRAN you can roll it back but any work
> done inside that (even nested trans ) are rolled back as well. If you iss
ue
> a plain ROLLBACK (not associated with a nested tran) then everything is
> rolled back.
> --
> Andrew J. Kelly SQL MVP
>
> "Scott" <Scott@.discussions.microsoft.com> wrote in message
> news:D5E50EB6-F175-4E4E-8758-9E4B412CC59F@.microsoft.com...
>
>|||On Fri, 2 Sep 2005 13:37:02 -0700, Scott wrote:

>What about if one of the action's is a call to another stored procedure. T
he
>action of that call will be rolled back right? Or is just if the action is
a
>statement in the same procedure?
Hi Scott,
EVERYTHING in a transaction is rolled back on a ROLLBACK. That's the
only way to satsify the "A" in the ACID properties. From Books Online:
[vbcol=seagreen]
(snip)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Anything you do after a Begin Tran is governed by that. It doesn't matter
if the statements are in called sp's or not.
Andrew J. Kelly SQL MVP
"Scott" <Scott@.discussions.microsoft.com> wrote in message
news:BCCFD268-AB8C-4158-8A47-169C4F57C912@.microsoft.com...[vbcol=seagreen]
> What about if one of the action's is a call to another stored procedure.
> The
> action of that call will be rolled back right? Or is just if the action
> is a
> statement in the same procedure?
> "Andrew J. Kelly" wrote:
>

Commit Interval in OLE DB Driver

Hi,

I am trying to develop a custom destination component. Is there a way to enable transaction / commit interval in OLE DB drivers?

Thanks,

Vipul

I would say this has to be built into the OLE-DB provider itself. You should then be able to set the value, and would expect it to be a documented property of the provider.

Saying that this is a feature I associate with bulk interfaces, which are not OLE-DB providers themselves, so is it a feature of the OLE-DB specification?

Commit Help

I might not be explaining this correct. I have a table right now that has 7
rows. I want to delete 1 of the rows. Then I want to go back and do the
rollback to undo the transaction of the delete. If it works the way I
understand, I should do a select statement and see the 7 rows. Delete the 1
I want. Do a select statement and see 6 rows. Then do the rollback. Then
another select statement and see the original 7 rows again. I can't do
something that gives me errors because I know I will get none. I know the
delete I am doing works. The issue is doing a delete and then a rollback to
obtain the information that I started with before I did the delete.
I can't do a stored procedure. I also can't do an else if program that
gives errors. All I want is a basic way to delete a row. Then view the
table to make sure it deleted, then go back and do the rollback command and
then view the table again to make sure that the original information is
there before I did the original delete.
Message posted via http://www.webservertalk.comNot sure I understand what you want, but, use QueryAnalyzer, then execute
the SQL exacyl like you described but wrapped in a begin transaction like so
begin transaction
select ...
delete...
select...
rollback...
end
JIM
"tina miller via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:b2387b603d094ec684efe5f85f1c30ef@.SQ
webservertalk.com...
>I might not be explaining this correct. I have a table right now that has 7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
> 1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback
> to
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command
> and
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com|||Read your previous post.
AMB
"tina miller via webservertalk.com" wrote:

> I might not be explaining this correct. I have a table right now that has
7
> rows. I want to delete 1 of the rows. Then I want to go back and do the
> rollback to undo the transaction of the delete. If it works the way I
> understand, I should do a select statement and see the 7 rows. Delete the
1
> I want. Do a select statement and see 6 rows. Then do the rollback. Then
> another select statement and see the original 7 rows again. I can't do
> something that gives me errors because I know I will get none. I know the
> delete I am doing works. The issue is doing a delete and then a rollback t
o
> obtain the information that I started with before I did the delete.
> I can't do a stored procedure. I also can't do an else if program that
> gives errors. All I want is a basic way to delete a row. Then view the
> table to make sure it deleted, then go back and do the rollback command an
d
> then view the table again to make sure that the original information is
> there before I did the original delete.
> --
> Message posted via http://www.webservertalk.com
>

Commit for altering column

Does altering table columns i.e chat to nchar require a
commit?...Hope it is not as it falls under DDL..
krishHi,
ALTER TABLE is a DDL statement. But you need to use COMMIT if you are doing
the ALTER Table inside a transaction
(Begin Tran .. Rollback tran .. Commit tran). This is same for CREATE ,
ALTER , DROP , TRUNCATE commands
Begin tran
alter table x_df drop constraint defaul1_cons
if @.@.error !=0
rollback tran
else
commit tran
SQL server does a autocommit if you are not using a trasnaction.
alter table x_df drop constraint defaul1_cons
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:4bf101c48038$708ae050$a601280a@.phx.gbl...
> Does altering table columns i.e chat to nchar require a
> commit?...Hope it is not as it falls under DDL..
> krish|||Hi,
I am running alter scripts from a table one by one by
reading a temp tables..and it is done via a cursor..
How should i handle then, is it worth do a commit after
each statement?..Would be great if you can give your
inputs...Also would it affect performance ...
krish..

>--Original Message--
>Hi,
>ALTER TABLE is a DDL statement. But you need to use
COMMIT if you are doing
>the ALTER Table inside a transaction
>(Begin Tran .. Rollback tran .. Commit tran). This is
same for CREATE ,
>ALTER , DROP , TRUNCATE commands
>Begin tran
>alter table x_df drop constraint defaul1_cons
>if @.@.error !=0
>rollback tran
>else
>commit tran
>SQL server does a autocommit if you are not using a
trasnaction.
>alter table x_df drop constraint defaul1_cons
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:4bf101c48038$708ae050$a601280a@.phx.gbl...
>
>.
>

Commit for altering column

Does altering table columns i.e chat to nchar require a
commit?...Hope it is not as it falls under DDL..
krish
Hi,
ALTER TABLE is a DDL statement. But you need to use COMMIT if you are doing
the ALTER Table inside a transaction
(Begin Tran .. Rollback tran .. Commit tran). This is same for CREATE ,
ALTER , DROP , TRUNCATE commands
Begin tran
alter table x_df drop constraint defaul1_cons
if @.@.error !=0
rollback tran
else
commit tran
SQL server does a autocommit if you are not using a trasnaction.
alter table x_df drop constraint defaul1_cons
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:4bf101c48038$708ae050$a601280a@.phx.gbl...
> Does altering table columns i.e chat to nchar require a
> commit?...Hope it is not as it falls under DDL..
> krish
|||Hi,
I am running alter scripts from a table one by one by
reading a temp tables..and it is done via a cursor..
How should i handle then, is it worth do a commit after
each statement?..Would be great if you can give your
inputs...Also would it affect performance ...
krish..

>--Original Message--
>Hi,
>ALTER TABLE is a DDL statement. But you need to use
COMMIT if you are doing
>the ALTER Table inside a transaction
>(Begin Tran .. Rollback tran .. Commit tran). This is
same for CREATE ,
>ALTER , DROP , TRUNCATE commands
>Begin tran
>alter table x_df drop constraint defaul1_cons
>if @.@.error !=0
>rollback tran
>else
>commit tran
>SQL server does a autocommit if you are not using a
trasnaction.
>alter table x_df drop constraint defaul1_cons
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:4bf101c48038$708ae050$a601280a@.phx.gbl...
>
>.
>

Commit for altering column

Does altering table columns i.e chat to nchar require a
commit?...Hope it is not as it falls under DDL..
krishHi,
ALTER TABLE is a DDL statement. But you need to use COMMIT if you are doing
the ALTER Table inside a transaction
(Begin Tran .. Rollback tran .. Commit tran). This is same for CREATE ,
ALTER , DROP , TRUNCATE commands
Begin tran
alter table x_df drop constraint defaul1_cons
if @.@.error !=0
rollback tran
else
commit tran
SQL server does a autocommit if you are not using a trasnaction.
alter table x_df drop constraint defaul1_cons
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:4bf101c48038$708ae050$a601280a@.phx.gbl...
> Does altering table columns i.e chat to nchar require a
> commit?...Hope it is not as it falls under DDL..
> krish|||Hi,
I am running alter scripts from a table one by one by
reading a temp tables..and it is done via a cursor..
How should i handle then, is it worth do a commit after
each statement?..Would be great if you can give your
inputs...Also would it affect performance ...
krish..
>--Original Message--
>Hi,
>ALTER TABLE is a DDL statement. But you need to use
COMMIT if you are doing
>the ALTER Table inside a transaction
>(Begin Tran .. Rollback tran .. Commit tran). This is
same for CREATE ,
>ALTER , DROP , TRUNCATE commands
>Begin tran
>alter table x_df drop constraint defaul1_cons
>if @.@.error !=0
>rollback tran
>else
>commit tran
>SQL server does a autocommit if you are not using a
trasnaction.
>alter table x_df drop constraint defaul1_cons
>Thanks
>Hari
>MCDBA
>
><anonymous@.discussions.microsoft.com> wrote in message
>news:4bf101c48038$708ae050$a601280a@.phx.gbl...
>> Does altering table columns i.e chat to nchar require a
>> commit?...Hope it is not as it falls under DDL..
>> krish
>
>.
>

Commit fail and transaction log is full

Hi,

Our testing server experienced a timeout exception when execute System.Data.SqlClient.SqlTransaction.Commit() in SQL Server 2000 thru .Net Framework 1.1. And this happened at 2007-02-13 18:07:05,954.
It was strange to us that all the insert statement can be executed without error within the transaction but the commit operation fails.
Moreover, after about 40 minutes, we found that the transaction log of this database is full.

Here is the exception and SQL Server 2000 Error Log:
Exception Stack Trace:
DateTime: 2007-02-13 18:07:05,954
Req Id: bccdae08-cc47-4f85-8f48-5f0b9dbbbf88
Exception: MyDatabaseException
Detail:
MyDatabaseException:
Index #0
Server: MySQLServer
Source: .Net SqlClient Data Provider
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
LineNumber: 0
Procedure: ConnectionRead (recv()).
State: 0
Error Number: -2 > System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, TdsParserState state)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.ReadNetlib(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ReadBuffer()
at System.Data.SqlClient.TdsParser.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior run, SqlCommand cmdHandler, SqlDataReader dataStream)
at System.Data.SqlClient.SqlInternalConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlConnection.ExecuteTransaction(String sqlBatch, String method)
at System.Data.SqlClient.SqlTransaction.Commit()

SQL Server Error Log:

2007-02-13 13:02:23.72 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944769, first LSN: 9434:22326:1, last LSN: 9434:22360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'E:\MSSQL\Backup\MyDatabaseName.bak'}).
2007-02-13 15:50:52.40 backup Database backed up: Database: MyDatabaseName, creation date(time): 2007/01/12(12:01:39), pages dumped: 944970, first LSN: 9442:38096:1, last LSN: 9443:748:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'Legato#4f96edfd-7fdb-4cd1-a740-3fe9a54d66c6'}).
2007-02-13 18:48:51.42 spid66 Error: 9002, Severity: 17, State: 6
2007-02-13 18:48:51.42 spid66 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:52:40.61 spid58 Error: 9002, Severity: 17, State: 6
2007-02-13 18:52:40.61 spid58 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:53:22.69 spid61 Error: 9002, Severity: 17, State: 6
2007-02-13 18:53:22.69 spid61 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..
2007-02-13 18:54:01.11 spid57 Error: 9002, Severity: 17, State: 6
2007-02-13 18:54:01.11 spid57 The log file for database 'MyDatabaseName' is full. Back up the transaction log for the database to free up some log space..

Are these 2 things related? Would a full transaction log cause a commit operation fail?

Please kindly advice.

Thanks a lot.

the error is self descriptive and the transaction log drive in the machine have no space now. What u need to do now is

(a) Truncate the Log

backup log SomeDatabase with truncate_only

(b) Shrink the log file

dbcc shrinkfile(SomeDatabaseLogicalFileName_Log)

(c) Take FUll Backup -- Very important otherwise the backup chain will break if it is in full recovery

backup database SomeDatabase to Disk='D:\SomeFile.bak' with init

To control the Future growth read the article mentioned below

Refer

http://support.microsoft.com/kb/873235

http://www.databasejournal.com/features/mssql/article.php/1460151

http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894

Madhu

commit and/or rollback transaction error

Hi,
I have a stored procedure that uses the commit and rollback transaction
functionalities.
However, it keeps on throwing me the error:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 3, current count = 4.
Can someone explain to me what does it mean by "Previous count" please?
ThanksHi
Posting the code will help, you may have a path though it where the BEGIN
TRANSACTION is not paired correctly to a COMMIT/ROLLBACK. This may be in a
stored procedure called by the main procedure.
Check out
http://support.microsoft.com/defaul...kb;en-us;158325
http://tinyurl.com/7rmo6
You may want to also read:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
There are also many posts on Google regarding this:
http://tinyurl.com/bjmw2
John
"Tina" wrote:

> Hi,
> I have a stored procedure that uses the commit and rollback transaction
> functionalities.
> However, it keeps on throwing me the error:
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 3, current count = 4.
> Can someone explain to me what does it mean by "Previous count" please?
> Thanks
>
>

Commit and Rollback?

Hi,

I'm using an SQL Express database over a network, using a C# Express program. So I had to use pure SQL connections and commands instead of using Data Sources (couldn't find a way for it to work). In the program / DB I've got a couple of Master - Detail situations. Something like:

Product:

--

productID

(...)

Acessories:

-

acessID

(...)

ProductAcess:

--

productID

acessID

So when inserting a new Product, I'll have to first insert the product (with product name, price, and so on) and once I get the product ID from the insert command, I'll insert the ProductAcess rows. I've found a problem in this though. If for some reason the insert of the product is successful, but the insert of ProductAcess

fails, I've got a big mess in hands because I'll have a row in Product with no rows in ProductAcess (which shouldn't happen in my program scenario). I could solve this by deleting all rows from the DB which connected in someway to the product that failed to insert, but would be far better and correct if I used a commit command at the end of the insert commands to make sure only the right data would be inserted (saving time and resources). I use this all the time in Oracle databases, but don't know if it is possible in SQL Express... Is it? How? Thanks

hi,

you can use transactions in Ado.Net as well, similarly to the trivial code snippet like

SqlConnection con = new SqlConnection(""); SqlCommand cmd = new SqlCommand(); SqlTransaction tx = con.BeginTransaction; cmd.Connection = con; cmd.Transaction = tx; int Res = cmd.ExecuteNonQuery(); cmd.Dispose; cmd = null; if (Res == 0) tx.Rollback; else tx.Commit; tx.Dispose; tx = null; con.Dispose; con = null;

regards