{"id":13173,"date":"2020-08-19T08:25:59","date_gmt":"2020-08-19T11:25:59","guid":{"rendered":"https:\/\/www.fabriciolima.net\/?p=13173"},"modified":"2020-08-20T09:06:41","modified_gmt":"2020-08-20T12:06:41","slug":"monitoramento-no-sql-server-o-que-fazer-quando-receber-um-alerta-de-crescimento-do-tempdb","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2020\/08\/19\/monitoramento-no-sql-server-o-que-fazer-quando-receber-um-alerta-de-crescimento-do-tempdb\/","title":{"rendered":"Monitoramento no SQL Server &#8211; O que fazer quando receber um alerta de crescimento do Tempdb?"},"content":{"rendered":"<p>Fala Pessoal,<\/p>\n<p>Em Setembro de 2019 liberei uma nova vers\u00e3o dos meus scripts para a cria\u00e7\u00e3o de 40 alertas no SQL Server:<\/p>\n<ul>\n<li><a href=\"https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/<\/a><\/li>\n<\/ul>\n<p>Legal Fabr\u00edcio, mas o que eu fa\u00e7o quando receber esses Alertas?<\/p>\n<p>Esse \u00e9 mais um v\u00eddeo para ajudar a responder essa pergunta:<\/p>\n<p><iframe loading=\"lazy\" title=\"Monitoramento no SQL Server - O que fazer quando receber um alerta de crescimento do Tempdb?\" width=\"1170\" height=\"658\" src=\"https:\/\/www.youtube.com\/embed\/8QYBEwWSEgQ?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture\" allowfullscreen><\/iframe><\/p>\n<p>Queries utilizadas nesse post:<\/p>\n<header>\n<pre class=\"lang:default decode:true\">\r\n\r\nselect *\r\nfrom Traces..Alert_Parameter\r\nwhere Nm_Alert = 'Tempdb MDF File Utilization'\r\n\r\n\r\n-- Analisar a utiliza\u00e7\u00e3o do tempdb\r\nSELECT a.name AS LogicalName,\r\n'SizeinMB' = (size\/128)\r\n,fileproperty(a.name, 'spaceused' )\/128 as UsedinMB\r\n,(size\/128) -fileproperty (a.name,'SpaceUsed')\/128 AS FreeInMB\r\n,'Free%'=cast (((a.size\/128.0)-fileproperty(a.name,'SpaceUsed')\/128.0)\/(a.size\/128.0)*100 as numeric(15))\r\n, ((a.size\/128.0)-fileproperty(a.name,'SpaceUsed')\/128.0) \/ SUM ((a.size\/128.0)-(fileproperty(a.name,'SpaceUsed')\/128)) OVER (PARTITION BY fg.data_space_id) As [PropFree%]\r\n,fg.name\r\nFROM sysfiles a LEFT join sys.filegroups fg \r\nON a.groupid = fg.data_space_id\r\n\r\n\r\n\r\nupdate Traces..Alert_Parameter\r\nset Vl_Parameter_2 = 4000  --default \u00e9 10000 MB\r\nwhere Nm_Alert = 'Tempdb MDF File Utilization'\r\n\r\nsp_whoisactive\r\n\r\n--Query que pode ajudar a pegar o que est\u00e1 no tempdb\r\n;with tab(session_id, host_name, login_name, totalalocadomb, text)\r\nas(\r\nSELECT a.session_id,\r\nb.host_name,\r\nb.login_name,\r\n( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 \/ 128 AS totalalocadomb,\r\nd.TEXT\r\nFROM sys.dm_db_session_space_usage a\r\nJOIN sys.dm_exec_sessions b ON a.session_id = b.session_id\r\nJOIN sys.dm_exec_connections c ON c.session_id = b.session_id\r\nCROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS d\r\nWHERE a.session_id &gt; 50\r\n--AND ( user_objects_alloc_page_count + internal_objects_alloc_page_count ) * 1.0 \/ 128 &gt; 10 -- Ocupam mais de 10 MB\r\n)\r\nselect top 20 * from tab order by 4 desc\r\n\r\n\r\nSELECT a.name AS LogicalName,\r\n'SizeinMB' = (size\/128)\r\n,fileproperty(a.name, 'spaceused' )\/128 as UsedinMB\r\n,(size\/128) -fileproperty (a.name,'SpaceUsed')\/128 AS FreeInMB\r\n,'Free%'=cast (((a.size\/128.0)-fileproperty(a.name,'SpaceUsed')\/128.0)\/(a.size\/128.0)*100 as numeric(15))\r\n, ((a.size\/128.0)-fileproperty(a.name,'SpaceUsed')\/128.0) \/ SUM ((a.size\/128.0)-(fileproperty(a.name,'SpaceUsed')\/128)) OVER (PARTITION BY fg.data_space_id) As [PropFree%]\r\n,fg.name\r\nFROM sysfiles a LEFT join sys.filegroups fg \r\nON a.groupid = fg.data_space_id\r\n\r\nkill 66\r\nkill 65\r\n\r\n\r\n\r\n----- rquivo para testar a carga no TEMPDB\r\n--drop table #StressTempDB\r\n\r\n SELECT TOP 1000000000\r\n        IDENTITY(INT,1,1) AS RowNum\r\n   INTO #StressTempDB\r\n   FROM master.sys.all_columns ac1,\r\n        master.sys.all_columns ac2,\r\n        master.sys.all_columns ac3;\r\nGO<\/pre>\n<p>&nbsp;<\/p>\n<\/header>\n<header><b>Gostou desse Post?<\/b><\/header>\n<p>Curta, comente, compartilhe\u2026<\/p>\n<p>Curta nossa p\u00e1gina no\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.facebook.com\/SouPowerTuning\" target=\"_blank\" rel=\"external noopener noreferrer nofollow\" data-wpel-link=\"external\">Facebook<\/a>\u00a0,\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.linkedin.com\/company\/sou-powertuning\" target=\"_blank\" rel=\"noopener external noreferrer nofollow\" data-wpel-link=\"external\">LinkedIn<\/a>\u00a0e\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.instagram.com\/soupowertuning\/\" target=\"_blank\" rel=\"noopener external noreferrer nofollow\" data-wpel-link=\"external\">Instagram<\/a>\u00a0para receber Dicas de Leituras, V\u00eddeos e Eventos sobre SQL Server.<\/p>\n<p>At\u00e9 a pr\u00f3xima.<\/p>\n<p>Abra\u00e7os,<\/p>\n<p>Fabr\u00edcio Lima<\/p>\n<p>CEO na Fabr\u00edcio Lima Solu\u00e7\u00f5es em BD<\/p>\n<p>Microsoft Data Platform MVP<\/p>\n<p>Instagram:<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.instagram.com\/fabriciofrancalima\/\" target=\"_blank\" rel=\"noopener external noreferrer nofollow\" data-wpel-link=\"external\">@fabriciofrancalima<\/a><\/p>\n<p>Linkedin:\u00a0<a class=\"external-link wpel-icon-left\" href=\"https:\/\/www.linkedin.com\/in\/fabriciofrancalima\/\" target=\"_blank\" rel=\"noopener noreferrer external nofollow\" data-wpel-link=\"external\">https:\/\/www.linkedin.com\/in\/fabriciofrancalima\/<\/a><\/p>\n<p>Consultoria:\u00a0comercial@powertuning.com.br<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Fala Pessoal, Em Setembro de 2019 liberei uma nova vers\u00e3o dos meus scripts para a cria\u00e7\u00e3o de 40 alertas no SQL Server: https:\/\/www.fabriciolima.net\/blog\/2019\/09\/22\/passo-a-passo-de-como-criar-40-alertas-para-monitorar-seu-sql-server\/ Legal Fabr\u00edcio, mas o que eu fa\u00e7o quando receber esses Alertas? Esse \u00e9 mais um v\u00eddeo para ajudar a responder essa pergunta: Queries utilizadas nesse post: select * from Traces..Alert_Parameter where Nm_Alert [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,942,991,33,280],"tags":[1261,132,1789,33,287,1790,1791],"class_list":["post-13173","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-consultoria-sql-server","category-dba-remoto","category-sql-server","category-virtual-pass-br","tag-alertas","tag-monitoramento","tag-problema-tempdb","tag-sql-server","tag-tempdb","tag-tempdb-cresceu","tag-tempdb-full"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/13173","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=13173"}],"version-history":[{"count":2,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/13173\/revisions"}],"predecessor-version":[{"id":13188,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/13173\/revisions\/13188"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=13173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=13173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=13173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}