{"id":2470,"date":"2024-12-26T17:17:30","date_gmt":"2024-12-26T09:17:30","guid":{"rendered":"http:\/\/www.jh1999.cn\/?p=2470"},"modified":"2026-03-02T17:19:10","modified_gmt":"2026-03-02T09:19:10","slug":"sql-server%e5%b8%b8%e7%94%a8%e7%bb%b4%e6%8a%a4%e5%91%bd%e4%bb%a4","status":"publish","type":"post","link":"http:\/\/www.jh1999.cn\/?p=2470","title":{"rendered":"SQL Server\u5e38\u7528\u7ef4\u62a4\u547d\u4ee4"},"content":{"rendered":"
\n\n

SQL Server2000\u5728X86\u7684\u8ba1\u7b97\u673a\u4e0a\uff0c\u53ea\u670932\u4f4d\u7248\u672c\uff0cSQL Server2005\u5f00\u59cb\u670964\u4f4d\u7248\u672c\uff0c\u7ef4\u62a4\u547d\u4ee4\u4e5f\u5f00\u59cb\u6709\u5f88\u591a\u7684\u4e0d\u540c\u3002\u672c\u6587\u63d0\u5230\u7684\u7ef4\u62a4\u547d\u4ee4\uff0c\u82e5\u65e0\u7279\u522b\u8bf4\u660e\uff0c\u90fd\u4ee5\u7ecf\u517864\u4f4d\u7248\u672cSQL Server2008R2\u4e3a\u4f8b\u3002<\/p>\n\n\n\n

\u4e00\u3001\u6307\u5b9a\u67d0\u4e9bIP\u624d\u80fd\u4f7f\u7528sa\u767b\u5165\u6570\u636e\u5e93\u3002\u8fd0\u884c\u4e0b\u9762\u8fd9\u6bb5\u4ee3\u7801\u5c31\u4f1a\u5728\u3010\u670d\u52a1\u5668\u3011\\\u3010\u670d\u52a1\u5668\u5bf9\u8c61(\u4f3a\u670d\u5668\u7269\u4ef6)\u3011\\\u3010\u89e6\u53d1\u5668\u3011\u4e0b\u751f\u6210tr_connection_limit<\/p>\n\n\n\n

CREATE TRIGGER [tr_connection_limit]\nON ALL SERVER WITH EXECUTE AS 'sa'\nFOR LOGON\nAS\nBEGIN\nIF ORIGINAL_LOGIN()= 'sa'\nAND\n(SELECT EVENTDATA().value('(\/EVENT_INSTANCE\/ClientHost)[1]', 'NVARCHAR(15)'))\nNOT IN('<local machine>','192.168.0.220','192.168.0.221')\n     ROLLBACK;\nEND;<\/code><\/pre>\n\n\n\n
\u4e8c\u3001\u67e5\u627e\u963b\u585e\u7684\u8fde\u63a5\u5e76\u5c06\u5176\u4e2d\u6b62\u3002<\/p>\n\n\n\n
    \n
  • SQL Server2000\uff0c\u6253\u5f00\u6570\u636e\u5e93\u7ba1\u7406\u5668 — Management — CurrentActivity — Process Info\uff0c\u627e\u5230\u663e\u793a\u4e3a\u963b\u585e\u7684\u8fdb\u7a0b\uff0c\u5c06\u5176\u4e2d\u6b62<\/li>\n\n\n\n
  • SQL Server2008\uff0c\u6253\u5f00\u6570\u636e\u5e93\u7ba1\u7406\u5668\uff0c\u9f20\u6807\u53f3\u51fb\u670d\u52a1\u5668\uff0c\u6d3b\u52a8\u76d1\u89c6\u5668\u3002\u76f4\u89c2\u52a8\u6001\u5730\u67e5\u770b\u8fdb\u7a0b\u60c5\u51b5\u3002<\/li>\n\n\n\n
  • SQL Server2008\uff0c\u6253\u5f00\u6570\u636e\u5e93\u7ba1\u7406\u5668\uff0c\u9f20\u6807\u53f3\u51fb\u670d\u52a1\u5668\uff0c\u5831\u8868\uff0c\u6a19\u6e96\u5831\u8868\uff0c\u6d3b\u52d5-\u6240\u6709\u9032\u884c\u5c01\u9396\u4ea4\u6613\u3002\u67e5\u770b\u963b\u585e\u8fdb\u7a0b\u7684\u4fe1\u606f\uff0c\u5e76\u80fd\u67e5\u770b\u9020\u6210\u963b\u585e\u7684SQL\u8a9e\u53e5\u3002<\/li>\n\n\n\n
  • \u57f7\u884c\u4e0b\u8ff0SQL\u8a9e\u53e5\uff08\u5e7e\u7a2e\u8a9e\u53e5\u5404\u6709\u7279\u8272\uff09\uff0c\u627e\u51fa\u963b\u585e\u7684\u8fdb\u7a0b\uff0c\u518d\u7528 kill session_id \u547d\u4ee4\u4e2d\u6b62\u8a72\u9032\u7a0b\u3002<\/li>\n<\/ul>\n\n\n\n
    --SQL Server2000\u4e13\u7528\nSelect spid,dp.[status],loginame,hostname,dd.name DBName,cmd as Command,\n  cpu CPUTime,physical_io,last_batch,[program_name],blocked\nFrom master.dbo.sysprocesses dp\nJoin master.dbo.sysdatabases dd ON dp.dbid = dd.dbid \nORDER BY DBName,[program_name],hostname\n\nSelect session_id,status,last_request_start_time,last_request_end_time,host_name,program_name,\n  host_process_id,client_version,login_name,cpu_time,total_scheduled_time,total_elapsed_time,\n  reads,writes,logical_reads,lock_timeout,deadlock_priority,row_count\nFrom sys.dm_exec_sessions\n\nSelect * from sys.dm_exec_requests\n\nSelect * from sys.dm_tran_locks \n\n--\u770b\u67d0\u9032\u7a0b\u5185\u5bb9\nDBCC INPUTBUFFER(211)\n\n--\u6bba\u6389\u67d0\u9032\u7a0b\n kill 211<\/code><\/pre>\n\n\n\n
    \u4e09\u3001sp_who2\u7684\u7528\u6cd5\uff0c\u4e5f\u80fd\u627e\u51fa\u52a0\u9501\u963b\u585e\u7684\u8fdb\u7a0bID\u3002<\/p>\n\n\n\n
    --\u67e5\u767b\u5f55\u8d26\u53f7sa\u7684\u8fdb\u7a0b\nsp_who2 'sa'\nsp_who2 @loginame='sa'\n--\u67e5\u4f1a\u8bddID8\u7684\u8fdb\u7a0b\nsp_who2 8\nsp_who2 '8'\nsp_who2 [8]\n--\u67e5\u5f53\u524d\u6d3b\u52a8\u7684\u8fdb\u7a0b\nsp_who2 ACTIVE\nsp_who2 'ACTIVE'\nsp_who2 [ACTIVE]<\/code><\/pre>\n\n\n\n
    \u56db\u3001\u6536\u7f29\u6570\u636e\u5e93\uff0c\u9488\u5bf9\u67d0\u6570\u636e\u5e93\u7684\u5168\u90e8\u6587\u4ef6\uff08mdf \u548c ldf\uff09\uff0c\u4e00\u822c\u7528\u4e8e\u5220\u9664\u5927\u91cf\u8bb0\u5f55\u540e\u91ca\u653e\u78c1\u76d8\u7a7a\u95f4\u3002<\/p>\n\n\n\n
    --\u538b\u7f29\u5e93\u3010jhERP\u3011\u5e76\u9884\u75595%\u7684\u672a\u7528\u7a7a\u95f4\nuse [jhERP] DBCC ShrinkDatabase(jhERP,5,NOTRUNCATE);\n--\u622a\u65b7\u8cc7\u6599\u5eab\u3010jhERP\u3011\nuse [jhERP] DBCC ShrinkDatabase(jhERP,TRUNCATEONLY);<\/code><\/pre>\n\n\n\n
    \u4e94\u3001\u6536\u7f29\u6570\u636e\u5e93\u6570\u636e\u6587\u4ef6(mdf)\u6216\u65e5\u5fd7\u6587\u4ef6(ldf)\u3002\u6536\u7f29\u65e5\u5fd7\u6587\u4ef6\u662f\u65e5\u5e38\u7ef4\u62a4\u5de5\u4f5c\u4e4b\u4e00\uff0c\u4e00\u822c\u7531\u6570\u636e\u5e93\u7684\u4ee3\u7406\u7a0b\u5e8f\u6392\u671f\u6267\u884c\uff0c\u800c\u6570\u636e\u6587\u4ef6\u53ea\u6709\u5728\u8fdb\u884c\u4e86\u5927\u91cf\u8bb0\u5f55\u5220\u9664\u65f6\u624d\u6267\u884c\u3002\u6536\u7f29\u6570\u636e\u5e93\u6587\u4ef6\u524d\uff0c\u8981\u628a\u6062\u590d\u6a21\u5f0f\u8bbe\u6210\u7b80\u5355\u3002<\/p>\n\n\n\n
    ALTER DATABASE jhERP SET RECOVERY SIMPLE WITH NO_WAIT\n--\u628atempdb.mdf\u538b\u7f29\u5230150MB\nuse tempdb dbcc ShrinkFile(tempdev, 150)\n--\u628ajhERP.mdf\u538b\u7f29\u523018000MB\nuse jhERP dbcc ShrinkFile(jhERP, 18000)\n--\u538b\u7f29tempdb\u7684\u65e5\u5fd7\nuse tempdb dbcc ShrinkFile(templog,0)\n--\u538b\u7f29jhERP\u7684\u65e5\u5fd7\nuse jhERP dbcc ShrinkFile(jhERP_log,0)\nuse jhVisProd dbcc ShrinkFile(jhVisProd_Log,0)\nALTER DATABASE jhERP SET RECOVERY FULL WITH NO_WAIT<\/code><\/pre>\n\n\n\n
    \u516d\u3001\u628a\u6570\u636e\u5e93\u5907\u4efd\u5230\u7f51\u7edc\u5185\u7684\u5171\u4eab\u76ee\u5f55\uff0c\u76f4\u63a5\u8986\u76d6\u5907\u4efd\u76ee\u5f55\u4e2d\u7684\u540c\u540d\u6587\u4ef6\u3002<\/p>\n\n\n\n
    Declare\t@varDate  varchar(60)\nDeclare\t@PROD \tvarchar(128)\nset\t@varDate  = Convert(varchar(6),getdate(),12)+RIGHT('0'+datename(hour,getdate()),2)+RIGHT('0'+datename(minute,getdate()),2)\nset\t@PROD= '\\\\192.168.0.168\\Databasebackup\\jhERP'+@varDate+'.bak'\nEXEC master..xp_cmdshell  'net use * \/delete \/y'\nEXEC master..xp_cmdshell  'net use  \\\\192.168.0.168\\Databasebackup  password  \/user:192.168.0.168\\ErpBacker'\nBackup database jhERP to disk = @PROD WITH INIT<\/code><\/pre>\n\n\n\n
    \u4e03\u3001\u91cd\u5efatempdb\u3002\u505c\u6b62SQL\u670d\u52a1\uff0c\u628atempdb.mdf \u548c templog.ldf\u4e24\u4e2a\u6587\u4ef6\u79fb\u5230\u5176\u5b83\u5730\u65b9\uff0c\u4ee5\u9632\u5931\u8d25\u4e86\u91cd\u6765\uff0c\u542f\u52a8SQL\u670d\u52a1\uff0c\u6b63\u5e38\u60c5\u51b5\u4e0b\u7cfb\u7edf\u4f1a\u81ea\u52a8\u91cd\u5efatempdb\u6570\u636e\u5e93\u3002<\/p>\n\n\n\n
    ----\u6709\u4ea4\u6613\u660e\u7ec6\u7684\u7269\u9879\u624d\u66f4\u65b0\u5408\u8ba1\u503c\nUpdate d1 set d1.OrderedQty=d2.poQty from stItSum d1,\n  (select itID,HouseID,sum(qtyIng)as poQty from mpReqLine \n    where mpReqType in('PI','WI') group by itID,HouseID) d2\n  where d1.itID=d2.itID and d1.HouseID=d2.HouseID\n\n----\u6240\u6709\u8bb0\u5f55\u90fd\u53c2\u4e0e\u66f4\u65b0\uff0c\u6ca1\u6709\u660e\u7ec6\u7684\u66f4\u65b0\u4e3aNull\nUpdate mpReqTable set VendID=(select top 1 d2.VendID from poItPrice d2,vacExchRate d3\n  where d1.itID=d2.itID and d2.Currency=d3.Currency order by d2.Price*d3.ExchRate)\n  from mpReqTable d1 <\/code><\/pre>\n\n\n\n
    \u516b\u3001\u91cd\u5efa\u6570\u636e\u5e93\u7684 ldf \u6863\u6848\u3002\u6b63\u5e38\u5378\u8f7d\u7684\u6570\u636e\u5e93\uff0c\u9644\u52a0\u65f6\u4f1a\u81ea\u52a8\u751f\u6210 ldf \u6863\u6848\uff0c\u5982\u679c\u51fa\u73b0\u610f\u5916\uff0c\u5c31\u9700\u8981\u7528\u547d\u4ee4\u91cd\u5efa\u3002\u4e0b\u9762\u4f8b\u5b50\u4e2d\uff0cAxStd\u662f\u6570\u636e\u5e93\u540d\uff0cAxStd_Log.LDF\u662f\u6570\u636e\u5e93\u8981\u91cd\u5efa\u7684\u65e5\u5fd7\u6863\u540d\u3002<\/p>\n\n\n\n
    sp_Configure 'Allow update',1\ngo\nreconfigure with override\ngo\nupdate sysdatabases set status=32768 where name='AxStd'\ngo\nsp_dboption 'AxStd','single user','true'\ngo\ndbcc rebuild_log ('AxStd','e:\\AxBack\\database\\AxStd_Log.LDF')\ngo\ndbcc checkdb('AxStd')\ngo\n\nupdate sysdatabases set status=28 where name='AxStd'\ngo\nsp_configure 'Allow updates',0\ngo\nreconfigure with override\ngo\nsp_dboption 'AxStd','single user','false'\ngo<\/code><\/pre>\n\n\n\n
    \u4e5d\u3001\u67e5\u8be2\u6570\u636e\u5e93\u4e2d\u5404\u8868\u7684\u5927\u5c0f<\/p>\n\n\n\n
    SELECT t.NAME AS TableName,p.rows AS RowCounts,SUM(a.data_pages) * 8 AS DataSpaceKB,\n  SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, \n  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB   \nFROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id\nINNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id\nINNER JOIN sys.allocation_units a ON p.partition_id = a.container_id\nWHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 \nGROUP BY t.Name, p.Rows\nORDER BY TableName\n\n--\u67e5\u78c1\u76e4\u7a7a\u9593\nEXEC xp_fixeddrives\n\n-- \u68c0\u67e5\u5185\u5b58\u914d\u7f6e\u60c5\u51b5\nEXEC sp_configure 'show advanced options', 1\nRECONFIGURE\nEXEC sp_configure 'max server memory'\nEXEC sp_configure 'min server memory'\nEXEC sp_configure 'show advanced options', 0\nRECONFIGURE<\/code><\/pre>\n\n\n\n
    \u5341\u3001\u67e5\u8be2\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u8868\u548c\u680f\u4f4d\u6e05\u5355<\/p>\n\n\n\n
    select d2.name as TblName,d1.Name as FieldName,d1.Length,d1.type,d1.colid\n  from sysColumns d1,sysObjects d2 \n  where d1.id=d2.id\n  order by d2.name,d1.name<\/code><\/pre>\n\n\n\n
    \u5341\u4e00\u3001\u67e5\u8be2\u5f53\u524d\u5e93\u4e2d\u5404\u6570\u636e\u8868\u7684\u8bb0\u5f55\u6570<\/p>\n\n\n\n
    Select a.name,a.rows from sysindexes a,sysobjects b   where a.name=b.name order by a.rows desc<\/code><\/pre>\n\n\n\n
    \u5341\u4e8c\u3001\u5728PowerShell\u4e2d\u67e5\u8be2SQL Server\u5185\u5b58\u72b6\u6001<\/p>\n\n\n\n
    sqlcmd -S \"192.168.0.246,1434\" -U sa -P ***** -Q \"DBCC MEMORYSTATUS\"<\/code><\/pre>\n\n
    <\/svg><\/div><\/div><\/div>","protected":false},"excerpt":{"rendered":"
    SQL Server2000\u5728X86\u7684\u8ba1\u7b97\u673a\u4e0a\uff0c\u53ea\u670932\u4f4d\u7248\u672c\uff0cSQL Server2005\u5f00\u59cb\u670964\u4f4d\u7248\u672c\uff0c … \u9605\u8bfb\u66f4\u591a<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5],"tags":[],"class_list":["post-2470","post","type-post","status-publish","format-standard","hentry","category-kfbj"],"_links":{"self":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts\/2470","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2470"}],"version-history":[{"count":17,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts\/2470\/revisions"}],"predecessor-version":[{"id":2663,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=\/wp\/v2\/posts\/2470\/revisions\/2663"}],"wp:attachment":[{"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2470"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2470"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.jh1999.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2470"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}