{"id":960,"date":"2019-02-28T16:49:53","date_gmt":"2019-02-28T22:49:53","guid":{"rendered":"http:\/\/www.sqlnuggets.com\/?p=960"},"modified":"2019-02-28T16:49:58","modified_gmt":"2019-02-28T22:49:58","slug":"configuring-database-data-and-log-files","status":"publish","type":"post","link":"https:\/\/sqlnuggets.com\/configuring-database-data-and-log-files\/","title":{"rendered":"Configuring Database Data And Log Files"},"content":{"rendered":"\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"576\" src=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280-1024x576.jpg\" alt=\"\" class=\"wp-image-964\" srcset=\"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280-1024x576.jpg 1024w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280-300x169.jpg 300w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280-768x432.jpg 768w, https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg 1280w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\n\n\n\n<p><br>One of the things that Database Administrators typically do is configure the Data and Log file growth settings for the databases on their servers. While these configurations vary by server and database (and by DBA!), I have outlined below a few of my personal preferences that I like to stick to.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Never Use The Defaults<\/h3>\n\n\n\n<p>While &#8220;never&#8221; may be a strong word, and I&#8217;m sure that there are cases with smaller databases that it&#8217;s perfectly fine, I am typically going to modify the default growth settings on the Data and Log files of my databases. The reasons for this vary, and the following scenarios are some examples of when I&#8217;ve needed to change Data or Log file settings:<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Configuring Growth Increments<\/h3>\n\n\n\n<p>The default data and log file growth increments of 1MB and 10{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} are just stupid. I realize that Microsoft needed to put a generic default here, but come on, this is SQL Server, not Access. These defaults are somewhat understandable for SQL Server 2005, but not all the way up to 2014! (<em>Prior to SQL Server 2005 the defaults were Data files 10{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3} and Log files 10{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}<\/em>) Even with SQL Server 2016, where the default auto-growth is 64MB for data and log files, I prefer to set mine higher. I typically won&#8217;t have an auto-growth set less than 512MB, but that&#8217;s just my personal preference. In actuality, I try to avoid auto-growths altogether. The reason for this is that auto-growths can be expensive. Each time an auto-growth event is performed SQL Server has to hold up database processing until the auto-growth is finished. So instead I&#8217;ll increase my data file size so that there&#8217;s plenty of free space, and then monitor it so that when it gets close to filling up the data files I can manually grow them during a maintenance window. I just have the defaults in place in case I&#8217;m not around when the database needs to grow.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- set data file auto-growth to 512MB\nALTER DATABASE YourDB MODIFY FILE (NAME=YourDBFile, FILEGROWTH=512MB);<\/code><\/pre>\n\n\n\n<p>For more information around auto-growth events, <a rel=\"noreferrer noopener\" aria-label=\"Redgate has a really good article (opens in a new tab)\" href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/sql-server-database-growth-and-autogrowth-settings\/\" target=\"_blank\">Redgate has a really good article<\/a>.  It gives some good information about how auto-growths can cause physical fragmentation in your data and log files.  And if you want the details about how log file auto-growths can cause problems with writing to your database, see their follow up article on <a rel=\"noreferrer noopener\" aria-label=\"Transaction Log Fragmentation (opens in a new tab)\" href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/sql-server-transaction-log-fragmentation-a-primer\/\" target=\"_blank\">Transaction Log Fragmentation<\/a>.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Limiting The Maximum Size<\/h3>\n\n\n\n<p>DBAs don&#8217;t like surprises.  And one of the surprises they like the least is getting a 3AM phone call where something has blown up a log file and filled up a hard drive.  And while the typical recommendation around log files is to allow them to grow to the size needed, I also prefer to put safeguards in place to keep them from filling up a drive. <\/p>\n\n\n\n<p>For example, if I have 4 log files sitting on a 100GB hard rive, I may set a maximum size on each log to make sure the drive doesn&#8217;t fill up. Or, maybe I have a log file that I know does a lot of critical processing and I want to make sure it has room for growth if it needs it. I can limit the size of the other logs to ensure they don&#8217;t take up all of the drive space.<\/p>\n\n\n\n<p>So if I wanted to set the maximum size of a log file to 12GB, I would run the following:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- set log file auto-growth to 12GB\nALTER DATABASE YourDB MODIFY FILE (NAME=YourDBLogFile, MAXSIZE=12GB);<\/code><\/pre>\n\n\n\n<p><em>(One thing to point out: If you notice, I specified the size in GB instead of MB in that last statement. SQL Server doesn&#8217;t care, you can use either one and it will figure it out. Pretty cool, huh?)<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Pre-sizing The TempDB Data And Log Files<\/h3>\n\n\n\n<p>TempDB is one of the hardest working databases on your server, and it sees a lot of action. When TempDB is in the middle of working on something, you don&#8217;t want it to have to stop for auto-growth events, this is why DBAs will typically create multiple data files for TempDB, put them on their own drive, and already have them grown to a size large enough for TempDB to work without having to worry about growing the files. In fact, one of the <a rel=\"noreferrer noopener\" aria-label=\"newer recommendations (opens in a new tab)\" href=\"https:\/\/www.brentozar.com\/archive\/2016\/01\/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server\/\" target=\"_blank\">newer recommendations<\/a> in the last few years (which I like) is to create 8 equally sized data files and size the files to take up the whole drive, then turn auto-growth off. That way it has an entire drive to use for its processing and doesn&#8217;t have to worry about growing.<\/p>\n\n\n\n<p>For example, if I wanted to set the data and log file size to 10GB, and turn off auto-growth:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>--grow tempdb data &amp; log files to specified size (10GB) and turn filegrowth off\nALTER DATABASE TempDB MODIFY FILE (NAME=tempdev, SIZE=10240MB, FILEGROWTH=0MB);\nALTER DATABASE TempDB MODIFY FILE (NAME=templog, SIZE=10240MB, FILEGROWTH=0MB);<\/code><\/pre>\n\n\n\n<p>Or, another way of accomplishing this would be to set the maximum size to what your current size is. <\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>--grow tempdb data &amp; log files to specified size (10GB) \nALTER DATABASE TempDB MODIFY FILE (NAME=tempdev, SIZE=10240MB, MAXSIZE=10240MB);\nALTER DATABASE TempDB MODIFY FILE (NAME=templog, SIZE=10240MB, MAXSIZE=10240MB);<\/code><\/pre>\n\n\n\n<p>There are a few other <a rel=\"noreferrer noopener\" aria-label=\"File Options (opens in a new tab)\" href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/t-sql\/statements\/alter-database-transact-sql-file-and-filegroup-options\" target=\"_blank\">File Options<\/a> as well, but these are the ones I typically use.  As always, don&#8217;t just take this and blindly implement it, be sure to test changes such as these to see if they are useful in your environment. <\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the things that Database Administrators typically do is configure the Data and Log file growth settings for the databases on their servers. While these configurations vary by server &#8230;<\/p>\n","protected":false},"author":2,"featured_media":964,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[68],"tags":[64,53,10,67],"class_list":["post-960","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-configurations","tag-data-files","tag-data-storage","tag-database-maintenance","tag-log-files"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>Configuring Database Data And Log Files - SQL Nuggets<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Configuring Database Data And Log Files - SQL Nuggets\" \/>\n<meta property=\"og:description\" content=\"One of the things that Database Administrators typically do is configure the Data and Log file growth settings for the databases on their servers. While these configurations vary by server ...\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL Nuggets\" \/>\n<meta property=\"article:published_time\" content=\"2019-02-28T22:49:53+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-02-28T22:49:58+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"720\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Eric Cobb\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@cfgears\" \/>\n<meta name=\"twitter:site\" content=\"@sqlnugg\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Eric Cobb\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#article\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/\"},\"author\":{\"name\":\"Eric Cobb\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\"},\"headline\":\"Configuring Database Data And Log Files\",\"datePublished\":\"2019-02-28T22:49:53+00:00\",\"dateModified\":\"2019-02-28T22:49:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/\"},\"wordCount\":861,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/cog-wheels-2125183_1280.jpg\",\"keywords\":[\"Data Files\",\"Data Storage\",\"Database Maintenance\",\"Log Files\"],\"articleSection\":[\"Configurations\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/\",\"name\":\"Configuring Database Data And Log Files - SQL Nuggets\",\"isPartOf\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#primaryimage\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/cog-wheels-2125183_1280.jpg\",\"datePublished\":\"2019-02-28T22:49:53+00:00\",\"dateModified\":\"2019-02-28T22:49:58+00:00\",\"breadcrumb\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#primaryimage\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/cog-wheels-2125183_1280.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2019\\\/02\\\/cog-wheels-2125183_1280.jpg\",\"width\":1280,\"height\":720},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/configuring-database-data-and-log-files\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Configuring Database Data And Log Files\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#website\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"name\":\"SQL Nuggets\",\"description\":\"Nuggets Of SQL Server Knowledge\",\"publisher\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\\\/\\\/www.sqlnuggets.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#organization\",\"name\":\"SQL Nuggets\",\"url\":\"http:\\\/\\\/www.sqlnuggets.com\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"contentUrl\":\"https:\\\/\\\/sqlnuggets.com\\\/wp-content\\\/uploads\\\/2023\\\/11\\\/website-logo.jpg\",\"width\":320,\"height\":54,\"caption\":\"SQL Nuggets\"},\"image\":{\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/sqlnugg\",\"https:\\\/\\\/www.linkedin.com\\\/in\\\/ericcobb\\\/\"]},{\"@type\":\"Person\",\"@id\":\"http:\\\/\\\/www.sqlnuggets.com\\\/#\\\/schema\\\/person\\\/210536254addbc1b9d2d95dc1448b38a\",\"name\":\"Eric Cobb\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg\",\"caption\":\"Eric Cobb\"},\"sameAs\":[\"https:\\\/\\\/x.com\\\/cfgears\"],\"url\":\"https:\\\/\\\/sqlnuggets.com\\\/author\\\/eric-cobb\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Configuring Database Data And Log Files - SQL Nuggets","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/","og_locale":"en_US","og_type":"article","og_title":"Configuring Database Data And Log Files - SQL Nuggets","og_description":"One of the things that Database Administrators typically do is configure the Data and Log file growth settings for the databases on their servers. While these configurations vary by server ...","og_url":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/","og_site_name":"SQL Nuggets","article_published_time":"2019-02-28T22:49:53+00:00","article_modified_time":"2019-02-28T22:49:58+00:00","og_image":[{"width":1280,"height":720,"url":"http:\/\/www.sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg","type":"image\/jpeg"}],"author":"Eric Cobb","twitter_card":"summary_large_image","twitter_creator":"@cfgears","twitter_site":"@sqlnugg","twitter_misc":{"Written by":"Eric Cobb","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#article","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/"},"author":{"name":"Eric Cobb","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a"},"headline":"Configuring Database Data And Log Files","datePublished":"2019-02-28T22:49:53+00:00","dateModified":"2019-02-28T22:49:58+00:00","mainEntityOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/"},"wordCount":861,"commentCount":0,"publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg","keywords":["Data Files","Data Storage","Database Maintenance","Log Files"],"articleSection":["Configurations"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/","url":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/","name":"Configuring Database Data And Log Files - SQL Nuggets","isPartOf":{"@id":"http:\/\/www.sqlnuggets.com\/#website"},"primaryImageOfPage":{"@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#primaryimage"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#primaryimage"},"thumbnailUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg","datePublished":"2019-02-28T22:49:53+00:00","dateModified":"2019-02-28T22:49:58+00:00","breadcrumb":{"@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#primaryimage","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg","width":1280,"height":720},{"@type":"BreadcrumbList","@id":"http:\/\/www.sqlnuggets.com\/configuring-database-data-and-log-files\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.sqlnuggets.com\/"},{"@type":"ListItem","position":2,"name":"Configuring Database Data And Log Files"}]},{"@type":"WebSite","@id":"http:\/\/www.sqlnuggets.com\/#website","url":"http:\/\/www.sqlnuggets.com\/","name":"SQL Nuggets","description":"Nuggets Of SQL Server Knowledge","publisher":{"@id":"http:\/\/www.sqlnuggets.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.sqlnuggets.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"http:\/\/www.sqlnuggets.com\/#organization","name":"SQL Nuggets","url":"http:\/\/www.sqlnuggets.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/","url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","contentUrl":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2023\/11\/website-logo.jpg","width":320,"height":54,"caption":"SQL Nuggets"},"image":{"@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/x.com\/sqlnugg","https:\/\/www.linkedin.com\/in\/ericcobb\/"]},{"@type":"Person","@id":"http:\/\/www.sqlnuggets.com\/#\/schema\/person\/210536254addbc1b9d2d95dc1448b38a","name":"Eric Cobb","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","url":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3ca1fc0c7054a668e048f09d412cd4ebf89833c4630fbbfccca78a0678a6bdc2?s=96&d=mm&r=pg","caption":"Eric Cobb"},"sameAs":["https:\/\/x.com\/cfgears"],"url":"https:\/\/sqlnuggets.com\/author\/eric-cobb\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/sqlnuggets.com\/wp-content\/uploads\/2019\/02\/cog-wheels-2125183_1280.jpg","jetpack_shortlink":"https:\/\/wp.me\/pdyDvE-fu","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":1396,"url":"https:\/\/sqlnuggets.com\/does-sql-server-now-distribute-transactions-across-multiple-log-files\/","url_meta":{"origin":960,"position":0},"title":"Does SQL Server Now Distribute Transactions Evenly Across Multiple Log Files?","author":"Eric Cobb","date":"June 23, 2020","format":false,"excerpt":"It is generally known throughout the SQL Server world that having multiple log files on a database is not a good idea. While multiple data files spread across multiple drives will usually give you a performance boost, the same does not hold true for database log files. I have always\u2026","rel":"","context":"In &quot;Availability Groups&quot;","block_context":{"text":"Availability Groups","link":"https:\/\/sqlnuggets.com\/category\/availability-groups\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/06\/disc-1085276_1280.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/06\/disc-1085276_1280.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/06\/disc-1085276_1280.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/06\/disc-1085276_1280.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/06\/disc-1085276_1280.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":487,"url":"https:\/\/sqlnuggets.com\/sql-scripts-find-data-log-file-information\/","url_meta":{"origin":960,"position":1},"title":"SQL Scripts: How To Find Data And Log File Information","author":"Eric Cobb","date":"July 18, 2017","format":false,"excerpt":"As a DBA, one of the things I routinely need to check is how much space is available in my database data and log files. \u00a0In this post of my\u00a0ongoing\u00a0SQL Scripts\u00a0series, we will take a look at a script that gives us some insight into these files in our database.\u2026","rel":"","context":"In &quot;SQL Scripts&quot;","block_context":{"text":"SQL Scripts","link":"https:\/\/sqlnuggets.com\/category\/sql-scripts\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/frank-mckenna-252014-e1500390452743.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/frank-mckenna-252014-e1500390452743.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/frank-mckenna-252014-e1500390452743.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/frank-mckenna-252014-e1500390452743.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/07\/frank-mckenna-252014-e1500390452743.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":706,"url":"https:\/\/sqlnuggets.com\/extended-event-track-data-log-file-size-changes\/","url_meta":{"origin":960,"position":2},"title":"Extended Event To Track Data And Log File Size Changes","author":"Eric Cobb","date":"January 31, 2018","format":false,"excerpt":"One of the things that a DBA tries to avoid\/prevent are automatic file growths in data and log files. We generally like to plan for these things and size the data and log files ourselves to prevent SQL Server from having to do it on its own.\u00a0 But sometimes you\u2026","rel":"","context":"In &quot;Extended Events&quot;","block_context":{"text":"Extended Events","link":"https:\/\/sqlnuggets.com\/category\/extended-events\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/01\/fast-traffic.jpg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/01\/fast-traffic.jpg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/01\/fast-traffic.jpg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/01\/fast-traffic.jpg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2018\/01\/fast-traffic.jpg?resize=1050%2C600&ssl=1 3x"},"classes":[]},{"id":1534,"url":"https:\/\/sqlnuggets.com\/setting-the-database-owner-compatibility-level-and-recovery-model-with-powershell\/","url_meta":{"origin":960,"position":3},"title":"Setting The Database Owner, Compatibility Level, And Recovery Model With PowerShell","author":"Eric Cobb","date":"December 30, 2020","format":false,"excerpt":"One of the great things about dbatool is that it allows you to run commands against multiple things. Sometimes you may need to run the same command on every server, or every database on a server, or every database on every server (not recommended). Today we're going to look at\u2026","rel":"","context":"In &quot;Configurations&quot;","block_context":{"text":"Configurations","link":"https:\/\/sqlnuggets.com\/category\/configurations\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=1050%2C600&ssl=1 3x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2020\/12\/pexels-photo-97077.jpeg?resize=1400%2C800&ssl=1 4x"},"classes":[]},{"id":27,"url":"https:\/\/sqlnuggets.com\/automating-backups-for-sql-server-2008-r2-express-edition\/","url_meta":{"origin":960,"position":4},"title":"Automating backups for SQL Server 2008 R2 Express Edition","author":"Eric Cobb","date":"June 9, 2015","format":false,"excerpt":"One of the limitations of SQL Server Express is that you can't automate backups in it via the SQL Server Agent like you can the other versions of SQL Server. You have to manually write the backup scripts and then schedule them to run in Windows Scheduled Tasks. This really\u2026","rel":"","context":"In &quot;Backups&quot;","block_context":{"text":"Backups","link":"https:\/\/sqlnuggets.com\/category\/backups\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":690,"url":"https:\/\/sqlnuggets.com\/corruption-backup-compression-tde-sql-2017\/","url_meta":{"origin":960,"position":5},"title":"Corruption With Backup Compression And TDE In SQL 2017","author":"Eric Cobb","date":"January 16, 2018","format":false,"excerpt":"Last week I spent a good bit of time dealing with a corruption issue, and as it turns out I may actually have found a bug in SQL Server 2017.\u00a0 I say \"may\" because even though I have been working a Microsoft SQL Server PFE (Premier Field Engineer) and providing\u2026","rel":"","context":"In &quot;Backups&quot;","block_context":{"text":"Backups","link":"https:\/\/sqlnuggets.com\/category\/backups\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=700%2C400&ssl=1 2x, https:\/\/i0.wp.com\/sqlnuggets.com\/wp-content\/uploads\/2017\/11\/corrupt-147974_1280.png?resize=1050%2C600&ssl=1 3x"},"classes":[]}],"_links":{"self":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/960","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/comments?post=960"}],"version-history":[{"count":0,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/posts\/960\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media\/964"}],"wp:attachment":[{"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/media?parent=960"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/categories?post=960"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlnuggets.com\/wp-json\/wp\/v2\/tags?post=960"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}