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.

An internal consistency error occurred..

Hi all,
i have copied out the database backup from development center to my local machine,it is having approx.8GB in size and when
i m trying to restote this database backup in SQL Server 2000 it is showing me error.
An internal consistency error occurred. Contact Technical Support for assistance.
RESTORE DATABASE is terminating abnormally.
Plz. anyone can help me..

mukul:confused:Run RESTORE VERIFYONLY to ensure the backup is valid, and KBA http://support.microsoft.com/kb/268481 for further information. Try getting another set of backup to assure the source database server hardware is intact.|||Hi Satya,
i have run RESTORE VERIFYONLY command and after this is showing following msg-
Server: Msg 3242, Level 16, State 2, Line 1
The file on device 'g:\backup\projectServer\ProjectServer_db_20070709 0000.bak' is not a valid Microsoft Tape Format backup set.Server: Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.
then what is this mean?
plz. help me out..

AMO - Dimension Size?

Hello Folks,

I've started to write an AMO application to get information relevant to DBA's out of our SSAS databases.

I've successfully got the estimated sizes for Cubes, MeasureGroups and individual partitions nicely listed.

However, i'm having trouble because i can't seem to find a property of the Dimension object that will give me the size.

The reason i'm doing this is because i've read that you need to watch the size (and memory consumption) of these babies.

How do i do this in AMO? Or any other means!

Many thanks.

Kyle (DBA)

First, if you aren't already using the AMO browser, then you should start. It was designed for this purpose. Look for it in the Samples, by default located at:

C:\Program Files\Microsoft SQL Server\90\Samples\Analysis Services\Programmability\AMO\AmoBrowser

DImensions are buckets holding attributes. Dimensions themselves don't have sizes, attributes do. To figure out the # of members in a dimension, you need to navigate down to the key attribute of the dimension. The size of the *key* attribute is probably what you are looking for.

_-_-_ Dave