July 11, 2007 at 4:57 am
Hi all,
SQL2000 (SP3) running on Windows 2003.
The transaction log of our reporting database has grown to over 10gb and needs to be truncated.
This database is updated each night from a non-Windows system and therefore has a 'Simple' recovery model.
This database is replicated out to customer specific satellite databases using Snapshot replication
We need to reduce the size of the transaction log because backups are taking too long and we are also running out of space.
Tried to run the following command to truncate the log
backup log [mydb] with no_log
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.
We really dont care about the records in the log as each day the subscribing databases are reloaded fully using snapshot replication.
The is no 'Log Reader Agent' in the Replication folder???
Can anyone tell me how I can truncate this log on a daily basis?
I've looked into sp_repldone but this looks more like an emergency solution as it would need publishing to dropped/enabled.
Thanks in advance
Dave
July 11, 2007 at 5:06 am
use DBCC sqlperf(logspace) to see how much your log file is used.
then use backup log dbname with truncate_only
then dbcc shrinkfile(logfilename,targetsize)
where traget size should be more than the size being used currently.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 11, 2007 at 5:18 am
Hi,
DBCC sqlperf(logspace) returns...
Database Logsize Log used %
XPS-2-SQL10667.6893.100380
backup log [xps-2-sql] with truncate_only
returns the same as doing with no_log (I thought no_log and truncate_only are synomymous)
I planned to do a shrinkdatabase when I've got the log size down a bit.
Dave.
July 11, 2007 at 5:22 am
Do a checkpoint in your database so that all the uncommited transaction commit then you will have enough space to truncate and allocate to OS.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 11, 2007 at 6:34 am
Was this database acting as a publisher in a transactional replication before snapshot replication was set up? If so, you may need to perform some 'cleaning up' in order to remove the 'replicated flags' in the transaction log, which is what is causing you some angst.
Paul
July 11, 2007 at 7:36 am
Not really sure although there's a good chance that is has been used to
'test' out different types of replication. Before this current setup went live
replication disabled/enabled. The transaction log was not truncated though.
Would it be worth dropping the transaction log and re-creating a new
one? Or would I still have the tidying up to do?
Dave.
July 11, 2007 at 7:39 am
No sure if I'm following. There are no uncommited transactions in this database as
it's updated three times each evening in the small hours.
I can certainly do a checkpoint command but what will this solve?
Dave
July 11, 2007 at 7:43 am
use [xps-2-sql]
go
checkpoint
backup log [xps-2-sql] with truncate_only
"The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed."
July 11, 2007 at 7:56 am
When you are *certain* about the way the database is being used, you can do the following:
1. execute sp_removedbreplication '<database name>' at the publisher;
2. try your shrink strategy again;
3. if (2) does not work, then you can use sp_detach_db to detach the database, delete the log file, then use sp_attach_db and supplying all but the log file path.
Paul
July 11, 2007 at 8:11 am
Hi,
Replication is definately snapshot but the way it's used is slightly
non standard. Basically all the SQL agent jobs created by the replication
setup procedure are scripted and merged into a single job.
Because of this I would like to avoid dropping and re-creating replication
if at all possible.
Your point #3 sounds interesting. Surely this will effectively get rid of the bloated
and unrequired logfile and allow me to create a new one??? Would this method allow me
to do the following as part of a daily scehduled job in the future?
a) truncate the log
b) dbcc shrinkdatabase
Dave
July 11, 2007 at 8:18 am
When you reattach a database without specifying the log file, SQL Server will create a new log file for you.
If you take this route, there should not be an issue with the log file as you are experiencing at this moment, because only transactional replication makes extensive use of the log file. HTH.
Paul
July 11, 2007 at 8:46 am
Hi,
Can't detach the database because replication is enabled.
Looks like I may have to completely remove replication and then re-create a new log file.
Can't see any other way around this.
Dave
July 11, 2007 at 10:11 am
You can always script the replication setup, which you should have done already for disaster recovery.
Do try and test the detach/attach, etc on a restored copy of the database if that is possible. When you are confident then try it out on the production system during your maintenance window.
Paul
July 13, 2007 at 3:57 am
issue an sp_repldone and then do the backup/truncate
July 16, 2007 at 1:18 pm
If you use sp_repldone you should be aware of the consequeces !!!!
* Noel
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply