SQL 2000 Shrink Question

[dakata24]

we're performing a shrink on a 120GB DB and it's taking longer than anticipated. It's showing as "Not Responding" in task manager. But when we view the processes, it sqlserver shows activity.

If we were to do a "End Task" while shrinking the db, will that corrupt/damage the db? or is it harmless like stopping in the middle of a disk defrag.? thanks

 

[KLin]

If it's still showing cpu time being used, that means it's still running.

 

[kamper]

You did back it up first right?

Do you have documentation about the shrinking process? It should say what kind of failsafes it has. I'd imagine that since a database is always designed to be safe, like in the event of a power failure or something, the same would apply here, but of course the last thing you want to do is take chances...

 

[oran.mcgee]

There might be other options out there. This is something that worked for me once but there are no guarantees that it will work at all times.

 

[oran.mcgee]

Stop SQL Server instance - Copy MDF and LDF files to another location - Delete original MDF and LDF files - Start SQL Server instance again - Create new database with exact same name and file names - Stop SQL Server - overwrite newly created MDF and LDF.

 

[oran.mcgee]

After this your database should be back online. If it is then go ahead and put it into EMERGANCY mode and SINGLE USER mode.

Finally go ahead and execute DBCC CHECKDB like this

DBCC CHECKDB (databaseName, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS

If you can get to this and execute last command successfully you should be good. If not then your only options are restoring from backup and/or using 3rd party tools.

 

[simas]

NO!!!! please do not do what what mentioned above - that is equivalent of throwing the engine out of the car while it is running just to stop it. you can always find and kills the process within the engine that is running the shrink and DB would be in consistent state (not sqlserver.exe but process within the engine)

also, much better - ping one of your DBAs, this is a trivial question for any SQL DBA to handle that would take few seconds total

1. identify the thread (SPID) in question
2. Stop it within SQLOS using kill command

Also, strongly recommend seeking help on upgrading you to something supportable and more secure , nothing should be running on SQL 2000 in year 2016

 

[simas]

and if you are interested in technical details, sql engine does two things when issued shrink command
1. move/reallocate data pages from the 'end' of the file into free space it can find. Once move, adjust high water mark to indicate how much of the file is 'used'
2. release the space above high water mark back to Windows OS if instructed so by the DBA

depending on how you issued shrink command and with what parameters, you can tell SQL engine just to do #2 above, both, or set specific targets for file size/free space. again, even an entry level DBA would tell you that and should know this by heart

I do not know who handles your support and strongly recommend adding someone with DBA skills to staff, contracting one for evaluation (if the need is short term) and working towards longer term solution (i.e. remote DBA/hosting services who will take care of such things as engine currency/patching/ high availability/ point in time and point of failure recovery ,etc).

 

[TheRyuu]

And in case anyone hasn't noticed this thread got necroed from 2005.