do log files also store select statement?

do log files also store select statement?Do they store any info wrt indexes. If I had clustered and non clustered
indexes , does the tran log backup files hold any info about the indexes.
Will i see reduced size in log file backups if i drop the indexes
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:08bc01c35c85$9fdf4080$a301280a@.phx.gbl...
> Nope. You can not rollback a select statement and it is
> not a transaction. TO see the current log you can issue
> select * from ::fn_dblog(null,null)
> HTH
> Ray Higdon MCSE, MCDBA, CCNA
> >--Original Message--
> >
> >
> >.
> >|||Hassan
The transaction logs capture all changes to your database,
as Ray said they don't record selects (except select into)
because they don't change the database.
When you insert, update or delete as well as making
changes to the data you can be making changes to the
indexes. So yes by dropping indexes (although the drop
will by caught by the log), you will reduce your logging a
small amount.
However indexes are usually there to aid performance, I
would be very careful about droping indexes to keep the
log smaller. The impact on your system is likely to be
higher than the small saving in log space. I would not
advise it unless you perform a very good audit of your
indexes and find that some are not being used.
If you are having problems with your log size, you may be
better off with performing transaction log backups more
often.
Hope this helps
John

Do I need to use "dbcc shrinkfile" command to shrink the transaction log?

HI,
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...
> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?
If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...
> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?
It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).
> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?
If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...
> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>
No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)
> Need your help ! thx a lot!
> Kennethsql

Do I need to use "dbcc shrinkfile" command to shrink the transaction log?

HI,
Q1. my database use "simple" recovery mode now. but i found that
the transaction log is still growth ... about 50Mb per day (database
size is about 1GB)... i found some forum said "if choose simple
recovery mode, no need to shrink the database" <- is it right?
Q2. If i create the maintenance plan, should i choose "reorganize data
and index pages", "update statistics used by query optimizer" and
"remove unused space from database files" (my database is "simple"
recovery mode) ?
Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
backup the tran log to keep the size of tran log (my database is
"simple" recovery mode) ?
Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
log (use the command with "dbcc shrinkfile" and backup tran log) '
any risk using "dbcc indexfrag?
Need your help ! thx a lot!
Kennethsee inline
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"chunman" <chunman@.iloveilove.com> wrote in message
news:3990957.0401280216.1035f23@.posting.google.com...

quote:

---

> HI,
> Q1. my database use "simple" recovery mode now. but i found that
> the transaction log is still growth ... about 50Mb per day (database
> size is about 1GB)... i found some forum said "if choose simple
> recovery mode, no need to shrink the database" <- is it right?

---

If your database was at some point in full recovery mode, and you have
switched to simple, you should probably use shrinkfile to reduce the size of
the log. Even if the db has ALWAYS been in simple recovery mode, all
transactions are logged, then the log is automatically truncated during
checkpoints. However the log must STILL grow to be large enought to handle
the largest single transaction you ever do ( plus everything else that
occurs during the largest transaction.). That might explain the size of your
log...

quote:

---

> Q2. If i create the maintenance plan, should i choose "reorganize data
> and index pages", "update statistics used by query optimizer" and
> "remove unused space from database files" (my database is "simple"
> recovery mode) ?

---

It is a normal index maintenance item to work on indexes... The reorg data
and index pages uses dbcc dbreindex ( which essentially drops and re-creates
all of the indexes.) the tables will be un-available during this time. A
less instrusive way to do index maintenance is to create a job that does
DBCC indexdefrag.. Indexdefrag attempts to do (essentially) the same thing
as dbreindex, but does not hold locks as much, so the tables will be more
available.
Index stats are automatically re-done when indexes are dropped/recreated.
However if you do index maintenance rarely, you may wish to update
statistics in-between index maintenance schedule times. Some people do this,
others do not, and reasonable people differ in their opinions. I try to do
index statistics with 100 sample as often as possible when the database
tables are being changed frequently.
The remove space is only necessary if you have deleted lots of records... I
choose NOT to have that in the plan, but to monitor that separately and make
my own decision (instead of automating this one).

quote:

---

> Q3. or i just use schedule job to run a "dbcc shrinkfile" command and
> backup the tran log to keep the size of tran log (my database is
> "simple" recovery mode) ?

---

If your database has always been in simple recovery mode, and you use
shrinkfile, it will probably re-grow to that same size as before whenever
the biggest transaction runs...

quote:

---

> Q4 is "dbcc INDEXFRAG" commnad useful to keep the size of transaction
> log (use the command with "dbcc shrinkfile" and backup tran log) '
> any risk using "dbcc indexfrag?
>

---

No, it is OK to use indexdefrag instead of dbreindex ( Many people do.)

quote:

---

> Need your help ! thx a lot!
> Kenneth

---

DML without logging

Is there a way to stop logging DML? I have a large delete and I dont want to log the operation, is it possible?Truly non-logged operations are not possible in MS-SQL.

The closest you could probably get would be to copy the data to another data source (most probably a file), delete the data there, truncate your original table, then reload the edited data source back into your table.

-PatP|||No...

But you can bcp the data you want to keep, TRUNCATE the TABLE and then bcp the data back in...use native mode (-n)

EDIT: DAMN, sniped again

And Non logged is a misnomer...even TRUNCAT is logged...but at the page level (I think)

Pat?

dml without generating log transactions ?

Hi There

I know the answer to this is probably no, but had to ask anyway.

Is there a way to perform a dml statement without generating anything in the transaction log ?

The reason i ask is that i have a database that uses simply recovery model, however i need to move a 1 billion row table to this DB, i know that even though it is in simple recovery it is one transaction, it will be written to the log until committed then the space will be released in the log file.

I am using a simple: insert into DW_DB..table select * from DB..other_table.

I have dropped all indexes before the operation.

However this is a big problem, the log for the db in simple recovery that i am moving the data to grew to 128 Gig and the disk ran out of space, the other drives on the machine do not have much space.

Is there a way i can move the billion row table into the new DB without generating such a huge log ?

Thanx

Hi There

Part 2 for the question:

The transaction has rolled back, however there is still 27 gigs space used in the transaction log, there are no open transactions in the db, the db is in simple recovery, i cannot backup the log as it is simple recovery, what is this 27 gigs in the transaction and how do i clear it ?

Thanx

|||

Please ignore my second comment, this problem went away after checkpointing the database, however any feedback ont he original post would be greatly appreciated.

Thanx

|||You can use select into command which is bulk operation and it is minimally logged in the case of simple recovery model.|||Thank you , this worked perfectly.

Distriubtion Error on Stored Procedure

Setting up Transactional Replication.

Log Reader Agent successfull.

Snapshot Agent successfull.

Distribution Agent Error (Replication is failing because of this error. I don't know what is wrong with the stored procedure that causes it not to replicate):

Date 1/19/2007 10:28:13 AM
Log Job History (IS36-MMS_20061213-MMSRepTest-IS4-30)

Step ID 2
Server IS4
Job Name IS36-MMS_20061213-MMSRepTest-IS4-30
Step Name Run agent.
Duration 00:00:11
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
2007-01-19 16:28:24.700 Category:COMMAND
Source: Failed Command
Number:
Message: CREATE PROCEDURE "dbo"."spBTG_GetEventsSince"(@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from
2007-01-19 16:28:24.700 Category:NULL
Source: Microsoft SQL Native Client
Number: 1018
Message: Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Here is the entire source of the sp:

GO

CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END

Linda, you can either change the stored procedure definition in the script generated by the snapshot agent to the following:

CREATE PROCEDURE [dbo].[spBTG_GetEventsSince](@.EventMin datetime, @.BoatID int) AS

BEGIN
create table #TmpEvents
(
BoatHistoryID int null,
PositionID int null,
Event varchar(50) null,
EventDateTime datetime null,
Direction char(1) null,
River char(3) null,
Mile numeric(6,2) null
)

insert into #TmpEvents
select bh.BoatHistoryID, null, Event, EventDateTime, Direction, riverloc, mileloc
--jds 6/22/05 hardcoded index to fix it when you call it from vb
-- from mtsEventHistory eh (NOLOCK)
-- left join mtsBoatHistory bh (NOLOCK) on bh.EventID = eh.EventID
from mtsEventHistory eh with (NOLOCK, index(PK_MtsEventHistory))
left join mtsBoatHistory bh with (NOLOCK, index(IDX_MtsBoatHistory_BoatID)) on bh.EventID = eh.EventID
where EventDateTime > @.EventMin
and BoatID = @.BoatID
and voidflag = 0
And eh.Event IN ('Pickup','Drop','Log Exchange','Off-Layup','Off-Repair','On-Layup','On-Repair','Morning Log','Bt Trip Dir Chg','End Boat Charter Out', 'Begin Boat Charter Out', 'Begin Boat Charter In')
insert into #TmpEvents
select null,BoatPositionID, Description, PositionDateTime, Direction, river, mile
from mtsBoatPosition (NOLOCK)
where PositionDateTime > @.EventMin
and BoatID = @.BoatID
select * from #TmpEvents Order By EventDateTime
drop table #TmpEvents
END

Or, you can change the compatibility level of your subscriber database to 80 or lower.

-Raymond

|||

Raymond,

This sp currently runs on SQL2000. However, before we implement replication, it will be running on 2005. Is the change "adding with" something that will be required for SQL 2005 or does it have to do with replication?

Linda

|||

The use of the "with" keyword with query hints is a SQL2005 requirement that is not directly related to replication. As I mentioned before, you can always change the dbcmptlevel of your subscriber database to 80 or lower so the old syntax in your procedure can be accepted.

-Raymond

|||

Raymond,

On the subscriber database, do I run this command:

EXEC sp_dbcmptlevel MMS_20061213, 80;

to change the cmptlevel?

I did that and now I am getting this error:

Command attempted:

/* ============================================================ */
/* View: vMcsHeaders */
/* ============================================================ */

CREATE VIEW "dbo"."vMcsHeaders" AS

/* VIEW: vMcsHeaders
ABSTRACT: Used by the Contract System to work with contract headers
AUTHOR DATE
EJB 3/31/98 Created
SRM 4/8/98 - Added Customer_Code
EJB 5/1/98 - added filter for only contracts that are not deleteme = 1
EJB 6/10/98 - Ad
(Transaction sequence number: 0x00046CEA0000F21D005600000000, Command ID: 3385)

Error messages:

Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

Invalid object name 'McsDemurrageSetUp'. (Source: MSSQLServer, Error number: 208)
Get help: http://help/208

I notice the McsDemurrageSetUp is it referencing is spelled McsDemurrageSetup. How do I track down what kind of error this is? (Perhaps, I did not get the compatibility set correctly to 80?)

|||

It would appear that you are replicating from a case-insensitive database to a case-sensitive database, this is not a supported scenario.

|||

Raymond,

If I run the command: sp_server_info, both databases say "MIXED" under identifier case. However, I did figure out the problem. The storedprocedure is referencing a table that was not replicated because it did not have a primary key. I did not notice that initially when I noticed the case was different. The database I am working with needs a lot of work before it can successfully be replicated!

Thanks,

Linda

'distributor_admin' is not defined as a remote login at the server

We have set up one of the clustered instance as a remote distributor and
replication has been working properly. However, on the event log of the
active node, I saw the following error appeared every few minutes:
Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
'distributor_admin' is not defined as a remote login at the server. Verify
that you have specified the correct login name. [CLIENT: 10.1.1.28].
REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
as a remote distributor and it is running on active node; the client
(10.1.1.28) is the passive node of the cluster. our environment is windows
2003 and sql server 2005 with sp2.
I wonder if anyone has any idea how to get rid of this error from my server.
Thanks in advance.
Zack.
I wonder if it is a managed news group by Microsoft, if not, someone knows
the managed news group since I was told that it is a managed group and
Microsoft will reply every post?
thanks for your kind reply because I really need to fix this error and don't
want to pay MS for this kind of error. We have paid too much to MS already
for support.
Zack.
m
"dp" wrote:

> We have set up one of the clustered instance as a remote distributor and
> replication has been working properly. However, on the event log of the
> active node, I saw the following error appeared every few minutes:
> Could not connect to server 'REPLICATIONSQL\DISTRIBUTOR' because
> 'distributor_admin' is not defined as a remote login at the server. Verify
> that you have specified the correct login name. [CLIENT: 10.1.1.28].
> REPLICATIONSQL\DISTRIBUTOR is the name of the clustered instance configured
> as a remote distributor and it is running on active node; the client
> (10.1.1.28) is the passive node of the cluster. our environment is windows
> 2003 and sql server 2005 with sp2.
> I wonder if anyone has any idea how to get rid of this error from my server.
> Thanks in advance.
> Zack.
>

Distribution Subsystem: agent (null) failed.

Win 2k & SQL 2k SP3a
This error appears in my Application Log. How can I fix/remove/delete
this? How can I determine what dB this is from?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:12/23/2005
Time:8:00:11 AM
User:ROUSES.COM\Administrator
Computer:ROUSEONE
Description:
Error: 14151, Severity: 18, State: 1
Replication-Replication Distribution Subsystem: agent (null) failed.
The subscription to publication '(null)' has expired and does not
exist.
Data:
0000: 47 37 00 00 12 00 00 00 G7.....
0008: 09 00 00 00 52 00 4f 00 ...R.O.
0010: 55 00 53 00 45 00 4f 00 U.S.E.O.
0018: 4e 00 45 00 00 00 0d 00 N.E....
0020: 00 00 64 00 69 00 73 00 ..d.i.s.
0028: 74 00 72 00 69 00 62 00 t.r.i.b.
0030: 75 00 74 00 69 00 6f 00 u.t.i.o.
0038: 6e 00 00 00 n...
Run your agents one by one to see if you can manually force this error in
your event log.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"LPR-3rd" <lreames@.gmail.com> wrote in message
news:1135354198.113526.29560@.z14g2000cwz.googlegro ups.com...
> Win 2k & SQL 2k SP3a
> This error appears in my Application Log. How can I fix/remove/delete
> this? How can I determine what dB this is from?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 12/23/2005
> Time: 8:00:11 AM
> User: ROUSES.COM\Administrator
> Computer: ROUSEONE
> Description:
> Error: 14151, Severity: 18, State: 1
> Replication-Replication Distribution Subsystem: agent (null) failed.
> The subscription to publication '(null)' has expired and does not
> exist.
> Data:
> 0000: 47 37 00 00 12 00 00 00 G7.....
> 0008: 09 00 00 00 52 00 4f 00 ...R.O.
> 0010: 55 00 53 00 45 00 4f 00 U.S.E.O.
> 0018: 4e 00 45 00 00 00 0d 00 N.E....
> 0020: 00 00 64 00 69 00 73 00 ..d.i.s.
> 0028: 74 00 72 00 69 00 62 00 t.r.i.b.
> 0030: 75 00 74 00 69 00 6f 00 u.t.i.o.
> 0038: 6e 00 00 00 n...
>

Distribution Database Log File Growth

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.

Distribution Database Log File Growth

SQL Server 2000 | Transactional Replication

Suspected Problem: Distribution Database Transaction Log Not Checkpointing

I have a distributor with a distribution database that keeps growing and growing (About 40 GB in 7 days). The database is using the SIMPLE recovery model but the log continues to accumulate data. I have spent time looking at articles such as: "Factors that keep log records alive" (http://msdn2.microsoft.com/en-us/library/ms345414.aspx) and the one thing that stands out is the Checkpoint. I noticed that I can run a manual checkpoint and clear the log. If the log records were still active, the checkpoint would not allow the log to be truncated. This leads me to believe that the server is not properly initiating checkpoints in the Distribution database even though Recovery Model = SIMPLE and the server Recovery Interval = 0.

I found this: "FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected" (http://support.microsoft.com/kb/909369/en-us) but I suspect this is a followup to a problem that may have been introduced with SP4 (since SP4 is a requirement for the hotfix). I am running SP3a (Microsoft SQL Server 2000 - 8.00.850) so I don't think that is the issue. I have several other nearly identical servers with the same version and configuration that have properly maintained log files.

SP4 is not a good option for me at this point - the next upgrade will be to SQL 2K5.

Any thoughts?

Jeff

I solved my own problem. The log file growth had nothing to do with it being the Distribution database. I stumbled upon a trace flag entry in the SQL Startup Parameters "-T3608" which is required to move certain system databases like Model (See article: http://support.microsoft.com/kb/224071/). The flag has been there for several months and was probably added the last time the server was rebuilt or storage was added. I removed the trace flag and checkpoints started occuring normally.

Distribution database became large

Hi,
I have noticed that distribution database which is used for transactional
replicatiton became large. Can I shrink the database? Transaction log is the
same size as the data file. I run dbcc loginfo(distribution), status field
shows that there are a lot of unused parts. Can I shrink transaction log at
least?
I would make sure the distribution database is in Full recovery model and
dump it frequently.
It is wise to try to size the transaction logs to prevent frequent
autogrows, and it is also wise to try to keep them as small as possible. You
will have to experiment to find the ideal size. 10% of the size of the data
files is frequently as good guess for smaller databases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:ADDC4A92-A5A4-4EE5-877A-754D307AC251@.microsoft.com...
> Hi,
> I have noticed that distribution database which is used for transactional
> replicatiton became large. Can I shrink the database? Transaction log is
> the
> same size as the data file. I run dbcc loginfo(distribution), status field
> shows that there are a lot of unused parts. Can I shrink transaction log
> at
> least?
>
|||Hi, Hilary,
1. Why do you suggest having distribution database in Full recovery model?
2. Is it worth to shrink transaction log of the distribution database? I
guess it has grown up in special case when there was very large transaction
to be delivered.
"Hilary Cotter" wrote:

> I would make sure the distribution database is in Full recovery model and
> dump it frequently.
> It is wise to try to size the transaction logs to prevent frequent
> autogrows, and it is also wise to try to keep them as small as possible. You
> will have to experiment to find the ideal size. 10% of the size of the data
> files is frequently as good guess for smaller databases.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:ADDC4A92-A5A4-4EE5-877A-754D307AC251@.microsoft.com...
>
>
|||1. because I like to maintain the size of my tlogs as much as possible. Full
does this. You can use bulk which offers best performance (except for
deletes) but you have no recoverability. Likewise you can use simple with no
recoverability but the transaction log can grow and as the log grows you get
performance degradation. With Full Recovery model you can set the size to
something and get recoverability and minimize auto grow.
2) Once it is sized adequately you should not have to shrink it. Right now I
think its a must to conserve space and to get a very slight performance
improvement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:CBCAAB13-58C6-407A-A338-040B37BA5961@.microsoft.com...[vbcol=seagreen]
> Hi, Hilary,
> 1. Why do you suggest having distribution database in Full recovery model?
> 2. Is it worth to shrink transaction log of the distribution database? I
> guess it has grown up in special case when there was very large
> transaction
> to be delivered.
> "Hilary Cotter" wrote:
|||Thanks, Hilary,
Your answers were very helpful.
Elena
"Hilary Cotter" wrote:

> 1. because I like to maintain the size of my tlogs as much as possible. Full
> does this. You can use bulk which offers best performance (except for
> deletes) but you have no recoverability. Likewise you can use simple with no
> recoverability but the transaction log can grow and as the log grows you get
> performance degradation. With Full Recovery model you can set the size to
> something and get recoverability and minimize auto grow.
> 2) Once it is sized adequately you should not have to shrink it. Right now I
> think its a must to conserve space and to get a very slight performance
> improvement.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:CBCAAB13-58C6-407A-A338-040B37BA5961@.microsoft.com...
>
>

Distribution Database

My distribution Database is set to Simple mode , but the log file is filling
up every few days. the error is as follow
The log file for database 'Distribution_CCMSQL' is full. Back up the
transaction log for the database to free up some log space..
what is going on how can I backup the transaction log when my database is in
Simple Mode.
Thanks,
run a dbcc opentran in there to see if there is an orphaned transaction.
Other than that I would put it into full and dump it every 15-20 minutes.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<msnews.microsoft.com> wrote in message
news:O4h99l7xHHA.3724@.TK2MSFTNGP06.phx.gbl...
> My distribution Database is set to Simple mode , but the log file is
> filling up every few days. the error is as follow
> The log file for database 'Distribution_CCMSQL' is full. Back up the
> transaction log for the database to free up some log space..
> what is going on how can I backup the transaction log when my database is
> in Simple Mode.
> Thanks,
>

Distribution Agent often dies with the following message. Why?

Replication Distribution Agent often dies with the following log entry. At that hour little is ever going on so I am surprised that the error occurs.

Failed Job -> JobName: Instance1-DB1-Instance2-33, StepName: Run agent., Message: Timeout expired. NOTE: The step was retried the requested number of times (10) without succeeding. The step failed..

Any ideas where to start?

Thanks

1. Did you run Distribution Agent in a SQL Agent job? If Yes, can you try to run distrib.exe through command line?

2. Was Distributor/Publisher/Subscriber started when you run the distribution?

3. Do the SQL Agent job owner has the access right to Distributor/Publisher/Subscriber?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

|||Yes, this runs as a Job and it runs normally most of the time so I assume the set-up is correct. It seems there is a transient issue that causes the issue. I just can't tell!|||

Can you check the cleanup job schedule?

What is the retention setting?

This posting is provided AS IS with no warranties, and confers no rights.

|||From the "timeout" message, it is most likely a network/connectivity issue.

Distribution agent fails with the following error message

The concurrent snapshot for publication<PubName> has not been activated by
the Log Reader Agent.
What did I do wrong ? I reinitinalized a subscription and the snapshot has
completed, but the distrib agent fails to start ..
has the snapshot agent started yet? Is the log reader agent started? Can you
bounce it?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim" <jim@.hotmail.com> wrote in message
news:%23USywHPZFHA.3648@.TK2MSFTNGP14.phx.gbl...
> The concurrent snapshot for publication<PubName> has not been activated by
> the Log Reader Agent.
> What did I do wrong ? I reinitinalized a subscription and the snapshot has
> completed, but the distrib agent fails to start ..
>
|||I tried all of those..
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OTgqX%23PZFHA.892@.TK2MSFTNGP10.phx.gbl...
> has the snapshot agent started yet? Is the log reader agent started? Can
you[vbcol=seagreen]
> bounce it?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Jim" <jim@.hotmail.com> wrote in message
> news:%23USywHPZFHA.3648@.TK2MSFTNGP14.phx.gbl...
by[vbcol=seagreen]
has
>
|||I'd drop the subscription and try again.
One more thing, if you deployed this publication from script make sure you
set @.status='active' in your sp_addpublication call,
ie
sp_addpublication 'test',@.status='active'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Jim" <jim@.hotmail.com> wrote in message
news:uLuYLySZFHA.228@.TK2MSFTNGP12.phx.gbl...
>I tried all of those..
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OTgqX%23PZFHA.892@.TK2MSFTNGP10.phx.gbl...
> you
> by
> has
>

Distribution Agent - Stored Procedure Error Logging

I know how I can set-up my distribution agent jobs to log them
to a file and up the verbosity of the procedure.
I am in the process of creating snap-shot's of my stored procedures
for replication. The Snapshot agent works just fine, it is
when I use the distribution agent to send it to the subscriber
that it will stop on the first error it finds.
Is there a way to have it run through all the Stored Procedures
so that I don't have to constantly re-create the publication
by removing the first problem sp?
It helps me give the sp's to our developers in one shot
rather than one at a time.
Dave
I think your best bet is to create a separate publication for each stored
procedure. This way the only procs which fail to be replicated are the
problem ones - the remainder will be replicated.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Gresham" <gresham@.panix.com> wrote in message
news:d62hq1$mar$1@.reader1.panix.com...
> I know how I can set-up my distribution agent jobs to log them
> to a file and up the verbosity of the procedure.
> I am in the process of creating snap-shot's of my stored procedures
> for replication. The Snapshot agent works just fine, it is
> when I use the distribution agent to send it to the subscriber
> that it will stop on the first error it finds.
>
> Is there a way to have it run through all the Stored Procedures
> so that I don't have to constantly re-create the publication
> by removing the first problem sp?
> It helps me give the sp's to our developers in one shot
> rather than one at a time.
>
> Dave
>