CheckDB behavior

Question

CheckDB behavior

calvo

SSChampion

Points: 12930
More actions
January 3, 2012 at 4:02 pm

#391239

While troubleshooting some quirky script behavior of my own making, I ran across something weird (to me) with CheckDB. Perhaps someone would be able to explain the behavior.
I was checking through the error logs and noticed a couple errors during my maintenance plan window. The errors occurred at the same time CheckDB is run on all the databases (weekly). Of 14 databases only 2 returned errors.
LogSQL Server Agent (Current - 1/3/2012 5:48:00 PM)
Message
[LOG] The data portion of event 8957 from MSSQL$<instance> is invalid
So I did some troubleshooting and found that it is happening while CheckDB is run on 2 sharePoint databases, just so happens they are the long named GUID databases.
When restoring the long-name-GUID database to a test server for further testing, a similar error appears upon restore.
LogSQL Server Agent (Current - 1/3/2012 5:48:00 PM)
Message
[LOG] The data portion of event 4356 from MSSQL$<instance> is invalid
If I rename the database to a shorter name, no error upon CheckDB run. I found that a database name 75 characters in length or longer will generate these errors upon CheckDB.
Has anyone experienced a similar problem?
For the record, CheckDB runs successfully and gives no errors to the application.
______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Viewing 9 posts - 1 through 9 (of 9 total)

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

Vikas S. Rajput SSC-Addicted Points: 475 More actions · Answer 1

When running queries against databases with large name, try referencing the databases in double quotes. Also, set quoted_identifier ON.

Thanks.

_________________

_____________
Vikas S. Rajput

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 2

Try with in square bracket i.e. [dbname].

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 3

Interesting one that, i've just done a test on SQL 2005 and 2012 for a DB with a name of 120chars in length and ran checkdb with nothing other than notification that checkdb had ran, written to the logs....

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

calvo SSChampion Points: 12930 More actions · Answer 4

This is on 2008R2 SP1

I'm not using double quotes or brackets to run CheckDB.

--75

select len('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvw')

CREATE DATABASE [abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvw] ON PRIMARY

( NAME = N'pp', FILENAME = N'c:\pp.mdf' , SIZE = 30720KB , FILEGROWTH = 20480KB )

LOG ON

( NAME = N'pp_log', FILENAME = N'c:\pp_log.ldf' , SIZE = 20480KB , FILEGROWTH = 65536KB )

use [abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvw]

go

dbcc checkdb

--74

select LEN('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv')

CREATE DATABASE [abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv] ON PRIMARY

( NAME = N'pp2', FILENAME = N'c:\pp2.mdf' , SIZE = 30720KB , FILEGROWTH = 20480KB )

LOG ON

( NAME = N'pp2_log', FILENAME = N'c:\pp_log2.ldf' , SIZE = 20480KB , FILEGROWTH = 65536KB )

use [abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuv]

go

dbcc checkdb

--73

select LEN('abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstu')

CREATE DATABASE [abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstu] ON PRIMARY

( NAME = N'pp21', FILENAME = N'c:\pp21.mdf' , SIZE = 30720KB , FILEGROWTH = 20480KB )

LOG ON

( NAME = N'pp21_log', FILENAME = N'c:\pp_log21.ldf' , SIZE = 20480KB , FILEGROWTH = 65536KB )

use [abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstu]

go

dbcc checkdb

and check the SQL Agent error log after each CheckDB

xp_readerrorlog 0,2,'[log] the data portion of event',null

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

Perry Whittle SSC Guru Points: 234040 More actions · Answer 5

ChrisTaylor (1/4/2012)
Interesting one that, i've just done a test on SQL 2005 and 2012 for a DB with a name of 120chars in length and ran checkdb with nothing other than notification that checkdb had ran, written to the logs....

a database name in later versions of SQL Server is of type SYSNAME which is effectively NVARCHAR(128)

Any database names with odd chars, etc will need square brackets as mentioned above

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

ChrisTaylor SSCarpal Tunnel Points: 4527 More actions · Answer 6

@perry - all i can say is that i've tested both my script (120chars DBName) and also with Calvo's script on both 2005 and sql 2012 RC0 (with and without square brackets) and i don't get that error for any scenario.

i've not got a 2008 SP1 environment available to match Calvo's setup i'm afraid

_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie

Perry Whittle SSC Guru Points: 234040 More actions · Answer 7

I've had issues in previous times backing up SharePoint databases. Qualifying the database names with QUOTENAME() has always resolved the issue

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"

calvo SSChampion Points: 12930 More actions · Answer 8

so here's my test

create database [78abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx]

ON PRIMARY

(NAME = N'78', FILENAME = N'<location>\78.mdf',SIZE = 30MB , FILEGROWTH = 1024KB )

LOG ON (NAME = N'78_log', FILENAME = N'<location>\78_log.ldf' , SIZE = 20MB , FILEGROWTH = 1024KB )

which creates just fine

Now, I've tried

USE [78abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx]

GO

DBCC CHECKDB

USE master

GO

DBCC CheckDB('78abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx')

USE master

GO

DBCC CheckDB([78abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx])

DECLARE @78 VARCHAR(78) = '78abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwx'

DBCC CheckDB (@78) WITH NO_INFOMSGS, ALL_ERRORMSGS

Which have all completed successfully but return the same error in the SQL Agent Log

I thought maybe if I created a maintenance plan to do the integrity check it might be different. It wasn't, same error.

SQL Server 2008R2 SP1 (Windows Server 2008R2[test server]) - receive error

SQL Server 2012 RC0 (Windows Server 2008R2[test server]) - no error

SQL Server 2008R2 (Windows XP[local]) - no error

Attached is the errors from the SQL Server error log and the SQL Agent Log.

I don't get it. I've tried qualifying with QUOTENAME() as suggested, still no change.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

CheckDB behavior

Cookies on SQLServerCentral