Results 1 to 11 of 11

Thread: SP Question

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: SP Question

    Hi All,

    I have a stored procedure that selects the dbsize from sysfiles table for each database and then inserts it into the user table. One of the databases was taken offline and my sp fails. My question is how to check if the db is offline, so my sp won't fail?

    Here is the select portion:


    OPEN dbnames_cursor

    FETCH NEXT FROM dbnames_cursor INTO @dbname, @dbid
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @dbsizestr = 'SELECT @dbsize = sum(convert(dec(15,2),size))
    FROM ' + @dbname + '.dbo.sysfiles
    WHERE fileid = 1'


    EXECUTE sp_executesql @dbsizestr, N'@dbsize decimal(15,2) output', @dbsize output

    Thanks.

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You'll need to add filtering to your cursor:

    Code:
    WHERE status = 0
    In other words, only databases which are online. Mor information at http://msdn2.microsoft.com/en-us/library/ms178534.aspx.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jul 2004
    Posts
    268
    I queryied sysdatabase table and only four tables have status of 0, the other ones have statuses of 16, 8, 24. But there is only one db that is offline. Also, the database that is offline has a status of 528, but when I checked the BOL the offline status is 512. I am a little bit confused now, not sure what to do.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh heh - no wonder you are confused:

    dbo.sysdatabases <> sys.databases

    and

    dbo.sysdatabases.status <> sys.databases.state

    What version are you using?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jul 2004
    Posts
    268
    I am using dbo.sysdatabases.status.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Code:
    Select databasepropertyex(name, 'Status')
    from sys.databases
    or
    Code:
    Select databasepropertyex(name, 'Status')
    from sysdatabases

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by inka
    I am using dbo.sysdatabases.status.
    Yes but what version? You should not really be querying that in 2005.

    Now - status in dbo.sysdatabases is a bitmask. Do you know how to read a bitmask in SQL Server?

    EDIT - or use MCrowley's cool snippet. New to me. I didn't know the old one was offski.
    NOTE from BoL:
    If the database is not started, properties that the Microsoft SQL Server 2005 Database Engine retrieves by accessing the database directly instead of retrieving the value from metadata will return NULL. That is, if the database has AUTO_CLOSE set to ON, or the database is otherwise offline.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2007
    Posts
    49
    If you are using SQL-Server 2005 then its very easy with view 'databases' -

    Select * from sys.databases
    where state_Desc = 'OFFLINE'

    If you are using Sql-Server 2000 then Infomration_Schema is useful,
    Following query can be used to list offline databases -

    Select *
    from
    master.dbo.sysdatabases
    where lower(name) not in
    (
    Select lower(Catalog_name)
    from
    Information_schema.schemata
    )
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  9. #9
    Join Date
    Jul 2004
    Posts
    268
    Mihirclarion,

    I ran the second query and it shows that I have two dbs that are offline, but only is offline. The other one was offline about a week ago, but currently it is online. Any idea?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    selectname
    from master.dbo.sysdatabases
    where status & 512 > 0
    
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jul 2004
    Posts
    268
    Great. Thanks that works for me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •