Transaction Log file
-
We are currently running SQL 2000 on a win 2000 server. The server is a P42.8 WITH 1.2mb ram.
Our problem is that eventhough we do full database backups regularly, the size of the transaction log keeps growing.
The recovery model is set to full with autoshrink on.
If I choose to keep the transaction log to 100 Mb then it errors saying the transaction log is full & needs backing up. I changed it to unrestricted.
Any ideas??
SCARBS
-
A full recovery model requires you to backup the trx log periodically in order to 'clear' the log. If all you want is to perform full backups periodically without bothering with trx logs, you should set the recovery model to simple e.g.
ALTER DATABASE xx SET RECOVERY SIMPLE
Having said that, you should weigh the pros and cons of each recovery model and choose carefully with regards to your environment.
You sure are pushing the limits with 1.2 MB RAM
.SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. - try this,
use master
backup database <databasename> to <devicename>
backup log <databasename> to <device name>
backup log <databasename> with truncate_only
use <databasename>
dbcc shrinkdatabase(<databasename>,<size in mb>, TRUNCATEONLY)
dbcc shrinkfile(<transaction log file id>,<size in mb>,Truncateonly)
repeat the dbcc shrinkfile command again...
hope it helps
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply