sp_helpdb error | Cannot insert the value NULL into column

Yesterday was one of the most hectic days at work after a long time. The server having high CPU utilization mentioned here, was at its best yesterday. CPU at 100% consistently. We are still fire fighting that issue. I will write about it once it is fixed.

I was on this call till 5 AM today morning and after 4 hours of sleep back in office. Too tired to come to office but I had to because of the special Friday situation we have in our team. The moment I entered Office one of the DBAs greeted me with “Hey Pradeep, before you open you laptop come on here. I have something to be fixed“. Oh no, not know.

Reluctantly I walked up to his desk to see the error message on the screen. He was running sp_helpdb on an instance and it was failing with the below error.

This was on an instance running SQL Server 2000. I had worked on this year around 2 years back. Here is how I had fixed it the first time I had seen this error.

While trying to generate the output sp_helpdb is unable to insert NULL values into a table. Where is it inserting? A peek into the content of sp_helpdb stored procedure would reveal it.

sp_helptext 'sp_helpdb'

Executing the above query would give the actual script that executed when sp_helpdb is called. The first occurrence of insert statement is where it was failing. That portion of sp_helpdb is as below.

/*
**  Initialize #spdbdesc from sysdatabases
*/
insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
		select name, suser_sname(sid), convert(nvarchar(11), crdate),
			dbid, cmptlevel from master.dbo.sysdatabases
			where (@dbname is null or name = @dbname)

What I did was took out only the select statement and executed it manually.

select name,
suser_sname(sid),
convert(nvarchar(11),
crdate),
dbid,
cmptlevel
from
master.dbo.sysdatabases

The value of suser_sname(sid) column in the output of this script had several NULL entries in it. It meant that the Owner for those databases were NULL. I had wrote about a similar issue on SQL Server 2005 here. This happens when the Owner Login is dropped or that user is deleted in the Active Directory. The resolution is simple. Change the Owner of those databases using the sp_changedbowner stored procedure.

A very good start to my sleepy day.

One thought on “sp_helpdb error | Cannot insert the value NULL into column

Comments are closed.