I recently encountered a SSIS package that was failing due to an ‘arithmetic overflow error converting IDENTITY to datatype int’;
Conversion/overflow errors aren’t that unusual – normally a data flow broken by some unexpected data (“no, there’s no chance that field would ever have a character in it”), or perhaps a column hitting max size (“INT will be enough for years, like, 5 years. I’ll have left the company by then”)
But that wasn’t the case here – the package and user tables involved were checked by the dev team and there was no possible overflow. I’d checked system databases for maxed-out identity columns and found nothing. Heads were scratched.
In hindsight, there were two major clues pointing to the cause;
There were many (5 figures) duplicate errors in the log. So many in fact that the SSMS GUI was throwing a memory exception trying to view them and I had to pull from them from catalog.operation_messages
These errors were occuring on validation – it hadn’t even started running!
Clue 1# suggested a systemic issue not related to any particular function of the package. Clue #2 suggested the user tables were not even getting touched so could not be throwing overflow errors. So, we’re looking at system tables.
But I’d checked those? Well, I’d checked system databases…
Let’s loop over all the user databases on the server and run the below script to check for identity columns of type int…
SELECT OBJECT_NAME(c.[object_id]) AS 'TableName',
c.[name] AS 'ColumnName',
c.system_type_id,
t.[name] AS 'Type'
FROM sys.all_columns c
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
WHERE c.is_identity = 1
AND t.[name] = 'int'
Oh, what’s this? That doesn’t look like a user table…I wonder…
Bingo.
This is the sysssislog table, usually found in msdb. Whoever installed SSIS on this box must have set it to the user database in question. And now it had exhausted its supply of IDENTITY values on the id column, triggering the execution/validation of any package logging to it to instantly fail and flood the operations log with overflow errors.
We didn’t need any of the logs, so truncating the table (which also resets the IDENTITY value) resolved the problem.
Hopefully you already know everything about your SQL estate, including what services are installed and what’s running them, either because it’s so small you just know, or, preferably, you have some kind of CMDB. But what if you don’t? Powershell comes to the rescue, of course.
This post details a script to capture basic information – service name, account, start mode and status – about the database engine, Agent and any SSRS/SSAS/SSRS installs. It requires remote WMI connectivity and, for SSIS, SMB access to enabled administrative shares on the drives where the service is installed.
(Note – if all you’re after is the Database Engine and/or SQL Agent info, and you’re running SQL 2008 R2 SP1 or higher, no need to carry on reading – just use the sys.dm_server_services DMV instead, it’s easy.)
To begin we need some kind of data source containing both the hostnames and instance names (MSSQLSERVER if default instance) of the servers in the estate – as we’ll be connecting to the hostname but making checks using the instance name. Either stored in a table;
Then we can loop over the servers and use the Get-WmiObject Win32_Service cmdlet. For the database engine, Agent, SSRS and SSAS this is straightforward – we’re filtering on the service name looking for a reference to the instance, then disregarding any CEIP services and pulling out a few pieces of information. For example, a snippet of the script checking for a SSAS install;
SSIS is a bit trickier. You may have noticed that, unlike for the Database Engine, SSRS or SSAS, the instance name is not part of the service name;
So we can’t just filter on the service name to check which instance the service is installed as part of. Instead we need to find the MsDtsSrvr.ini.xml file associated with the service (by using the service PathName), open it up as XML (this is where the requirement for SMB and administrative share access arises) and look at the DtsServiceConfiguration.TopLevelFolders.Folder.ServerName element;
An example output from the whole script, ran on a single server;
This shows I have the database engine and agent running from MSAs, and two extra services installed – SSAS running as a local service and SSIS running as an MSA, all set to auto-start and only SSIS not currently running.
The entire skeleton script is below (github link), using the CSV file example for retrieving host/instance names as a base and simply writing the output to console – it’s easily modifiable to change the source and/or save the output to a file or table.
Introduced in SQL Server 2008, the date datatype brought storage of dates in the YYYY-MM-DD format – 0001-01-01 to 9999-12-31, 3 bytes. This was an addition to the existing types of datetime – 2021-03-09 12:20:16.021, 8 bytes – and smalldatetime – 2021-03-09 12:20, 4 bytes.
It solved a range problem (datetimes can’t store years prior to 1753, smalldatetimes1900) that previously required hacking around, but so did the new datetime2type, so that’s not why I’m nominating it my favourite data type for T-SQL Tuesday.
The reason is simply what it is – a date without a time component.
Often we just want a date and don’t care about the time at all, nevermind the time to millisecond accuracy. Think transaction dates, staff joining dates, DOBs, etc. But the existing types foisted a time upon the column along with extra bytes of storage, and I couldn’t begin to count the times I’ve had to type this pattern;
WHERE DateTimeColumn BETWEEN '2020-03-09 00:00:00.000' AND '2020-03-09 23:59:59.999'
-- or if performance is not an issue
WHERE CONVERT(DATE, DateTimeColumn) = '2020-03-09'
With date it’s simple;
WHERE DateColumn = '2020-03-09'
Three cheers for this three part, three byte hero.
Now, I was still at college when date was born and years away from touching SQL. One might think that 13 years later the code I’ve been working with recently as a dev/DBA would have plentiful usage of date when appropriate…
Unfortunately not. This would have been very different if date had been in SQL 2005 or 2000, but it wasn’t – so the result is oodles of datetimes left lying around because the schemas had already been designed. Then they hang around not only because existing code is extended over the years, but because people doing entirely new deployments copy conventions from existing codebases.
It’s a great example of a ghost of a missing feature still haunting the present. I wonder how many GB/TB (/PB??) on the planet is wasted by datetimes with 00:00:00.000 time components right now.
The login auditing setting on a SQL Server instance controls the auditing of logins – either all, successful only, failed only or none. It’s set via SSMS on the Server properties dialog;
and is responsible for these types of message in the log;
You might want to change this for several reasons – perhaps you have strict auditing requirements so want everything logged, or perhaps you only care about failures – especially if you have a noisy application that floods the log with successful logins.
It’s straightforward enough to change in SSMS – though note that a service restart is required for changes to take effect – but what if you want to change this setting on a bunch of servers? Fortunately, it can be programmatically read/changed using the (undocumented) xp_instance_regread and xp_instance_regwrite extended stored procedures, reading from/writing to the Windows registry value AuditLevel (specifically, the value in the correct key for the instance the proc is executed from) – that the setting in SSMS actually represents;
Illustration purposes only – we don’t need to go into the Registry manually for this.
This key maps to the following values;
0 – None
1 – Successful logins only
2 – Failed logins only
3 – All logins only
To retrieve the value, we can use xp_instance_regread ;
DECLARE @result INT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUTSELECT @result AS 'AuditLevel'
To set it, we use xp_instance_regwrite. An example of setting it to 2 (Failed logins only);
So by calling this procedure we can script out a read/change to the setting to multiple instances at once using Powershell. (Yep, we could directly modify the registry with Powershell and skip the call, but doing it this way benefits from the wrapping of the registry modification in the SQL extended proc, and thus the correct key for the instance being automatically used thanks to the ‘instance’ versions of the reg read/write procs vs the standard xp_regread/regwrite)
Retrieving the key is a straightforward iteration over instances. In this example I’ve populated an $instances variable with an array of instance names, but this can be substituted with reading from a text file or database;
After modification, the first script can be re-ran to confirm the modifications – and don’t forget a service restart is required to kick the setting into action.
Disclaimer – the registry-editing procedures used here are undocumented, and editing the registry is generally best avoided – any such code should be tested thoroughly before use in production.
