Sql-server – How to resolve MDF and LDF files remaining in use

sql serversql-server-2000windows

I am attempting to copy off approximately 220 gigs of database MDF and LDF files from a MS SQL server 2000 install in order to transfer ownership to a third party.

The error I get when I try to stop the database using the enterprise manager is

Error 1051: A Stop Control Has Been Sent To A Service Which Other Running Services Are Dependent On

Going to the service and looking at the dependencies tab does not show any other services running in relation to it. If I stop the actual Windows service, and try to copy it tells me that the databases are still in use. I have changed the service from automatic to manual and rebooted, and it still insists the files are in use.

I don't particularly care about copying the log files, but how can I resolve the error to do a clean shutdown and be able to copy off at least the MDF files?

Best Answer

The Restart-Service cmdlet sends a stop message and then a start message to the Windows Service Controller for a specified service. If you specify -force parameter, it means override restrictions that prevent the command from succeeding, e.g. stop and restart a service that has dependent services.

However, Windows Server manager launched via services.msc you mentioned is not running with force parameter when you click restart button there.

Thus if you want to do that, either stop the dependent services first, then retry or use the PowerShell command line to restart.

Related Solutions

Sql-server – How to attach an .mdf file that was not properly detached

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies.

You might try the following, in case the problem is isolated to the log:

CREATE DATABASE [GRMStemp]
  ON (FILENAME = 'c:\wherever\MDF_File.mdf')
  FOR ATTACH_REBUILD_LOG;

If that fails, there is a forum post on MSDN by Paul Randal which describes a process that might work. Basically:

on your 2008 instance, create a database with a data file of the exact same size (I wouldn't bother trying to put a 2000 instance anywhere)
shutdown SQL Server
rename the new database's mdf file, and paste the old one in its place
bring up the server and let the database attempt to be recovered and then go into suspect mode
put the database into emergency mode with ALTER DATABASE GRMStemp SET EMERGENCY;
run DBCC CHECKDB (GRMStemp, REPAIR_ALLOW_DATA_LOSS); which will rebuild the log and run full repair

BTW If the client has no idea what was in the database, and wasn't responsible enough to maintain proper backups, how important could the data be?

Sql-server – What are the risks of copying MDF/LDF files without detaching the DB first

It should be OK. after all, that's what the entire goal of recovery is, to survive any abrupt interrupt. Instance shutdown is actually quite a graceful shutdown, will properly close all DBs (should be no pending xact to rollback on recovery).

But the gist of it is that you're using a sledgehammer to break an egg shell. Copying using BACKUP FULL WITH COPY ONLY/RESTORE is faster and safer. Even file copy can be achieved more efficient, just set the DB offline and the files will be accessible for copy, then bring the DBs back online.

About overwriting the destination, there are some potential problems. It obviously does work, after all your team was doing to all along... But you can and up with discrepancies what the instance knows about the database from data in the master catalog and what the database itself claims in its boot page (things like service_broker_id or compatibility level for instance). These can be resolved, but you will have problems just understanding what is wrong. Basically, why the DB behaves one way in production and another way on support. Again, there is simply no reason to go into this gray area. Use BACKUP/RESTORE, is safer and faster.

Best Answer

Related Solutions

Sql-server – How to attach an .mdf file that was not properly detached

Sql-server – What are the risks of copying MDF/LDF files without detaching the DB first

Related Question