How Do I Spot Identity Columns That Are About to Max Out?

,

Every so often, usually in the middle of the night or on a holiday weekend, an identity column will hit the maximum size for it’s data type and stop allowing new values to be inserted into the table. It goes without saying that an identity column with enough activity to hit the maximum value of even a regular integer indicates a busy table. Oddly enough, none of the monitoring tools that I have looks for an identity column that is about to fill up.

Since I had identified a real problem I decided it was time to turn to my problem solver, SQL Server Management Studio. I was able to quickly throw together a stored procedure that I can install on each of my machines then add custom alerts to my monitoring tools to call it. Since some tools run at the server level and others at the database level I wrote the script to work at the database level. For simplicity I have included a script at the bottom to run this stored procedure on all databases on a server.

The stored procedure logic is to get all identity columns in a database, using the column type from sys.types to calculate percent full based on current identity value vs. maximum identity value for that type. The inner query then returns those values back to the outer query to be filtered and have severity assigned. I could have written the whole thing as a single select rather than nesting it but writing it this way made it so much more readable.

Here is the code for the stored procedure:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE PROCEDURE dbo.sp_dba_check_identities_for_space @warning_threshold_in_pct int = 80, @error_threshold_in_pct int = 90
AS

    SELECT  @warning_threshold_in_pct = @error_threshold_in_pct

    WHERE   @warning_threshold_in_pct > @error_threshold_in_pct

    SELECT  database_name,

            table_name,

            column_type,

            percent_used,

            CASE WHEN percent_used >= @error_threshold_in_pct THEN 'ERROR' ELSE 'WARNING' END AS severity

    FROM    (

                SELECT  DB_NAME() as database_name,

                        OBJECT_NAME(c.object_id) as table_name,

                        IDENT_CURRENT(OBJECT_NAME(c.object_id)) as index_seed,

                        t.name as column_type,

                        (IDENT_CURRENT(OBJECT_NAME(c.object_id)) /

                        CASE

                            WHEN    t.name = 'bigint'

                                THEN    9223372036854775807.00

                            WHEN    t.name = 'int'

                                THEN    2147483647.00

                            WHEN    t.name = 'smallint'

                                THEN    32767.00

                            WHEN    t.name = 'tinyint'

                                THEN    255.00

                            WHEN    t.name = 'numeric'

                                THEN    POWER(10, c.precision) - 1

                            WHEN    t.name = 'decimal'

                                THEN    POWER(10, c.precision) - 1

                            WHEN    t.name = 'money'

                                THEN    922337203685477.5807

                            WHEN    t.name = 'smallmoney'

                                THEN    214748.3647

                            ELSE    1.00

                        END) * 100 AS percent_used

                FROM    sys.columns c

                        INNER JOIN sys.types t

                            ON c.system_type_id = t.system_type_id

                WHERE   c.is_identity = 1

                            AND OBJECTPROPERTY(c.object_id, 'IsUserTable') = 1

            ) dt

    WHERE   percent_used > @warning_threshold_in_pct

GO

EXEC sys.sp_MS_marksystemobject 'sp_dba_check_identities_for_space'

Here is a script to run the stored procedure for all databases on a server:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE #sp_dba_check_identities_for_space_results
(

    database_name   sysname,

    table_name      sysname,

    column_type     sysname,

    percent_used    int,

    severity        varchar(20)
)

EXEC sp_MSforeachdb 'USE ?

INSERT #sp_dba_check_identities_for_space_results

    EXEC dbo.sp_dba_check_identities_for_space'

   
SELECT  database_name,

        table_name,

        column_type,

        percent_used,

        severity
FROM    #sp_dba_check_identities_for_space_results

DROP TABLE #sp_dba_check_identities_for_space_results

Please let me know if you run into any issues, have any ideas that would make this stored procedure better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.

Rate

Share

Share

Rate