General Database Questions (shrink db, suspect db)

Question

General Database Questions (shrink db, suspect db)

HookSQLDBA7

SSChampion

Points: 10709
More actions
October 20, 2004 at 6:49 am

#83866

I am trying to shrink a database (27 GB Size, 3 GB Space Available) and 'move the pages to beginning of the file'. This is taking longer than 20 minutes. I have cancelled it. I noticed when I went into Task Manager, Enterprise Manager shows as 'not responding' during the shrink operation. Is this normal? Is SQL Server still performing the shrink operation behind the scenes? Should I just leave it running. It has been a very long time since this database has been shrunk.
Does SQL Server 2000 Backups compresses or shrink the files automatically during the backup process?
What is the best method to recover from a suspect user database? Is there a Knowledge Base Article on this topic?
Thanks in advance, Kevin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

iLoveSQL SSCrazy Points: 2888 More actions · Answer 1

If the database is badly fregment, over 20min for 27GB is normal and depends on Server power, storage, etc, it can take more then half day to shrink. The best method that I think is that you shouldn't have canceled but since you already did, just leave until it finishes all the process. You can check the process by using "sp_who 'active'" to see if the process is still running.

About the suspect user database, you can read this http://support.microsoft.com/default.aspx?scid=kb;en-us;165918

There are so many resources about suspect database in the site you can just search for it and get the better response from other good DBAs

Dave-175556 SSC Eights! Points: 896 More actions · Answer 2

i had shrink process ran for almost 15 hours. So time to shrink database or file really varies on database condition. Another thing you might want to check physical_io or cpu of the shrink process wether it is changing or not just to make sure the process is running fine.

For the suspected user database, I would try couple of procedures before going for restore.

1. try sp_resetstatus and then restart sql services.

2. Backup log file with no_truncate option. then try to detach database using sp_detach_db and then try to attach it back using sp_attach_db.

If none of this two work then I would go for restore of databases or follow the KB iloveSQL has mentioned.

Afro Blanca SSC-Addicted Points: 499 More actions · Answer 3

I'm currently in a situation where I'm trying to shrink a DB and it's taking a real long time. This is sort of an extreme example - the DB size is 217GB, and only about 35GB are actually in use. This is the thing - I tell it to shrink the DB, and it does that lovely Windows thing where everything inside the window "blanks out." The drive light isn't flickering much. Task manager says that the application is "not responding," and when I do an sp_who 'active', the process is apparently "sleeping."

So, I'm thinking, "Ok, this locked up." I check the CPU utilization in task manager, and it's averaging around 20-25%. However, after I cancel the process, CPU utilization goes down to about 5%. So it's obviously doing something.

What should I do? Just let it run? Is it bad to use the DB while it's shrinking?

Hemantgiri S. Goswami SSC-Addicted Points: 443 More actions · Answer 4

Hi,

do not use Enterprise Manager to shrink the file , instead use dbcc shrinkfile command to get more control over what you do... and for

resetting the suspect database read :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp

HTH

Regards

Hemantgiri S. Goswami | SQL Server Specialist & Consultant
SQL Server Citation[/url] | Follow me on Twitter