{"id":79,"date":"2020-01-07T05:22:28","date_gmt":"2020-01-06T19:22:28","guid":{"rendered":"http:\/\/data-engineer.net\/?p=79"},"modified":"2023-02-04T18:10:49","modified_gmt":"2023-02-04T08:10:49","slug":"shrink-sql-database","status":"publish","type":"post","link":"https:\/\/data-engineer.net\/?p=79","title":{"rendered":"Shrink SQL Database"},"content":{"rendered":"\n<h2>Problem<\/h2>\n\n\n\n<p>How to shrink a SQL Database and manage log file size?<\/p>\n\n\n\n<h2>Solution<\/h2>\n\n\n\n<p>In order to shrink a db you need to set the recovery mode to simple first. You may set it to full at end.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>USE {database-name}; \nGO \n-- Truncate the log by changing the database recovery model to SIMPLE. \nALTER DATABASE {database-name}\nSET RECOVERY SIMPLE; \nGO \n-- Shrink the truncated log file to 1 MB. \nDBCC SHRINKFILE ({database-file-name}, 1); \nGO \n-- Reset the database recovery model. \nALTER DATABASE {database-name}\nSET RECOVERY FULL; \nGO<\/code><\/pre>\n\n\n\n<p>In order to manage the size of log files in SQL Server you need to right click on the database in SSMS. Go to Properties. Go to File page and click on three dots next to database file(s) with their File Type set to LOG. Then you can manage the file size.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img src=\"https:\/\/lh4.googleusercontent.com\/LRn8ZQbMpXvPlymvvACkVv7HUZNULB_KGxxmT8PwA55E4cQtfKD8AX8wfdzHHgCT7inEg3Q6mFaSrzUip5rWX5-LVL5_CsE2YF5Qg6OunFtdR-PXfSyUI8V0qOBMO3E6aa0PlklD\" alt=\"\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>How to shrink a SQL Database and manage log file size?<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[2],"tags":[25,24,9],"_links":{"self":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts\/79"}],"collection":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=79"}],"version-history":[{"count":1,"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts\/79\/revisions"}],"predecessor-version":[{"id":80,"href":"https:\/\/data-engineer.net\/index.php?rest_route=\/wp\/v2\/posts\/79\/revisions\/80"}],"wp:attachment":[{"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=79"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=79"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-engineer.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=79"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}