July 13, 2011 at 10:40 am
Ok I apologize in advance for this one, usually I can solve this, but for some reason I can't figure out the cause of this one. I have a mirrored database that the transaction log is 10GB and won't shrink, The mirror reports as synchronized, dbcc opentran reports 0 open transactions, the tlog backups are succeeding every 15 minutes. What else can cause the tlog not to shrink?
July 13, 2011 at 11:57 am
Is there an error, or is the log just not visibly shrunk?
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2011 at 12:14 pm
mortalic (7/13/2011)
What else can cause the tlog not to shrink?
The need for the space. Just because the tlog backups are happening doesn't mean the log is going to shrink. There a couple of factors to consider.
1) How big was the tlog when it was first created? If it was created at 10MB, it's not going to shrink below that point no matter what you do.
2) The backups don't automatically shrink the log. It truncates the log. There's a difference. Truncation removes unneeded transactions, allowing for new transactions to be saved in that part of the log. EDIT: It does not alter the actual size of the transaction log.
July 13, 2011 at 12:21 pm
Thank you both for the reply, there is no error. It is currently 10GB and the initial size is the same. It was originally created to be 500MB but obviously log growth has happened as the database gets busier.
Normal log size for this is about 1-5GB, I rarely see it this big. I know why it got there, there is an abnormal job that we ran that generated about 9GB of tlog backups this morning and that is why I am trying to shrink it, so that it gets back down to it's normal size. It completed properly though leaving no transaction open.
July 13, 2011 at 12:26 pm
Here's a script I use to shrink the t-logs manually if they stretch too big due to failed backups or something:
--try/catch guarantees that shrink log will not happen if backup errors
BEGIN TRY
USE mydatabase
--backup database first to truncate inactive portion of log file
BACKUP DATABASE mydatabase TO DISK = 'c:\sqlbackup\mydatabase_[currentdate].bak'
ALTER DATABASE mydatabase
SET RECOVERY SIMPLE
DBCC SHRINKFILE (mydatabase_log,1000)-- 1 gb
ALTER DATABASE mydatabase
SET RECOVERY FULL
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
Brandie:
Is is redundant for me to SET RECOVERY SIMPLE? I think I used to use that WITH ROLLBACK IMMEDIATE to truncate the log, if I didn't care about doing a new backup.
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2011 at 12:39 pm
Pretty sure you need to stick with full recovery for mirroring, Seth.
You also don't need to set the recovery model to simple for using dbcc shrinkfile.
In your script below you are breaking your recovery chain rendiering all your log backups until the next full backup useless.
Craig Outcalt
July 13, 2011 at 12:46 pm
SQLBOT (7/13/2011)
Pretty sure you need to stick with full recovery for mirroring, Seth.You also don't need to set the recovery model to simple for using dbcc shrinkfile.
In your script below you are breaking your recovery chain rendiering all your log backups until the next full backup useless.
Yes, mirroring requires full recovery for sure... My question to Brandie was in general, not a mirroring environment. And the recent full backups can still be restored, albeit without the log backups.
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2011 at 1:06 pm
seth delconte (7/13/2011)
Here's a script I use to shrink the t-logs manually if they stretch too big due to failed backups or something:
--try/catch guarantees that shrink log will not happen if backup errorsBEGIN TRY
USE mydatabase
--backup database first to truncate inactive portion of log file
BACKUP DATABASE mydatabase TO DISK = 'c:\sqlbackup\mydatabase_[currentdate].bak'
ALTER DATABASE mydatabase
SET RECOVERY SIMPLE
DBCC SHRINKFILE (mydatabase_log,1000)-- 1 gb
ALTER DATABASE mydatabase
SET RECOVERY FULL
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH
Brandie:
Is is redundant for me to SET RECOVERY SIMPLE? I think I used to use that WITH ROLLBACK IMMEDIATE to truncate the log, if I didn't care about doing a new backup.
Honestly, Seth, unless I'm missing something in your code, I think it's wrong. You don't truncate a log with a regular database backup. A regular database backup only touches the database data file. Log truncates happen with a transaction log backup. And switching the DB to SIMPLE recovery merely to get out of backing up the log is scary bad.
Don't do that. You're breaking your database. It might not seem that way now, but when you really really need things to work and they don't, you'll waste a lot of time trying to figure out what went wrong.
If you need the database in FULL recovery mode for mirroring, then back it, and your transaction log, up properly. And don't shrink the log unless you absolutely have to. If you regularly backup the log file, the only reason the log should grow is if it really needs that extra space.
July 13, 2011 at 1:27 pm
Agreed, Seth your script would not be a good idea, especially in my situation with a mirror. You want to keep your backup history as long as you keep your backups.
Brandie, you keep coming back to the tlog actually needing the space, but from what I can tell there are virtually no transactions occurring right now (one or two now and then) and the mirror is fully synchronized. What would be the next best way to determine what is causing the tlog to stay that size for an extended period of time?
July 13, 2011 at 1:35 pm
seth delconte (7/13/2011)
SQLBOT (7/13/2011)
Pretty sure you need to stick with full recovery for mirroring, Seth.You also don't need to set the recovery model to simple for using dbcc shrinkfile.
In your script below you are breaking your recovery chain rendiering all your log backups until the next full backup useless.
Yes, mirroring requires full recovery for sure... My question to Brandie was in general, not a mirroring environment. And the recent full backups can still be restored, albeit without the log backups.
You should consider modifying that script and reducing the surface area of risk.
Craig Outcalt
July 13, 2011 at 1:36 pm
Honestly, Seth, unless I'm missing something in your code, I think it's wrong. You don't truncate a log with a regular database backup. A regular database backup only touches the database data file. Log truncates happen with a transaction log backup. And switching the DB to SIMPLE recovery merely to get out of backing up the log is scary bad.
Don't do that. You're breaking your database. It might not seem that way now, but when you really really need things to work and they don't, you'll waste a lot of time trying to figure out what went wrong.
If you need the database in FULL recovery mode for mirroring, then back it, and your transaction log, up properly. And don't shrink the log unless you absolutely have to. If you regularly backup the log file, the only reason the log should grow is if it really needs that extra space.
Thanks for the input... I have been wrong about the database backup vs. log backup... and it makes perfect sense. Not sure how I overlooked that! And as for switching to SIMPLE RECOVERY to truncate the log, it looks like others are using it, but you are correct; it is not recommended. Thanks. 🙂
http://blog.sqlauthority.com/2010/05/03/sql-server-shrinkfile-and-truncate-log-file-in-sql-server-2008/[/url]
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2011 at 1:44 pm
SQLBOT (7/13/2011)
seth delconte (7/13/2011)
SQLBOT (7/13/2011)
Pretty sure you need to stick with full recovery for mirroring, Seth.You also don't need to set the recovery model to simple for using dbcc shrinkfile.
In your script below you are breaking your recovery chain rendiering all your log backups until the next full backup useless.
Yes, mirroring requires full recovery for sure... My question to Brandie was in general, not a mirroring environment. And the recent full backups can still be restored, albeit without the log backups.
You should consider modifying that script and reducing the surface area of risk.
How would you modify it to shrink the log file (if the log got way too big) in a way that would preserve the log recovery chain?
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2011 at 2:03 pm
Take a log backup, then do the shrinkfile.
Use the truncateonly option in shrinkfile to release all the space after the log truncation point.
Craig Outcalt
July 13, 2011 at 2:23 pm
From msdn:
TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files.
That won't help on a Log file.
Here is the query I am trying to use with no luck.
DBCC SHRINKFILE (N'logname' , 1024)
July 13, 2011 at 2:29 pm
mortalic (7/13/2011)
From msdn:TRUNCATEONLY
Releases all free space at the end of the file to the operating system but does not perform any page movement inside the file. The data file is shrunk only to the last allocated extent.
target_size is ignored if specified with TRUNCATEONLY.
TRUNCATEONLY is applicable only to data files.
That won't help on a Log file.
Here is the query I am trying to use with no luck.
DBCC SHRINKFILE (N'logname' , 1024)
I think TRUNCATE_ONLY for BACKUP LOG has been deprecated as of 2008... http://msdn.microsoft.com/en-us/library/ms186865.aspx
_________________________________
seth delconte
http://sqlkeys.com
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply