0

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

0

1 Answer 1

0

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:

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

  3. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.