Backup does not clear the log files...
-
HI can anyone tell me why the backup is not clearing the log files. MSSQL2000
Full backup should do this automattically - it does not
Log Back up & Remove inactive entries check box is ticked - does not work either
Log files just keep on growing and growing ... unless we do a dbshrinkfile...not exactly the way one wants to operate now is it...
Any and all help will be appreciated.
I have considered limiting the size that the log files can grow to, but am concerned this will potentially bring system to a grinding halt...
The best part is the system was running fine for over two years with very little input required...now it is like a naughty little brat that needs to be watched constantly...
-
Are you talking about the physical size of the log files, or the fact that inactive log entries are not truncated during the backup? Full and Differential backups do not remove inactive log entries in the transaction log. This is only done by the transaction log backup.
If you are talking about the physical size of the transaction log, this will grow to accomadate the number of log records written potentially taking up the entire disk if allowed. It is possible that you may need to consider taking transaction log backups more frequently to keep the physical size of the log down.
Also, the transaction log backup itself, will not shrink the physical size of the transaction log. This can be done manually, like it looks like you are doing, or by setting the autoshrink option to ON, but I don't recommend that on a production database, as it could affect system performance.
Sorry if this doesn't help much.
Lynn
-
Iinactive log entries are not being truncated by the transaction log backup...that is precisly the concern .
Although I must admit I thought that a full backup did this too...
What happens then is that every so ofetn, aprox every two weeks there is a need to run dbccshrinkfile - truncate only
Anyway, yes I am concerned about limiting the size of the transaction logs as this will inevitibly bring the system to a grinding halt, problem is that I cant afford the system to carry on the way it is... It has become a high maintainance system...and it never was before...
-
Looking at BOL, dbcc shrinkfile truncate_only, releases unused space in the log file to the operating system, shrinking the physical size of the transaction log.
You may want to look at the transaction log using the task pad view of the database before and after a transaction log backup. You might find that inactive log entries are being truncated. The issue you are having my require that you do your tranasction log backups more frequently. How often do you do transaction log backups now? Has your system experienced any significant growth in use recently?
-
I had a database that was behaving similarly. What I ended up doing was taking a transaction log backup at the end of the day (when no users were connected), running a dbcc shrinkfile with truncate_only, then setting, via a database maintenance plan, my transaction log to be backed up every hour during core business hours. (I take a full database backup nightly.) I then specified "Remove files older than 1 week" for my .TRN files to keep from taking up so much disk space.
Hope this helps.
-
Check to see if the database is marked for publication for replication. If so, then you might have transactions in the log marked for replication which have not been picked up by a distribution process. Usually this is a result of replication not being properly removed.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply