Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 R2 File Stream Sets Restricted Growth of Log File to 2TB Expand / Collapse
Author
Message
Posted Thursday, June 27, 2013 11:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 10:10 PM
Points: 824, Visits: 109
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?



Post #1468267
Posted Thursday, June 27, 2013 11:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1468275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse