Problem with SYSDATABASES and DATABASEPROPERTYEX

  • I was putting together a simple monitoring script this morning when I ran into something unexpected. I was querying the sysdatabases table to get the current status of the production db's. When I used the DATABASEPROPERTYEX() function to translate the "Status" column for me, I had a NULL status for a handful of production db's. This seemed very odd in that the actual numeric status was the same as some of the other prod db's that translated successfully. Has anybody else ran into this issue before? And if so, how did you fix it?

    Thanks

    JT

    I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

  • Just noticed that this is double posted here.

    Is this a SQL Server 2000 or SQL Server 2005 question?

  • My apologies for the dual posting. I had been reading a post in the 2005 area and thought I had clicked back into the 2000 area. When I made it back into the 2000 area to check the status of my post, I didn't see it. So that is why I reposted it. I tried to remove the post in the 2005 area, but couldn't. Anyway...here is the code that I am using.

    SELECT [name],

    dbid,

    sid,

    mode,

    DATABASEPROPERTYEX([NAME], 'Status') as translated_status,

    status,

    status2,

    crdate,

    reserved,

    category,

    cmptlevel,

    [filename],

    version

    FROM [ProdDBServer].master.dbo.sysdatabases

    I receive ONLINE for some of my prod db's and NULL for some of the others.

    I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

  • I ran your code on my development server, and it worked fine. I have to ask the question, are you sure all the databases were started? That is about the only thing I can come up with as other properties would return null if the database isn't started.

    Have you tried the code on any other servers?

  • Looking at BOL, NULL is returned on error or if a caller does not have permission to view the object.

    That makes permissions another area to consider, depending on what user you are running the query under.

  • Now that I think about it, I wonder if it has something to do with the way we create our prod db's. We use a central db that has sp's and repl jobs to create the db and its schema. The reason I am thinking this is because upon closer inspection, the db's that are created by default(msdb, model, master, tempdb, distribution, etc.) all have correct status values. The db's that we create through our processes are the one's with the funky NULL status. And if I create a db through EM, then I get a correct status. I might have to sit down and review our process for creating our db's;)

    I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

  • Lynn Pettis (3/2/2009)


    Looking at BOL, NULL is returned on error or if a caller does not have permission to view the object.

    That makes permissions another area to consider, depending on what user you are running the query under.

    I am running it as the a local user with sa access.

    I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

  • Lynn:

    I have an experiment for you. I want you to create two databases on your dev server. Create the first one through your local EM to your dev environment. Then for the second I want you to rdp to your dev server and create it through the local EM on your dev server.

    My results of this test are as follows:

    DB creation from my laptop to my dev environment yielded a status of NULL

    DB created directly on the server yielded a status of ONLINE

    For connections use the same username and password to get access. So the only difference being that I created one from my laptop on the deb box and the other directly on the server.

    Am I making any sense with this? Or am I just smoking something?! 😀

    I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

  • You made sense, and I have no problems. Didn't matter if the database was created from my desktop system or when logged in (rdp) to the server under the same username.

    I can't recreate your problem at this time.

  • Figure it out. Linked server authentication. It should have been obvious to me...but it's those little things that tend to bite you in the rear. Thanks for your time.

    I compare being a DBA to that of Smokey The Bear. Helping to prevent wildfires in your forest of servers and applications.

  • Not a problem. Having another set of eyes makes you look at things you might not have otherwise.

  • Hey JT, are you sure you are getting the correct results. In my testing it appears DATABASEPROPERTYEX([NAME], 'Status') only returns data from local server not the remote linked server.

    Bob
    -----------------------------------------------------------------------------
    How to post to get the best help[/url]

  • Sorry, am I thick? Linked Server Authentication? What was the answer - it's not at all clear

  • Aha! Now I've worked out what's what here. DATABASEPROPERTYEX was running locally and so only gives results for databases on the remote server that happen to have the same names - typically the system dbs. The answer is to use OPENQUERY - that way it runs on the target server.

Viewing 14 posts - 1 through 13 (of 13 total)

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