DBCC LOGINFO changes in 2012+

Question

DBCC LOGINFO changes in 2012+

Beatrix Kiddo

SSC-Dedicated

Points: 32407
More actions
April 18, 2016 at 3:03 am

#325735

Hi. I wonder if anybody can help me with troubleshooting a very old SSIS package. The person who created it has left, and I'm trying to work my way through it to figure out what it does and how.
From what I can see it connects to every instance in our environment and runs a series of checks against them, then compiles the data centrally for reporting purposes. One of the tasks which is failing creates a temporary table on each instance, executes DBCC LOGINFO WITH NO_INFOMSGS and enters the results into the temporary table (then collects it later, then drops the temp table). However it fails with the following error message:
Executing the query " CREATE TABLE [tempdb].[dbo].[Obfuscated]..." failed with the following error: "Column name or number of supplied values does not match table definition."
This is because the temporary table contains 5 columns:
CREATE TABLE [dbo].[Obfuscated](
[Server] [nvarchar](128) NOT NULL,
[dtMessage] [datetime] NULL,
[SPID] [varchar](50) NULL,
[vchMessage] [nvarchar](4000) NULL,
[ID] [int] NULL
which matches the DBCC LOGINFO output up to 2008 R2, but from 2012 onwards DBCC LOGINFO contains an extra column, RecoveryUnitId, which means that this script fails for any 2012 or 2014 instances. (Changing the structure of the temporary table doesn't work because then all the 2008 R2 & older instances fail instead.) The next step uses a cursor to insert the data for each database into the temp table.
What's the best way to resolve this? I think I'd want to update this script so that it checks the version of SQL Server and creates a 5-column temp table for any version up to 2008 R2, and a 6-column temp table for any version 2012 and above. Does that seem reasonable, and if so, could you give me some pointers for how to go about doing it please? My T-SQL is definitely an area I need to improve on.
Thanks very much.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I think I'd set it up so there's a completely different task for the newer versions, and to test for server version in the control flow. That keeps the SQL code in the tasks as clean as possible.

John

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Quick suggestion, add default values to the new columns

😎

,NEW_COL INT NOT NULL DEFAULT(0)

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 3

Thanks for the replies. The default is a good idea, but it's still failing in 2008 R2 with Msg 213, Level 16, State 7, Line 1

Column name or number of supplied values does not match table definition.

because of this:

DECLARE @DBName nvarchar(60)
DECLARE @SQLString nvarchar (2000)
DECLARE c_db CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE status&512 = 0 and status&32 = 0
OPEN c_db
FETCH NEXT FROM c_db INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLString = 'USE [' + @DBName + '] DBCC LOGINFO WITH NO_INFOMSGS'
[highlight="#ffff11"] INSERT [tempdb].[dbo].[Obfuscated]
EXECUTE(@SQLString)[/highlight]
--
FETCH NEXT FROM c_db INTO @DBName
END
DEALLOCATE c_db

Can I get this information some other way than through a DBCC? I've Googled but there's surprisingly little out there.

Thanks

John, thanks for your reply too.

I think I'd set it up so there's a completely different task for the newer versions, and to test for server version in the control flow. That keeps the SQL code in the tasks as clean as possible.

Could you expand on that a bit please? Are you saying to do the check in the control flow, and execute one task if the version is 2008 R2 or lower, and another if it's 2012 or higher?

Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Beatrix Kiddo (4/18/2016)
Are you saying to do the check in the control flow, and execute one task if the version is 2008 R2 or lower, and another if it's 2012 or higher?

Yes. Then you don't risk ugly IF statement constructs, dynamic SQL and/or code failing at parse time when objects or columns don't exist.

John

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 5

Thanks, both. I've got it working now.

I also found a blog post that helped me find where to get some of the data from:

http://www.sqlphilosopher.com/wp/2012/08/a-few-changes-in-sql-server-2012-dbcc-loginfo-and-dbcc-checkdb/