[dba-SQLServer] Compacting a data file

jwcolby jwcolby at colbyconsulting.com
Tue Nov 13 10:12:59 CST 2007


Yep, I did that.  SQL Server is using ~50% of the processor time and ~1.7 gb
of memory.  It is chugging away doing something. 


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 Mark A
Matte
Sent: Tuesday, November 13, 2007 10:26 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [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_hotmailn
ews
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com





More information about the dba-SQLServer mailing list