{"id":50,"date":"2017-09-27T22:18:47","date_gmt":"2017-09-27T20:18:47","guid":{"rendered":"http:\/\/wchmurze.cloud\/?p=50"},"modified":"2019-08-18T15:06:37","modified_gmt":"2019-08-18T13:06:37","slug":"kiedy-skonczy-sie-identity","status":"publish","type":"post","link":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/","title":{"rendered":"Kiedy sko\u0144czy si\u0119 identity"},"content":{"rendered":"<p>To by\u0142a d\u0142uga bezsenna noc. Szczekaj\u0105ce psy s\u0105siada, deszcz bij\u0105cy o szyby pokoju.<br \/>\nCzwarta nad ranem, gdy w ko\u0144cu uda\u0142o si\u0119 zasn\u0105\u0107, kom\u00f3rka s\u0142u\u017cbowa zacz\u0119\u0142a dzwoni\u0107.<\/p>\n<p><span style=\"color: #ff0000;\">Msg 8115, Level 16, State 1, Line 1<\/span><br \/>\n<span style=\"color: #ff0000;\"> Arithmetic overflow error converting IDENTITY to data type int.<\/span><br \/>\n<span style=\"color: #ff0000;\"> Arithmetic overflow occurred.<\/span><br \/>\nPo raz kolejny przepe\u0142ni\u0142 si\u0119 autonumer jednej z licznych tabel na serwerze produkcyjnym.<\/p>\n<p>Jak si\u0119 przed tym obroni\u0107 ? Jak oszacowa\u0107 kiedy sko\u0144czy nam si\u0119 mo\u017cliwo\u015b\u0107 generowania autonumeracji ?<br \/>\nCzy da si\u0119 opracowa\u0107 proaktywny mechanizm wspomagaj\u0105cy codziennie utrzymanie system\u00f3w ?<\/p>\n<p>Spr\u00f3bujmy oszacowa\u0107 to jedynie na podstawie metadanych.<\/p>\n<p>Co mamy do dyspozycji ?<\/p>\n<p>Liczb\u0119 rekord\u00f3w w tabeli, jej dat\u0119 utworzenia, aktualn\u0105 warto\u015b\u0107 licznika, maksymaln\u0105 warto\u015b\u0107 licznika.<\/p>\n<p>Zastosowany jest najprostszy z mo\u017cliwych. algorytm\u00f3w.<\/p>\n<p>Zak\u0142adamy, \u017ce liczba rekord\u00f3w przyrasta liniowo w czasie, parametry tego wzrostu s\u0105 tak dopasowane, \u017ce w chwili <strong>t0<\/strong> (dla daty utworzenia) liczba rekord\u00f3w wynosi 0, a dla chwili <strong>tk<\/strong> (dla daty katastrofy, czyli przepe\u0142nienia typu ca\u0142kowitego) liczba rekord\u00f3w jest r\u00f3wna maksymalnej.<\/p>\n<p>Poni\u017cej umieszczono szkic skryptu:<\/p>\n<pre lang=\"tsql\">\r\n\r\n\r\nSET tran isolation level READ uncommitted \r\n\r\nIF object_id('tempdb..#DataTypeMaxValue') IS  NULL\r\n \r\nBEGIN\r\n \r\n  CREATE TABLE #DataTypeMaxValue (DataType VARCHAR(50), MaxValue BIGINT)\r\n \r\nEND\r\n \r\n \r\nIF object_id('tempdb..#report') IS NOT  NULL\r\n \r\nBEGIN\r\n \r\n DROP TABLE #report\r\n \r\nEND\r\n \r\n \r\nINSERT INTO #DataTypeMaxValue VALUES\r\n   ('tinyint' , 255),\r\n   ('smallint' , 32767),\r\n   ('int' , 2147483647),\r\n   ('bigint' , 9223372036854775807)\r\n \r\n \r\nSELECT\r\n   DISTINCT OBJECT_NAME (IC.object_id) AS TableName,\r\n   CONVERT(VARCHAR(10),TN.create_date,120) AS CreateDate,\r\n   DATEDIFF(DAY,CONVERT(VARCHAR(10),TN.create_date,120),getdate()) AS NumberOfDaysFromCreation,\r\n   IC.name AS ColumnName,\r\n   TYPE_NAME(IC.system_type_id) AS ColumnDataType,\r\n   DTM.MaxValue AS MaxDataTypeValue,\r\n   IC.seed_value IdentitySeed,\r\n   IC.increment_value AS IdentityIncrement,\r\n   IC.last_value,\r\n   sum(DBPS.row_count) AS NumberOfRows,\r\n   abs((try_convert(DECIMAL(9,2),try_CONVERT(BIGINT,IC.last_value)*100\/DTM.MaxValue))) AS ReachMaxValuePercent ,\r\n   CONVERT(datetime,NULL) AS DisasterDay\r\n   INTO #report\r\n \r\nFROM sys.identity_columns IC\r\n   JOIN sys.tables TN ON IC.object_id = TN.object_id\r\n   JOIN #DataTypeMaxValue DTM ON TYPE_NAME(IC.system_type_id)=DTM.DataType\r\n   JOIN sys.dm_db_partition_stats DBPS ON DBPS.object_id =IC.object_id\r\n  \r\nWHERE DBPS.row_count >=1000  \r\n\tAND DBPS.partition_number IN (0,1) \r\n\tAND DBPS.index_id<2\r\n \r\ngroup BY\r\n \r\nOBJECT_NAME (IC.object_id) ,\r\n   CONVERT(VARCHAR(10),TN.create_date,120) ,\r\n   DATEDIFF(DAY,CONVERT(VARCHAR(10),TN.create_date,120),getdate()),\r\n \r\n   IC.name,\r\n   TYPE_NAME(IC.system_type_id) ,\r\n   DTM.MaxValue ,\r\n   IC.seed_value ,\r\n   IC.increment_value,\r\n   IC.last_value,\r\n   abs((try_convert(DECIMAL(9,7),try_CONVERT(BIGINT,IC.last_value)*100\/DTM.MaxValue)))\r\n \r\n\r\n   -- obsluga ujemnych wartosci IDENT_CURRENT\r\n \r\nupdate r\r\n \r\nSET\r\nReachMaxValuePercent=1*try_convert(DECIMAL(9,7),ABS(try_convert(BIGINT,-r.MaxDataTypeValue)-try_convert(BIGINT,r.last_value))*100\/r.MaxDataTypeValue)\r\n \r\nfrom #report r\r\nwhere ReachMaxValuePercent>0\r\nand LAST_VALUE<0\r\n \r\nUPDATE  r\r\nSET DisasterDay=dateadd(DAY,NumberOfDaysFromCreation*100\/ReachMaxValuePercent,CReateDate)\r\nFROM #report r\r\nWHERE ReachMaxValuePercent>0\r\nand LAST_VALUE<>0\r\n \r\n \r\nselect DisasterDay,tablename, r.* from  #report r\r\nWHERE ReachMaxValuePercent>0\r\norder by r.DisasterDay,r.tablename\r\n \r\nDROP TABLE #report\r\n \r\nDROP TABLE #DataTypeMaxValue\r\n\r\n<\/pre>\n<p>Dla tych, kt\u00f3rych takie rozwa\u017cania nudz\u0105, ciekawe mo\u017ce si\u0119 wyda\u0107, to \u017ce przekroczenie zakresu zmiennej ca\u0142kowitej spowodowa\u0142o wiele katastrof, kt\u00f3re wynika\u0142y ze z\u0142ego oszacowania czasu pracy oprogramowania i zastosowanego typu zmiennej. Warto o tym pomy\u015ble\u0107 podczas projektowania rozwi\u0105zania bazodanowego na d\u0142ugie lata.<\/p>\n<p><a href=\"https:\/\/bulldogjob.pl\/news\/114-upiorne-bugi-male-bledy-wielkie-katastrofy\">https:\/\/bulldogjob.pl\/news\/114-upiorne-bugi-male-bledy-wielkie-katastrofy<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>To by\u0142a d\u0142uga bezsenna noc. Szczekaj\u0105ce psy s\u0105siada, deszcz bij\u0105cy o szyby pokoju. Czwarta nad ranem, gdy w ko\u0144cu uda\u0142o si\u0119 zasn\u0105\u0107, kom\u00f3rka s\u0142u\u017cbowa zacz\u0119\u0142a dzwoni\u0107. Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting IDENTITY to &hellip; <a href=\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\">Continued<\/a><\/p>\n","protected":false},"author":1,"featured_media":1073,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[8,2],"tags":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v19.13 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Kiedy sko\u0144czy si\u0119 identity - W chmurze o chmurze i nie tylko<\/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:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\" \/>\n<meta property=\"og:locale\" content=\"pl_PL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Kiedy sko\u0144czy si\u0119 identity - W chmurze o chmurze i nie tylko\" \/>\n<meta property=\"og:description\" content=\"To by\u0142a d\u0142uga bezsenna noc. Szczekaj\u0105ce psy s\u0105siada, deszcz bij\u0105cy o szyby pokoju. Czwarta nad ranem, gdy w ko\u0144cu uda\u0142o si\u0119 zasn\u0105\u0107, kom\u00f3rka s\u0142u\u017cbowa zacz\u0119\u0142a dzwoni\u0107. Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting IDENTITY to &hellip; Continued\" \/>\n<meta property=\"og:url\" content=\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\" \/>\n<meta property=\"og:site_name\" content=\"W chmurze o chmurze i nie tylko\" \/>\n<meta property=\"article:published_time\" content=\"2017-09-27T20:18:47+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2019-08-18T13:06:37+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/wchmurze.cloud\/wp-content\/uploads\/2018\/04\/sql-glass.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"700\" \/>\n\t<meta property=\"og:image:height\" content=\"467\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"djkormo\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Napisane przez\" \/>\n\t<meta name=\"twitter:data1\" content=\"djkormo\" \/>\n\t<meta name=\"twitter:label2\" content=\"Szacowany czas czytania\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minuty\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\"},\"author\":{\"name\":\"djkormo\",\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\"},\"headline\":\"Kiedy sko\u0144czy si\u0119 identity\",\"datePublished\":\"2017-09-27T20:18:47+00:00\",\"dateModified\":\"2019-08-18T13:06:37+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\"},\"wordCount\":244,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\"},\"articleSection\":[\"migawka\",\"t-sql\"],\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\",\"url\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\",\"name\":\"Kiedy sko\u0144czy si\u0119 identity - W chmurze o chmurze i nie tylko\",\"isPartOf\":{\"@id\":\"https:\/\/wchmurze.cloud\/#website\"},\"datePublished\":\"2017-09-27T20:18:47+00:00\",\"dateModified\":\"2019-08-18T13:06:37+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#breadcrumb\"},\"inLanguage\":\"pl-PL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Strona g\u0142\u00f3wna\",\"item\":\"https:\/\/wchmurze.cloud\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Kiedy sko\u0144czy si\u0119 identity\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/wchmurze.cloud\/#website\",\"url\":\"https:\/\/wchmurze.cloud\/\",\"name\":\"W chmurze o chmurze i nie tylko\",\"description\":\"W chmurze o chmurze i nie tylko\",\"publisher\":{\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/wchmurze.cloud\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"pl-PL\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323\",\"name\":\"djkormo\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"pl-PL\",\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g\",\"caption\":\"djkormo\"},\"logo\":{\"@id\":\"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/\"},\"url\":\"https:\/\/wchmurze.cloud\/index.php\/author\/djkormo\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Kiedy sko\u0144czy si\u0119 identity - W chmurze o chmurze i nie tylko","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:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/","og_locale":"pl_PL","og_type":"article","og_title":"Kiedy sko\u0144czy si\u0119 identity - W chmurze o chmurze i nie tylko","og_description":"To by\u0142a d\u0142uga bezsenna noc. Szczekaj\u0105ce psy s\u0105siada, deszcz bij\u0105cy o szyby pokoju. Czwarta nad ranem, gdy w ko\u0144cu uda\u0142o si\u0119 zasn\u0105\u0107, kom\u00f3rka s\u0142u\u017cbowa zacz\u0119\u0142a dzwoni\u0107. Msg 8115, Level 16, State 1, Line 1 Arithmetic overflow error converting IDENTITY to &hellip; Continued","og_url":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/","og_site_name":"W chmurze o chmurze i nie tylko","article_published_time":"2017-09-27T20:18:47+00:00","article_modified_time":"2019-08-18T13:06:37+00:00","og_image":[{"width":700,"height":467,"url":"https:\/\/wchmurze.cloud\/wp-content\/uploads\/2018\/04\/sql-glass.jpg","type":"image\/jpeg"}],"author":"djkormo","twitter_card":"summary_large_image","twitter_misc":{"Napisane przez":"djkormo","Szacowany czas czytania":"2 minuty"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#article","isPartOf":{"@id":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/"},"author":{"name":"djkormo","@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323"},"headline":"Kiedy sko\u0144czy si\u0119 identity","datePublished":"2017-09-27T20:18:47+00:00","dateModified":"2019-08-18T13:06:37+00:00","mainEntityOfPage":{"@id":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/"},"wordCount":244,"commentCount":0,"publisher":{"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323"},"articleSection":["migawka","t-sql"],"inLanguage":"pl-PL","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/","url":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/","name":"Kiedy sko\u0144czy si\u0119 identity - W chmurze o chmurze i nie tylko","isPartOf":{"@id":"https:\/\/wchmurze.cloud\/#website"},"datePublished":"2017-09-27T20:18:47+00:00","dateModified":"2019-08-18T13:06:37+00:00","breadcrumb":{"@id":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#breadcrumb"},"inLanguage":"pl-PL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/wchmurze.cloud\/index.php\/2017\/09\/27\/kiedy-skonczy-sie-identity\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Strona g\u0142\u00f3wna","item":"https:\/\/wchmurze.cloud\/"},{"@type":"ListItem","position":2,"name":"Kiedy sko\u0144czy si\u0119 identity"}]},{"@type":"WebSite","@id":"https:\/\/wchmurze.cloud\/#website","url":"https:\/\/wchmurze.cloud\/","name":"W chmurze o chmurze i nie tylko","description":"W chmurze o chmurze i nie tylko","publisher":{"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/wchmurze.cloud\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"pl-PL"},{"@type":["Person","Organization"],"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/9832cc6f86f99f541d983d2b8d60f323","name":"djkormo","image":{"@type":"ImageObject","inLanguage":"pl-PL","@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/14a901b808871fa98086ae259c45d646?s=96&d=mm&r=g","caption":"djkormo"},"logo":{"@id":"https:\/\/wchmurze.cloud\/#\/schema\/person\/image\/"},"url":"https:\/\/wchmurze.cloud\/index.php\/author\/djkormo\/"}]}},"_links":{"self":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts\/50"}],"collection":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/comments?post=50"}],"version-history":[{"count":17,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts\/50\/revisions"}],"predecessor-version":[{"id":362,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/posts\/50\/revisions\/362"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/media\/1073"}],"wp:attachment":[{"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/media?parent=50"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/categories?post=50"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wchmurze.cloud\/index.php\/wp-json\/wp\/v2\/tags?post=50"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}