Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Переполнение IDENTITY: что можно сделать?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
субж.
19 янв 16, 18:12    [18702788]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Slava_Nik
Member

Откуда: из России
Сообщений: 901
Winnipuh,

сбросить его. если нет первых значений.
Либо тип данных поменять на bigint
19 янв 16, 18:13    [18702797]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Winnipuh,

DBCC RESEED
19 янв 16, 18:13    [18702799]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Minamoto
Winnipuh,

DBCC RESEED

Ну то есть
DBCC CHECKIDENT (table_name, RESEED , new_reseed_value)

конечно же.
19 янв 16, 18:18    [18702806]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp
GO

CREATE TABLE #temp (a SMALLINT IDENTITY(1,1) PRIMARY KEY, b BIT)
GO

SET NOCOUNT ON

INSERT INTO #temp (b)
VALUES (DEFAULT)
GO 32767

DBCC CHECKIDENT (#temp, RESEED , -32767) -- <<<<

INSERT INTO #temp (b)
VALUES (DEFAULT)

SELECT * FROM #temp


Как предлагал ранее Minamoto можно сделать RESEED. Когда у меня заканчивались значение в IDENTITY, а менять тип было леньки, то можно сделать RESEED на минимальное значение типа.
19 янв 16, 18:45    [18702889]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
AlanDenton
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
    DROP TABLE #temp
GO

CREATE TABLE #temp (a SMALLINT IDENTITY(1,1) PRIMARY KEY, b BIT)
GO

SET NOCOUNT ON

INSERT INTO #temp (b)
VALUES (DEFAULT)
GO 32767

DBCC CHECKIDENT (#temp, RESEED , -32767) -- <<<<

INSERT INTO #temp (b)
VALUES (DEFAULT)

SELECT * FROM #temp


Как предлагал ранее Minamoto можно сделать RESEED. Когда у меня заканчивались значение в IDENTITY, а менять тип было леньки, то можно сделать RESEED на минимальное значение типа.


да, в минимум.
19 янв 16, 19:06    [18702933]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Красиво было бы в int->bigint поменять, но на большой таблице слишком длинаня операция
19 янв 16, 19:07    [18702941]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Winnipuh
Красиво было бы в int->bigint поменять, но на большой таблице слишком длинаня операция


на большой таблице внезапно выгоднее скопировать таблицу чем менять тип поля
20 янв 16, 14:08    [18705985]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Alexander Us
Member

Откуда:
Сообщений: 1161
Winnipuh,

Лучше мониторить и заранее знать, что скоро переполнение.
Извините, что лень переводить. Думаю идею поймёте:

-- =============================================
-- Author:		A.Uz
-- Create date: 05.03.2013
-- Description:	Liefert Informationen for Idenit&#228;tsspalten in jeder Dattenbank
-- Beispiel 1: sp_Check_Identities 40.0                   -- Tabellenausgabe 
-- Beispiel 2: sp_Check_Identities 40.0, 'a.b@c.de'       -- Mailbenachrichtigung
--
-- Quelle : http://sqlfool.com/2011/01/identity-columns-are-you-nearing-the-limits/
-- =============================================
ALTER PROCEDURE [dbo].[sp_Check_Identities]	

	@AlarmWennBelegtMehrAlsProzent decimal(5,2) = 99.0,
	@SemikolongetrennteEmails varchar(200) = NULL	
	
AS
BEGIN	
	SET NOCOUNT ON;
       
  
Create Table #identityStatus
(
      database_name     varchar(128)
    , table_name        varchar(128)
    , column_name       varchar(128)
    , data_type         varchar(128)
    , last_value        bigint
    , max_value         bigint
)

 
/* Use an undocumented command to run a SQL statement in each database on a server */
Execute sp_msforeachdb '
    Use [?];
    Insert Into #identityStatus
    Select ''?'' As [database_name]
        , Object_Name(id.object_id, DB_ID(''?'')) As [table_name]
        , id.name As [column_name]
        , t.name As [data_type]
        , Cast(id.last_value As bigint) As [last_value]
        , Case 
            When t.name = ''tinyint''   Then 255 
            When t.name = ''smallint''  Then 32767 
            When t.name = ''int''       Then 2147483647 
            When t.name = ''bigint''    Then 9223372036854775807
          End As [max_value]
    From sys.identity_columns As id
    Join sys.types As t
        On id.system_type_id = t.system_type_id
    Where id.last_value Is Not Null';
 
/* Retrieve our results and format it all prettily */
Select 
      Datenbank=database_name      
    , Tabelle=table_name
    , Spalte=column_name
    , Typ=data_type
    
    , AktWert=last_value
    
    , MaxWert=max_value
    
    , AnzFrei=max_value-last_value
      
    
    , ProzFrei=
      Case 
        When last_value < 0 Then 100
        Else cast((1 - last_value*1.0 / max_value) * 100  as decimal(24,4))
      End   
      
    , ProzBelegt=
      Case 
        When last_value < 0 Then 0
        Else Cast((last_value*1.0 / max_value) * 100 As decimal(24,4))
      End     
      
    , Alarm=
        Case 
         When last_value*1.0 / max_value >= @AlarmWennBelegtMehrAlsProzent / 100.0
            Then 1
         Else 0
        End 
        
into #results                      
From #identityStatus
where database_name not in ('msdb','','','')
Order By ProzFrei;


DECLARE @body NVARCHAR(MAX)
SET     @body = 
N'<head>
<style>
p{font-family: Courier New; font-size:11px; color:blue}
table{border-collapse:collapse;}
table{padding:0px 4px 0px 4px;}
table, td, th {border:1px solid black;}
th {background-color:Crimson; color:white;}
table{font-size:12px;}
</style>
</head>'
 
set @body=@body+ N'<p>Mindestens eine der Identit&#228;tsspalten hat mehr als ' + cast(@AlarmWennBelegtMehrAlsProzent as varchar(20)) + '% der Werte belegt'

set @body = @body +  N'<table>'
    + N'<tr><th>Datenbank</th><th>Tabelle</th><th>Spalte</th><th>Typ</th><th>akt.Wert</th><th>max.Wert</th><th>Anz.Frei</th><th>% Frei</th><th>% Belegt</th></tr>'
    + CAST((
        SELECT  p.Datenbank		AS td,
                p.Tabelle		AS td,
                p.Spalte		AS td,
                p.Typ			AS td,
                p.AktWert		AS td,
                p.MaxWert		AS td,
                p.AnzFrei		AS td,
                p.ProzFrei		AS td,
                p.ProzBelegt	AS td              
        FROM    #results p
        WHERE   Alarm=1
        FOR XML RAW('tr'), ELEMENTS
    ) AS NVARCHAR(MAX))
    + N'</table>'

declare @procname varchar(200)
set @procname = isnull(OBJECT_NAME(@@PROCID),'_sp_Check_Identities_')
    
set @body = @body + 
'<p>Job: "Tools: Check Identity Columns"' +
'<br>Detaillierte &#220;bersicht &#252;ber Prozeduraufruf auf: ' + @@SERVERNAME  + 
'<br>Beispiel 1: ' + DB_NAME() + '..' + @procname + ' 90.5                      -- zeigt alle Identit&#228;tsspalten die haben >= 90.5% Werte belegt'  +  
'<br>Beispiel 2: ' + DB_NAME() + '..' + @procname + ' 90.5, ''aaa@bbb.de''  -- schickt Mail falls eine der Spalten belegt >= 90.5% der Werte'       
   
if len(@SemikolongetrennteEmails)>0 and exists(select * from #results where alarm = 1 )
begin   
    exec msdb..sp_send_dbmail
	@profile_name		='aaa@bb.de', 
	@recipients			=@SemikolongetrennteEmails, 	
	@copy_recipients	='', 
	@body_format		='HTML',
	@subject			='Identit&#228;tsspalte l&#228;uft bald &#252;ber', 
	@body				=@body,
	@importance			='HIGH'   
end
else
begin
   select * from #results order by ProzBelegt desc
end	
   
Drop Table #identityStatus;

END
20 янв 16, 15:55    [18706729]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Crimean
Winnipuh
Красиво было бы в int->bigint поменять, но на большой таблице слишком длинаня операция


на большой таблице внезапно выгоднее скопировать таблицу чем менять тип поля


С одной стороны, но с другой стороны, когад это случается у клиента посреди работы, он не дает столько времени ;-)
20 янв 16, 16:08    [18706831]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Winnipuh,

так как раз сделать select * into NewTable оказывается _быстрее_ чем делать alter column
а индексы и так и так перестраивать. я тоже именно про требуемое время говорил
20 янв 16, 19:13    [18707819]     Ответить | Цитировать Сообщить модератору
 Re: Переполнение IDENTITY: что можно сделать?  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Crimean
Winnipuh,

так как раз сделать select * into NewTable оказывается _быстрее_ чем делать alter column
а индексы и так и так перестраивать. я тоже именно про требуемое время говорил


Это 100%, согласен, там правда еще связи есть, буду экспериментировать.
зы. единственным решением в той ситуации оказалось сбить в минимальное число, другого они бы не выдержали, не дождались бы
21 янв 16, 08:27    [18708884]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить