log file on subscriber db growing fast

replication between to sql 2005 servers the publication type is snapshot and
the
subscriber type is transactional
I have a problem with fast growing log files on the subscription database
the data in the db is 48mb but the log file is 60 gb
this is becomming a problem i urgently need to know how to purge the log files
If you don't need to back the logs up and just want them to stay small, then
change the recovery mode to simple. Once the data has been purged, you'll
need to shrink the size of the log file to recover the extra space. If the
simple recovery mode doesn't cause the occupied space to fall, have a look
for an open transaction (dbcc opentran) and kill the relevant SPID if
necessary.
HTH,
Paul Ibison
|||Paul thank you very much it did the job
"Paul Ibison" wrote:

> If you don't need to back the logs up and just want them to stay small, then
> change the recovery mode to simple. Once the data has been purged, you'll
> need to shrink the size of the log file to recover the extra space. If the
> simple recovery mode doesn't cause the occupied space to fall, have a look
> for an open transaction (dbcc opentran) and kill the relevant SPID if
> necessary.
> HTH,
> Paul Ibison
|||thanx Paul,
it worked perfectly
"Paul Ibison" wrote:

> If you don't need to back the logs up and just want them to stay small, then
> change the recovery mode to simple. Once the data has been purged, you'll
> need to shrink the size of the log file to recover the extra space. If the
> simple recovery mode doesn't cause the occupied space to fall, have a look
> for an open transaction (dbcc opentran) and kill the relevant SPID if
> necessary.
> HTH,
> Paul Ibison
|||Paul thank you very much it did the job
"Paul Ibison" wrote:

> If you don't need to back the logs up and just want them to stay small, then
> change the recovery mode to simple. Once the data has been purged, you'll
> need to shrink the size of the log file to recover the extra space. If the
> simple recovery mode doesn't cause the occupied space to fall, have a look
> for an open transaction (dbcc opentran) and kill the relevant SPID if
> necessary.
> HTH,
> Paul Ibison
|||thanx Paul,
it worked perfectly
"Paul Ibison" wrote:

> If you don't need to back the logs up and just want them to stay small, then
> change the recovery mode to simple. Once the data has been purged, you'll
> need to shrink the size of the log file to recover the extra space. If the
> simple recovery mode doesn't cause the occupied space to fall, have a look
> for an open transaction (dbcc opentran) and kill the relevant SPID if
> necessary.
> HTH,
> Paul Ibison
|||thanx Paul it worked fine
if you she this message three times it is because the ms server cannot be
found everytime i post my response
"Paul Ibison" wrote:

> If you don't need to back the logs up and just want them to stay small, then
> change the recovery mode to simple. Once the data has been purged, you'll
> need to shrink the size of the log file to recover the extra space. If the
> simple recovery mode doesn't cause the occupied space to fall, have a look
> for an open transaction (dbcc opentran) and kill the relevant SPID if
> necessary.
> HTH,
> Paul Ibison

Log all performed SQL Statements

Hi there,

I have a little problem concerning the replication of a SQL Server database. The situation is as follows:

We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.

The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.

Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.

Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?

Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.

So long

TheSentinel85

P.S. I am using MSDE / SQL Server 2000

Transactional replication is just design for this case.

It will replay every command at Server B that already issued at Server A.

It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.

Also SQL Server 2000 and MSDE already support it.

Thanks,

Zuomin
|||

Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.

Do you have any good online resource where I can read about replication of SQL Server?

Thanks,

TheSentinel85

|||Sure. Here are some:

Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx

Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.

Thanks,

Zuomin
|||

I have now tried to set up the Replication...again.

But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.

Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.

Is there anything I need to take special care of when setting up the replication?

Thanks,

TheSentinel85

|||Sync replication:

Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.

Option 2:
Set up SQL Agent Job do the sync.

Thanks,

Zuomin
|||

Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.

And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?

|||

Thanks to you both.

@.ZUOMIN:

ok I will try it manually.

@.JRPM:

Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?

How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.

EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(

So long

|||

You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.

|||

Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:

http://technet.microsoft.com/en-us/library/ms151756.aspx

Buck Woody|||

Hi everyone,

sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.

Hopefully we are going to test the whole thing in the next days...

I will let you know what the results are....

Thank you all so far for your suggestions...

So long

TheSentinel85

Log all performed SQL Statements

Hi there,

I have a little problem concerning the replication of a SQL Server database. The situation is as follows:

We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.

The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.

Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.

Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?

Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.

So long

TheSentinel85

P.S. I am using MSDE / SQL Server 2000

Transactional replication is just design for this case.

It will replay every command at Server B that already issued at Server A.

It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.

Also SQL Server 2000 and MSDE already support it.

Thanks,

Zuomin
|||

Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.

Do you have any good online resource where I can read about replication of SQL Server?

Thanks,

TheSentinel85

|||Sure. Here are some:

Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx

Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.

Thanks,

Zuomin
|||

I have now tried to set up the Replication...again.

But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.

Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.

Is there anything I need to take special care of when setting up the replication?

Thanks,

TheSentinel85

|||Sync replication:

Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.

Option 2:
Set up SQL Agent Job do the sync.

Thanks,

Zuomin
|||

Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.

And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?

|||

Thanks to you both.

@.ZUOMIN:

ok I will try it manually.

@.JRPM:

Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?

How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.

EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(

So long

|||

You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.

|||

Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:

http://technet.microsoft.com/en-us/library/ms151756.aspx

Buck Woody|||

Hi everyone,

sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.

Hopefully we are going to test the whole thing in the next days...

I will let you know what the results are....

Thank you all so far for your suggestions...

So long

TheSentinel85

Log all performed SQL Statements

Hi there,

I have a little problem concerning the replication of a SQL Server database. The situation is as follows:

We have an application and a SQL server database running at location A. We now want to have a copy at a Location B of the application and the database. Location B does not need to change anything in the program or the database - it's all just read.

The two locations are only connected by a very slow VPN line once in while. The program files can be transferred by creating a compressed backup file and sending it over the VPN line. The database, however is too large - even compressed it takes far too long to send it. The replication with SQL Server did not work out as well.

Now my idea was to perform only once a full backup at Site A and then send only the differential backups to Site B. Since they are performing full backups at Site A every night, unfortunately this does not work either.

Now my question is, if there is any possibility to do some thing like this. I thought it would be good to just create a log file storing every SQL Statement that has been performed on the Site A server and just send this text file. Is this possible?

Thank you very much for you help in advance. Any other solutions or hints are appreciated as well.

So long

TheSentinel85

P.S. I am using MSDE / SQL Server 2000

Transactional replication is just design for this case.

It will replay every command at Server B that already issued at Server A.

It's based on logs. And it will keep the data at Server B consistent with Server A within the same transactional.

Also SQL Server 2000 and MSDE already support it.

Thanks,

Zuomin
|||

Hm...well I just tried to use the replication but it just did not work. It justs does not synchronize the databases and I don't get any errors.

Do you have any good online resource where I can read about replication of SQL Server?

Thanks,

TheSentinel85

|||Sure. Here are some:

Plan: http://msdn2.microsoft.com/en-us/library/aa179423(SQL.80).aspx

Implement: http://msdn2.microsoft.com/en-us/library/aa237152(SQL.80).aspx

Also for prototype, you can setup the replication from Enterprise Manager. The generate the script as start up.

Thanks,

Zuomin
|||

I have now tried to set up the Replication...again.

But I just get the thing to work. I can set up the server for distribution and I can add a publication. On the B site I can add a Subscription (Pull). The problem is: it just does not synchronize the databases.

Thanks for the web pages but I think that is quite the same what is found in the help file - and that was the first resource I looked at.

Is there anything I need to take special care of when setting up the replication?

Thanks,

TheSentinel85

|||Sync replication:

Option 1:
run executalble file, distrib.exe for transactional replication, replmerg.exe for merge replication.

Option 2:
Set up SQL Agent Job do the sync.

Thanks,

Zuomin
|||

Have you looked at log shipping to a read only standby database? Depending on the data change volumne, it may or may not be an answer, but is another approach to replication.

And what do you mean that replication did not synchronize the databases? I did not see in the thread how you knew?

|||

Thanks to you both.

@.ZUOMIN:

ok I will try it manually.

@.JRPM:

Isn't the log empty if they do a complete backup at site A every night? Or is log shipping different from the transaction log backup?

How I know it did not replicate? The database was just empty and the SQL Agent did not do anything.

EDIT: I just saw that log shipping is not working as it is only available in SQL Server Enterprise Edition.... :-(

So long

|||

You could still perform log shipping within Standard edition, http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx fyi.

|||

Don't give up on replication - it can be a little confusing at first, but it is actually quite useful. Check out these links to help you find the problem:

http://technet.microsoft.com/en-us/library/ms151756.aspx

Buck Woody|||

Hi everyone,

sry I didn't respond a little bit faster. I have now set up the whole thing in a different way. What I do now is perform a Trace on INSERTS, UPDATES and EXEC of SPs on the Site A server and just send this trace file to my other server. I will have to see if this costs too much performance, but I can not imagine that. The trace file upload which is done via FTP should be much quicker than replicating or log shipping or anything, as the compressed trace has only some hundrer kb.

Hopefully we are going to test the whole thing in the next days...

I will let you know what the results are....

Thank you all so far for your suggestions...

So long

TheSentinel85

locks preventing backup of database

The database is configured for single publisher, many subscribers,
merge replication. The maintenance plan started to fail a couple of
months ago and the database would not get backed up. After clearing
all the locks, I am able to backup the database manually. The locks
return again and I'm not able to backup the database with the
maintenance plan. How can I get around the lock issue or solve it so
that I can backup the database again?

Thanks,
Chris"cloverme" <ads4sms@.yahoo.com> wrote in message
news:fe5c75a8.0401071659.5b96ba2c@.posting.google.c om...
> The database is configured for single publisher, many subscribers,
> merge replication. The maintenance plan started to fail a couple of
> months ago and the database would not get backed up. After clearing
> all the locks, I am able to backup the database manually. The locks
> return again and I'm not able to backup the database with the
> maintenance plan. How can I get around the lock issue or solve it so
> that I can backup the database again?

How are you trying to back it up? I've never seen locks prevent a db
backup.

> Thanks,
> Chris

locks on tables...

i have transactional replication with a push, updatable subscriber
configured and
when updates (UPDATEs and DELETEs wrapped in a single transaction) are
executed on the subscriber the changes EVENTUALLY make it back to the
publisher as they should.
the problem i am having is when these sp_MSsync_upd_<tablename> and
sp_MSupd_<tablename> sprocs run on the publisher when the changes occur on
the subscriber they are locking these tables and the entire website (on
publisher side) is unusable due to locks on the records.
but whats weird is that a trace on the publisher that shows all the
sp_MSsync_upd_<tablename> and sp_MSupd_<tablename> calls have SO MANY OF
THEM compared to the actual number of records being deleted on the
subscriber.
Any ideas.
-Terry
Yes, this is one of the problems of using updateable subscribers. Each
singleton in a batch is fired over the network and can cause huge latency
issues, like the one you are seeing. Perhaps you should revisit how you are
doing your replication and select a more appropriate model.
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
"Terry Mulvany" <terry.mulvany@.rouseservices.com> wrote in message
news:OYEe4tnZHHA.3928@.TK2MSFTNGP03.phx.gbl...
>i have transactional replication with a push, updatable subscriber
>configured and
> when updates (UPDATEs and DELETEs wrapped in a single transaction) are
> executed on the subscriber the changes EVENTUALLY make it back to the
> publisher as they should.
> the problem i am having is when these sp_MSsync_upd_<tablename> and
> sp_MSupd_<tablename> sprocs run on the publisher when the changes occur on
> the subscriber they are locking these tables and the entire website (on
> publisher side) is unusable due to locks on the records.
> but whats weird is that a trace on the publisher that shows all the
> sp_MSsync_upd_<tablename> and sp_MSupd_<tablename> calls have SO MANY OF
> THEM compared to the actual number of records being deleted on the
> subscriber.
> Any ideas.
> -Terry
>
>