recovery mode and log size

hi friends,
after setting merge replication my log file is incresing continuouly.its going in gbs.what should be the optimum log file size for a database.what are the microsoft recommendations regarding this
what should be the best recovery mode to implement.it is simple,full,bulklogback.
which should be the efficient .
what is the microsoft recommendations for this
please help
thanks
reddy
Reddy,
the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size, ru
n DBCC SHRINGFILE.
HTH,
Paul Ibison
|||paul,
thanks for your solution
regards
reddy
"Paul Ibison" wrote:

> Reddy,
> the size of the transaction log depends on too many factors to recommend a fixed value. As you're using merge replication, this shouldn't be related to transactions not read from the log.
> The frequency of the backup of the database depends on the normal factors - personally I backup the database each evening and the log every 30 mins.
> If it is particularly large then you might increase the frequency of your log backups (if you're doing things this way). Generally, to remove committed transactions - backup the log, truncate the log or use simple recovery mode. To reduce the log size,
run DBCC SHRINGFILE.
> HTH,
> Paul Ibison

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

recordset

Friends,

Is there any thing like a recordset concept in sql server,
where i could loop through and update each rows

I want to update a null column of a table with some values.

I already made a cursor(Shown below)

Instead of this can I make any recordset/resultset and update each rows?

Tks..

Declare @.RpID int
Declare @.JobID int
declare @.ID int
declare @.cnt int

set @.cnt = 0
set @.ID = 0

declare c1 cursor for

select Reprint_ID, Job_ID from ReprintInfo order by Job_ID
open c1
fetch next from c1 into @.RpID, @.JobID
while @.@.Fetch_Status = 0
begin

if (@.ID = @.JobID)
set @.cnt = @.cnt + 1
else
set @.cnt = 1

update reprintinfo set Reprintcount = @.cnt
where Reprint_ID = @.RpID

set @.ID = @.JobID

fetch next from c1 into @.RpID,@.JobID

end

close c1
deallocate c1

Thanks,

MG

A CURSOR is the closest thing to a 'recordset'. However, it is usually NOT the best way to do things in SQL Server. SQL Server handles SET based operations very efficiently.

It seems that you are consecutively numbering the rows for each JoB_ID. That can most likely be done using a SET based approach.

What version of SQL Server are you using?

|||

One way to perform the update if you have an index based on JOB_ID is to use the Transact SQL update extensions in something like:

create table dbo.reprintInfo
( Reprint_ID integer,
Job_ID integer,
reprintCount integer
)
go

create index reprintInfo_updNdx
on reprintInfo (job_id)
go

insert into reprintInfo
select 1, 1, null union all
select 2, 1, null union all
select 3, 2, null union all
select 4, 3, null union all
select 5, 3, null union all
select 6, 3, null union all
select 7, 3, null union all
select 8, 4, null
select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 NULL
-- 2 1 NULL
-- 3 2 NULL
-- 4 3 NULL
-- 5 3 NULL
-- 6 3 NULL
-- 7 3 NULL
-- 8 4 NULL

declare @.count integer
declare @.lastJobId integer
set @.count = 1

update reprintInfo
set reprintCount
= case when @.lastJobId = job_id
then @.count
else 1
end,
@.count = case when @.lastJobId is null
or @.lastJobId <> job_id
then 1
else @.count + 1
end,
@.lastJobId = job_Id
from reprintInfo (index=reprintInfo_updNdx)

select * from reprintInfo

-- Reprint_ID Job_ID reprintCount
-- -- --
-- 1 1 1
-- 2 1 2
-- 3 2 1
-- 4 3 1
-- 5 3 2
-- 6 3 3
-- 7 3 4
-- 8 4 1

record updated datetime

hi friends,
i want to find the record updated datetime. if 1 record is updated in the
table, I want to find the datetime of the updation.
I can't insert a column with timestamp and then insert the data to find. is
there any back process that stores this information.
thanks
vanitha--No, if you have to keep track of these changes you have to implement
that on your own.
CREATE TABLE TracKTable
(
ActionOccured CHAR(1),
Tablename SYSNAME,
ModifiedDate datetime DEFAULT GETDATE(),
ModifiedUser varchar(100) DEFAULT SYSTEM_USER,
ModifiedHost varchar(100) DEFAULT HOST_NAME()
)
CREATE TRIGGER TRG_SomeTriggerOnATable ON SomeTable
FOR INSERT,UPDATE,DELETE
AS
BEGIN
DECLARE @.TriggerAction CHAR(1)
IF (SELECT COUNT(*) From INSERTED) > 0
IF (SELECT COUNT(*) From DELETED) > 0
SET @.TriggerAction = 'U'
ELSE SET @.TriggerAction = 'I'
ELSE SET @.TriggerAction = 'D'
INSERT INTO TrackTable
(ActionOccured,Tablename)
SELECT
@.TriggerAction,
'SomeTable'
END
HTH, Jens Suessmeyer.|||Vanitha
create update trigger which updates getdate() whenevercolumn is updated.Or
insert getdate() directly from the stored procedure.
--
Regards
R.D
--Knowledge gets doubled when shared
"vanitha" wrote:

> hi friends,
> i want to find the record updated datetime. if 1 record is updated in the
> table, I want to find the datetime of the updation.
> I can't insert a column with timestamp and then insert the data to find. i
s
> there any back process that stores this information.
> thanks
> vanitha
>
>|||no I can't insert or update the value inside the table. I also can't
implement triggers. I shd find if there is any back process that does this.
thanks
vanitha
"R.D" wrote:
> Vanitha
> create update trigger which updates getdate() whenevercolumn is updated.O
r
> insert getdate() directly from the stored procedure.
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "vanitha" wrote:
>|||No, there isnt. You could use a log reader thrid party tool for that
like www.lumigent.de logexplorer
HTH, Jens Suessmeyer.