dbcc shrinkdatabase (SDE)

I'm running SQL Server 7.0 on a 450 NT 4.0 server with 256 MB of memeory and two 25 GB disk drives. My database called SDE is 9 GB in size. Yesterday I entered

dbcc shrinkdatabase (SDE)

in the Query Analyzer window to try and regain some disk space. After 19.5 hours the process is still running. There is nothing else running on the server. Is shrinking a database always so slow? How can I estimate in advance the time required to shrink a database? Is shrinking a database of this size ever viable?

Thx
RayK
raykata2ddotcomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tchalkovCommented:
is your server doing something? I mean is you CPU high  or is there any disk activity?
If no then try to run dbcc opentran(sde)
this will show you all open transaction on your database(I'm not sure but it is possible that open transactions could have some impact on shrinkdatabase).
then check the sql server error log and WinNT event log.
0
raykata2ddotcomAuthor Commented:
I gave up and killed the process after 20.5 hours.

To answer your questions: There was nothing else running on the server, it is used strictly for development. There were no open transactions and no other connections to the database. Task Manager showed the cpu usage fluctuating between 5 and 40 %. There was constant disk activity.

I won't be trying this again until I have a spare weekend to tie up this server.

Thx
RayK
0
tchalkovCommented:
try to run dbcc checkdb before running shrinkdb - there could be some errors in your database
0
FatsoCommented:
Generally when I shrink my database (2.5Gb) it only takes a few minutes, however on one occasion it tooks hours to run.
In this instant I  believe the cause was a process I was running whereby a temporary file was created and part way through some data transformations the process stopped unexpectedly - anyway the temporary file was never closed and this is the only time I have experienced such a problem.
No transactions were showing as open, cehcked the cpu usage and everything was in order, that's the only think I can put  it down to.
0
highmarksCommented:
why don't you shrink your database from enterprise manager.
I have found out it to be quite fast when I had tried it on 1200MB database it took only 21 minutes.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.