Analysis of a 400GB database after SQL server restarts takes forever (or maybe hang)

Hello there,

We have a database used by a thrid party application that is 400GB in size. After the server gave some warnings, we restarted the machine and SQL server is doing an analysis on the database so it can be recovered. The recovery analysis of the 400GB database has been run for 3 days and only 7% complete. I doubt it'll ever finish. Because this recovery analysis is a background process which I can not kill, I can not drop the database either because it places an exclusive lock on the database when SQL server is doing analysis. We're using SQL server 2000 enterprise edition, sp3. Now I think that we probably should not have such a big database in the first place. Right now, I just want to stop the analysis if I can so I can either truncate the tables in that 400GB or drop the database completely. Not sure if start sql server in cmd with single user mode can do or not. Any suggestions are appreciated.

thanks,

-Jessie

if the database is not important and you do have a copy of it backed up, you can terminate sqlserver service; start a new session in console mode and drop the database.

e.g.

sqlservr.exe -c -f -m

|||

Thanks OJ for your reply! This database is important but we can't afford every time the server restarts, it takes 1 day to only recover 3% of this db. That "-f" stops the recovery of the user databases. But when I tried to run "use 400GBdb", it gave me this error:Server: Msg 913, Level 16, State 4, Line 1
Could not find database ID 12. Database may not be activated yet or may be in transition.

I'll first back the data/log files to tape before I drop the db. But are you sure I can drop the db even the system can not find the dbid?

-Jessie

|||

Jessie,

You can start sqlserver without doing any recovery of any user databases.

e.g.

sqlservr.exe -s"your_instance" -f -m

then connect to the instance and drop the database

drop database "your_db"

|||

Jessie,

You can start sqlserver without doing any recovery of any user databases.

e.g.

sqlservr.exe -s"your_instance" -f -m

then connect to the instance and drop the database

drop database "your_db"

|||

Thanks OJ! Yes, using -f can stop recovery of user database. But because user db is not recovered, they are not accessible. You'll get:dbid not found error. I rebooted the SQL serve twice and both recovery of the 400GB hung when the recovery is 99% complete. From SQL server error log: The Scheduler 3 appears to be hung. SPID 17, ECID 0, UMS Context 0x03716938. I guess 400 GB is beyong SQL server 2000 limit.

What I did is that I stopped the SQL server, delete the log file and restarted the SQL server. Then the db is marked as suspect and I deleted the db.

Alternatives to SQL Server Merge Replication

Hello,

I would like to know what alternatives are avaliable to SQL Server Merge Replication. I am also looking for Third Party Tools. Can anyone name a few for me.

Microsoft supplied alternatives: RDA (Remote data access), ADO.NET Synchronization Services (currentluy in beta), ASP.NET Web Services (rolling your own).|||Is ADO.NET Synchronization Services as powerful as Merge Replication?|||

Just a hunch on why you might be looking for alternatives:

I know many developers are implementing alternatives to RDA and replications in effort to reduce number of CALs required for SQL Server. They believe what if N devices are connected to this “alternative” and “alternative” connects to SQL Server you only need one CAL as there’s only one physical connection to SQL Server. That is, however, not the case and SQL Server EULA has a clause about “through proxy” connection to prevent that scenario so you would need N CALs regardless. If that’s the reason you’re looking for alternatives you might just use RDA/Replication and save time and money on development.

Another reason for alternatives is security –developers don’t want IIS installed as they believe it’s unsecure. If that’s the case I would like to point out IIS has been around for years and runs on millions of hosts around the globe. Thinking self made solution would me more secure is quite perky.

Alternatives to Merge Replication

Hi,

Can anyone help me with third party tools that perform the same way as SQL Servers Merge replication.

What is wrong with Merge Replication?|||

Following is my problem

I am using SQL Server 2000 Merge Replication. Sometimes when the data is replicated there are conflicts which when examined show it is due to voliation of foreign key constraint. But the data (keys) in already present in the master tables. This is so because i think the data in the details table is relpicated first instead of the master table. The conflicts are resolved properly when done using the conflict viewer.

|||

In SQL 2000 you can choose not to replicate foreign key constraints or replicate them and mark them as NOT FOR REPLICATION. See BOL topic http://msdn2.microsoft.com/en-us/library/ms152529.aspx.

In SQL 2005, we have better retry logic as well as article ordering which greatly reduces the need to resolve conflicts if the above doesn't work for you.