Link to home
Start Free TrialLog in
Avatar of raykata2ddotcom
raykata2ddotcom

asked on

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
Avatar of tchalkov
tchalkov

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.
Avatar of raykata2ddotcom

ASKER

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
try to run dbcc checkdb before running shrinkdb - there could be some errors in your database
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.
ASKER CERTIFIED SOLUTION
Avatar of highmarks
highmarks

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial