June 27, 2013 at 11:19 am
This is my first foray into using file streams. I have created three OLTP databases on my SQL 2008 R2 server for some new applications under development and then added File Stream to each. After a couple of weeks I got a notice that my log file drive was nearly full. Investigating the cause I found that on each one of the new databases, the log file was set to restricted growth at 2TB and as long as I have selected "Enable Autogrowth" it will not allow me to change that to something less or to unrestricted. Is this some sort of bug or by design?
My databases and logs are being backed up; so it should be truncating the log; but, as the log grows it never allows shrinking on truncation because the restricted file size is not allowing it to shrink. I realize that allowing auto growth comes at a performance cost; but, I am okay with that normally as I have it set to 20%. I am however surprised by the linkage between auto growth and restricted growth size when using file streams. I don't recall any mention/warning of this in the documentation. Does it have something to do with my file group setup (My data and log are in the Primary file group, and by default the file stream is in its own file group)--in other words, do I need to put the tables associated with the file stream data into a different file group as well? Is there some other setting affecting this?
June 27, 2013 at 11:44 am
2TB is the maximum possible size for a transaction log file, so unrestricted and limited to 2TB are exactly the same. It's not a side effect of file streams.
Restricted size has nothing to do with the file not shrinking. The log file will not shrink automatically, no file will. If you really have a good reason to shrink, you do it manually, but it's not something you want to do often.
p.s. Auto growth should not be set to a %, it should be set to a fixed size in MB.
p.p.s. Log files aren't in primary, they're not part of any filegroup. Filegroups are sets of data files only.
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply