{"id":1032,"date":"2018-01-04T06:49:40","date_gmt":"2018-01-04T06:49:40","guid":{"rendered":"http:\/\/www.dbafox.com\/?p=1032"},"modified":"2018-01-19T14:42:20","modified_gmt":"2018-01-19T13:42:20","slug":"sql-server-monitor-log-space","status":"publish","type":"post","link":"https:\/\/dbafox.com\/?p=1032","title":{"rendered":"SQL Server &#8211; Monitor Log Space"},"content":{"rendered":"\n<pre class=\"toolbar-overlay:false lang:tsql decode:true \" title=\"Monitor Transaction Log Space\" >\r\n\r\nDBCC SQLPERF(logspace)\r\n\r\n----------------------------------------------------------------------------------------------\r\n\r\nDECLARE @sql_command varchar(1024)\r\nDECLARE @logtable TABLE\r\n(\r\n   [Database Name] VARCHAR(1000) NULL,  \r\n   [Log Size (MB)] numeric,\r\n   [Log Space Used (%)] numeric,\r\n   [Status] VARCHAR(128) NULL  \r\n)\r\n\r\nSELECT @sql_command = 'dbcc sqlperf (logspace)'\r\n\r\nINSERT INTO @logtable EXEC (@sql_command)\r\n\r\nselect * from @logtable where [Database Name] = 'RFDB'\r\n\r\nSELECT \/* ignore this *\/ command,\r\n            r.session_id, r.blocking_session_id,\r\n            s.text,\r\n            start_time,\r\n            percent_complete, \r\n            CAST(((DATEDIFF(s,start_time,GetDate()))\/3600) as varchar) + ' hour(s), '\r\n                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)\/60 as varchar) + 'min, '\r\n                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,\r\n            CAST((estimated_completion_time\/3600000) as varchar) + ' hour(s), '\r\n                  + CAST((estimated_completion_time %3600000)\/60000 as varchar) + 'min, '\r\n                  + CAST((estimated_completion_time %60000)\/1000 as varchar) + ' sec' as est_time_to_go,\r\n            dateadd(second,estimated_completion_time\/1000, getdate()) as est_completion_time \r\nFROM sys.dm_exec_requests r\r\nCROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s\r\n\r\nDECLARE @whotable TABLE\r\n(\r\n   SPID INT,  \r\n   Status VARCHAR(1000) NULL,  \r\n   Login SYSNAME NULL,  \r\n   HostName SYSNAME NULL,  \r\n   BlkBy SYSNAME NULL,  \r\n   DBName SYSNAME NULL,  \r\n   Command VARCHAR(1000) NULL,  \r\n   CPUTime INT NULL,  \r\n   DiskIO INT NULL,  \r\n   LastBatch VARCHAR(1000) NULL,  \r\n   ProgramName VARCHAR(1000) NULL,  \r\n   SPID2 INT,\r\n   REQUESTID INT\r\n)\r\n \r\nINSERT INTO @whotable EXEC sp_who2\r\n \r\nselect * from @whotable order by BlkBy desc\r\n\r\n\r\n\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>DBCC SQLPERF(logspace) &#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;- DECLARE @sql_command varchar(1024) DECLARE @logtable TABLE ( [Database Name] VARCHAR(1000) NULL, [Log Size (MB)] numeric, [Log Space Used (%)] numeric, [Status] VARCHAR(128) NULL ) SELECT @sql_command = &#8216;dbcc sqlperf (logspace)&#8217; INSERT INTO @logtable EXEC (@sql_command) select * from @logtable where [Database Name] = &#8216;RFDB&#8217; SELECT \/* ignore this *\/ command, r.session_id, r.blocking_session_id,&hellip;&nbsp;<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","pgc_sgb_lightbox_settings":"","footnotes":""},"categories":[24],"tags":[],"class_list":["post-1032","post","type-post","status-publish","format-standard","hentry","category-ms-sql-server"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v28.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>SQL Server - Monitor Log Space - dbafox<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/dbafox.com\/?p=1032\" \/>\n<meta property=\"og:locale\" content=\"en_GB\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server - Monitor Log Space - dbafox\" \/>\n<meta property=\"og:description\" content=\"DBCC SQLPERF(logspace) ---------------------------------------------------------------------------------------------- DECLARE @sql_command varchar(1024) DECLARE @logtable TABLE ( [Database Name] VARCHAR(1000) NULL, [Log Size (MB)] numeric, [Log Space Used (%)] numeric, [Status] VARCHAR(128) NULL ) SELECT @sql_command = &#039;dbcc sqlperf (logspace)&#039; INSERT INTO @logtable EXEC (@sql_command) select * from @logtable where [Database Name] = &#039;RFDB&#039; SELECT \/* ignore this *\/ command, r.session_id, r.blocking_session_id,&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/dbafox.com\/?p=1032\" \/>\n<meta property=\"og:site_name\" content=\"dbafox\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-04T06:49:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-01-19T13:42:20+00:00\" \/>\n<meta name=\"author\" content=\"Ray Fox\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ray Fox\" \/>\n\t<meta name=\"twitter:label2\" content=\"Estimated reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1032#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1032\"},\"author\":{\"name\":\"Ray Fox\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"headline\":\"SQL Server &#8211; Monitor Log Space\",\"datePublished\":\"2018-01-04T06:49:40+00:00\",\"dateModified\":\"2018-01-19T13:42:20+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1032\"},\"wordCount\":6,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"articleSection\":[\"MS SQL Server\"],\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/dbafox.com\\\/?p=1032#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1032\",\"url\":\"https:\\\/\\\/dbafox.com\\\/?p=1032\",\"name\":\"SQL Server - Monitor Log Space - dbafox\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\"},\"datePublished\":\"2018-01-04T06:49:40+00:00\",\"dateModified\":\"2018-01-19T13:42:20+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1032#breadcrumb\"},\"inLanguage\":\"en-GB\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/dbafox.com\\\/?p=1032\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/?p=1032#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/dbafox.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server &#8211; Monitor Log Space\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/dbafox.com\\\/#website\",\"url\":\"https:\\\/\\\/dbafox.com\\\/\",\"name\":\"dbafox\",\"description\":\"DBA Technical Information\",\"publisher\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/dbafox.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-GB\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/287adc474c1aebd398752ac4dcfe27eb\",\"name\":\"Ray Fox\",\"logo\":{\"@id\":\"https:\\\/\\\/dbafox.com\\\/#\\\/schema\\\/person\\\/image\\\/\"},\"sameAs\":[\"http:\\\/\\\/www.dbafox.com\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Server - Monitor Log Space - dbafox","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":"https:\/\/dbafox.com\/?p=1032","og_locale":"en_GB","og_type":"article","og_title":"SQL Server - Monitor Log Space - dbafox","og_description":"DBCC SQLPERF(logspace) ---------------------------------------------------------------------------------------------- DECLARE @sql_command varchar(1024) DECLARE @logtable TABLE ( [Database Name] VARCHAR(1000) NULL, [Log Size (MB)] numeric, [Log Space Used (%)] numeric, [Status] VARCHAR(128) NULL ) SELECT @sql_command = 'dbcc sqlperf (logspace)' INSERT INTO @logtable EXEC (@sql_command) select * from @logtable where [Database Name] = 'RFDB' SELECT \/* ignore this *\/ command, r.session_id, r.blocking_session_id,&hellip;&nbsp;","og_url":"https:\/\/dbafox.com\/?p=1032","og_site_name":"dbafox","article_published_time":"2018-01-04T06:49:40+00:00","article_modified_time":"2018-01-19T13:42:20+00:00","author":"Ray Fox","twitter_misc":{"Written by":"Ray Fox","Estimated reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/dbafox.com\/?p=1032#article","isPartOf":{"@id":"https:\/\/dbafox.com\/?p=1032"},"author":{"name":"Ray Fox","@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"headline":"SQL Server &#8211; Monitor Log Space","datePublished":"2018-01-04T06:49:40+00:00","dateModified":"2018-01-19T13:42:20+00:00","mainEntityOfPage":{"@id":"https:\/\/dbafox.com\/?p=1032"},"wordCount":6,"commentCount":0,"publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"articleSection":["MS SQL Server"],"inLanguage":"en-GB","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/dbafox.com\/?p=1032#respond"]}]},{"@type":"WebPage","@id":"https:\/\/dbafox.com\/?p=1032","url":"https:\/\/dbafox.com\/?p=1032","name":"SQL Server - Monitor Log Space - dbafox","isPartOf":{"@id":"https:\/\/dbafox.com\/#website"},"datePublished":"2018-01-04T06:49:40+00:00","dateModified":"2018-01-19T13:42:20+00:00","breadcrumb":{"@id":"https:\/\/dbafox.com\/?p=1032#breadcrumb"},"inLanguage":"en-GB","potentialAction":[{"@type":"ReadAction","target":["https:\/\/dbafox.com\/?p=1032"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/dbafox.com\/?p=1032#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/dbafox.com\/"},{"@type":"ListItem","position":2,"name":"SQL Server &#8211; Monitor Log Space"}]},{"@type":"WebSite","@id":"https:\/\/dbafox.com\/#website","url":"https:\/\/dbafox.com\/","name":"dbafox","description":"DBA Technical Information","publisher":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/dbafox.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-GB"},{"@type":["Person","Organization"],"@id":"https:\/\/dbafox.com\/#\/schema\/person\/287adc474c1aebd398752ac4dcfe27eb","name":"Ray Fox","logo":{"@id":"https:\/\/dbafox.com\/#\/schema\/person\/image\/"},"sameAs":["http:\/\/www.dbafox.com"]}]}},"_links":{"self":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1032","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1032"}],"version-history":[{"count":2,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1032\/revisions"}],"predecessor-version":[{"id":1067,"href":"https:\/\/dbafox.com\/index.php?rest_route=\/wp\/v2\/posts\/1032\/revisions\/1067"}],"wp:attachment":[{"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1032"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1032"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dbafox.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1032"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}