{"id":290,"date":"2017-03-28T12:51:51","date_gmt":"2017-03-28T07:21:51","guid":{"rendered":"http:\/\/pramodsingla.wordpress.com\/?p=290"},"modified":"2017-03-28T12:51:51","modified_gmt":"2017-03-28T07:21:51","slug":"__trashed-7","status":"publish","type":"post","link":"https:\/\/pramodsingla.com\/?p=290","title":{"rendered":"SQL Server -DBA Must Know commands"},"content":{"rendered":"
\n

Following are couple of DBA must know commands.<\/h2>\n

–Get\u00a0the\u00a0db\u00a0details<\/i><\/span>
\nEXEC\u00a0SP_HELPDB\u00a0‘Db_name’<\/span>
\n–know\u00a0the\u00a0definition\u00a0of\u00a0the\u00a0objects<\/i><\/span>
\nEXEC\u00a0SP_HELPTEXT<\/span>\u00a0‘object_name’<\/span>
\n–Identify\u00a0the\u00a0size\u00a0of\u00a0database\u00a0and\u00a0table<\/i><\/span>
\nEXEC\u00a0SP_SPACEUSED<\/span>
\n–Know\u00a0the\u00a0details\u00a0of\u00a0the\u00a0disk\u00a0drives\u00a0<\/i><\/span>
\nEXEC\u00a0MASTER<\/span>.<\/span>.<\/span>XP_FIXEDDRIVES<\/span>
\n—Quick\u00a0view\u00a0of\u00a0SQL\u00a0server\u00a0sessions<\/i><\/span>
\nEXEC\u00a0SP_WHO2<\/span>
\n–It’s\u00a0advanced\u00a0version\u00a0of\u00a0sp_who2\u00a0but\u00a0not\u00a0shipped\u00a0with\u00a0SQL\u00a0server.Get\u00a0it\u00a0from\u00a0the\u00a0following\u00a0link.\u00a0<\/i><\/span>
\nEXEC\u00a0SP_WHOISACTIVE<\/span>
\n–Used\u00a0in\u00a0Query\u00a0tuning\u00a0for\u00a0getting\u00a0the\u00a0logical\u00a0reads<\/i><\/span>
\nSET\u00a0STATISTICS\u00a0IO\u00a0ON<\/span>
\n–Used\u00a0in\u00a0Query\u00a0tuning\u00a0for\u00a0getting\u00a0the\u00a0CPU\u00a0time<\/i><\/span>
\nSET\u00a0STATISTICS\u00a0TIME\u00a0ON<\/span>
\n–Clean\u00a0the\u00a0data\u00a0buffers.Used\u00a0in\u00a0query\u00a0tuning.<\/i><\/span>
\nDBCC\u00a0DROPCLEANBUFFERS<\/span>
\n–Free\u00a0the\u00a0procedure\u00a0cache.Used\u00a0in\u00a0query\u00a0tuning.<\/i><\/span>
\nDBCC\u00a0FREEPROCCACHE<\/span>
\n–Check\u00a0the\u00a0Error\u00a0logs\u00a0in\u00a0efficient\u00a0way<\/i><\/span>
\nEXEC\u00a0MASTER<\/span>.<\/span>DBO<\/span>.<\/span>XP_READERRORLOG<\/span>\u00a00<\/span>,<\/span>\u00a01<\/span>,<\/span>\u00a0‘search\u00a0String\u00a01’<\/span>,<\/span>\u00a0‘search\u00a0String\u00a02’<\/span>,<\/span>\u00a0‘2017-01-10’<\/span>,<\/span>\u00a0‘2017-02-20’<\/span>,<\/span>\u00a0N’desc’<\/span>
\n–Check\u00a0the\u00a0query\u00a0a\u00a0session\u00a0is\u00a0running.<\/i><\/span>
\nDBCC\u00a0INPUTBUFFER<\/span>(<\/span><<\/span>session_id<\/span>><\/span>)<\/span>
\n–Get\u00a0current\u00a0log\u00a0file\u00a0path\u00a0\u00a0<\/i><\/span>
\nSELECT\u00a0Serverproperty<\/span>(<\/span>‘ErrorLogFileName’<\/span>)<\/span>
\n–Database\u00a0consistency\u00a0check<\/i><\/span>
\nDBCC\u00a0CHECKDB\u00a0WITH\u00a0ESTIMATEONLY<\/span>;<\/span><\/h2>\n

DBCC\u00a0CHECKDB\u00a0WITH\u00a0NO_INFOMSGS;<\/span><\/h2>\n

DBCC\u00a0CHECKDB(<\/span>DBNAME<\/span>)<\/span>\u00a0WITH\u00a0NO_INFOMSGS<\/span>,<\/span>\u00a0ALL_ERRORMSGS<\/span>\u00a0SELECT\u00a0Databasepropertyex\u00a0<\/span>(<\/span>‘kcc_dw’<\/span>,<\/span>\u00a0<\/span>‘STATUS’<\/span>)<\/span>\u00a0\u00a0<\/span>\u00a0\u00a0<\/span>\u00a0\u00a0<\/span>\u00a0\u00a0\u00a0<\/span><\/h2>\n

DBCC\u00a0sqlperf<\/span>(<\/span>‘logspace’<\/span>)<\/span>\u00a0<\/span><\/h2>\n<\/blockquote>\n","protected":false},"excerpt":{"rendered":"

Following are couple of DBA must know commands. –Get\u00a0the\u00a0db\u00a0details EXEC\u00a0SP_HELPDB\u00a0‘Db_name’ –know\u00a0the\u00a0definition\u00a0of\u00a0the\u00a0objects EXEC\u00a0SP_HELPTEXT\u00a0‘object_name’ –Identify\u00a0the\u00a0size\u00a0of\u00a0database\u00a0and\u00a0table EXEC\u00a0SP_SPACEUSED –Know\u00a0the\u00a0details\u00a0of\u00a0the\u00a0disk\u00a0drives\u00a0 EXEC\u00a0MASTER..XP_FIXEDDRIVES —Quick\u00a0view\u00a0of\u00a0SQL\u00a0server\u00a0sessions EXEC\u00a0SP_WHO2 –It’s\u00a0advanced\u00a0version\u00a0of\u00a0sp_who2\u00a0but\u00a0not\u00a0shipped\u00a0with\u00a0SQL\u00a0server.Get\u00a0it\u00a0from\u00a0the\u00a0following\u00a0link.\u00a0 EXEC\u00a0SP_WHOISACTIVE[…]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_mi_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[1],"tags":[71,159],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/290"}],"collection":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=290"}],"version-history":[{"count":0,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=\/wp\/v2\/posts\/290\/revisions"}],"wp:attachment":[{"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=290"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=290"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/pramodsingla.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=290"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}