March 16, 2015 at 10:22 am
Isnt this a weird thing? one of my database data file is 100 GB and the log file is 500 GB.
DB is in full recovery model and the transaction logs happen once in 6 hours.
Even then, the Database log file isn't reducing in size.
What do we do now?
March 16, 2015 at 10:28 am
Backing up the log does not reduce the file size, so at some point you used 500GB of log space between transaction log backups. If you need to reclaim that space run a shrinkfile on the transaction log right after a backup.
March 16, 2015 at 10:29 am
ZZartin (3/16/2015)
Backing up the log does not reduce the file size, so at some point you used 500GB of log space between transaction log backups. If you need to reclaim that space run a shrinkfile on the transaction log right after a backup.
Right after the full backup or the transaction log backup?
March 16, 2015 at 10:31 am
Transaction log backup.
March 16, 2015 at 10:44 pm
The log backup just reclaims the space for committed transactions within the log. It doesn't affect the size of the log at all. If you do need to reduce that file, you'll need to run a shrink on the file. Yes, after the log backup completes. But, even then, you may not be able to shrink the file much. It depends on where, within the file, the currently active transactions are being written. You might have to do a couple of shrink commands after a couple of log backups.
6 hours is a rather long time on long backups. It means, in general, your business is willing to lose up to 6 hours worth of data because that's the amount of time between log backups. I'd double check with them that they're comfortable with that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 17, 2015 at 4:47 am
How about changing the recovery model of the database from full to simple, shrink the file and put that back in full recovery model.
Of course, I will take a full backup before and after this activity.
March 17, 2015 at 1:01 pm
It will work. But is this production? In order to insure that no data will be lost while database in simple recovery model, try to get an outage window from business users/managers.
But what is more important is what will come next. You will need to research the reasons that lead to such huge size of tran log, like to increase frequency of log backup, analyze VLF's with DBCC loginfo, and why log cannot be shrunk after log backup. Do you have replication, log shipping, mirroring? Do you frequently have open transactions that run for long time?
These questions must be answered, otherwise in just few days you will have the same situation when you will need to switch to simple recovery model and run backup.
March 18, 2015 at 3:16 am
SQL Guy 1 (3/17/2015)
It will work. But is this production? In order to insure that no data will be lost while database in simple recovery model, try to get an outage window from business users/managers.But what is more important is what will come next. You will need to research the reasons that lead to such huge size of tran log, like to increase frequency of log backup, analyze VLF's with DBCC loginfo, and why log cannot be shrunk after log backup. Do you have replication, log shipping, mirroring? Do you frequently have open transactions that run for long time?
These questions must be answered, otherwise in just few days you will have the same situation when you will need to switch to simple recovery model and run backup.
After I changed the database to simple recovery model too, am unable to shrink the database log file.
when I ran dbcc opentran, I see the following results:
Transaction information for database 'HF production data'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (33896:7154:2)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The database is currently not configured for replication though.
what other options do I have now?
March 18, 2015 at 3:32 am
Are you unable to shrink because of an error or is it just not shrinking?
run this and let us know what it returned:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'HFM_Prod_Data'
looks like it might be a long-running or uncommitted transaction.
You can check which part of the log file is active by running "DBCC LOGINFO" and looking at the status of the VLF.
March 18, 2015 at 7:19 am
Like I said, you probably won't be able to shrink immediately. The unused log space and closed transactions are before the open transactions. Until that changes, you're going to need to backup and checkpoint again.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 18, 2015 at 8:37 am
I finally followed this article and the issue got fixed
http://stackoverflow.com/questions/12542089/how-to-close-a-sql-server-2008-transaction
However, CDC wasnt enabled on my database :hehe:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply