Best of TechNet Blogs–SQL Server 2008 Trace Flag–T 1117

SQL Server Sizing, HA and Performance hints has a blog out on the –T 1117 trace flag that I happened upon; there has been a lot of info about this trace flag, but I never really paid much attention to it outside of the context of tempdb. This blog reminds that the flag, when set, can affect file growth on all files for a database in a given instance, which I missed previously. So if you have a non-tempdb database as I have below and set the files to grow by 200MB for instance, the trace flag does in fact grow these files using the proportional file algorithm as the author describes. Below I see mine grow using the simple script here:

use Test

GO

SELECT CAST('' AS char(7075)) AS col

INTO testTbl

FROM sys.syscolumns a, sys.syscolumns b

My user database does grow in fact by 200MB and both files are included, which I never knew.

The bold line at the bottom of his post, however, does pretty much hit the nail on the head, and that is to go ahead and size the files fully if possible from the get-go. Sometimes this isn’t possible, so be careful when shrinking and growing files. This is also well documented, and wreaks havoc on fragmentation.

Thanks,

Lee

---------------------

Note: This post has not been approved by an MCM.

http://blogs.technet.com/technet_blog_images/b/sql_server_sizing_ha_and_performance_hints/