{"id":522,"date":"2025-07-28T09:22:43","date_gmt":"2025-07-28T00:22:43","guid":{"rendered":"https:\/\/sejiwon.com\/?p=522"},"modified":"2025-07-28T09:22:43","modified_gmt":"2025-07-28T00:22:43","slug":"exprired-subscribtion-cleanup%ec%97%90%eb%9f%ac%ec%9d%b4%ec%9c%a0","status":"publish","type":"post","link":"https:\/\/sejiwon.com\/?p=522","title":{"rendered":"Exprired Subscribtion CleanUp\uc5d0\ub7ec\uc774\uc720"},"content":{"rendered":"\n<p><code>Expired Subscription Clean Up<\/code> \uc791\uc5c5\uc774 \uc2e4\ud328\ud558\ub294 \uacbd\uc6b0\ub294 \uc8fc\ub85c <strong>\ub9ac\ud50c\ub9ac\ucf00\uc774\uc158 \uad6c\uc131 \ubb38\uc81c, \uad8c\ud55c \ubd80\uc871, \ub370\uc774\ud130 \uc190\uc0c1, \ub0b4\ubd80 \uc2dc\uc2a4\ud15c \uc624\ub958<\/strong> \ub54c\ubb38\uc785\ub2c8\ub2e4. \uc774 \uc791\uc5c5\uc740 <strong>\ub9cc\ub8cc\ub41c \uad6c\ub3c5(subscription)<\/strong> \uc744 \uc81c\uac70\ud558\ub294 \uc5ed\ud560\uc744 \ud558\uba70, \ub9ac\ud50c\ub9ac\ucf00\uc774\uc158\uc744 \uc720\uc9c0\uad00\ub9ac\ud558\ub294 \ub370 \uc911\uc694\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \uc8fc\uc694 \uc6d0\uc778 \ubc0f \ud574\uacb0 \ubc29\ubc95<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. <strong>\uad6c\ub3c5\uc790 \uc815\ubcf4 \uc190\uc0c1 \ub610\ub294 \uc77c\uce58\ud558\uc9c0 \uc54a\uc74c<\/strong><\/h3>\n\n\n\n<ul>\n<li><code>MSsubscriptions<\/code>, <code>MSreplication_subscriptions<\/code> \ud14c\uc774\ube14\uc5d0 <strong>\uad6c\ub3c5 \uc815\ubcf4\uac00 \uc190\uc0c1<\/strong>\ub418\uc5c8\uac70\ub098 <strong>Publisher\/Subscriber \uac04 \uba54\ud0c0\ub370\uc774\ud130 \ubd88\uc77c\uce58<\/strong>\uac00 \ubc1c\uc0dd\ud55c \uacbd\uc6b0.<\/li>\n<\/ul>\n\n\n\n<p>\ud83d\udee0\ufe0f \ud574\uacb0:<\/p>\n\n\n\n<ul>\n<li><code>distribution<\/code> DB\uc5d0\uc11c \uc190\uc0c1\ub41c \uad6c\ub3c5 \uc815\ubcf4 \ud655\uc778.<\/li>\n\n\n\n<li>\ud544\uc694 \uc2dc \uc218\ub3d9\uc73c\ub85c \uc798\ubabb\ub41c \ub808\ucf54\ub4dc\ub97c \uc0ad\uc81c\ud558\uac70\ub098 <code>sp_subscription_cleanup<\/code> \uc0ac\uc6a9.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. <strong>\ubc30\ud3ec\uc790(Distributor)\uc5d0 \uc798\ubabb\ub41c \uc5f0\uacb0 \ub610\ub294 \uc124\uc815<\/strong><\/h3>\n\n\n\n<ul>\n<li>\ubc30\ud3ec\uc790\uc5d0\uc11c Publisher \ub610\ub294 Subscriber\uc5d0 \ub300\ud55c \uc5f0\uacb0 \uc124\uc815\uc774 \uc798\ubabb\ub418\uc5b4 \uc624\ub958 \ubc1c\uc0dd.<\/li>\n<\/ul>\n\n\n\n<p>\ud83d\udee0\ufe0f \ud574\uacb0:<\/p>\n\n\n\n<ul>\n<li><code>sp_helpdistributor<\/code>, <code>sp_helpdistributiondb<\/code>, <code>sp_helpdistpublisher<\/code> \ub4f1\uc744 \uc0ac\uc6a9\ud574 \uad6c\uc131 \ud655\uc778.<\/li>\n\n\n\n<li><code>linked server<\/code> \uc124\uc815 \ubb38\uc81c \ud655\uc778.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. <strong>\uc5d0\uc774\uc804\ud2b8 \uad8c\ud55c \ubd80\uc871<\/strong><\/h3>\n\n\n\n<ul>\n<li>SQL Server Agent \uacc4\uc815 \ub610\ub294 \uc2e4\ud589 \uacc4\uc815\uc774 \ud544\uc694\ud55c \uad8c\ud55c(\uc608: <code>db_owner<\/code>)\uc744 \uac00\uc9c0\uace0 \uc788\uc9c0 \uc54a\uc74c.<\/li>\n<\/ul>\n\n\n\n<p>\ud83d\udee0\ufe0f \ud574\uacb0:<\/p>\n\n\n\n<ul>\n<li>SQL Agent \uc791\uc5c5\uc744 \uc2e4\ud589\ud558\ub294 \uacc4\uc815\uc5d0 <code>distribution<\/code> DB\uc5d0 \ub300\ud55c \ucda9\ubd84\ud55c \uad8c\ud55c \ubd80\uc5ec.<\/li>\n\n\n\n<li>SSMS\uc5d0\uc11c \uc791\uc5c5 \uc18d\uc131 \u2192 <code>Steps<\/code> \u2192 \uc2e4\ud589 \uacc4\uc815 \ud655\uc778.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">4. <strong>\uc624\ub798\ub41c\/\ube44\uc815\uc0c1\uc801\uc778 \uad6c\ub3c5\uc774 \uc0ad\uc81c\ub418\uc9c0 \uc54a\uc74c<\/strong><\/h3>\n\n\n\n<ul>\n<li>\uc77c\ubd80 \uad6c\ub3c5\uc740 \uc774\ubbf8 \uc218\ub3d9\uc73c\ub85c \uc0ad\uc81c\ub418\uc5c8\uc9c0\ub9cc, \uad00\ub828 \uba54\ud0c0\ub370\uc774\ud130\uac00 \ub0a8\uc544 \uc788\uc5b4 \uc791\uc5c5\uc774 \uc2e4\ud328\ud568.<\/li>\n<\/ul>\n\n\n\n<p>\ud83d\udee0\ufe0f \ud574\uacb0:<\/p>\n\n\n\n<ul>\n<li><code>MSsubscriptions<\/code>, <code>MSreplication_subscriptions<\/code> \ub4f1\uc758 \ud14c\uc774\ube14\uc5d0\uc11c orphan\ub41c \ud56d\ubaa9\uc744 \ud655\uc778\ud558\uace0 \uc218\ub3d9 \uc0ad\uc81c \uac00\ub2a5.<\/li>\n\n\n\n<li>\uc608: sql\ubcf5\uc0ac\ud3b8\uc9d1<code>SELECT * FROM distribution.dbo.MSsubscriptions WHERE status = 0;<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">5. <strong>Job\uc774 \uc624\ub798 \uac78\ub824 Timeout \ub610\ub294 Deadlock \ubc1c\uc0dd<\/strong><\/h3>\n\n\n\n<ul>\n<li>\uad6c\ub3c5 \uc218\uac00 \ub9ce\uac70\ub098 \uc624\ub798\ub41c \ub808\ucf54\ub4dc\uac00 \ub9ce\uc544 \uc0ad\uc81c \uc2dc \uc2dc\uac04\uc774 \uc624\ub798 \uac78\ub824 Job\uc774 Timeout\ub428.<\/li>\n<\/ul>\n\n\n\n<p>\ud83d\udee0\ufe0f \ud574\uacb0:<\/p>\n\n\n\n<ul>\n<li>\uc791\uc5c5 \uc2dc\uac04 \ub298\ub9ac\uae30 (Job \uc124\uc815 \u2192 Advanced \u2192 Retry\/Timeout \uc124\uc815)<\/li>\n\n\n\n<li>\ud544\uc694 \uc2dc \uad6c\ub3c5\uc744 \ubd84\ud560\ud558\uc5ec \uc218\ub3d9 \uc815\ub9ac<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83d\udd0d \uc5d0\ub7ec \uba54\uc2dc\uc9c0 \uc608\uc2dc\ubcc4 \uc6d0\uc778<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>\uc5d0\ub7ec \uba54\uc2dc\uc9c0<\/th><th>\uc6d0\uc778 \ubc0f \uc124\uba85<\/th><\/tr><\/thead><tbody><tr><td><code>Cannot drop the subscription because it does not exist.<\/code><\/td><td>\uad6c\ub3c5\uc774 \uc774\ubbf8 \uc0ad\uc81c\ub418\uc5c8\ub294\ub370 \uba54\ud0c0\ub370\uc774\ud130\uac00 \ub0a8\uc544\uc788\uc74c<\/td><\/tr><tr><td><code>The subscription does not exist.<\/code><\/td><td>\uc704\uc640 \ub3d9\uc77c<\/td><\/tr><tr><td><code>The process could not execute 'sp_MSsubscription_cleanup'<\/code><\/td><td>\ub0b4\ubd80 \ud504\ub85c\uc2dc\uc800 \ud638\ucd9c \uc2e4\ud328, \uad8c\ud55c \ub610\ub294 \ub370\uc774\ud130 \uc190\uc0c1<\/td><\/tr><tr><td><code>Timeout expired<\/code><\/td><td>\uc0ad\uc81c \uc791\uc5c5\uc774 \uc624\ub798 \uac78\ub824 \uc2dc\uac04 \ucd08\uacfc<\/td><\/tr><tr><td><code>Violation of PRIMARY KEY constraint<\/code><\/td><td>\uc0ad\uc81c \uacfc\uc815\uc5d0\uc11c \ubb34\uacb0\uc131 \uc81c\uc57d \uc704\ubc18 \ubc1c\uc0dd<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2705 \ud655\uc778 \ucffc\ub9ac \uc608\uc2dc<\/h2>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \ubc30\ud3ec\uc790\uc5d0\uc11c \ub9cc\ub8cc\ub41c \uad6c\ub3c5 \uc870\ud68c\nUSE distribution;\nSELECT * FROM MSsubscriptions WHERE status = 0;\n\n-- \ub9cc\ub8cc\ub41c \uad6c\ub3c5 \uc911 \uc0ad\uc81c \ub300\uc0c1 \ud655\uc778\nEXEC sp_replmonitorhelppublisher;\nEXEC sp_replmonitorhelpsubscription;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\ud83e\uddfc \uc218\ub3d9 \uc815\ub9ac \ubc29\ubc95 (\uc8fc\uc758 \ud544\uc694!)<\/h2>\n\n\n\n<blockquote class=\"wp-block-quote\">\n<p>\u26a0\ufe0f <strong>\ud56d\uc0c1 \ubc31\uc5c5 \ud6c4 \uc9c4\ud589\ud558\uc138\uc694.<\/strong><\/p>\n<\/blockquote>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>-- \uc218\ub3d9 \uad6c\ub3c5 \uc0ad\uc81c \uc608\uc2dc\nUSE distribution;\nDELETE FROM MSsubscriptions WHERE status = 0 AND subscriber_db = 'Your_SubDB_Name';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">\u2733\ufe0f \uad8c\uc7a5 \uc870\uce58 \uc21c\uc11c<\/h2>\n\n\n\n<ol>\n<li><strong>SQL Agent Job &gt; Expired Subscription Clean Up<\/strong> \u2192 \uc791\uc5c5 \ud788\uc2a4\ud1a0\ub9ac \ud655\uc778<\/li>\n\n\n\n<li><code>distribution<\/code> DB \ub0b4 \uad6c\ub3c5 \uad00\ub828 \ud14c\uc774\ube14 \uc870\ud68c<\/li>\n\n\n\n<li><code>sp_replmonitorhelppublisher<\/code>, <code>sp_replmonitorhelpsubscription<\/code> \uc2e4\ud589\ud574 \uc0c1\ud0dc \uc810\uac80<\/li>\n\n\n\n<li>\ud544\uc694 \uc2dc \uc218\ub3d9 \uc815\ub9ac \ub610\ub294 \ub9ac\ud50c\ub9ac\ucf00\uc774\uc158 \uc7ac\uc124\uc815 \uace0\ub824<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Expired Subscription Clean Up \uc791\uc5c5\uc774 \uc2e4\ud328\ud558\ub294 \uacbd\uc6b0\ub294 \uc8fc\ub85c \ub9ac\ud50c\ub9ac\ucf00\uc774\uc158 \uad6c\uc131 \ubb38\uc81c, \uad8c\ud55c \ubd80\uc871, \ub370\uc774\ud130 \uc190\uc0c1, \ub0b4\ubd80 \uc2dc\uc2a4\ud15c \uc624\ub958 \ub54c\ubb38\uc785\ub2c8\ub2e4. \uc774 \uc791\uc5c5\uc740 \ub9cc\ub8cc\ub41c \uad6c\ub3c5(subscription) \uc744 \uc81c\uac70\ud558\ub294 \uc5ed\ud560\uc744 \ud558\uba70, \ub9ac\ud50c\ub9ac\ucf00\uc774\uc158\uc744 \uc720\uc9c0\uad00\ub9ac\ud558\ub294 \ub370 \uc911\uc694\ud569\ub2c8\ub2e4. \u2705 \uc8fc\uc694 \uc6d0\uc778 \ubc0f \ud574\uacb0 \ubc29\ubc95 1. \uad6c\ub3c5\uc790 \uc815\ubcf4 \uc190\uc0c1 \ub610\ub294 \uc77c\uce58\ud558\uc9c0 \uc54a\uc74c \ud83d\udee0\ufe0f \ud574\uacb0: 2. \ubc30\ud3ec\uc790(Distributor)\uc5d0 \uc798\ubabb\ub41c \uc5f0\uacb0 \ub610\ub294 \uc124\uc815 \ud83d\udee0\ufe0f \ud574\uacb0: 3.\u2026 <span class=\"read-more\"><a href=\"https:\/\/sejiwon.com\/?p=522\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[11],"tags":[],"_links":{"self":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/522"}],"collection":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=522"}],"version-history":[{"count":1,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/522\/revisions"}],"predecessor-version":[{"id":524,"href":"https:\/\/sejiwon.com\/index.php?rest_route=\/wp\/v2\/posts\/522\/revisions\/524"}],"wp:attachment":[{"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=522"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=522"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sejiwon.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=522"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}