January 3, 2012 at 4:02 pm
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.
January 3, 2012 at 9:18 pm
When running queries against databases with large name, try referencing the databases in double quotes. Also, set quoted_identifier ON.
Thanks.
_________________
_____________
Vikas S. Rajput
January 4, 2012 at 2:50 am
Try with in square bracket i.e. [dbname].
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
January 4, 2012 at 5:20 am
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
January 4, 2012 at 8:41 am
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
January 4, 2012 at 8:52 am
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" 😉
January 4, 2012 at 9:37 am
@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
January 4, 2012 at 9:40 am
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" 😉
January 13, 2012 at 12:51 pm
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply