ÈçºÎ¼ì²éSQL Server×èÈû
--²éѯӦÓóÌÐòµÄµÈ´ý
SELECT TOP 10
wait_type,waiting_tasks_count AS tasks,
wait_time_ms,max_wait_time_ms AS max_wait,
signal_wait_time_ms AS signal
from sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
--²éѯÔÚÈÎһʱ¿ÌËùÓÐÊÚȨ¸øµ±Ç°Ö´ÐÐÊÂÎñ»òµ±Ç°Ö´ÐÐÊÂÎñµÈ´ýµÄËø
SELECT
request_session_id AS spid,resource_type AS rt,
resource_database_id AS rdb,
(CASE resource_type
WHEN 'OBJECT' THEN OBJECT_NAME(resource_associated_entity_id)
WHEN 'DATABASE' THEN ' '
ELSE (SELECT OBJECT_NAME(object_id)
from sys.partitions WHERE hobt_id=resource_associated_entity_id)
END)AS objname,
resource_description AS rd,
request_mode AS rm,
request_status AS rs
from sys.dm_tran_locks
--×èÈûµÄÉú´æÆÚºÍÕý±»×èÈûÊÂÎñÖ´ÐеÄSQLÓï¾ä
SELECT
t1.resource_type,
'databse'=DB_NAME(resource_database_id),
'blk object'=resource_associated_entity_id,
request_mode,request_session_id,wait_duration_ms,
(SELECT SUBSTRING(text,statement_start_offset/2+1,
(CASE WHEN statement_end_offset=-1
THEN LEN(CONVERT(NVARCHAR(max),text))*2
ELSE statement_end_offset
END -statement_start_offset)/2)
from sys.dm_exec_sql_text(sql_handle)
) AS query_text,
t1.resource_description
from sys.dm_tran_locks AS t1,
sys.dm_os_waiting_tasks t2,
sys.dm_exec_requests t3
WHERE
t1.lock_owner_address=t2.resource_address AND
t1.request_request_id=t3.request_id AND
t2.session_id=t3.session_id
Ïà¹ØÎĵµ£º
C#¡¢ASP.NETÖн«Excel±íÖеÄÊý¾Ýµ¼Èëµ½Sql ServerÊý¾Ý¿âÖжÔÓ¦µÄ±íÖС£
ÕâÀïÎÒÃǽ«E:\test.xls
ÖÐSheet1
±íÖеÄÊý¾Ýµ¼Èëµ½testÊý¾Ý¿âÖеÄmy_test
±íÖС£
Èí¼þ£ºVS2005£¬SQL Server2000,
test.xlsÖÐSheet1±íÖÐÓÐÈý¸ö×ֶΣ¬±àºÅ¡¢Ãû³Æ¡¢±¸×¢¡£¾ßÌåÄÚÈÝÈçÏÂͼ¡£
1¡¢
Ê×ÏÈ£¬ÎÒÃÇÒ ......
ÎÒÃÇÒª×öµ½²»µ«»áдSQL,»¹Òª×öµ½Ð´³öÐÔÄÜÓÅÁ¼µÄSQL,ÒÔÏÂΪ±ÊÕßѧϰ¡¢ÕªÂ¼¡¢²¢»ã×ܲ¿·Ö×ÊÁÏÓë´ó¼Ò·ÖÏí£¡
£¨1£© Ñ¡Ôñ×îÓÐЧÂʵıíÃû˳Ðò(Ö»ÔÚ»ùÓÚ¹æÔòµÄÓÅ»¯Æ÷ÖÐÓÐЧ)£º
ORACLEµÄ½âÎöÆ÷°´ÕÕ´ÓÓÒµ½×óµÄ˳Ðò´¦Àífrom×Ó¾äÖеıíÃû£¬from×Ó¾äÖÐдÔÚ×îºóµÄ±í(»ù´¡±í driving table)½«±»×îÏÈ´¦Àí£ ......
Äã´´½¨µÄÿһ¸ö±¸·Ý¶¼ÊÇÒ»¸ö±¸·ÝÉ豸£¬¹ØÓÚËüµÄϸ½ÚÐÅÏ¢¶¼´æ´¢ÔÚmsdb..backupset±íÖС£Ò»¸ö±¸·ÝÉ豸¿ÉÒÔ±»´æ´¢ÔÚµ¥Ò»Îļþ£¬»òÊǶà¸öÎļþÖС£Í¬Ñù£¬Ò»¸öÎļþÒ²¿ÉÒÔ´æ´¢¶à¸ö±¸·ÝÉ豸¡£
ËùÒÔ£¬¼ÙÈçÄãÿ´Î±¸·Ý¶¼Ê¹ÓÃÏàͬµÄÎļþÃû£¬Îļþ¾Í»áÒ»Ö±Ôö³¤¡£Ò»¸öÆÕ±éµÄÎó½âÊÇ£ºÈç¹ûÄãÿ´ÎʹÓÃÏàͬµÄÎļþÃû£¬ÄǾɵı¸·ÝÉ豸¾Í»á±»¸²¸Ç¡ ......
1£º
Sql server 2005ÈÕÖ¾ÎļþÌ«´ó,ʹÆä¼õСµÄ·½·¨
ÔËÐÐÏÂÃæµÄÈýÐÐ PMDataCenter ΪÊý¾Ý¿âÃû:
backup log PMDataCenter with NO_LOG
backup log PMDataCenter with TRUNCATE_ONLY
DBCC SHRINKDATABASE(PMDataCenter) ......