SHRINK Database Question

SHRINK Database Question

Post by Larry Carne » Sat, 12 Jan 2002 23:31:34



Warning: SQL Admin is a newbie!  :)

Setting:  MFG Facility, 5 Stations adding records to
table 7 days a week, 24 hours a day.

I received a call that all stations were having a problem
adding records.  The stations would scan a part which
would query the database and (based on a value) add the
part to the appropriate table.

Whenever they needed to add to the table, an hourglass
would appear and after a few minutes the record would be
added.

I went to the server (Windows 2000 Server, Dual
PIII933's, 144 Gigs of RAID 5 with 64 Meg Cache on the
controller, 3 Gigs of ram) and its utilization was below
3% and the database was at 2 gigs.  

I decided to SHRINK the database and upon doing so,
everyone was able to work and response time was fine.

However, I must confess I am at a loss why running the
SHRINK command would have had such an impact.   The DB
size nor the space free changed appreciably so I am not
sure why this would have helped so much...

Any feedback would be great as I am learning as much as I
can about SQL...

Larry Carnes

MCP/MCSE/MCT

 
 
 

SHRINK Database Question

Post by chri » Sun, 13 Jan 2002 00:23:10


If the DB or Log were near capacity then shrinking a little could help but
only for a short time. You should probably read up on "Backup Log" and
"Truncating the Transaction Log" as your problem may reoccur again shortly.

> Warning: SQL Admin is a newbie!  :)

> Setting:  MFG Facility, 5 Stations adding records to
> table 7 days a week, 24 hours a day.

> I received a call that all stations were having a problem
> adding records.  The stations would scan a part which
> would query the database and (based on a value) add the
> part to the appropriate table.

> Whenever they needed to add to the table, an hourglass
> would appear and after a few minutes the record would be
> added.

> I went to the server (Windows 2000 Server, Dual
> PIII933's, 144 Gigs of RAID 5 with 64 Meg Cache on the
> controller, 3 Gigs of ram) and its utilization was below
> 3% and the database was at 2 gigs.

> I decided to SHRINK the database and upon doing so,
> everyone was able to work and response time was fine.

> However, I must confess I am at a loss why running the
> SHRINK command would have had such an impact.   The DB
> size nor the space free changed appreciably so I am not
> sure why this would have helped so much...

> Any feedback would be great as I am learning as much as I
> can about SQL...

> Larry Carnes

> MCP/MCSE/MCT


 
 
 

SHRINK Database Question

Post by Larry Carne » Sun, 13 Jan 2002 00:34:07


Thanks for the reply Chris.

I forgot to mention that this is SQL2000 and the Transaction log is set
to grow automatically.

Could what you mention still occur even with this option set?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Shrink database question

Hi,

Do anybody know way to shrink database to size smaller then data size.

Somebody did alter to "model" database on server to 22 Mb, and  problem
now that each new database
on server will have size at least 22 Mb.
I have "model" database with 21 Mb data size and 1 Mb log size.
About 19 Mb of data size is free, but I cannot execute shrink to size
smaller then 22 Mb !

    TIA,
                Karasik Igor


2. Ok, new question ....

3. File open dialogs in VFP5.0

4. Database file shrink if auto-shrink off

5. Static Web object and SQL database

6. Shrinking Database Only Shrinks the Log?

7. Views for inserting into joins?

8. shrinking databases - performance question

9. Newbie Question - Shrink database

10. How to shrink the Database size and Database Device size

11. Auto Shrink not shrinking

12. Shrink tables doesn't shrink enough