{"id":151,"date":"2010-04-28T19:14:00","date_gmt":"2010-04-28T19:14:00","guid":{"rendered":"http:\/\/madprogrammer76.wordpress.com\/2010\/04\/28\/checking-active-process-on-sql-server"},"modified":"2013-12-18T15:41:30","modified_gmt":"2013-12-18T20:41:30","slug":"checking-active-process-on-sql-server","status":"publish","type":"post","link":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/","title":{"rendered":"Checking Active Process on Sql Server"},"content":{"rendered":"

I have needed to monitor the SQL servers a little more than usual. I am mostly concerned about memory and CPU usage. By properly setting our program names in the SQL connection string, I am able to find the offending applications a little easier. There are times, that I set the application and routine in the connection string when the offending process is not so obvious.<\/p>\n

Here is my script that I use to view the offending SQL processes<\/p>\n

SQL Query using SysProcess<\/span><\/p>\n

use<\/span> master
\ndeclare<\/span> @memlimit as int<\/span>, @cpulimit as int<\/span>
\nset<\/span> @memlimit = 100 — Memory Filter Limit<\/span>
\nset <\/span>@cpulimit = 200 — CPU Filter Limit<\/span>
\nselect <\/span>spid, login_time, last_batch, hostname, program_name, memusage,cpu
\nfrom <\/span>sysprocesses <\/span>
\nwhere <\/span>( memusage > @memlimit or cpu > @cpulimit )<\/span><\/p><\/blockquote>\n

Once I have a list of offending processes, I seek and fix the code has the issue. If the process is a closed application like the BlackBerry\u00ae Mobile Data Server Connection Service, I check for updates or in rare cases set up a scheduled task to restart the service.<\/p>\n

\"\"<\/div>\n","protected":false},"excerpt":{"rendered":"

I have needed to monitor the SQL servers a little more than usual. I am mostly concerned about memory and CPU usage. By properly setting our program names in the SQL connection string, I am able to find the offending applications a little easier. There are times, that I set the application and routine in … Continue reading Checking Active Process on Sql Server<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,78],"tags":[185,186],"yoast_head":"\r\nChecking Active Process on Sql Server - LDNDeveloper<\/title>\r\n<meta name=\"description\" content=\"How to check active processes on SQL Server\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Checking Active Process on Sql Server - LDNDeveloper\" \/>\r\n<meta property=\"og:description\" content=\"How to check active processes on SQL Server\" \/>\r\n<meta property=\"og:url\" content=\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\" \/>\r\n<meta property=\"og:site_name\" content=\"LDNDeveloper\" \/>\r\n<meta property=\"article:published_time\" content=\"2010-04-28T19:14:00+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2013-12-18T20:41:30+00:00\" \/>\r\n<meta name=\"author\" content=\"andrewpallant\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@ldnDeveloper\" \/>\r\n<meta name=\"twitter:site\" content=\"@LdnDeveloper\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"andrewpallant\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\"},\"author\":{\"name\":\"andrewpallant\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97\"},\"headline\":\"Checking Active Process on Sql Server\",\"datePublished\":\"2010-04-28T19:14:00+00:00\",\"dateModified\":\"2013-12-18T20:41:30+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\"},\"wordCount\":180,\"commentCount\":0,\"publisher\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"keywords\":[\"performance\",\"sysProcess\"],\"articleSection\":[\"How To\",\"SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\",\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\",\"name\":\"Checking Active Process on Sql Server - LDNDeveloper\",\"isPartOf\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#website\"},\"datePublished\":\"2010-04-28T19:14:00+00:00\",\"dateModified\":\"2013-12-18T20:41:30+00:00\",\"description\":\"How to check active processes on SQL Server\",\"breadcrumb\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/andrewpallant.ca\/wordpress\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Checking Active Process on Sql Server\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#website\",\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/\",\"name\":\"LDNDeveloper\",\"description\":\"Learning, Growing and Sharing.\",\"publisher\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/andrewpallant.ca\/wordpress\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84\",\"name\":\"ldnDeveloper\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg\",\"contentUrl\":\"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg\",\"width\":512,\"height\":512,\"caption\":\"ldnDeveloper\"},\"logo\":{\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\"},\"description\":\"Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http:\/\/www.unlatched.com\/#sthash.8DiTkpKy.dpuf\",\"sameAs\":[\"http:\/\/www.andrewpallant.ca\",\"https:\/\/x.com\/LdnDeveloper\"]},{\"@type\":\"Person\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97\",\"name\":\"andrewpallant\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g\",\"contentUrl\":\"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g\",\"caption\":\"andrewpallant\"},\"url\":\"http:\/\/andrewpallant.ca\/wordpress\/author\/andrewpallant\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Checking Active Process on Sql Server - LDNDeveloper","description":"How to check active processes on SQL Server","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"Checking Active Process on Sql Server - LDNDeveloper","og_description":"How to check active processes on SQL Server","og_url":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/","og_site_name":"LDNDeveloper","article_published_time":"2010-04-28T19:14:00+00:00","article_modified_time":"2013-12-18T20:41:30+00:00","author":"andrewpallant","twitter_card":"summary_large_image","twitter_creator":"@ldnDeveloper","twitter_site":"@LdnDeveloper","twitter_misc":{"Written by":"andrewpallant","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#article","isPartOf":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/"},"author":{"name":"andrewpallant","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97"},"headline":"Checking Active Process on Sql Server","datePublished":"2010-04-28T19:14:00+00:00","dateModified":"2013-12-18T20:41:30+00:00","mainEntityOfPage":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/"},"wordCount":180,"commentCount":0,"publisher":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"keywords":["performance","sysProcess"],"articleSection":["How To","SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/","url":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/","name":"Checking Active Process on Sql Server - LDNDeveloper","isPartOf":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#website"},"datePublished":"2010-04-28T19:14:00+00:00","dateModified":"2013-12-18T20:41:30+00:00","description":"How to check active processes on SQL Server","breadcrumb":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/andrewpallant.ca\/wordpress\/checking-active-process-on-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/andrewpallant.ca\/wordpress\/"},{"@type":"ListItem","position":2,"name":"Checking Active Process on Sql Server"}]},{"@type":"WebSite","@id":"http:\/\/andrewpallant.ca\/wordpress\/#website","url":"http:\/\/andrewpallant.ca\/wordpress\/","name":"LDNDeveloper","description":"Learning, Growing and Sharing.","publisher":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/andrewpallant.ca\/wordpress\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/f6f5bb1ac3e0c5a54a8b5ce35fd67b84","name":"ldnDeveloper","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/","url":"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg","contentUrl":"https:\/\/andrewpallant.ca\/wordpress\/wp-content\/uploads\/2017\/05\/cropped-AAEAAQAAAAAAAAXQAAAAJDQxMGRlMzFjLWM4ODctNDk1NC05M2EyLWE1NDNhNTRiZjVlYw-2.jpg","width":512,"height":512,"caption":"ldnDeveloper"},"logo":{"@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/"},"description":"Andrew Pallant (@LdnDeveloper) has been a web, database and desktop developer for over 16 years. Andrew has worked on projects that ranged from factory automation to writing business applications. Most recently he has been heavily involved in various forms for ecommerce projects. Over the years Andrew has worn many hats: Project Manager, IT Manager, Lead Developer, Supervisor of Developers and many more - See more at: http:\/\/www.unlatched.com\/#sthash.8DiTkpKy.dpuf","sameAs":["http:\/\/www.andrewpallant.ca","https:\/\/x.com\/LdnDeveloper"]},{"@type":"Person","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/0e7b5e71751000e8f66b17b69ef4ab97","name":"andrewpallant","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"http:\/\/andrewpallant.ca\/wordpress\/#\/schema\/person\/image\/","url":"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g","contentUrl":"http:\/\/1.gravatar.com\/avatar\/?s=96&d=mm&r=g","caption":"andrewpallant"},"url":"http:\/\/andrewpallant.ca\/wordpress\/author\/andrewpallant\/"}]}},"_links":{"self":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/151"}],"collection":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/comments?post=151"}],"version-history":[{"count":1,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/151\/revisions"}],"predecessor-version":[{"id":1145,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/posts\/151\/revisions\/1145"}],"wp:attachment":[{"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/media?parent=151"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/categories?post=151"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/andrewpallant.ca\/wordpress\/wp-json\/wp\/v2\/tags?post=151"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}