July 30, 2010 at 6:55 pm
I just wanted to throw this out there...
I think I have a third party vendor that is trying to give me the run around about a process that their software uses. I'm trying to do a little research on my end before I lay the smack down.
Their software creates a temp DB for each user that logs in to the software and when they log out it drops the temp database. After much back and forth with the vendor on this and a SQL Trace to capture what was going on the vendor admitted that their process that creates the temp DB for the user actually tries to attach the db first. This generates a Sev 16 alert on the server because the database files do not exist since dropping the DB deletes the data and log files from the server. They claim that the process to attach the database first is a fix because SQL Server does not always delete the database files from the server. They also state that this is a known issue with Microsoft with SQL 2000, 2005 and 2008.
After some research on my side I can't find anything that says dropping a DB fro SQL Server leaves data files behind.
Has anyone run into this issue? I personally have not but I wanted to check with my fellow DBA's to see if anyone has and I'm completely crazy.
If someone has had this issue can you get me a KB number or something that I can read about it....
Thanks,
Kevin
July 30, 2010 at 8:40 pm
I have never run into this issue and have not been able to find anything on it.
I would have them cite the source.
Also, why do they have so many permissions that they are able to create a database? They should find an alternative method of performing that routine.
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
July 31, 2010 at 1:14 am
Dropping a database does not leave behind the data and log files.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
July 31, 2010 at 1:56 am
I've seen that with offline databases being dropped, the files weren't removed. (I should test with which version this was)
I've also seen that when dropping a big database, but that occurred only once on a 2008R2 ctp.
I haven't encountered it after its RTM, but I have to admit I didn't put that flaw in my testing scenarios or scripts.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 31, 2010 at 3:34 am
I have seen files left behind if the DB was not online at the time it was dropped. It's something I have to remember to clean up when I'm playing with suspect/corrupt databases. Could be that they're taking the DB offline before dropping it. Rather than try to attach first, they should make sure that either the DB is online when dropped or that they cleanup the files when they drop the DB.
I wouldn't call it a 'known issue', more like normal behaviour.
I do have to ask... Why the hell are they creating and dropping entire databases each time a user logs in?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2010 at 1:06 pm
kmonroe (7/30/2010)
I just wanted to throw this out there...I think I have a third party vendor that is trying to give me the run around about a process that their software uses. I'm trying to do a little research on my end before I lay the smack down.
Their software creates a temp DB for each user that logs in to the software and when they log out it drops the temp database. After much back and forth with the vendor on this and a SQL Trace to capture what was going on the vendor admitted that their process that creates the temp DB for the user actually tries to attach the db first. This generates a Sev 16 alert on the server because the database files do not exist since dropping the DB deletes the data and log files from the server. They claim that the process to attach the database first is a fix because SQL Server does not always delete the database files from the server. They also state that this is a known issue with Microsoft with SQL 2000, 2005 and 2008.
After some research on my side I can't find anything that says dropping a DB fro SQL Server leaves data files behind.
Has anyone run into this issue? I personally have not but I wanted to check with my fellow DBA's to see if anyone has and I'm completely crazy.
If someone has had this issue can you get me a KB number or something that I can read about it....
Thanks,
Kevin
This is not an issue, it is the documented behaviour for SQL Server.
http://technet.microsoft.com/en-us/library/ms178613.aspx?ppud=4
SQL Server 2008 Books Online (August 2008)
DROP DATABASE (Transact-SQL)
"Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted. These files can be deleted manually by using Windows Explorer. To remove a database from the current server without deleting the files from the file system, use sp_detach_db"
I have serious doubts about any vendor software that dynamically creates and drops databases for each user. That is the real issue here.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply