July 13, 2011 at 2:38 pm
SQLBOT (7/13/2011)
Take a log backup, then do the shrinkfile.Use the truncateonly option in shrinkfile to release all the space after the log truncation point.
Jonathan Kehayias[/url] doesn't agree with you:
'I don't know anyone that I consider to be a "seasoned" professional that would be making an arguement for using TRUNCATE_ONLY over switching to SIMPLE mode to clear the log. That is not intended as a personal attack, but I can provide you links from most of the big names in the SQL Server community that explain the pitfall of using TRUNCATE_ONLY and why it shouldn't be in the product." -From http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/9997e75e-f126-4901-9379-de540a708ec9
_________________________________
seth delconte
http://sqlkeys.com
July 13, 2011 at 2:51 pm
seth delconte (7/13/2011)
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
Truncate_Only on shrinkfile != truncateonly on log backups.
The option Truncate_only on shrink file is, as mortalic stated, only valid for data files. It's ignored for log file.
The option truncateonly on log backups was deprecated in 2005, removed in 2008 and is in absolutely no way related to the option on shrink file (other than the name)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2011 at 5:33 am
mortalic (7/13/2011)
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?
A third party tlog reader. But I don't use one, so I don't know if that will just show the transactions that are occurring or if it will show the status of the transactions as well (checkpointed, truncated, etc.).
I'm positive Gail Shaw has something on her blog (SQL in the Wild) about telling the difference, but my GoogleFu has failed me. I'm not typing in the correct tags to find that particular post. I know she did testing on checkpoints and truncated transactions. I just can't remember what else was going on in that post in order to find it.
EDIT: Oh, hey! Gail is here! Maybe she can remember what blog post I'm crucifying with my poor memory. @=)
Oh, and just because there are no transactions happening Right Now, doesn't mean that previous transactions have been truncated, mortalic. Stop focusing on the Right Now. SQL Server doesn't do Right Now very well when it comes to transactions. It works in its own timeframe.
July 14, 2011 at 5:39 am
mortalic (7/13/2011)
It is currently 10GB and the initial size is the same.
Wait. Initial size is 10GB? What is the file's initial size on the principal server?
I don't think SQL Server will let you shrink smaller than that now. Not if the initial size has been changed. SQL Server is pretty adamant about not going below the initial file size, no matter what you try.
July 14, 2011 at 5:49 am
Brandie Tarvin (7/14/2011)
EDIT: Oh, hey! Gail is here! Maybe she can remember what blog post I'm crucifying with my poor memory. @=)
No such blog post.
Maybe you're thinking of this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2011 at 5:53 am
GilaMonster (7/14/2011)
Brandie Tarvin (7/14/2011)
EDIT: Oh, hey! Gail is here! Maybe she can remember what blog post I'm crucifying with my poor memory. @=)No such blog post.
Maybe you're thinking of this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
Yes. That's exactly what I was thinking of, and is exactly why I couldn't find it on your blog. @=)
Thanks, Gail.
July 14, 2011 at 9:09 am
I think that's the smoking gun.
Gail, result of the query you provide in that post shows that LOG_BACKUP is the culprit.
This brings up a couple questions. Assuming a log backup got deleted (there were space issues on the backup folder last week, so I'm wondering if some of the tlog backups were removed to make space), will it cycle past when the backup expiration period occurs (7 days for my backups)?
If not, what options do I have to get this under control?
July 14, 2011 at 9:17 am
mortalic (7/14/2011)
Assuming a log backup got deleted (there were space issues on the backup folder last week, so I'm wondering if some of the tlog backups were removed to make space), will it cycle past when the backup expiration period occurs (7 days for my backups)?
Will what cycle past?
The presence of the log backup files has absolutely no effect whatsoever on the transaction log. You could take the log backup and immediately delete it for all that SQL cares (ok, if you do then no point in time recovery which is the whole point of log backups)
If log reuse wait is log backup, you need to take a log backup.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2011 at 9:35 am
That's why I'm confused, my transaction log backups run every 15 minutes, no failures for the last couple weeks.
July 14, 2011 at 9:47 am
Then there's probably no problem here. If the backups are succeeding and the log is not growing, there's no problem. Wait reuse reasons are normal, as long as they're not prolonged there's no problem.
As Brandie said, looking at the 'right now' doesn't help in figuring out what happened in the past. There are operations that take a lot of log space. Index rebuilds are the main one. The fact that right now you're only using a small amount of the log doesn't mean that the small amount is all you ever need.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 14, 2011 at 3:56 pm
As Brandie said, looking at the 'right now' doesn't help in figuring out what happened in the past. There are operations that take a lot of log space. Index rebuilds are the main one. The fact that right now you're only using a small amount of the log doesn't mean that the small amount is all you ever need.
Ok, I can agree with that, but I don't understand fully. So today the log file reports an initial size of 5876 vs yesterdays 10,176. That means I could probably shrink it down to that if I wanted to, but I think I'll let the shrink job take care of it tonight. I need to understand this though, what can i do to determine what causes this to fluctuate so much?
July 15, 2011 at 1:43 am
You shouldn't have a shrink job. The recommended practice with the log is to back it up regularly, let it reach the size it needs to be for regular operations and log backup frequency and leave it alone.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 15, 2011 at 5:26 am
Why do you feel the need to shrink your log file? Are you having serious space issues?
If not, don't obsess about file shrinking. It's not worth the headaches you're about to cause for yourself.
If so, then look into adding more hard drive space as your solution. You're going to need it if you expect to keep adding data to your principal database (and hence your secondary db).
July 15, 2011 at 5:38 am
July 15, 2011 at 5:42 am
Jayanth_Kurup (7/15/2011)
Run checkpoint and then try doing a shrink file
I disagree with this advice for reasons stated before this answer was posted.
Viewing 15 posts - 16 through 30 (of 41 total)
You must be logged in to reply to this topic. Login to reply