Re: Subscriber Transaction Logs Too Large

Thanks for all your help - I really appreciate it. In your last reply, you
mentioned not shrinking the file too much on SQL 2000. I forgot to mention in
my original post tha I am doing replication between SQL 2000 (Server A) and
SQL 2005 (Server B). That doesn't change anything you mentioned, does it?

Again, thanks for all your help.

"Hilary Cotter" wrote:

the command to dump the log is

Backup log DatabaseName to disk='test.bak'

TO change the recovery model you can do an
alter database databaseName
set RECOVERY FULL

alter database databaseName
set BULK_LOGGED

You don't want to shrink the log too much in SQL 2000. Shrink it to the
point where it doesn't autogrow between dumps.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Lucas Davenport" <LucasDavenport@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:14B7EE07-0074-461F-BB04-6851E9A91C0B@xxxxxxxxxxxxxxxx
I presume this could be done from a script and scheduled with the SQL
Server
Agent to occur at the beginning and end of the snapshot replication. For
instance, set the database to bulk-logged before replication - let the
replication occur - set the database back to full-recovery model. Is that
right?

If so, I think I can handle that part. The part I have a question about is
dropping or dumping the log every 15 minutes. I'm a bit of a novice at
this
stuff, so would you mind elaborating a bit on that portion of your
suggestion?

Lastly, I think I can handle shrinking the log file 2 or more times. I can
do that from a scheduled task at the completion of the replication event
and
just add it to the above series of events, right?

Thanks a bunch for your assistance.

"Hilary Cotter" wrote:

Hello Lucas. I would advise you to put the subscriber database into bulk
logged recovery model when the snapshot is being applied. After that put
it
in full recovery model and dump the log every 15 minutes or so.

You will need to shrink the log 2-4 or possibly more times to get it to a
manageable size after putting it in full recover model.

--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.

This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.

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



"Lucas Davenport" <Lucas Davenport@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:DD8789BC-EE71-4B71-8C24-0489ABC004ED@xxxxxxxxxxxxxxxx
Hello everyone,

I am having a problem with transaction logs on my subscriber server in
a
snapshot/transactional replication environment. My publisher (server A)
has
six (6) databases available for subscription. My subscriber (server B)
pulls
it's subscription from server A for reporting purposes every two hours.
The
log files (db.ldf) and the transaction log backup files are enormous.
I've
attempted truncating the logs, but they won't budge. I have also tried
skrinking to files, but that doesn't work either. This is my first
venture
into replication, and I finally got it to work correctly, only to find
out
that my reporting server stopped this morning because it was out of
space.

For now, I dropped the databases and restored them from a backup
(forcing
the server to recreate new files for the MDF and LDF). This works, but
I
do
not want to do this every two weeks. Any suggestion on how I can get
the
log
files truncated on a regualr basis or generally reduced in size
overall?

Thanks in Advance.






.

Relevant Pages

  • Re: How to Replicate an SQL Server 2000 Database
    ... i logged into sql query analyser first as windows authentication and this is ... The server '' does not exist. ... Then i logged in as i do to administer the Database remotely and got this: ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)
Loading