Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2012
    Posts
    14

    Red face Unanswered: SQL Server database keeps disappearing!

    I'm using Express edition 2008 with sql management studio
    My problem is when I click on + sign before Databases in management studio ,sometimes I find my database, which I created earlier GONE and after some times it keeps coming back ,why is this happening and what is the solution to it?.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    99.5% of the time when this is reported to me, the problem is "user error" of some kind. Either they have too many SQL Servers open in an SSMS instance and get confused, they have multiple copies of SSMS open (sometimes on different Windows machines and sometimes on a single machine), etc.

    Are you ever losing data or schema in the database? If that is the case, then it is possible that the database is actually being dropped and recreated, but the chance of that happening is tiny.

    You have piqued my curiousity, but I'm still pretty sure that this is just a gremlin.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Look at the auto_close option and see if it's set to true. Might not be the problem, but worth a look.

  4. #4
    Join Date
    Nov 2012
    Posts
    14
    Quote Originally Posted by Pat Phelan View Post
    99.5% of the time when this is reported to me, the problem is "user error" of some kind. Either they have too many SQL Servers open in an SSMS instance and get confused, they have multiple copies of SSMS open (sometimes on different Windows machines and sometimes on a single machine), etc.

    Are you ever losing data or schema in the database? If that is the case, then it is possible that the database is actually being dropped and recreated, but the chance of that happening is tiny.

    You have piqued my curiousity, but I'm still pretty sure that this is just a gremlin.

    -PatP
    I have only one SQL server on my windows machine,and there are only one copy of SSMS(Management studio I guess) is open .
    when the database comes back I see no data is being missing.

  5. #5
    Join Date
    Nov 2012
    Posts
    14
    Quote Originally Posted by corncrowe View Post
    Look at the auto_close option and see if it's set to true. Might not be the problem, but worth a look.
    No it is set to be false.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    shot in the dark.... is it named "model"? or do you have a restore job scheduled in the agent that is wiping it out? Try executing in each of the databases on the server...

    Code:
    SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition like '%DROP%DATABASE%'
    OR definition like '%RESTORE%'
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Aug 2008
    Posts
    147
    are there any messages in the logs?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  8. #8
    Join Date
    Nov 2012
    Posts
    14
    Quote Originally Posted by JackVamvas View Post
    are there any messages in the logs?
    where to look for log files ,and if I locate it how do I know what is wrong and where?I really don't know much about sql.

  9. #9
    Join Date
    Nov 2012
    Posts
    14
    Quote Originally Posted by Thrasymachus View Post
    shot in the dark.... is it named "model"? or do you have a restore job scheduled in the agent that is wiping it out? Try executing in each of the databases on the server...

    Code:
    SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition like '%DROP%DATABASE%'
    OR definition like '%RESTORE%'
    no it is called testme

Tags for this Thread

Posting Permissions

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