SQL Server and shrinking

Jack_Crouch

Well Known Member
OK... I am an idiot here on SQL Server.


Trying to get the hang of it. Starting to like what I can do in Enterprise
Manager.

But I have what I thought was a simple thing (probably is)...

We copied PY7333 Central objects over to PD7333 using the UBE for this.
F980011 and F980021 are the xref files and take up 5.5gb (all the other
files combined take up 2.5gb). I don't want any data in these files.
So I ran a TRUNCATE TABLE command on both of them. Seemed to work like a
champ.

However the



PD7333data.mdf still remains 8.5gb. So I attempted to shrink the database
(from Enterprise Manager). Well, this file grew to 21.5gb (and the log
went to 3.3gb).

So, I'll bite. Is there an Enlarge command that actually makes the file
smaller? What am I doing wrong?





AS400 V4R4, B733.2, SP11.3, NT-SQL7 for CO
 
Doesn't seem normal to me! You may want to use the Database Maintenance Plan
to maintain your databases and see if the size shrinks. The plan will add
jobs to your SQL scheduler based on the schedules you created with the plan.

Hope this helps a bit!
 
In talking to MS... Turns out that MS has a bug with shrinking DBs with files that have mixed text and binary objects. We are on SP2 of SQL7. SP3 has a hot fix for this.

We are trying a workaround from MS at the moment.

AS400 V4R4, B733.2, SP11.3, NT-SQL7 for CO
 
Re: RE: SQL Server and shrinking

Sorry if I am boring everyone with this... But this is what MS said:

"Text/image data is the same thing as binary large objects. In sp2, a new feature was added to scan for free pages. This had the result of causing text data to not be fully packed in an 8K page and thus the size of the database would swell instead of shrink. So, prior to sp2, we wouldn’t see this problem. As I understand it, the post-sp3 hotfix essentially disables free page scans during the time the database is being shrunk."

So this could be an issue for anyone out there on SQL 7 SP2. Our PD7333 path went from 2.7gb to 25gb when we ran the shrink. So... watch out for this.

AS400 V4R4, XE+XU1+15ESUs, SP14.2, NT-SQL7 for CO
 
All said and done. Here are 3 questions.
1. Does shrinking the database corrupt the data?
2. If I apply the hotfix, can I run shrink database from the enterprise manage to actually shrink the inflated database?
 
Solack :

1. As far as I know, shrinking doesn't corrupt data unless your server
crashes in the middle of the process.
2. You can shrink database either from Enterprise Manager or form Query
Analyzer. I recommend you to be at SQL 7 SP3 or SP4.

Sebastian Sajaroff (JDELIST)

----------------------------------------------------------------------------
Strictly Personal and Confidential.
This email may contain confidential and proprietary material for the sole
use of the intended recipient. Any review or distribution by others is
strictly prohibited. If you are not the intended recipient please contact
the sender and delete all copies.Thanks.
.
Este mensaje es confidencial.
Puede contener informacion amparada por el secreto profesional. Si usted ha
recibido este e-mail por error, por favor comuniquenoslo inmediatamente via
e-mail y tenga la amabilidad de eliminarlo de su sistema; no debera copiar
el mensaje ni divulgar su contenido a ninguna persona. Muchas gracias.
----------------------------------------------------------------------------
 
Re: RE: SQL Server and shrinking

Aaaargghh

What are you doing, Jack

Come back to the light........don't go to the dark side.....

;-p

Seriously though, when are you going to put the Central Objects in DB2 ? Is there a reason you haven't done this yet ?

Jon Steel
 
Back
Top