Drop Database bug?

• AzureFan

  SSC Rookie

  Points: 39

  More actions

  March 2, 2017 at 10:55 am

  #335427

  Has anyone seen this?  Is it a know bug or intended behavior?

  In a 2016 Developer edition build 13.0.4001.  

  There is an existing database.  

  Running the following sql:

  use master;
  drop database mydatabase;

  I can verify the database files are not longer in the directory.  Also the database no longer appears in sysdatabase.  

  This followup statement is run:

  create database mydatabase;

  Here's the weird part.  The database is created, but all of the tables reappear as if the schema was restored.

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  March 2, 2017 at 11:21 am

  #1931290

  AzureFan - Thursday, March 2, 2017 10:55 AM

  Has anyone seen this?  Is it a know bug or intended behavior?

  In a 2016 Developer edition build 13.0.4001.  

  There is an existing database.  

  Running the following sql:

  use master;
  drop database mydatabase;

  I can verify the database files are not longer in the directory.  Also the database no longer appears in sysdatabase.  

  This followup statement is run:

  create database mydatabase;

  Here's the weird part.  The database is created, but all of the tables reappear as if the schema was restored.

  Isn't that just cache from SSMS? How are you checking the objects?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  March 2, 2017 at 11:23 am

  #1931292

  Luis Cazares - Thursday, March 2, 2017 11:21 AM

  AzureFan - Thursday, March 2, 2017 10:55 AM

  Has anyone seen this?  Is it a know bug or intended behavior?

  In a 2016 Developer edition build 13.0.4001.  

  There is an existing database.  

  Running the following sql:

  use master;
  drop database mydatabase;

  I can verify the database files are not longer in the directory.  Also the database no longer appears in sysdatabase.  

  This followup statement is run:

  create database mydatabase;

  Here's the weird part.  The database is created, but all of the tables reappear as if the schema was restored.

  Isn't that just cache from SSMS? How are you checking the objects?

  That is the SSMS caching.

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events

• AzureFan

  SSC Rookie

  Points: 39

  More actions

  March 2, 2017 at 11:34 am

  #1931300

  I still see the tables when I connect to the server from a different server that has never connected to the first server.  

  Does the cache get stored on the server itself for some reason, rather than local in SSMS?

  Is there a way to flush the cache to make sure I'm not going crazy?

  Also happens in SSMS versions from 2012 to the newest release.  I can also select from the tables, but there is no data in them.

• AzureFan

  SSC Rookie

  Points: 39

  More actions

  March 2, 2017 at 11:41 am

  #1931308

  Just had a co-worker that has never connected to that server, connect and verify.  The co-worker also sees the tables.

• Thom A

  SSC Guru

  Points: 99143

  More actions

  March 2, 2017 at 11:52 am

  #1931312

  Testing the following on both SQL Server 2016 Dev (Windows 10), and SQL vNext (Ubuntu 16.04.2) returns what I would expect:
  Msg 208, Level 16, State 1, Line 30
  Invalid object name 'Test'.
  CREATE DATABASE ToBeDeleted;
GO

  USE ToBeDeleted;
GO

  CREATE TABLE Test (ID INT);
GO

  INSERT INTO Test
VALUES (1),(2),(3);
GO

  SELECT *
FROM Test;
GO

  USE master;
GO

  DROP DATABASE ToBeDeleted;
GO

  CREATE DATABASE ToBeDeleted;
GO

  USE ToBeDeleted;
GO

  SELECT *
FROM Test;
GO

  USE master;
GO

  DROP DATABASE ToBeDeleted;
GO
  The table Test no longer existed when I tried to get data from it, even though I recreated the database with the exact same name. Both Systems have very different file systems, so that didn't even cause a difference (not that I expected it anyway).

  Do you mean you can see the database and tables in the object explorer? You can clear that by clicking the little refresh icon near the top of it (the circular arrow). If it's your intellisense, then Ctrl+Shift+R.

  If you really can still see all the data still, I'd say you've not dropped that database like you thought you did.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• AzureFan

  SSC Rookie

  Points: 39

  More actions

  March 2, 2017 at 11:52 am

  #1931313

  Nevermind, apparently a developer deployed all the tables to model.

• SQLRNNR

  SSC Guru

  Points: 281344

  More actions

  March 2, 2017 at 1:44 pm

  #1931349

  AzureFan - Thursday, March 2, 2017 11:52 AM

  Nevermind, apparently a developer deployed all the tables to model.

  Aha - that would do it. Probably did not document it was done that way either? :Whistling::Whistling:

  Jason...AKA CirqueDeSQLeil
  _______________________________________________
  I have given a name to my pain...MCM SQL Server, MVP
  SQL RNNR
  Posting Performance Based Questions - Gail Shaw[/url]
  Learn Extended Events