September 8, 2004 at 8:31 pm
Ours is a heavy transactions environment where millions of records of updated per hour. Our to shrink transactional logs without losing transactions of record at that point. we do every three hours transactional backup.
Thanks in advance
September 8, 2004 at 9:50 pm
DBCC SHRINKFILE is the command you are after. When used for a transaction log file, it will only release space that is at the end of the log file. If there are transactions in that part of the file that have not been backed up ( I assume you are using FULL Recovery), then the log file will not shrink past the last part of the log file. You will need to try again "later" if this occurs.
DBCC SHRINKFILE does not cause any transactions in the log file to be lost.
September 9, 2004 at 3:38 pm
DBCC SHRINKFILE will shrink the physical file, but if you're in a heavy transactional environment, that may not be desirable. First, you probably need to increase the frequency of your transaction log backups. Once you are successfully keeping your log utilization low, then you can think about shrinking the physical file.
A transaction log backup automatically removes the old transactions from the log, and stores them in the backup. So as long as you keep the backups, your transactions are safe.
Steve
September 9, 2004 at 11:59 pm
I had a similar issue a while back.... I found using the following query helped out quite a bit, especially when I scheduled it to run at specific intervals:
Checkpoint
go
Backup log (DBNAME) with Truncate_only
go
Dbcc Shrinkfile (DB LOG FILE)
Hope this helps....
September 10, 2004 at 2:58 am
As some people have already said, you need to do transaction log backups to save your log information. Do not use the truncate_only option unless you really want to loose the log data.
Also, you need to think carefully about why you want to shrink the logs, and the performance impact this will have. Repeated shrink and growth of any DB file will give very bad disk file fragmentation that will harm your performance. We avoid shrinking any DB file unless we expect the space released to not be needed for the next 3 months.
We run transaction log backups every hour. We also have an alert set up for every database to monitor log file use. If this goes over 60%, it will run the transaction log backup. This alert helps keep our log file sizes under control, because any peaks in logging normally get dumped to a backup file before the log file needs to grow.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 10, 2004 at 7:29 am
Hello,
if by "transactional backup" you mean the transaction log backup, then 3 hours is IMHO not often enough and you should consider making it more often. I know this is highly individual, but activity in our DB is much much lower, and we do the log backup every 15 minutes. This helps to keep the size of log manageable - which is around 10GB on our system. Frequent log backups also increase security, in case of failure you only lose work that was done after the last backup (unless the backup files are damaged, too... that's why we copy them at once to several locations).
I agree with those that warn against log file shrinking. We never do that, unless it happens that - due to some extraordinary activity - our log file grows over 25 GB and the disk is almost full. We are always careful to shrink it to its "normal" size (10-15 GB), even if 95% of should be free at the moment.
cheers, Vladan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply