{"id":1474,"date":"2010-12-10T10:02:14","date_gmt":"2010-12-10T10:02:14","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=1474"},"modified":"2012-07-06T03:14:40","modified_gmt":"2012-07-06T03:14:40","slug":"%e7%b3%bb%e7%bb%9f%e6%95%b0%e6%8d%ae%e5%ba%93","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2010\/12\/10\/%e7%b3%bb%e7%bb%9f%e6%95%b0%e6%8d%ae%e5%ba%93\/","title":{"rendered":"\u7cfb\u7edf\u6570\u636e\u5e93"},"content":{"rendered":"<pre escaped=\"true\" lang=\"tsql\">\r\n\u4e00\u4e2a\u65b0\u7684SQL Server 2005\u5b89\u88c5\u603b\u662f\u5305\u62ec\u56db\u4e2a\u6570\u636e\u5e93\uff1amaster\u3001model\u3001tempdb\u548cmsdb\u3002\u8fd8\u5305\u542b\u7b2c\u4e94\u4e2a&ldquo;\u9690\u85cf\u7684&rdquo;\u6570\u636e\u5e93Resource\r\n\r\n1\uff0cmodel \u6570\u636e\u5e93\u662f Microsoft SQL Server \u521b\u5efa\u5176\u4ed6\u6570\u636e\u5e93\uff08\u5305\u62ec tempdb \u6570\u636e\u5e93\u548c\u7528\u6237\u6570\u636e\u5e93\uff09\u65f6\u4f7f\u7528\u7684\u6a21\u677f\u3002\u521b\u5efa\u6570\u636e\u5e93\u65f6\uff0cmodel \u6570\u636e\u5e93\u7684\u5168\u90e8\u5185\u5bb9\uff08\u5305\u62ec\u6570\u636e\u5e93\u9009\u9879\uff09\u90fd\u4f1a\u88ab\u590d\u5236\u5230\u65b0\u6570\u636e\u5e93\u4e2d\u3002\r\n\r\n2\uff0ctempdb \u7cfb\u7edf\u6570\u636e\u5e93\u662f\u8fde\u63a5\u5230 SQL Server \u5b9e\u4f8b\u7684\u6240\u6709\u7528\u6237\u90fd\u53ef\u7528\u7684\u5168\u5c40\u8d44\u6e90\uff0c\u5b83\u4fdd\u5b58\u6240\u6709\u4e34\u65f6\u8868\u548c\u4e34\u65f6\u5b58\u50a8\u8fc7\u7a0b\u3002\r\n\r\n3\uff0cmaster:\u8d26\u53f7\u3001\u670d\u52a1\u5668\u4fe1\u606f\uff0c\u5728 SQL Server 2005 \u4e2d\uff0c\u7cfb\u7edf\u5bf9\u8c61\u4e0d\u518d\u5b58\u50a8\u5728 master \u6570\u636e\u5e93\u4e2d\uff0c\u800c\u662f\u5b58\u50a8\u5728 Resource \u6570\u636e\u5e93\u4e2d\r\n\r\n4\uff0cResource:Resource\u6570\u636e\u5e93\u7684\u7269\u7406\u6587\u4ef6\u540d\u4e3a Mssqlsystemresource.mdf,\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u6b64\u6587\u4ef6\u4f4d\u4e8e x:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Data\\Mssqlsystemresource.mdf\u3002\r\nSQL Server \u7cfb\u7edf\u5bf9\u8c61\uff08\u4f8b\u5982 sys.objects\uff09\u5728\u7269\u7406\u4e0a\u6301\u7eed\u5b58\u5728\u4e8e Resource \u6570\u636e\u5e93\u4e2d\uff0c\u4f46\u5728\u903b\u8f91\u4e0a\uff0c\u5b83\u4eec\u51fa\u73b0\u5728\u6bcf\u4e2a\u6570\u636e\u5e93\u7684 sys \u67b6\u6784\u4e2d\u3002Resource \u6570\u636e\u5e93\u4e0d\u5305\u542b\u7528\u6237\u6570\u636e\u6216\u7528\u6237\u5143\u6570\u636e\u3002\r\n\r\nSQL Server \u4e0d\u80fd\u5907\u4efd Resource \u6570\u636e\u5e93\u3002Resource \u6570\u636e\u5e93\u4f9d\u8d56\u4e8e master \u6570\u636e\u5e93\u7684\u4f4d\u7f6e\u3002\u5982\u679c\u79fb\u52a8\u4e86 master \u6570\u636e\u5e93\uff0c\u5219\u5fc5\u987b\u4e5f\u5c06 Resource \u6570\u636e\u5e93\u79fb\u52a8\u5230\u76f8\u540c\u7684\u4f4d\u7f6e\u3002\r\n\r\n5\uff0cmsdb\uff0c\u5305\u62ec\u8ba1\u5212\u4fe1\u606f\u3001\u5907\u4efd\u4e0e\u8fd8\u539f\u5386\u53f2\u8bb0\u5f55\u4fe1\u606f\u3001\u90ae\u4ef6\u4fe1\u606f\r\nbackupmediafamily --\u5907\u4efd\u6587\u4ef6\u5730\u5740\r\nbackupset         --\u5907\u4efd\u64cd\u4f5c\u5386\u53f2\r\nrestorefile\t\t  --\u8fd8\u539f\u6587\u4ef6\u5730\u5740                  --\r\nrestorehistory    --\u8fd8\u539f\u64cd\u4f5c\u5386\u53f2\r\n--\u5907\u4efd\u8be6\u7ec6\r\nSELECT b.database_name,b.backup_start_date,b.backup_finish_date,b2.physical_device_name\r\nFROM backupset b\r\nJOIN backupmediafamily b2 ON b.media_set_id = b2.media_set_id\r\nORDER BY b.backup_start_date DESC\r\n\r\nsysjobs --job\r\nsysjobhistory --job\u6267\u884c\u5386\u53f2\r\n\r\nSELECT * FROM sysjobs\r\nSELECT * FROM sysjobschedules\r\nSELECT * FROM sysjobsteps\r\n--job\u8be6\u7ec6\r\nSELECT a.job_id,a.name,s.next_run_date, s.next_run_time,\r\ns2.step_name,s2.command\r\nFROM sysjobs a\r\nJOIN sysjobschedules s ON a.job_id = s.job_id\r\nJOIN sysjobsteps s2 ON a.job_id = s2.job_id\r\n\r\n--job\u72b6\u6001\r\n--\u53d6\u51fa\u5728\u6267\u884c\u7684job\r\n&nbsp;exec msdb.dbo.sp_help_job @Category_Name = N&#39;REPL-Merge&#39;,@execution_status = 1\r\nexecution_status:\r\n1 \u6b63\u5728\u6267\u884c\u3002\r\n2 \u6b63\u5728\u7b49\u5f85\u7ebf\u7a0b\u3002\r\n3 \u5728\u4e24\u6b21\u91cd\u8bd5\u4e4b\u95f4\u3002\r\n4 \u7a7a\u95f2\u3002\r\n5 \u6302\u8d77\u3002\r\n7 \u6b63\u5728\u6267\u884c\u5b8c\u6210\u64cd\u4f5c\r\n--\u4e0d\u8fc7\uff0c\u82e5\u60f3\u5c06\u5b58\u50a8\u8fc7\u7a0b\u6267\u884c\u7684\u7ed3\u679c\u653e\u5230\u4e00\u4e34\u65f6\u8868\u4e2d\uff0c\u4e0a\u8ff0\u5b58\u50a8\u8fc7\u7a0b\u5c06\u62a5\u5d4c\u5957\u9519\u8bef\uff0c\u6b64\u65f6\uff0c\u5e94\u7528master.dbo.xp_sqlagent_enum_jobs\r\nexec master.dbo.xp_sqlagent_enum_jobs 1,hello &nbsp;\r\n\/*p_sqlagent_enum_jobs &lt;is sysadmin (0 or 1)&gt;,\r\n &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &lt;job owner name&gt;\r\n&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp; [, &lt;job id&gt;]\r\n\r\n&nbsp;&nbsp; &nbsp;The first parameter identifies whether you want to return information about all jobs on the server, \r\n    or just jobs owned by a particular job owner. If you specify &quot;0&quot; for this first parameter, \r\n    it means you want to return job information for a particular job owner. If you specify a &quot;1,&quot; \r\n    it means you want information for all jobs. The second parameter identifies the job owner. \r\n    This parameter is required on all calls to this XP but is only used when you specify &quot;0&quot; for the \r\n    first parameter. The third and last parameter only needs to be provided if you want to return \r\n    information about a particular job_id. \r\n*\/\r\n\u53c2\u8003\uff1ahttp:\/\/www.databasejournal.com\/features\/mssql\/article.php\/10894_3491201_2\/Detecting-The-State-of-a-SQL-Server-Agent-Job.htm\r\n\r\n--\u5f00\u5173job\r\nmsdb.dbo.sp_stop_job @job_id = &#39;adfsdfs-sdfsdf&#39;\r\nmsdb.dbo.sp_start_job @job_id =&#39;adfsdfs-sdfsdf&#39;\r\n\r\nsysmail_mailitems --\u90ae\u4ef6\u660e\u7ec6\r\n\r\n\r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u4e2a\u65b0\u7684SQL Server 2005\u5b89\u88c5\u603b\u662f\u5305\u62ec\u56db\u4e2a\u6570\u636e\u5e93\uff1amaster\u3001model\u3001tempdb\u548cmsdb [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-1474","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1474","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=1474"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/1474\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=1474"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=1474"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=1474"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}