Post reply

Alter Database -- Offline a database

  • January 30, 2009 at 5:12 pm

    #206138

    So, I have been delegated the lead DBA for our SQL Server environment and have been working A LOT lately to really understand SQL Server; the engine, terminology, transact-sql, backups and learning about the system tables and catalog views.

    I was playing around with alter database and off lining a database, then querying system tables (catalog views now) to see the result.

    Curious thing though: After I offlined the DB:

    alter database dbname set offline

    I go back and query:

    select name, physical_name AS CurrentLocation, state_desc

    from sys.master_files

    where database_id = dbid

    Shows it online?

    Am I missing something here?

    EDIT: I did not stop/start the DB at anytime.

    Thanks,

    Jason

  • January 31, 2009 at 2:20 am

    #935966

    sys.master_files describes datafiles, not databases.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • January 31, 2009 at 2:58 pm

    #936025

    PaulB (1/31/2009)

    sys.master_files describes datafiles, not databases.

    Ahh...duh. :Whistling:

    Still trying to learn all the system catalog views. 🙂

    What would be a better view to query off to see status of all the DB's?

    Cheers,

    Jason

  • January 31, 2009 at 3:42 pm

    #936036

    Jason try

    select name, state, state_desc from sys.databases where state = 6

    will show offline databases. Or check for

    where state_desc = 'OFFLINE'

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

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

  • January 31, 2009 at 5:42 pm

    #936057

    Perry Whittle (1/31/2009)

    Jason try

    select name, state, state_desc from sys.databases where state = 6

    will show offline databases. Or check for

    where state_desc = 'OFFLINE'

    thanks. I will give that a shot.

  • February 1, 2009 at 4:56 am

    #936133

    Thanks for the query!



    My blog

  • February 1, 2009 at 8:50 am

    #936162

    Ignacio A. Salom Rangel (2/1/2009)

    Thanks for the query!

    :w00t: What?!... not following; could you please elaborate 😀

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  • February 1, 2009 at 1:24 pm

    #936198

    Perry Whittle (1/31/2009)

    Jason try

    select name, state, state_desc from sys.databases where state = 6

    will show offline databases. Or check for

    where state_desc = 'OFFLINE'

    I meant this query. I found it handy for checking the databases state using sqlcmd.



    My blog

  • June 29, 2009 at 3:39 am

    #1016676

    Hi,

    i want script for bringing all userdatabases offline at a time

  • June 30, 2009 at 4:58 am

    #1017462

    what about databases that have open connections what do you want to do about these

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

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

  • June 30, 2009 at 7:40 am

    #1017571

    Perry Whittle (6/30/2009)

    what about databases that have open connections what do you want to do about these

    use master

    go

    /* set DB offline */

    alter database myDB set single_user

    with ROLLBACK IMMEDIATE; -- all connections are terminated

    go

    alter database myDB set offline;

    go

    /* set DB online */

    alter database myDB set online; -- database in single-user mode

    go

    alter database myDB set multi_user; -- allow multi-user access

    go

    Have a look at DMV sys.databases (columns user_access, state). I shouldn't be too difficult to write a little script to set all user dbs offline.

    Sven

  • June 30, 2009 at 11:56 am

    #1017828

    thanks verymuch for your reply .....Sven

  • June 30, 2009 at 3:02 pm

    #1017981

    declare @sql nvarchar(max)

    set @sql = ''

    select @sql = @sql + 'alter database [' + name + '] set offline;

    '

    from sys.databases where database_id > 4

    exec (@sql)

    --all system databases occupy id from 1 to 4,

    --anything else is a user database

    yes you can rollback immediate but as a user wouldnt you be pretty peeved if you were just disconnected. What do you want to do about connected users, how will you handle disconnecting them (at a personal level too). Just something to think about

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

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

  • June 30, 2009 at 3:11 pm

    #1017991

    Thanks alot for the script

    we have some procedure to avoid users already connected

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

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