When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name => 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>> Possibly the trigger creates a cursor without deallocating it. Check the
>> trigger code.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> When I want to make an update on a table which make a trigger action, it
>> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
>> already exist"
>> Can anyone please give me more info on msg 16915 or how to solve it?
>> Thanks.
>>
>>
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> > When I want to make an update on a table which make a trigger action, it
> > gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> > already exist"
> >
> > Can anyone please give me more info on msg 16915 or how to solve it?
> > Thanks.
> >
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>> Actually, I doubt I found the cause of error.
>> Here is situation. I define trigger A in table TB1 and trigger B in table
>> TB2. The change in record in TB1 make trigger A act in which write record
>> to
>> TB2 and make trigger B act in which write record to TB3. Much like a
>> chain.
>> But then when I make change in TB1, I got error message saying cursor
>> already exists. Can anyone please help? Thanks.
>>
>> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
>> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>> > When I want to make an update on a table which make a trigger action,
>> > it
>> > gives me error "msg16915, level 16, State 1, Procedure ..." and
>> > "cursor
>> > already exist"
>> >
>> > Can anyone please give me more info on msg 16915 or how to solve it?
>> > Thanks.
>> >
>>
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Showing posts with label state. Show all posts
Showing posts with label state. Show all posts
Wednesday, March 21, 2012
msg16915
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>
|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>
|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>
|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Possibly the trigger creates a cursor without deallocating it. Check the trigger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
>
|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>
|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>
|||Perhaps the inner trigger creates a cursor with the same name as already was created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record to
> TB2 and make trigger B act in which write record to TB3. Much like a chain.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>
|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>
|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
msg16915
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the tri
gger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx
.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thank
s.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was
created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.g
bl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record
to
> TB2 and make trigger B act in which write record to TB3. Much like a chai
n.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.Possibly the trigger creates a cursor without deallocating it. Check the tri
gger code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx
.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it? Thank
s.
>|||In fact, this is the code, can anyone help:
if exists (select id from sysobjects where name = 'apple.A4000_ins_upd_trg'
and
type = 'TR')
begin
drop trigger apple.A4000_ins_upd_trg
print 'A4000_ins_upd_trg is dropped.'
end
go
create trigger apple.A4000_ins_upd_trg on apple.APRN4000
for update, insert
as
declare @.schoolyear varchar(1),
@.UNID varchar(20),
@.OBJECT_TYPE char(1),
@.SCHOOLNUM varchar(3),
@.STULINK numeric(5),
@.SEQUENCE numeric(5),
@.STATUS char(1),
@.RELATION varchar(2),
@.LASTNAME varchar(20),
@.FIRSTNAME varchar(20),
@.MIDDLENAME varchar(13),
@.SALUTATION varchar(4),
@.EMPLOYER varchar(40),
@.OCCUPATION varchar(30),
@.WRKTEL numeric(9),
@.WRKEXTN varchar(4),
@.ALTTEL numeric(9),
@.TELEPHONE numeric(9),
@.USERSTAMP varchar(10),
@.PERMNUM varchar(12),
@.GRADE varchar(2),
@.FAMILYNUM numeric(5),
@.ADDRESS1 varchar(50),
@.ADDRESS2 varchar(40),
@.AREA varchar(30),
@.TERRITORY varchar(25),
@.FAXNO varchar(11),
@.ATTENTION varchar(30),
@.REFCODE varchar(30),
@.counter int,
@.rec_count_before int,
@.rec_count_after int
-- initialization
select @.schoolyear = '4'
select @.counter = 1
-- count how many records in inserted table
select @.rec_count_after = count(STULINK)
from inserted
-- count how many records in before table
select @.rec_count_before = count(STULINK)
from deleted
-- define cursor
declare inserted_curs cursor for
select SCHOOLNUM,
STULINK,
SEQUENCE,
STATUS,
RELATION,
LASTNAME,
FIRSTNAME,
MIDDLENAME,
SALUTATION,
EMPLOYER,
OCCUPATION,
WRKTEL,
WRKEXTN,
ALTTEL,
TELEPHONE
from inserted
open inserted_curs
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
if @.rec_count_before = 0
-- insert action
begin
print 'insert action'
print @.STULINK
print @.sequence
insert into dbo.EAI_ADM_PARENT
(STULINK,
SEQUENCE)
VALUES
(@.STULINK,
@.SEQUENCE)
end
else
-- update action
-- we can assume the record exists in EAI_A4000
begin
print 'update action start'
while @.counter <= @.rec_count_after
begin
update dbo.EAI_ADM_PARENT
set STATUS = @.STATUS,
RELATION = @.RELATION,
LASTNAME = @.LASTNAME,
FIRSTNAME = @.FIRSTNAME,
MIDDLENAME = @.MIDDLENAME,
SALUTATION = @.SALUTATION,
EMPLOYER = @.EMPLOYER,
OCCUPATION = @.OCCUPATION,
WRKTEL = @.WRKTEL,
WRKEXTN = @.WRKEXTN,
ALTTEL = @.ALTTEL,
TELEPHONE = @.TELEPHONE
from apple.APRN4000 P
where P.STULINK = @.STULINK
and P.SEQUENCE = @.SEQUENCE
-- get next record
select @.counter = @.counter + 1
if @.counter <= @.rec_count_after
begin
fetch inserted_curs
into @.SCHOOLNUM,
@.STULINK,
@.SEQUENCE,
@.STATUS,
@.RELATION,
@.LASTNAME,
@.FIRSTNAME,
@.MIDDLENAME,
@.SALUTATION,
@.EMPLOYER,
@.OCCUPATION,
@.WRKTEL,
@.WRKEXTN,
@.ALTTEL,
@.TELEPHONE
end
end
end
-- ending session
close inserted_curs
deallocate inserted_curs
print 'successful'
go
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Where can I check the message meaning?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
> Possibly the trigger creates a cursor without deallocating it. Check the
> trigger code.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can any one please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:Oa5t1LE2EHA.1408@.TK2MSFTNGP10.phx.gbl...
> In fact, this is the code, can anyone help:
>
> if exists (select id from sysobjects where name =
> 'apple.A4000_ins_upd_trg' and
> type = 'TR')
> begin
> drop trigger apple.A4000_ins_upd_trg
> print 'A4000_ins_upd_trg is dropped.'
> end
> go
> create trigger apple.A4000_ins_upd_trg on apple.APRN4000
> for update, insert
> as
> declare @.schoolyear varchar(1),
> @.UNID varchar(20),
> @.OBJECT_TYPE char(1),
> @.SCHOOLNUM varchar(3),
> @.STULINK numeric(5),
> @.SEQUENCE numeric(5),
> @.STATUS char(1),
> @.RELATION varchar(2),
> @.LASTNAME varchar(20),
> @.FIRSTNAME varchar(20),
> @.MIDDLENAME varchar(13),
> @.SALUTATION varchar(4),
> @.EMPLOYER varchar(40),
> @.OCCUPATION varchar(30),
> @.WRKTEL numeric(9),
> @.WRKEXTN varchar(4),
> @.ALTTEL numeric(9),
> @.TELEPHONE numeric(9),
> @.USERSTAMP varchar(10),
> @.PERMNUM varchar(12),
> @.GRADE varchar(2),
> @.FAMILYNUM numeric(5),
> @.ADDRESS1 varchar(50),
> @.ADDRESS2 varchar(40),
> @.AREA varchar(30),
> @.TERRITORY varchar(25),
> @.FAXNO varchar(11),
> @.ATTENTION varchar(30),
> @.REFCODE varchar(30),
> @.counter int,
> @.rec_count_before int,
> @.rec_count_after int
>
> -- initialization
> select @.schoolyear = '4'
> select @.counter = 1
>
> -- count how many records in inserted table
> select @.rec_count_after = count(STULINK)
> from inserted
> -- count how many records in before table
> select @.rec_count_before = count(STULINK)
> from deleted
>
> -- define cursor
> declare inserted_curs cursor for
> select SCHOOLNUM,
> STULINK,
> SEQUENCE,
> STATUS,
> RELATION,
> LASTNAME,
> FIRSTNAME,
> MIDDLENAME,
> SALUTATION,
> EMPLOYER,
> OCCUPATION,
> WRKTEL,
> WRKEXTN,
> ALTTEL,
> TELEPHONE
> from inserted
> open inserted_curs
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> if @.rec_count_before = 0
> -- insert action
> begin
> print 'insert action'
> print @.STULINK
> print @.sequence
> insert into dbo.EAI_ADM_PARENT
> (STULINK,
> SEQUENCE)
> VALUES
> (@.STULINK,
> @.SEQUENCE)
> end
> else
> -- update action
> -- we can assume the record exists in EAI_A4000
> begin
> print 'update action start'
> while @.counter <= @.rec_count_after
> begin
> update dbo.EAI_ADM_PARENT
> set STATUS = @.STATUS,
> RELATION = @.RELATION,
> LASTNAME = @.LASTNAME,
> FIRSTNAME = @.FIRSTNAME,
> MIDDLENAME = @.MIDDLENAME,
> SALUTATION = @.SALUTATION,
> EMPLOYER = @.EMPLOYER,
> OCCUPATION = @.OCCUPATION,
> WRKTEL = @.WRKTEL,
> WRKEXTN = @.WRKEXTN,
> ALTTEL = @.ALTTEL,
> TELEPHONE = @.TELEPHONE
> from apple.APRN4000 P
> where P.STULINK = @.STULINK
> and P.SEQUENCE = @.SEQUENCE
> -- get next record
> select @.counter = @.counter + 1
> if @.counter <= @.rec_count_after
> begin
> fetch inserted_curs
> into @.SCHOOLNUM,
> @.STULINK,
> @.SEQUENCE,
> @.STATUS,
> @.RELATION,
> @.LASTNAME,
> @.FIRSTNAME,
> @.MIDDLENAME,
> @.SALUTATION,
> @.EMPLOYER,
> @.OCCUPATION,
> @.WRKTEL,
> @.WRKEXTN,
> @.ALTTEL,
> @.TELEPHONE
> end
> end
> end
>
> -- ending session
> close inserted_curs
> deallocate inserted_curs
> print 'successful'
> go
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:Oo3U0$D2EHA.3708@.TK2MSFTNGP14.phx.gbl...
>|||Actually, I doubt I found the cause of error.
Here is situation. I define trigger A in table TB1 and trigger B in table
TB2. The change in record in TB1 make trigger A act in which write record to
TB2 and make trigger B act in which write record to TB3. Much like a chain.
But then when I make change in TB1, I got error message saying cursor
already exists. Can anyone please help? Thanks.
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
> When I want to make an update on a table which make a trigger action, it
> gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
> already exist"
> Can anyone please give me more info on msg 16915 or how to solve it?
> Thanks.
>|||Perhaps the inner trigger creates a cursor with the same name as already was
created by the outer
trigger?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.g
bl...
> Actually, I doubt I found the cause of error.
> Here is situation. I define trigger A in table TB1 and trigger B in table
> TB2. The change in record in TB1 make trigger A act in which write record
to
> TB2 and make trigger B act in which write record to TB3. Much like a chai
n.
> But then when I make change in TB1, I got error message saying cursor
> already exists. Can anyone please help? Thanks.
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
>|||Can anyone please help?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:usqoWuQ2EHA.1152@.TK2MSFTNGP14.phx.gbl...
> Perhaps the inner trigger creates a cursor with the same name as already
> was created by the outer
> trigger?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:O1NItpQ2EHA.3324@.tk2msftngp13.phx.gbl...
>|||You've got more problems with this trigger than just that error.
First of all, you have some serious logic bugs and some flawed usage.
1. Try to avoid cursors always, but especially inside of a trigger.
2. The INSERTED and DELETED tables will only store rows that were modified.
If Inserts, DELETED will be NULL; if Update, INSERTED and DELETED will
contain the same records; if Delete, INSERTED will be NULL. Neither of
these will have the same count as the base table unless this is the first
statement executed against the base table or was applied to the entire
table.
3. If you do use a cursor, you need to either code a GLOBAL CURSOR, and need
to check for its existence before you create it or use TEMPORARY cursors,
LOCAL.
4. The logic was a little conveluted; so, I'm not sure what the intent of
the trigger is but you could have simplified the code dramatically by using
constraints to handle most of this and then only using set-based statements
within the trigger for the specialized rules.
5. The error message you are getting is due to the trigger firing more than
once simultaneously due to concurrent users. The cursor already exists when
the second, or n th firing occurs.
Sincerely,
Anthony Thomas
""Allen Iverson"" <no_spam@.bk.com> wrote in message
news:%23fePwaD2EHA.2156@.TK2MSFTNGP10.phx.gbl...
When I want to make an update on a table which make a trigger action, it
gives me error "msg16915, level 16, State 1, Procedure ..." and "cursor
already exist"
Can anyone please give me more info on msg 16915 or how to solve it? Thanks.
Msg:15335
Server: Msg 15335, Level 11, State 1, Procedure
sp_rename, Line 342
Error: The @.newname value 'BC_SLATFix' is already in use
as a object name and would cause a duplicate that is not
permitted.
Thanks reply
hi,
While using sp_rename stored procedure make sure the value that you are
passing as a new name of the object, does not exists in the database .
Ex:
sp_rename 'old_name,'new_name' --object with the new_name must be existing
in the database.
Vishal Parkar
vgparkar@.yahoo.co.in
sql
sp_rename, Line 342
Error: The @.newname value 'BC_SLATFix' is already in use
as a object name and would cause a duplicate that is not
permitted.
Thanks reply
hi,
While using sp_rename stored procedure make sure the value that you are
passing as a new name of the object, does not exists in the database .
Ex:
sp_rename 'old_name,'new_name' --object with the new_name must be existing
in the database.
Vishal Parkar
vgparkar@.yahoo.co.in
sql
Msg 911, Level 1, State 10, Line 1, Could not locate entry insysdatabases for database
The SharePoint_config log file is getting too big, I want to shrink
it. I find some blog and follow the instructions, but when I execute
it, I got the Msg 911, Level 1, State 10, Line 1 error
Here is what I did:
C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
\pipe\mssql$microsoft##ssee\sql\query -E
1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
2> to disk = 'd:\sharepoint\config.bak'
3> go
Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
Could not locate entry in sysdatabases for database
'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
No entry found with that name. Make sure that the name is entered
correctly.
Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
Backup log is terminating abnormally.
Can you help? Thanks.
Aaron
Hi
I have never seen the name pipe connection in the server parameter for
SQLCMD but it seems to work!! It would be easier to do something like
np
local) though!!
The error message says that it can find the database in sysdatabases and
that seems to be the issue.
Try
SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
to get the name
John
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron
|||Yes, I use the select name from sysdatabases and it display the name. I also
look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
and the file name is there.
"Tibor Karaszi" wrote:
> The backup command tries to backup a database named
> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
> the correct database name for the backup command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>
|||I did that and it did display the file name.
"John Bell" wrote:
> Hi
> I have never seen the name pipe connection in the server parameter for
> SQLCMD but it seems to work!! It would be easier to do something like
> np
local) though!!
> The error message says that it can find the database in sysdatabases and
> that seems to be the issue.
> Try
> SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
> to get the name
> John
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>
>
|||Thanks Tibor.
"Tibor Karaszi" wrote:
> Then There's probably something special with the SQL Server that comes with SharePoint (assuming
> that you are 100% positive of the correct database name and that you connected to the correct SQL
> Server instance). You might want to try this in a SharePoint group and see if they have some hints.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
>
|||Hi
In which case try
SELECT 'backup log [' + name + '] to disk = ''d:\sharepoint\config.bak''
FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
And the run the command generated in the output.
John
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:005608A7-6260-4D19-BF44-A09A87E4CD90@.microsoft.com...[vbcol=seagreen]
>I did that and it did display the file name.
> "John Bell" wrote:
it. I find some blog and follow the instructions, but when I execute
it, I got the Msg 911, Level 1, State 10, Line 1 error
Here is what I did:
C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
\pipe\mssql$microsoft##ssee\sql\query -E
1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
2> to disk = 'd:\sharepoint\config.bak'
3> go
Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
Could not locate entry in sysdatabases for database
'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
No entry found with that name. Make sure that the name is entered
correctly.
Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
Backup log is terminating abnormally.
Can you help? Thanks.
Aaron
Hi
I have never seen the name pipe connection in the server parameter for
SQLCMD but it seems to work!! It would be easier to do something like
np
The error message says that it can find the database in sysdatabases and
that seems to be the issue.
Try
SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
to get the name
John
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron
|||Yes, I use the select name from sysdatabases and it display the name. I also
look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
and the file name is there.
"Tibor Karaszi" wrote:
> The backup command tries to backup a database named
> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
> the correct database name for the backup command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>
|||I did that and it did display the file name.
"John Bell" wrote:
> Hi
> I have never seen the name pipe connection in the server parameter for
> SQLCMD but it seems to work!! It would be easier to do something like
> np
> The error message says that it can find the database in sysdatabases and
> that seems to be the issue.
> Try
> SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
> to get the name
> John
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>
>
|||Thanks Tibor.
"Tibor Karaszi" wrote:
> Then There's probably something special with the SQL Server that comes with SharePoint (assuming
> that you are 100% positive of the correct database name and that you connected to the correct SQL
> Server instance). You might want to try this in a SharePoint group and see if they have some hints.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
>
|||Hi
In which case try
SELECT 'backup log [' + name + '] to disk = ''d:\sharepoint\config.bak''
FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
And the run the command generated in the output.
John
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:005608A7-6260-4D19-BF44-A09A87E4CD90@.microsoft.com...[vbcol=seagreen]
>I did that and it did display the file name.
> "John Bell" wrote:
Msg 911, Level 1, State 10, Line 1, Could not locate entry in
The SharePoint_config log file is getting too big, I want to shrink
it. I find some blog and follow the instructions, but when I execute
it, I got the Msg 911, Level 1, State 10, Line 1 error
Here is what I did:
C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
\pipe\mssql$microsoft##ssee\sql\query -E
1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
2> to disk = 'd:\sharepoint\config.bak'
3> go
Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
Could not locate entry in sysdatabases for database
'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
No entry found with that name. Make sure that the name is entered
correctly.
Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
Backup log is terminating abnormally.
Can you help? Thanks.
AaronThe backup command tries to backup a database named
"SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
the correct database name for the backup command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron|||Hi
I have never seen the name pipe connection in the server parameter for
SQLCMD but it seems to work!! It would be easier to do something like
np:(local) though!!
The error message says that it can find the database in sysdatabases and
that seems to be the issue.
Try
SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
to get the name
John
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron|||I did that and it did display the file name.
"John Bell" wrote:
> Hi
> I have never seen the name pipe connection in the server parameter for
> SQLCMD but it seems to work!! It would be easier to do something like
> np:(local) though!!
> The error message says that it can find the database in sysdatabases and
> that seems to be the issue.
> Try
> SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
> to get the name
> John
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> > The SharePoint_config log file is getting too big, I want to shrink
> > it. I find some blog and follow the instructions, but when I execute
> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >
> > Here is what I did:
> >
> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> > \pipe\mssql$microsoft##ssee\sql\query -E
> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> > 2> to disk = 'd:\sharepoint\config.bak'
> > 3> go
> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> > Could not locate entry in sysdatabases for database
> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> > No entry found with that name. Make sure that the name is entered
> > correctly.
> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> > Backup log is terminating abnormally.
> >
> > Can you help? Thanks.
> >
> > Aaron
>
>|||Yes, I use the select name from sysdatabases and it display the name. I also
look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
and the file name is there.
"Tibor Karaszi" wrote:
> The backup command tries to backup a database named
> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
> the correct database name for the backup command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> > The SharePoint_config log file is getting too big, I want to shrink
> > it. I find some blog and follow the instructions, but when I execute
> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >
> > Here is what I did:
> >
> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> > \pipe\mssql$microsoft##ssee\sql\query -E
> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> > 2> to disk = 'd:\sharepoint\config.bak'
> > 3> go
> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> > Could not locate entry in sysdatabases for database
> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> > No entry found with that name. Make sure that the name is entered
> > correctly.
> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> > Backup log is terminating abnormally.
> >
> > Can you help? Thanks.
> >
> > Aaron
>|||Then There's probably something special with the SQL Server that comes with SharePoint (assuming
that you are 100% positive of the correct database name and that you connected to the correct SQL
Server instance). You might want to try this in a SharePoint group and see if they have some hints.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
> Yes, I use the select name from sysdatabases and it display the name. I also
> look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
> and the file name is there.
> "Tibor Karaszi" wrote:
>> The backup command tries to backup a database named
>> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not,
>> specify
>> the correct database name for the backup command.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <achan@.rosewater.com> wrote in message
>> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>> > The SharePoint_config log file is getting too big, I want to shrink
>> > it. I find some blog and follow the instructions, but when I execute
>> > it, I got the Msg 911, Level 1, State 10, Line 1 error
>> >
>> > Here is what I did:
>> >
>> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
>> > \pipe\mssql$microsoft##ssee\sql\query -E
>> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
>> > 2> to disk = 'd:\sharepoint\config.bak'
>> > 3> go
>> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
>> > Could not locate entry in sysdatabases for database
>> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
>> > No entry found with that name. Make sure that the name is entered
>> > correctly.
>> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
>> > Backup log is terminating abnormally.
>> >
>> > Can you help? Thanks.
>> >
>> > Aaron
>>|||Thanks Tibor.
"Tibor Karaszi" wrote:
> Then There's probably something special with the SQL Server that comes with SharePoint (assuming
> that you are 100% positive of the correct database name and that you connected to the correct SQL
> Server instance). You might want to try this in a SharePoint group and see if they have some hints.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
> > Yes, I use the select name from sysdatabases and it display the name. I also
> > look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
> > and the file name is there.
> >
> > "Tibor Karaszi" wrote:
> >
> >> The backup command tries to backup a database named
> >> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not,
> >> specify
> >> the correct database name for the backup command.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> <achan@.rosewater.com> wrote in message
> >> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> >> > The SharePoint_config log file is getting too big, I want to shrink
> >> > it. I find some blog and follow the instructions, but when I execute
> >> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >> >
> >> > Here is what I did:
> >> >
> >> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> >> > \pipe\mssql$microsoft##ssee\sql\query -E
> >> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> >> > 2> to disk = 'd:\sharepoint\config.bak'
> >> > 3> go
> >> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> >> > Could not locate entry in sysdatabases for database
> >> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> >> > No entry found with that name. Make sure that the name is entered
> >> > correctly.
> >> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> >> > Backup log is terminating abnormally.
> >> >
> >> > Can you help? Thanks.
> >> >
> >> > Aaron
> >>
> >>
>|||Hi
In which case try
SELECT 'backup log [' + name + '] to disk = ''d:\sharepoint\config.bak''
FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
And the run the command generated in the output.
John
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:005608A7-6260-4D19-BF44-A09A87E4CD90@.microsoft.com...
>I did that and it did display the file name.
> "John Bell" wrote:
>> Hi
>> I have never seen the name pipe connection in the server parameter for
>> SQLCMD but it seems to work!! It would be easier to do something like
>> np:(local) though!!
>> The error message says that it can find the database in sysdatabases and
>> that seems to be the issue.
>> Try
>> SELECT Name FROM master..sysdatabases where NAME LIKE
>> 'SharePoint_config%'
>> to get the name
>> John
>>
>> <achan@.rosewater.com> wrote in message
>> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>> > The SharePoint_config log file is getting too big, I want to shrink
>> > it. I find some blog and follow the instructions, but when I execute
>> > it, I got the Msg 911, Level 1, State 10, Line 1 error
>> >
>> > Here is what I did:
>> >
>> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
>> > \pipe\mssql$microsoft##ssee\sql\query -E
>> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
>> > 2> to disk = 'd:\sharepoint\config.bak'
>> > 3> go
>> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
>> > Could not locate entry in sysdatabases for database
>> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
>> > No entry found with that name. Make sure that the name is entered
>> > correctly.
>> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
>> > Backup log is terminating abnormally.
>> >
>> > Can you help? Thanks.
>> >
>> > Aaron
>>
it. I find some blog and follow the instructions, but when I execute
it, I got the Msg 911, Level 1, State 10, Line 1 error
Here is what I did:
C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
\pipe\mssql$microsoft##ssee\sql\query -E
1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
2> to disk = 'd:\sharepoint\config.bak'
3> go
Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
Could not locate entry in sysdatabases for database
'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
No entry found with that name. Make sure that the name is entered
correctly.
Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
Backup log is terminating abnormally.
Can you help? Thanks.
AaronThe backup command tries to backup a database named
"SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
the correct database name for the backup command.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron|||Hi
I have never seen the name pipe connection in the server parameter for
SQLCMD but it seems to work!! It would be easier to do something like
np:(local) though!!
The error message says that it can find the database in sysdatabases and
that seems to be the issue.
Try
SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
to get the name
John
<achan@.rosewater.com> wrote in message
news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> The SharePoint_config log file is getting too big, I want to shrink
> it. I find some blog and follow the instructions, but when I execute
> it, I got the Msg 911, Level 1, State 10, Line 1 error
> Here is what I did:
> C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> \pipe\mssql$microsoft##ssee\sql\query -E
> 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> 2> to disk = 'd:\sharepoint\config.bak'
> 3> go
> Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> Could not locate entry in sysdatabases for database
> 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> No entry found with that name. Make sure that the name is entered
> correctly.
> Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> Backup log is terminating abnormally.
> Can you help? Thanks.
> Aaron|||I did that and it did display the file name.
"John Bell" wrote:
> Hi
> I have never seen the name pipe connection in the server parameter for
> SQLCMD but it seems to work!! It would be easier to do something like
> np:(local) though!!
> The error message says that it can find the database in sysdatabases and
> that seems to be the issue.
> Try
> SELECT Name FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
> to get the name
> John
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> > The SharePoint_config log file is getting too big, I want to shrink
> > it. I find some blog and follow the instructions, but when I execute
> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >
> > Here is what I did:
> >
> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> > \pipe\mssql$microsoft##ssee\sql\query -E
> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> > 2> to disk = 'd:\sharepoint\config.bak'
> > 3> go
> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> > Could not locate entry in sysdatabases for database
> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> > No entry found with that name. Make sure that the name is entered
> > correctly.
> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> > Backup log is terminating abnormally.
> >
> > Can you help? Thanks.
> >
> > Aaron
>
>|||Yes, I use the select name from sysdatabases and it display the name. I also
look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
and the file name is there.
"Tibor Karaszi" wrote:
> The backup command tries to backup a database named
> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not, specify
> the correct database name for the backup command.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> <achan@.rosewater.com> wrote in message
> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> > The SharePoint_config log file is getting too big, I want to shrink
> > it. I find some blog and follow the instructions, but when I execute
> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >
> > Here is what I did:
> >
> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> > \pipe\mssql$microsoft##ssee\sql\query -E
> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> > 2> to disk = 'd:\sharepoint\config.bak'
> > 3> go
> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> > Could not locate entry in sysdatabases for database
> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> > No entry found with that name. Make sure that the name is entered
> > correctly.
> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> > Backup log is terminating abnormally.
> >
> > Can you help? Thanks.
> >
> > Aaron
>|||Then There's probably something special with the SQL Server that comes with SharePoint (assuming
that you are 100% positive of the correct database name and that you connected to the correct SQL
Server instance). You might want to try this in a SharePoint group and see if they have some hints.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
> Yes, I use the select name from sysdatabases and it display the name. I also
> look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
> and the file name is there.
> "Tibor Karaszi" wrote:
>> The backup command tries to backup a database named
>> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not,
>> specify
>> the correct database name for the backup command.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> <achan@.rosewater.com> wrote in message
>> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>> > The SharePoint_config log file is getting too big, I want to shrink
>> > it. I find some blog and follow the instructions, but when I execute
>> > it, I got the Msg 911, Level 1, State 10, Line 1 error
>> >
>> > Here is what I did:
>> >
>> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
>> > \pipe\mssql$microsoft##ssee\sql\query -E
>> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
>> > 2> to disk = 'd:\sharepoint\config.bak'
>> > 3> go
>> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
>> > Could not locate entry in sysdatabases for database
>> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
>> > No entry found with that name. Make sure that the name is entered
>> > correctly.
>> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
>> > Backup log is terminating abnormally.
>> >
>> > Can you help? Thanks.
>> >
>> > Aaron
>>|||Thanks Tibor.
"Tibor Karaszi" wrote:
> Then There's probably something special with the SQL Server that comes with SharePoint (assuming
> that you are 100% positive of the correct database name and that you connected to the correct SQL
> Server instance). You might want to try this in a SharePoint group and see if they have some hints.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron" <Aaron@.discussions.microsoft.com> wrote in message
> news:AC3CD1A0-A462-4398-A560-84842EB40433@.microsoft.com...
> > Yes, I use the select name from sysdatabases and it display the name. I also
> > look into the c:\windows\sysmsi\ssee\mssql.2005\mssql\data where it reside
> > and the file name is there.
> >
> > "Tibor Karaszi" wrote:
> >
> >> The backup command tries to backup a database named
> >> "SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639". Do you have such database? If not,
> >> specify
> >> the correct database name for the backup command.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> <achan@.rosewater.com> wrote in message
> >> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
> >> > The SharePoint_config log file is getting too big, I want to shrink
> >> > it. I find some blog and follow the instructions, but when I execute
> >> > it, I got the Msg 911, Level 1, State 10, Line 1 error
> >> >
> >> > Here is what I did:
> >> >
> >> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
> >> > \pipe\mssql$microsoft##ssee\sql\query -E
> >> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
> >> > 2> to disk = 'd:\sharepoint\config.bak'
> >> > 3> go
> >> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
> >> > Could not locate entry in sysdatabases for database
> >> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
> >> > No entry found with that name. Make sure that the name is entered
> >> > correctly.
> >> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
> >> > Backup log is terminating abnormally.
> >> >
> >> > Can you help? Thanks.
> >> >
> >> > Aaron
> >>
> >>
>|||Hi
In which case try
SELECT 'backup log [' + name + '] to disk = ''d:\sharepoint\config.bak''
FROM master..sysdatabases where NAME LIKE 'SharePoint_config%'
And the run the command generated in the output.
John
"Aaron" <Aaron@.discussions.microsoft.com> wrote in message
news:005608A7-6260-4D19-BF44-A09A87E4CD90@.microsoft.com...
>I did that and it did display the file name.
> "John Bell" wrote:
>> Hi
>> I have never seen the name pipe connection in the server parameter for
>> SQLCMD but it seems to work!! It would be easier to do something like
>> np:(local) though!!
>> The error message says that it can find the database in sysdatabases and
>> that seems to be the issue.
>> Try
>> SELECT Name FROM master..sysdatabases where NAME LIKE
>> 'SharePoint_config%'
>> to get the name
>> John
>>
>> <achan@.rosewater.com> wrote in message
>> news:3876feb7-df80-4b77-ba38-487da8f57257@.62g2000hsn.googlegroups.com...
>> > The SharePoint_config log file is getting too big, I want to shrink
>> > it. I find some blog and follow the instructions, but when I execute
>> > it, I got the Msg 911, Level 1, State 10, Line 1 error
>> >
>> > Here is what I did:
>> >
>> > C:\Program Files\Microsoft SQL Server\90\tools\binn > sqlcmd -S \\.
>> > \pipe\mssql$microsoft##ssee\sql\query -E
>> > 1> backup log [SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639]
>> > 2> to disk = 'd:\sharepoint\config.bak'
>> > 3> go
>> > Msg 911, Level 1, State 10, Server JITTERBUG\microsoft##ssee, Line 1
>> > Could not locate entry in sysdatabases for database
>> > 'SharePoint_Config_5eb4c0dd-5315-4e4e-8969-10a020979639'
>> > No entry found with that name. Make sure that the name is entered
>> > correctly.
>> > Msg 3013, Level 16, State 1, Server JITTERBUG\microsoft##ssee, Line 1
>> > Backup log is terminating abnormally.
>> >
>> > Can you help? Thanks.
>> >
>> > Aaron
>>
Msg 9002, Level 17, State 4, Line 2
I'm trying to clear some data from a SQL DB table, but i'm getting this
error:
I'm just running a Delete Query using Date Diff function. I read in some
web site that this maybe a problem with SQL Server Management Tools. I
appreciate any help to get around this and any suggestions would be great.
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'EventSentry' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Thanks for answering.Fialoa,
Your delete transaction is writting too many log records and filling it up.
If you want to be able to recover then you need to make the log bigger or
make your delete statement work on a smaller subset.
Chris
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||Hi fiaola
SQL Server has to log every data modification operation, every insert,
update and delete. If the log is full, nothing can be written to it and no
data modifications can be done. This has nothing to do with the tool you are
using.
Just do what the messages says. Look at the sys.databases view and tell us
what the log_reuse_wait_desc says. Also look at the recovery model
SELECT log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'EventSentry'
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||Thanks for the quick reply, here are the results.
Log_Reuse_Wait_Desc = NOTHING
Recovery_Model_Desc = SIMPLE
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Hi fiaola
> SQL Server has to log every data modification operation, every insert,
> update and delete. If the log is full, nothing can be written to it and no
> data modifications can be done. This has nothing to do with the tool you
> are using.
> Just do what the messages says. Look at the sys.databases view and tell us
> what the log_reuse_wait_desc says. Also look at the recovery model
> SELECT log_reuse_wait_desc, recovery_model_desc
> FROM sys.databases
> WHERE name = 'EventSentry'
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "fiaola" <fiaola@.mail.com> wrote in message
> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in some
>> web site that this maybe a problem with SQL Server Management Tools. I
>> appreciate any help to get around this and any suggestions would be
>> great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>|||It might just be that your log is not big enough for the operation you were
doing.
How big is your log file, and is it set to autogrow?
You can get the information in the sys.files view in the database that is
having the problems.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uH1ss$YUIHA.5816@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick reply, here are the results.
> Log_Reuse_Wait_Desc = NOTHING
> Recovery_Model_Desc = SIMPLE
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Hi fiaola
>> SQL Server has to log every data modification operation, every insert,
>> update and delete. If the log is full, nothing can be written to it and
>> no data modifications can be done. This has nothing to do with the tool
>> you are using.
>> Just do what the messages says. Look at the sys.databases view and tell
>> us what the log_reuse_wait_desc says. Also look at the recovery model
>> SELECT log_reuse_wait_desc, recovery_model_desc
>> FROM sys.databases
>> WHERE name = 'EventSentry'
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||Modify your delete to do it in batches. Pick date ranges that will only
delete 1-5% of the table at once max for each range and then you will
probably be ok.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||But we still don't know how big the log is. I've seen logs that are set at
the default 1 MB with no autogrow.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o71gecmi9nmfb@.corp.supernews.com...
> Modify your delete to do it in batches. Pick date ranges that will only
> delete 1-5% of the table at once max for each range and then you will
> probably be ok.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "fiaola" <fiaola@.mail.com> wrote in message
> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in some
>> web site that this maybe a problem with SQL Server Management Tools. I
>> appreciate any help to get around this and any suggestions would be
>> great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>|||Problem fixed. Yes, i had to change the query to process less data and i
have set my logs to Autogrow for any future problems.
Thanks for your help. This is a GREAT news group with fast response.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
> But we still don't know how big the log is. I've seen logs that are set at
> the default 1 MB with no autogrow.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||Good point. I haven't seen tlogs set with no autogrow that I recall.
Almost every client I have been at still has the defaults for data/log size
and growths (2MB and 1MB growth for data and 1MB and 10% growth for tlog,
IIRC). Makes me look good when I have them defrag the hard drive and pick
up a big perf gain. :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
> But we still don't know how big the log is. I've seen logs that are set at
> the default 1 MB with no autogrow.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||You're welcome. I'm glad our suggestions helped.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:ubRniqiUIHA.536@.TK2MSFTNGP06.phx.gbl...
> Problem fixed. Yes, i had to change the query to process less data and i
> have set my logs to Autogrow for any future problems.
> Thanks for your help. This is a GREAT news group with fast response.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases
>> Thanks for answering.
>>
>>
>>
>|||Kevin,
We also try and size our data and log files with limited growth. I have seen
problems with an unlimited log and percent full log alerts.
How much improvement would a disk defrag give you on todays raided disks?
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o7ttr3dli55fd@.corp.supernews.com...
> Good point. I haven't seen tlogs set with no autogrow that I recall.
> Almost every client I have been at still has the defaults for data/log
> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
> tlog, IIRC). Makes me look good when I have them defrag the hard drive
> and pick up a big perf gain. :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases
>> Thanks for answering.
>>
>>
>>
>|||Limiting your log size is fine, but you should also make sure you have a
good idea of how big it needs to be and then add a generous percentage extra
before setting the upper limit.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Kevin,
> We also try and size our data and log files with limited growth. I have
> seen problems with an unlimited log and percent full log alerts.
> How much improvement would a disk defrag give you on todays raided disks?
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>|||Kalen,
We do that as best we can. We have alerts fired if the data file expands and
then we would look at the log size too. We also would get messages if the
log does get full.
My question is to see how much improvement we might get by defragging.
Thanks
Chris
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OAAg9h1UIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Limiting your log size is fine, but you should also make sure you have a
> good idea of how big it needs to be and then add a generous percentage
> extra before setting the upper limit.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>>
>|||I had a client with 300K+ file fragments when I arrived. 18% throughput
improvement from OS file defrag operation. It was on a small RAID10 set (6
drives IIRC).
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Kevin,
> We also try and size our data and log files with limited growth. I have
> seen problems with an unlimited log and percent full log alerts.
> How much improvement would a disk defrag give you on todays raided disks?
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>|||kevin,
What can we use to see how many file fragments there are? Running W2K3 SP2.
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5m2iv7ec365@.corp.supernews.com...
>I had a client with 300K+ file fragments when I arrived. 18% throughput
>improvement from OS file defrag operation. It was on a small RAID10 set (6
>drives IIRC).
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>>
>|||Hi Chris
Actually my comment was primarily directly at the OP. You've been around
long enough that I'm sure you've figured out most of the important stuff.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9J8RU7UIHA.5264@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> We do that as best we can. We have alerts fired if the data file expands
> and then we would look at the log size too. We also would get messages if
> the log does get full.
> My question is to see how much improvement we might get by defragging.
> Thanks
> Chris
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OAAg9h1UIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Limiting your log size is fine, but you should also make sure you have a
>> good idea of how big it needs to be and then add a generous percentage
>> extra before setting the upper limit.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read in
>>> some web site that this maybe a problem with SQL Server Management
>>> Tools. I appreciate any help to get around this and any suggestions
>>> would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>
>>
>>
>>
>>
>|||I would recommend diskkeeper. There are many others from symantec,
perfectdisk (raxco maybe?), mcaffee probably has one, one built into windows
(downgraded version of diskkeeper IIRC).
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
> kevin,
> What can we use to see how many file fragments there are? Running W2K3
> SP2.
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read in
>>> some web site that this maybe a problem with SQL Server Management
>>> Tools. I appreciate any help to get around this and any suggestions
>>> would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>
>>
>>
>>
>>
>|||Last question on this.
Can these products run in a scan mode when SQL is running so we have a
picture of the fragmentation? I am the DBA, the Infrastructure guys do the
Windows maintenance.
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13odvkmn08vcfa0@.corp.supernews.com...
>I would recommend diskkeeper. There are many others from symantec,
>perfectdisk (raxco maybe?), mcaffee probably has one, one built into
>windows (downgraded version of diskkeeper IIRC).
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> kevin,
>> What can we use to see how many file fragments there are? Running W2K3
>> SP2.
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>>> Modify your delete to do it in batches. Pick date ranges that will
>>> only delete 1-5% of the table at once max for each range and then
>>> you will probably be ok.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "fiaola" <fiaola@.mail.com> wrote in message
>>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read
>>> in some web site that this maybe a problem with SQL Server
>>> Management Tools. I appreciate any help to get around this and any
>>> suggestions would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||I know for a fact that several of them can. I don't know about all of them.
BTW, It is recommended that you shut sql server down during a defrag
operation, but I have at least 2 clients using diskkeeper that do not do
this. Do so at your own risk though.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uoeWXFHVIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Last question on this.
> Can these products run in a scan mode when SQL is running so we have a
> picture of the fragmentation? I am the DBA, the Infrastructure guys do the
> Windows maintenance.
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13odvkmn08vcfa0@.corp.supernews.com...
>>I would recommend diskkeeper. There are many others from symantec,
>>perfectdisk (raxco maybe?), mcaffee probably has one, one built into
>>windows (downgraded version of diskkeeper IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> kevin,
>> What can we use to see how many file fragments there are? Running W2K3
>> SP2.
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I
>> have seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for
>> data/log size and growths (2MB and 1MB growth for data and 1MB and
>> 10% growth for tlog, IIRC). Makes me look good when I have them
>> defrag the hard drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>>> But we still don't know how big the log is. I've seen logs that are
>>> set at the default 1 MB with no autogrow.
>>>
>>> --
>>> HTH
>>> Kalen Delaney, SQL Server MVP
>>> www.InsideSQLServer.com
>>> http://blog.kalendelaney.com
>>>
>>>
>>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>>> news:13o71gecmi9nmfb@.corp.supernews.com...
>>> Modify your delete to do it in batches. Pick date ranges that will
>>> only delete 1-5% of the table at once max for each range and then
>>> you will probably be ok.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "fiaola" <fiaola@.mail.com> wrote in message
>>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read
>>> in some web site that this maybe a problem with SQL Server
>>> Management Tools. I appreciate any help to get around this and
>>> any suggestions would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find
>>> out why space in the log cannot be reused, see the
>>> log_reuse_wait_desc column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
error:
I'm just running a Delete Query using Date Diff function. I read in some
web site that this maybe a problem with SQL Server Management Tools. I
appreciate any help to get around this and any suggestions would be great.
Msg 9002, Level 17, State 4, Line 2
The transaction log for database 'EventSentry' is full. To find out why
space in the log cannot be reused, see the log_reuse_wait_desc column in
sys.databases
Thanks for answering.Fialoa,
Your delete transaction is writting too many log records and filling it up.
If you want to be able to recover then you need to make the log bigger or
make your delete statement work on a smaller subset.
Chris
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||Hi fiaola
SQL Server has to log every data modification operation, every insert,
update and delete. If the log is full, nothing can be written to it and no
data modifications can be done. This has nothing to do with the tool you are
using.
Just do what the messages says. Look at the sys.databases view and tell us
what the log_reuse_wait_desc says. Also look at the recovery model
SELECT log_reuse_wait_desc, recovery_model_desc
FROM sys.databases
WHERE name = 'EventSentry'
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||Thanks for the quick reply, here are the results.
Log_Reuse_Wait_Desc = NOTHING
Recovery_Model_Desc = SIMPLE
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Hi fiaola
> SQL Server has to log every data modification operation, every insert,
> update and delete. If the log is full, nothing can be written to it and no
> data modifications can be done. This has nothing to do with the tool you
> are using.
> Just do what the messages says. Look at the sys.databases view and tell us
> what the log_reuse_wait_desc says. Also look at the recovery model
> SELECT log_reuse_wait_desc, recovery_model_desc
> FROM sys.databases
> WHERE name = 'EventSentry'
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "fiaola" <fiaola@.mail.com> wrote in message
> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in some
>> web site that this maybe a problem with SQL Server Management Tools. I
>> appreciate any help to get around this and any suggestions would be
>> great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>|||It might just be that your log is not big enough for the operation you were
doing.
How big is your log file, and is it set to autogrow?
You can get the information in the sys.files view in the database that is
having the problems.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:uH1ss$YUIHA.5816@.TK2MSFTNGP06.phx.gbl...
> Thanks for the quick reply, here are the results.
> Log_Reuse_Wait_Desc = NOTHING
> Recovery_Model_Desc = SIMPLE
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:eMohs$WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Hi fiaola
>> SQL Server has to log every data modification operation, every insert,
>> update and delete. If the log is full, nothing can be written to it and
>> no data modifications can be done. This has nothing to do with the tool
>> you are using.
>> Just do what the messages says. Look at the sys.databases view and tell
>> us what the log_reuse_wait_desc says. Also look at the recovery model
>> SELECT log_reuse_wait_desc, recovery_model_desc
>> FROM sys.databases
>> WHERE name = 'EventSentry'
>>
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||Modify your delete to do it in batches. Pick date ranges that will only
delete 1-5% of the table at once max for each range and then you will
probably be ok.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"fiaola" <fiaola@.mail.com> wrote in message
news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> I'm trying to clear some data from a SQL DB table, but i'm getting this
> error:
> I'm just running a Delete Query using Date Diff function. I read in some
> web site that this maybe a problem with SQL Server Management Tools. I
> appreciate any help to get around this and any suggestions would be great.
> Msg 9002, Level 17, State 4, Line 2
> The transaction log for database 'EventSentry' is full. To find out why
> space in the log cannot be reused, see the log_reuse_wait_desc column in
> sys.databases
> Thanks for answering.
>|||But we still don't know how big the log is. I've seen logs that are set at
the default 1 MB with no autogrow.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o71gecmi9nmfb@.corp.supernews.com...
> Modify your delete to do it in batches. Pick date ranges that will only
> delete 1-5% of the table at once max for each range and then you will
> probably be ok.
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "fiaola" <fiaola@.mail.com> wrote in message
> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in some
>> web site that this maybe a problem with SQL Server Management Tools. I
>> appreciate any help to get around this and any suggestions would be
>> great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>|||Problem fixed. Yes, i had to change the query to process less data and i
have set my logs to Autogrow for any future problems.
Thanks for your help. This is a GREAT news group with fast response.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
> But we still don't know how big the log is. I've seen logs that are set at
> the default 1 MB with no autogrow.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||Good point. I haven't seen tlogs set with no autogrow that I recall.
Almost every client I have been at still has the defaults for data/log size
and growths (2MB and 1MB growth for data and 1MB and 10% growth for tlog,
IIRC). Makes me look good when I have them defrag the hard drive and pick
up a big perf gain. :-)
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
> But we still don't know how big the log is. I've seen logs that are set at
> the default 1 MB with no autogrow.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column in
>> sys.databases
>> Thanks for answering.
>>
>>
>|||You're welcome. I'm glad our suggestions helped.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"fiaola" <fiaola@.mail.com> wrote in message
news:ubRniqiUIHA.536@.TK2MSFTNGP06.phx.gbl...
> Problem fixed. Yes, i had to change the query to process less data and i
> have set my logs to Autogrow for any future problems.
> Thanks for your help. This is a GREAT news group with fast response.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases
>> Thanks for answering.
>>
>>
>>
>|||Kevin,
We also try and size our data and log files with limited growth. I have seen
problems with an unlimited log and percent full log alerts.
How much improvement would a disk defrag give you on todays raided disks?
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13o7ttr3dli55fd@.corp.supernews.com...
> Good point. I haven't seen tlogs set with no autogrow that I recall.
> Almost every client I have been at still has the defaults for data/log
> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
> tlog, IIRC). Makes me look good when I have them defrag the hard drive
> and pick up a big perf gain. :-)
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will only
>> delete 1-5% of the table at once max for each range and then you will
>> probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting this
>> error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out why
>> space in the log cannot be reused, see the log_reuse_wait_desc column
>> in sys.databases
>> Thanks for answering.
>>
>>
>>
>|||Limiting your log size is fine, but you should also make sure you have a
good idea of how big it needs to be and then add a generous percentage extra
before setting the upper limit.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Kevin,
> We also try and size our data and log files with limited growth. I have
> seen problems with an unlimited log and percent full log alerts.
> How much improvement would a disk defrag give you on todays raided disks?
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>|||Kalen,
We do that as best we can. We have alerts fired if the data file expands and
then we would look at the log size too. We also would get messages if the
log does get full.
My question is to see how much improvement we might get by defragging.
Thanks
Chris
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OAAg9h1UIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Limiting your log size is fine, but you should also make sure you have a
> good idea of how big it needs to be and then add a generous percentage
> extra before setting the upper limit.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>>
>|||I had a client with 300K+ file fragments when I arrived. 18% throughput
improvement from OS file defrag operation. It was on a small RAID10 set (6
drives IIRC).
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
> Kevin,
> We also try and size our data and log files with limited growth. I have
> seen problems with an unlimited log and percent full log alerts.
> How much improvement would a disk defrag give you on todays raided disks?
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>|||kevin,
What can we use to see how many file fragments there are? Running W2K3 SP2.
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13od5m2iv7ec365@.corp.supernews.com...
>I had a client with 300K+ file fragments when I arrived. 18% throughput
>improvement from OS file defrag operation. It was on a small RAID10 set (6
>drives IIRC).
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth for
>> tlog, IIRC). Makes me look good when I have them defrag the hard drive
>> and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are set
>> at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> I'm trying to clear some data from a SQL DB table, but i'm getting
>> this error:
>> I'm just running a Delete Query using Date Diff function. I read in
>> some web site that this maybe a problem with SQL Server Management
>> Tools. I appreciate any help to get around this and any suggestions
>> would be great.
>> Msg 9002, Level 17, State 4, Line 2
>> The transaction log for database 'EventSentry' is full. To find out
>> why space in the log cannot be reused, see the log_reuse_wait_desc
>> column in sys.databases
>> Thanks for answering.
>>
>>
>>
>>
>>
>|||Hi Chris
Actually my comment was primarily directly at the OP. You've been around
long enough that I'm sure you've figured out most of the important stuff.
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:O9J8RU7UIHA.5264@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> We do that as best we can. We have alerts fired if the data file expands
> and then we would look at the log size too. We also would get messages if
> the log does get full.
> My question is to see how much improvement we might get by defragging.
> Thanks
> Chris
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OAAg9h1UIHA.2268@.TK2MSFTNGP02.phx.gbl...
>> Limiting your log size is fine, but you should also make sure you have a
>> good idea of how big it needs to be and then add a generous percentage
>> extra before setting the upper limit.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read in
>>> some web site that this maybe a problem with SQL Server Management
>>> Tools. I appreciate any help to get around this and any suggestions
>>> would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>
>>
>>
>>
>>
>|||I would recommend diskkeeper. There are many others from symantec,
perfectdisk (raxco maybe?), mcaffee probably has one, one built into windows
(downgraded version of diskkeeper IIRC).
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
> kevin,
> What can we use to see how many file fragments there are? Running W2K3
> SP2.
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>> Modify your delete to do it in batches. Pick date ranges that will
>> only delete 1-5% of the table at once max for each range and then you
>> will probably be ok.
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "fiaola" <fiaola@.mail.com> wrote in message
>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read in
>>> some web site that this maybe a problem with SQL Server Management
>>> Tools. I appreciate any help to get around this and any suggestions
>>> would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>
>>
>>
>>
>>
>|||Last question on this.
Can these products run in a scan mode when SQL is running so we have a
picture of the fragmentation? I am the DBA, the Infrastructure guys do the
Windows maintenance.
Thanks
Chris
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:13odvkmn08vcfa0@.corp.supernews.com...
>I would recommend diskkeeper. There are many others from symantec,
>perfectdisk (raxco maybe?), mcaffee probably has one, one built into
>windows (downgraded version of diskkeeper IIRC).
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Chris Wood" <anonymous@.microsoft.com> wrote in message
> news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> kevin,
>> What can we use to see how many file fragments there are? Running W2K3
>> SP2.
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I have
>> seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for data/log
>> size and growths (2MB and 1MB growth for data and 1MB and 10% growth
>> for tlog, IIRC). Makes me look good when I have them defrag the hard
>> drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>> But we still don't know how big the log is. I've seen logs that are
>> set at the default 1 MB with no autogrow.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o71gecmi9nmfb@.corp.supernews.com...
>>> Modify your delete to do it in batches. Pick date ranges that will
>>> only delete 1-5% of the table at once max for each range and then
>>> you will probably be ok.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "fiaola" <fiaola@.mail.com> wrote in message
>>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read
>>> in some web site that this maybe a problem with SQL Server
>>> Management Tools. I appreciate any help to get around this and any
>>> suggestions would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find out
>>> why space in the log cannot be reused, see the log_reuse_wait_desc
>>> column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||I know for a fact that several of them can. I don't know about all of them.
BTW, It is recommended that you shut sql server down during a defrag
operation, but I have at least 2 clients using diskkeeper that do not do
this. Do so at your own risk though.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Chris Wood" <anonymous@.microsoft.com> wrote in message
news:uoeWXFHVIHA.5360@.TK2MSFTNGP03.phx.gbl...
> Last question on this.
> Can these products run in a scan mode when SQL is running so we have a
> picture of the fragmentation? I am the DBA, the Infrastructure guys do the
> Windows maintenance.
> Thanks
> Chris
> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
> news:13odvkmn08vcfa0@.corp.supernews.com...
>>I would recommend diskkeeper. There are many others from symantec,
>>perfectdisk (raxco maybe?), mcaffee probably has one, one built into
>>windows (downgraded version of diskkeeper IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:e6$B%23Y9UIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> kevin,
>> What can we use to see how many file fragments there are? Running W2K3
>> SP2.
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13od5m2iv7ec365@.corp.supernews.com...
>>I had a client with 300K+ file fragments when I arrived. 18% throughput
>>improvement from OS file defrag operation. It was on a small RAID10 set
>>(6 drives IIRC).
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Chris Wood" <anonymous@.microsoft.com> wrote in message
>> news:u0Spi6uUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>> Kevin,
>> We also try and size our data and log files with limited growth. I
>> have seen problems with an unlimited log and percent full log alerts.
>> How much improvement would a disk defrag give you on todays raided
>> disks?
>> Thanks
>> Chris
>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>> news:13o7ttr3dli55fd@.corp.supernews.com...
>> Good point. I haven't seen tlogs set with no autogrow that I recall.
>> Almost every client I have been at still has the defaults for
>> data/log size and growths (2MB and 1MB growth for data and 1MB and
>> 10% growth for tlog, IIRC). Makes me look good when I have them
>> defrag the hard drive and pick up a big perf gain. :-)
>> --
>> Kevin G. Boles
>> Indicium Resources, Inc.
>> SQL Server MVP
>> kgboles a earthlink dt net
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:u3AgCjgUIHA.4584@.TK2MSFTNGP03.phx.gbl...
>>> But we still don't know how big the log is. I've seen logs that are
>>> set at the default 1 MB with no autogrow.
>>>
>>> --
>>> HTH
>>> Kalen Delaney, SQL Server MVP
>>> www.InsideSQLServer.com
>>> http://blog.kalendelaney.com
>>>
>>>
>>> "TheSQLGuru" <kgboles@.earthlink.net> wrote in message
>>> news:13o71gecmi9nmfb@.corp.supernews.com...
>>> Modify your delete to do it in batches. Pick date ranges that will
>>> only delete 1-5% of the table at once max for each range and then
>>> you will probably be ok.
>>>
>>> --
>>> Kevin G. Boles
>>> Indicium Resources, Inc.
>>> SQL Server MVP
>>> kgboles a earthlink dt net
>>>
>>>
>>> "fiaola" <fiaola@.mail.com> wrote in message
>>> news:uLj2q0WUIHA.4768@.TK2MSFTNGP02.phx.gbl...
>>> I'm trying to clear some data from a SQL DB table, but i'm getting
>>> this error:
>>> I'm just running a Delete Query using Date Diff function. I read
>>> in some web site that this maybe a problem with SQL Server
>>> Management Tools. I appreciate any help to get around this and
>>> any suggestions would be great.
>>> Msg 9002, Level 17, State 4, Line 2
>>>
>>> The transaction log for database 'EventSentry' is full. To find
>>> out why space in the log cannot be reused, see the
>>> log_reuse_wait_desc column in sys.databases
>>>
>>> Thanks for answering.
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>>
>>
>
Subscribe to:
Posts (Atom)