[dba-SQLServer] Compacting a data file

Personally, I would check task manager...to determine if anything is happening...memory usage specifically.

Just a thought...

As I've mentioned before...I have a friend who works with the same type and size of datasets...and a 12 to 20 hour rebuild of a db is not unheard of.


Best of luck,

Mark

> From: jwcolby at colbyconsulting.com
> To: dba-sqlserver at databaseadvisors.com
> Date: Tue, 13 Nov 2007 10:07:16 -0500
> Subject: Re: [dba-SQLServer] Compacting a data file
>
>>Given the number of rows involved, did you expect this to happen quickly?
>
> LOL. YES! I pumped raw caffeine into the caffeine port on the front of the
> computer.
>
> Seriously though, I see no sign that is happening. AFAICT the temp file is
> out on my C: drive (which has plenty of room) and it is only about 28 mb
> right now and not changing size. In fact the main data file and log file
> are likewise not varying in size. So what is this thing doing? The shrink
> database dialog is still up and the little indicator says activity is
> happening.
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
> Fuller
> Sent: Tuesday, November 13, 2007 9:47 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Compacting a data file
>
> The physical size of the db is not of interest. The number of rows is the
> guideline here. A "re-organize pages" operation writes the entire db to
> tempdb and then creates a copy from tempdb and only when said copy is
> complete does it destroy the original and rename the new copy to the
> original name. Given the number of rows involved, did you expect this to
> happen quickly?
>
> A.
>
> On 11/13/07, jwcolby  wrote:
>>
>> I made the foolish mistake of asking SQL Server to compact a large
>> (200GB) file using "reorganize pages". This is a static table with
>> about 40% empty space and I foolishly thought this might be a good
>> thing to do. OK FINE, I WAS FOOLISH ALRIGHT! 8-(
>>
>> I started it last night about 6:00 pm and as of this morning at 9:17
>> AM it is still chugging away. Does anyone know whether this will
>> actually finish some day and further whether that someday will be
>> today or next January?
>> Fast server, lots of memory, fast disk. LOTS of CPU activity (50% of
>> two cores).
>>
>> This is a database that I need to get some work done on.
>>
>> John W. Colby
>> Colby Consulting
>> www.ColbyConsulting.com
>>
>> _______________________________________________
>> dba-SQLServer mailing list
>> dba-SQLServer at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>> http://www.databaseadvisors.com
>>
>>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>

_________________________________________________________________
Boo! Scare away worms, viruses and so much more! Try Windows Live OneCare!
http://onecare.live.com/standard/en-us/purchase/trial.aspx?s_cid=wl_hotmailnews