{"id":6648,"date":"2022-03-06T07:07:00","date_gmt":"2022-03-06T06:07:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=6648"},"modified":"2022-11-10T11:25:54","modified_gmt":"2022-11-10T10:25:54","slug":"clear-transaction-log","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/","title":{"rendered":"Clear the SQL Server transaction log and fix the 9002 error"},"content":{"rendered":"\n<h4 class=\"has-text-align-center wp-block-heading\"><strong><em>How to clear the SQL Server transaction log and fix the error 9002?<\/em><\/strong><\/h4>\n\n\n\n<p>It is time to clear the transaction log when a SQL Server database is slow, or the available disk space is getting abnormally small. Indeed, it is sometimes enough to clear the SQL Server transaction log and reduce the files in the SQL Server database. Often, during this operation, this message appears : The database transaction log is full. Or Check the log_reuse_wait column of sys.databases.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#clear-sql-server-transaction-log-and-manage-the-9002-error\" >Clear SQL Server transaction log and manage the 9002 error<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#run-shrinkdatabase-to-clear-the-transaction-log\" >Run SHRINKDATABASE to clear the transaction log<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#run-shrinkfile-to-reduce-the-database-files-size\" >Run SHRINKFILE to reduce the database files size<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-clear-sql-server-transaction-log-and-manage-the-9002-error\"><span class=\"ez-toc-section\" id=\"clear-sql-server-transaction-log-and-manage-the-9002-error\"><\/span>Clear SQL Server transaction log and manage the 9002 error<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Before emptying the logs, inspect the data in the sys.databases system view of the database and locate the table that is not in the NOTHING state. To avoid the error &#8220;The database transaction log is full&#8221; and to shrink the files, it is necessary to execute two commands, shrink database and then shrink file. Indeed the full detailed error message looks like this one:<\/p>\n\n\n\n<p><em>Microsoft SQL Server Error 9002 : The transaction log for database YOUR-DB is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc COLUMN IN SYS.DATABASES (#9002)<\/em><\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"run-shrinkdatabase-to-clear-the-transaction-log\"><\/span>Run SHRINKDATABASE to clear the transaction log<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>In addition, if trying to run a DBCC SHRINKDATABASE script like the one below. So, run the SHRINKDATABASE command and replace DATABASE_EXAMPLE with the name of the database file. The command will reduce the size of the database on the disk. Adapt the path with the file, stored in a folder of this type, ideally on a dedicated disk: D:\\SQL_DATA\\DBFILE\\<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">USE DATABASE_EXAMPLE;\n\nDBCC SHRINKDATABASE(N'DATABASE_EXAMPLE', 10, TRUNCATEONLY);\n<\/pre>\n\n\n\n<p>In my example, by default the database name looks like this <strong><em>DATABASE_EXAMPLE.mdf<\/em><\/strong>. Note that a SQL Server database main file is a <em>MDF<\/em><strong> <\/strong>file for Master Database File.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"run-shrinkfile-to-reduce-the-database-files-size\"><\/span>Run SHRINKFILE to reduce the database files size<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Then use the SHRINKFILE command to replace DATABASE_EXAMPLE with the name of the SQL Server log file, for example in a dedicated folder like this one D:\\SQL_DATA\\DB\\DBLOG\\. In my example, by default the log file name is like this DATABASE_EXAMPLE_log.<strong>ldf<\/strong>. It&#8217;s a LDF file,  for Log Database File.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">ALTER DATABASE DWH SET RECOVERY SIMPLE;\n\nDBCC SHRINKFILE ('DATABASE_EXAMPLE_log', 1);\n\nALTER DATABASE DATABASE_EXAMPLE SET RECOVERY FULL;\n<\/pre>\n\n\n\n<p>If an error of this type occurs when running one of the commands:<\/p>\n\n\n\n<p><em>The database transaction log is full. Check the log_reuse_wait_desc column in sys.databases. <\/em><\/p>\n\n\n\n<p>Then check the active transactions from the log_reuse_wait column. To do so, run this query in SQL Server Management Studio.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT [name]\n,[database_id]\n,[source_database_id]\n,[owner_sid]\n,[create_date]\n,[compatibility_level]\n,[collation_name]\n,[user_access]\n,[user_access_desc]\n,[is_read_only]\n,[is_auto_close_on]\n,[is_auto_shrink_on]\n,[state]\n,[state_desc]\n,[is_in_standby]\n,[is_cleanly_shutdown]\n,[is_supplemental_logging_enabled]\n,[snapshot_isolation_state]\n,[snapshot_isolation_state_desc]\n,[is_read_committed_snapshot_on]\n,[recovery_model]\n,[recovery_model_desc]\n,[page_verify_option]\n,[page_verify_option_desc]\n,[is_auto_create_stats_on]\n,[is_auto_update_stats_on]\n,[is_auto_update_stats_async_on]\n,[is_ansi_null_default_on]\n,[is_ansi_nulls_on]\n,[is_ansi_padding_on]\n,[is_ansi_warnings_on]\n,[is_arithabort_on]\n,[is_concat_null_yields_null_on]\n,[is_numeric_roundabort_on]\n,[is_quoted_identifier_on]\n,[is_recursive_triggers_on]\n,[is_cursor_close_on_commit_on]\n,[is_local_cursor_default]\n,[is_fulltext_enabled]\n,[is_trustworthy_on]\n,[is_db_chaining_on]\n,[is_parameterization_forced]\n,[is_master_key_encrypted_by_server]\n,[is_published]\n,[is_subscribed]\n,[is_merge_published]\n,[is_distributor]\n,[is_sync_with_backup]\n,[service_broker_guid]\n,[is_broker_enabled]\n,[log_reuse_wait]\n,[log_reuse_wait_desc]\n,[is_date_correlation_on]\n,[is_cdc_enabled]\n,[is_encrypted]\n,[is_honor_broker_priority_on]\nFROM [tempdb].[sys].[databases]<\/pre>\n\n\n\n<p>Then inspect the databases corresponding to the rows with the status <strong><em>ACTIVE_TRANSACTION<\/em><\/strong> in the <em>l<strong>og_reuse_wait_desc<\/strong><\/em> column. Furthermore, it is necessary to run the SHRINKFILE and SHRINKDATABASE script on the database(s) in question to unblock the status and return it to NOTHING.<\/p>\n\n\n\n<p>Finally, after emptying the SQL Server transaction log, it is possible to reduce the database without any problem. Thus, this article shows the procedure to completely <a href=\"https:\/\/expert-only.com\/en\/sql-server-db\/delete-sql-server-database-with-a-script\/\">delete a useless SQL Server database<\/a>.<\/p>\n\n\n\n<p><a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/why-you-should-not-shrink-your-data-files\/\" target=\"_blank\" rel=\"noreferrer noopener\">Reducing logs is not always a good idea for performance<\/a>. Indeed, the relational database management system, or RDBMS, fragment the data after the database reduction. And when rebuilding the indexes, they will take up all the necessary space. That&#8217;s it for this post on how to fix the common SQL Server 9002 error, well known by DBAs.<\/p>\n\n\n\n<div class=\"schema-faq wp-block-yoast-faq-block\"><div class=\"schema-faq-section\" id=\"faq-question-1649568994300\"><strong class=\"schema-faq-question\">How to clear the transaction log with SQL Server ?<\/strong> <p class=\"schema-faq-answer\">To clear the SQL Server transaction log and avoid error 9002, use the commands DBCC SHRINKDATABASE and DBCC SHRINKFILE. To reduce the database and the associated files respectively.<\/p> <\/div> <\/div>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"7K02AdMXRd\"><a href=\"https:\/\/expert-only.com\/en\/dba\/cannot-drop-the-database\/\">Cannot drop the database because it is currently in use SQL Server Error<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Cannot drop the database because it is currently in use SQL Server Error&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/dba\/cannot-drop-the-database\/embed\/#?secret=B1xTAVxmJP#?secret=7K02AdMXRd\" data-secret=\"7K02AdMXRd\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to clear the SQL Server transaction log and fix the error 9002? It is time to clear the transaction log when a SQL Server database is slow, or the available disk space is getting abnormally small. Indeed, it <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\" title=\"Clear the SQL Server transaction log and fix the 9002 error\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6041,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[488],"tags":[],"class_list":{"0":"post-6648","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-dba"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Clear the SQL Server transaction log and fix the 9002 error - DBA<\/title>\n<meta name=\"description\" content=\"How to clear the SQL Server transaction log ? To resolve the 9002 error : &quot;The transaction log for database is full.&quot;, reduce the log file.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Clear the SQL Server transaction log and fix the 9002 error\" \/>\n<meta property=\"og:description\" content=\"How to clear the SQL Server transaction log ? To resolve the 9002 error : &quot;The transaction log for database is full.&quot;, reduce the log file.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-03-06T06:07:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-11-10T10:25:54+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Clear the SQL Server transaction log and fix the 9002 error\",\"datePublished\":\"2022-03-06T06:07:00+00:00\",\"dateModified\":\"2022-11-10T10:25:54+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\"},\"wordCount\":591,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg\",\"articleSection\":[\"SQL Server DBA\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#respond\"]}]},{\"@type\":[\"WebPage\",\"FAQPage\"],\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\",\"url\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\",\"name\":\"Clear the SQL Server transaction log and fix the 9002 error - DBA\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg\",\"datePublished\":\"2022-03-06T06:07:00+00:00\",\"dateModified\":\"2022-11-10T10:25:54+00:00\",\"description\":\"How to clear the SQL Server transaction log ? To resolve the 9002 error : \\\"The transaction log for database is full.\\\", reduce the log file.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#breadcrumb\"},\"mainEntity\":[{\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#faq-question-1649568994300\"}],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Clear the SQL Server transaction log and fix the 9002 error\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#faq-question-1649568994300\",\"position\":1,\"url\":\"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#faq-question-1649568994300\",\"name\":\"How to clear the transaction log with SQL Server ?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"To clear the SQL Server transaction log and avoid error 9002, use the commands DBCC SHRINKDATABASE and DBCC SHRINKFILE. To reduce the database and the associated files respectively.\",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Clear the SQL Server transaction log and fix the 9002 error - DBA","description":"How to clear the SQL Server transaction log ? To resolve the 9002 error : \"The transaction log for database is full.\", reduce the log file.","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":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/","og_locale":"en_US","og_type":"article","og_title":"Clear the SQL Server transaction log and fix the 9002 error","og_description":"How to clear the SQL Server transaction log ? To resolve the 9002 error : \"The transaction log for database is full.\", reduce the log file.","og_url":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-03-06T06:07:00+00:00","article_modified_time":"2022-11-10T10:25:54+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Clear the SQL Server transaction log and fix the 9002 error","datePublished":"2022-03-06T06:07:00+00:00","dateModified":"2022-11-10T10:25:54+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/"},"wordCount":591,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg","articleSection":["SQL Server DBA"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#respond"]}]},{"@type":["WebPage","FAQPage"],"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/","url":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/","name":"Clear the SQL Server transaction log and fix the 9002 error - DBA","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg","datePublished":"2022-03-06T06:07:00+00:00","dateModified":"2022-11-10T10:25:54+00:00","description":"How to clear the SQL Server transaction log ? To resolve the 9002 error : \"The transaction log for database is full.\", reduce the log file.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#breadcrumb"},"mainEntity":[{"@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#faq-question-1649568994300"}],"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/06\/one-hundred-64289A5CC0F_1920x1080.jpeg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Clear the SQL Server transaction log and fix the 9002 error"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#faq-question-1649568994300","position":1,"url":"https:\/\/expert-only.com\/en\/dba\/clear-transaction-log\/#faq-question-1649568994300","name":"How to clear the transaction log with SQL Server ?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"To clear the SQL Server transaction log and avoid error 9002, use the commands DBCC SHRINKDATABASE and DBCC SHRINKFILE. To reduce the database and the associated files respectively.","inLanguage":"en-US"},"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6648","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=6648"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/6648\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6041"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=6648"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=6648"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=6648"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}