Not all tables are replicated, some use merge and the others are snapshot based and published regularly to the public website facing server.
However, there's a lot of data (well, large textual data) that's being transferred and it seems to be generating massive log files that continue to grow and grow.
I'm fairly new to adminning an SQL Server box, so was wondering if anyone can tell me what the best way to keep it under control is? I've heard its possible to truncate the logs, effectively deleting any data that has already been processed by subscribing
servers etc.?
As I said, I'm very much new to this and would really appreciate some guidance, if only to the right part of the SQL Server Books Online
Thanks,
Paul
Paul,
In your case you are not using transactional replication, so there are no
complications, and backing up the log will truncate it. Have a look at
BACKUP in BOL. Regular backups of the log should be part of a normal
disaster recovery strategy, and they will also prevent the log file getting
large. If you are running out of disk space then you can create another
logfile on another disk. If the logfile has grown large and this is quite
exceptional, you can use DBCC SHRINKFILE to remove the resultant whitespace.
However, if the logfile will only increase in size again due to normal use,
it is better not to shrink it.
HTH,
Paul Ibison
|||Paul,
I'll double check on the backup process. We do run backups at the end of each evening, but the logs still seem to be growing continuously.
This is the command that runs every evening:
"BACKUP DATABASE [PT] TO DISK = N'D:\PT.bak' WITH INIT , NOUNLOAD , NAME = N'PT backup', NOSKIP , STATS = 10, NOFORMAT"
Is there anything in there that looks wrong? At the moment the space allocated to the transaction log is sitting at around 4.53GB. As I said, as far as I can see the only replication being used is merge and snapshot (in the replication and replication mon
itor folders in enterprise manager).
Thanks again for your help,
Paul
"Paul Ibison" wrote:
> Paul,
> In your case you are not using transactional replication, so there are no
> complications, and backing up the log will truncate it. Have a look at
> BACKUP in BOL. Regular backups of the log should be part of a normal
> disaster recovery strategy, and they will also prevent the log file getting
> large. If you are running out of disk space then you can create another
> logfile on another disk. If the logfile has grown large and this is quite
> exceptional, you can use DBCC SHRINKFILE to remove the resultant whitespace.
> However, if the logfile will only increase in size again due to normal use,
> it is better not to shrink it.
> HTH,
> Paul Ibison
>
>
|||Do you need point in time recovery? If not you should consider using the simple recovery model, and use auto shrink or schedule shrinks of your transaction log.
You need to do at least one backup for the transaction log to start recycling itself (IIRC).
There is a chance that there is an open transaction which is preventing your transaction log from truncating itself. Run a dbcc opentran to identify this tranasaction and if possible kill it.
Also have a look at the following kb articles on more information on how to shrink your transaction log.
http://support.microsoft.com/default...&Product=sql2k
http://support.microsoft.com/default...&Product=sql2k
SQL 7 specific
http://support.microsoft.com/default...b;EN-US;256650
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Paul Ingles" wrote:
> Paul,
> I'll double check on the backup process. We do run backups at the end of each evening, but the logs still seem to be growing continuously.
> This is the command that runs every evening:
> "BACKUP DATABASE [PT] TO DISK = N'D:\PT.bak' WITH INIT , NOUNLOAD , NAME = N'PT backup', NOSKIP , STATS = 10, NOFORMAT"
> Is there anything in there that looks wrong? At the moment the space allocated to the transaction log is sitting at around 4.53GB. As I said, as far as I can see the only replication being used is merge and snapshot (in the replication and replication m
onitor folders in enterprise manager).[vbcol=seagreen]
> Thanks again for your help,
> Paul
> "Paul Ibison" wrote:
|||dbcc opentran reports the following:
Oldest active transaction:
SPID (server process ID) : 107
UID (user ID) : 1
Name : ClearChangeBits
LSN : (649:40395:1)
Start time : Jun 22 2004 12:55:30:030PM
I'm currently running a complete database backup and hopefully that'll clean out the transaction log file so we can get a few more months out of it. Is that the likely oldest active transaction? I can't find any reference to ClearChangeBits and the Start
Time seems extremely recent for it to be causing problems.
Thanks again for all the links!
"Hilary Cotter" wrote:
[vbcol=seagreen]
> Do you need point in time recovery? If not you should consider using the simple recovery model, and use auto shrink or schedule shrinks of your transaction log.
> You need to do at least one backup for the transaction log to start recycling itself (IIRC).
> There is a chance that there is an open transaction which is preventing your transaction log from truncating itself. Run a dbcc opentran to identify this tranasaction and if possible kill it.
> Also have a look at the following kb articles on more information on how to shrink your transaction log.
> http://support.microsoft.com/default...&Product=sql2k
> http://support.microsoft.com/default...&Product=sql2k
> SQL 7 specific
> http://support.microsoft.com/default...b;EN-US;256650
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
>
> "Paul Ingles" wrote:
monitor folders in enterprise manager).[vbcol=seagreen]
|||Paul,
this is a database backup script. You should have one for the log, otherwise
it just carries on growing (a full database backup doesn't truncate the
inactive portion of the log - that occurs after the log backup).
Alternatively as Hilary says using the simple recovery model will truncate
the log regularly and automatically (on a checkpoint). Once truncated you
just need to shring the file (DBCC SHRINKFILE) as log truncation does not
reduce the size of a physical log file, it reduces the size of the logical
log.
HTH,
Paul Ibison
No comments:
Post a Comment