Un-attaching SPL 2008 BD not willing to Attach I have a 5 year old SQL Server 2008 database.  There are actually 3 databases

NL_Dev
NL_Test
NL_Prod


Each has a log file. We have been using the development database, but all three seam to be linked together. The .MDF files are all about 3.2GB, but the dev log file is 450GB.

I was trying to shrink the files, but only had 10GB's of space left on the array.

I had a backup of NL_Dev and didn't think it would make any difference to delete the test log, so I stopped SQL Server and deleted the test log File. It didn't make any size difference, but now I can't access the data in NL_Dev.

I tried un-attaching the DB, but now I can't attach it since the file is missing.

I am not a DBA, just a small business owner that has worked with Access and SQL Server for 20 years, and I've run out of rope to hang myself. Please Help

Gordon Mills
Newlife Electronics
I was trying to Shrink the files, but only had 10GB's of space left on the Array.


Get another LUN assigned to you. Extend the LUN. Buy a bigger disk. Add an external drive. Do something other than compromise your data because of disk space shortages. This is a problem that can be solved without ever touching your database in ways it doesn't like.


  I Stopped SQL and deleted the Test Log File


Please don't ever do this - it is a pretty good way to corrupt your data, because stopping the SQL Server service and deleting the log file can make it impossible for SQL Server to recover in-flight transactions that were running when you stopped the service.


  I tried Un-attaching the DB, but now I can't Attach it since the file is missing.


You can try CREATE DATABASE ... FOR ATTACH_REBUILD_LOG, which will re-create the log file (see the documentation), but this still may not help because it still requires a clean shutdown of SQL Server (we don't know that that happened), and who knows what has happened to your .mdf file in the meantime. 

My suggestions:


Forget trying to re-attach the .mdf file and just restore your backup.
Read this post in full:


Why Does the Transaction Log Keep Growing or Run Out of Space?

If that is too much for you, and you care about your data at all, you need to consider hiring someone who can handle it. You don't necessarily need someone full-time; a remote DBA service will probably be sufficient.