{"id":177,"date":"2011-11-23T10:28:23","date_gmt":"2011-11-23T02:28:23","guid":{"rendered":"http:\/\/www.royalwzy.com\/?p=177"},"modified":"2013-12-30T14:16:54","modified_gmt":"2013-12-30T06:16:54","slug":"%e5%a6%82%e4%bd%95%e6%94%b6%e7%bc%a9%e6%95%b0%e6%8d%ae%e5%ba%93%e6%97%a5%e5%bf%97%e6%96%87%e4%bb%b6","status":"publish","type":"post","link":"http:\/\/www.royalwzy.com\/?p=177","title":{"rendered":"\u5982\u4f55\u6536\u7f29\u6570\u636e\u5e93\u65e5\u5fd7\u6587\u4ef6"},"content":{"rendered":"

\u4e0a\u7f51\u67e5\u4e86\u4e00\u4e0b<\/span>,<\/span>\u5f88\u591a\u4eba\u90fd\u9047\u5230\u8fc7\u8fd9\u6837\u7684\u60c5\u51b5<\/span>:<\/span>\u6570\u636e\u5e93\u7684\u6570\u636e\u6587\u4ef6\u624d<\/span>2G<\/span>\u5de6\u53f3<\/span>,<\/span>\u4f46\u662f\u65e5\u5fd7\u6587\u4ef6\u5c31\u5df2\u7ecf<\/span>20G<\/span>\u591a<\/span>,<\/span>\u5982\u4f55\u6536\u7f29\u6570\u636e\u5e93\u65e5\u5fd7\u6587\u4ef6\u5462<\/span>?<\/span><\/span><\/span><\/p>\n

\u5927\u81f4\u7684\u65b9\u6cd5\u6709\u4ee5\u4e0b\u51e0\u79cd<\/span>:<\/span><\/span><\/span><\/p>\n

1.<\/span>DUMP\u00a0<\/span>TRANSACTION database_name WITH NO_LOG\u00a0<\/span>\u6e05\u7a7a\u4e8b\u52a1\u65e5\u5fd7<\/span><\/span><\/span><\/p>\n

2.<\/span>BACKUP LOG WITH NO_LOG<\/span>\u00a0<\/span>\u622a\u65ad\u4e8b\u52a1\u65e5\u5fd7<\/span><\/span><\/span><\/p>\n

3.<\/span>BACKUP LOG WITH TRUNCATE_ONLY<\/span>\u00a0<\/span>\u622a\u65ad\u4e8b\u52a1\u65e5\u5fd7<\/span><\/span><\/span><\/p>\n

4.<\/span>DBCC<\/span>\u00a0SHRINKDATABASE()\u00a0<\/span>\u6536\u7f29\u6307\u5b9a\u6570\u636e\u5e93\u4e2d\u7684\u6570\u636e\u6587\u4ef6\u548c\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f<\/span><\/span><\/span><\/p>\n

5.DBCC SHRINKFILE()\u00a0<\/span>\u6536\u7f29\u5f53\u524d\u6570\u636e\u5e93\u7684\u6307\u5b9a\u6570\u636e\u6216\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f<\/span><\/span><\/span><\/p>\n

6.<\/span>\u5220\u9664\u65e5\u5fd7\u6587\u4ef6<\/span><\/span><\/span><\/p>\n

\u5176\u4e2d\u524d<\/span>3<\/span>\u4e2d\u65b9\u6cd5\u662f<\/span>SQL Server 2008 \u4e2d\u4e0d\u518d\u53ef\u7528\u7684\u6570\u636e\u5e93\u5f15\u64ce\u529f\u80fd<\/span>,<\/span>\u4f7f\u7528\u4e86\u7b2c\u56db\u79cd\u65b9\u6cd5\u611f\u89c9\u6548\u679c\u4e0d\u660e\u663e<\/span>.<\/span>\u4e0b\u9762\u6211\u4eec\u4ecb\u7ecd\u4e00\u4e0b\u5982\u4f55\u4f7f\u7528\u540e\u4e24\u79cd\u65b9\u6cd5\u6536\u7f29\u6570\u636e\u5e93\u65e5\u5fd7\u6587\u4ef6<\/span>.<\/span><\/span><\/span><\/p>\n

1.<\/span>\u4f7f\u7528<\/span>DBCC SHRINKFILE()\u00a0<\/span>\u6536\u7f29\u5f53\u524d\u6570\u636e\u5e93\u7684\u6307\u5b9a\u6570\u636e\u6216\u65e5\u5fd7\u6587\u4ef6\u7684\u5927\u5c0f<\/span>,<\/span>\u4ee5<\/span>AdventureWorks<\/span>\u6570\u636e\u5e93\u4e3a\u4f8b<\/span><\/span><\/span><\/p>\n

USE AdventureWorks;<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

—\u00a0<\/span>\u8bbe\u7f6e<\/span>AdventureWorks<\/span>\u6570\u636e\u5e93\u7684\u6062\u590d\u6a21\u5f0f\u4e3a\u7b80\u5355<\/span><\/span><\/span><\/p>\n

ALTER DATABASE AdventureWorks<\/span><\/span><\/p>\n

SET RECOVERY SIMPLE;<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

—\u00a0<\/span>\u6536\u7f29\u6570\u636e\u5e93\u65e5\u5fd7\u6587\u4ef6\u5230<\/span>1M<\/span><\/span><\/span><\/p>\n

DBCC SHRINKFILE (AdventureWorks_Log, 1);<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

—\u00a0<\/span>\u8bbe\u7f6e<\/span>AdventureWorks<\/span>\u6570\u636e\u5e93\u7684\u6062\u590d\u6a21\u5f0f\u4e3a\u5b8c\u5168<\/span><\/span><\/span><\/p>\n

ALTER DATABASE AdventureWorks<\/span><\/span><\/p>\n

SET RECOVERY FULL;<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

2.<\/span>\u5220\u9664\u65e5\u5fd7\u6587\u4ef6<\/span>,<\/span>\u4ee5<\/span>AdventureWorks<\/span>\u6570\u636e\u5e93\u4e3a\u4f8b<\/span><\/span><\/span><\/p>\n

USE master<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

— \u5206\u79bbAdventureWorks\u6570\u636e\u5e93<\/span><\/span><\/p>\n

EXEC sp_detach_db ‘AdventureWorks’<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

— \u5220\u9664\u65e5\u5fd7\u6587\u4ef6<\/span><\/span><\/p>\n

EXEC master..xp_cmdshell ‘del “C:\/Program Files\/Microsoft SQL Server\/MSSQL10.MSSQLSERVER\/MSSQL\/DATA\/AdventureWorks_Log.ldf”‘<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

— \u5982\u679ccmdshell\u529f\u80fd\u4e0d\u53ef\u4f7f\u7528\u5219\u4f7f\u7528\u4e0b\u9762\u8bed\u53e5\u6fc0\u6d3bcmdshell\u529f\u80fd<\/span><\/span><\/p>\n

USE master<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

sp_configure ‘show advanced options’, 1<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

RECONFIGURE WITH OVERRIDE<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

sp_configure<\/span><\/span>\u00a0‘xp_cmdshell’, 1<\/span><\/span><\/p>\n

RECONFIGURE WITH OVERRIDE<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\u00a0<\/span><\/span><\/p>\n

— \u4f7f\u7528sp_attach_single_file_db\u9644\u52a0.mdf\u6587\u4ef6<\/span><\/span><\/p>\n

USE master<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

EXEC sp_attach_single_file_db ‘AdventureWorks’, ‘C:\/Program Files\/Microsoft SQL Server\/MSSQL10.MSSQLSERVER\/MSSQL\/DATA\/AdventureWorks_Data.mdf’<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n

\u6216\u8005<\/span><\/span><\/p>\n

— \u4f7f\u7528”CREATE DATABASE database_name FOR ATTACH_REBUILD_LOG”\u9644\u52a0\u6570\u636e\u5e93(\u63a8\u8350\u4f7f\u7528)<\/span><\/span><\/p>\n

CREATE DATABASE AdventureWorks ON<\/span><\/span><\/p>\n

(FILENAME = ‘C:\/Program Files\/Microsoft SQL Server\/MSSQL10.MSSQLSERVER\/MSSQL\/DATA\/AdventureWorks_Data.mdf’)<\/span><\/span><\/p>\n

FOR ATTACH_REBUILD_LOG<\/span><\/span><\/p>\n

GO<\/span><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

\u4e0a\u7f51\u67e5\u4e86\u4e00\u4e0b,\u5f88\u591a\u4eba\u90fd\u9047\u5230\u8fc7\u8fd9\u6837\u7684\u60c5\u51b5:\u6570\u636e\u5e93\u7684\u6570\u636e\u6587\u4ef6\u624d2G\u5de6\u53f3,\u4f46\u662f\u65e5\u5fd7\u6587\u4ef6\u5c31\u5df2\u7ecf20G\u591a,\u5982\u4f55\u6536\u7f29\u6570\u636e\u5e93 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[16],"class_list":["post-177","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql_server_2008_r2"],"_links":{"self":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=177"}],"version-history":[{"count":0,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=\/wp\/v2\/posts\/177\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=177"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.royalwzy.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}