Relation betw space occup.(database used %)and shrink mainte

andy76

Hello,

I ask you if there is a relation between these 2 aspects:

1) database used % (file -> database information) / expand

2) shrink job of maintenance plan (Integration Service of SQL 2005)

My question is:

the shrink job can compact the space unused setted by point 1) ?
Or are they different compressions?

Thank you

DenSter

1 - Database expand does not do anything on SQL Server, that function is for the native database only. To increase the file on SQL Server, you do File, Database, Alter, and you change the file size there, or you do it directly on SQL Server.
2 - You should never shrink the files. Keep them at a stable size and leave them alone. When you regularly shrink the file, SQL Server will automatically expand it when it needs to, which is always when it is in use, so it will always affect the performance negatively. Also, constant expansion and shrinking will leave the file fragmented, which also negatively affects performance.

There is no compression involved in either things. Think of the file as a bowl, with the popcorn in the bowl being the data. Expanding the file means to make the bowl bigger. Shrinking means to find a smaller bowl that still fits all the popcorn. Why would you want to put your popcorn in a smaller bowl when you know you're going to need to put more in it? SQL Server 2008 has data compression functionality, but that does not have anything to do with shrinking or expanding the files themselves.