Should I Shrink Database?

[TonyScarpelli]

I've been deleting a lot of rows of data in many of the tables in one of my test databases.

Should I shrink the database after all these deletes or does it matter?

Thanks.

Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[mrdenny]

If you are short on free drive space, go ahead and shrink the database. If you aren't don't bother.

Shrinking the database does nothing but make the SQL Server do more work later on when it has to regrow the database again later.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[TonyScarpelli]

Can I ask a question or three about deleted rows?

If rows are deleted in a table do they go away or do they hang around?

If they still exist in the table doesn't this take up space in memory or in the drive?

How do you get rid of the this space or does it get re-used?

Thanks.


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102

 

[ptheriault]

If rows are deleted in a table do they go away or do they hang around?

Once the delete it commited (which is implied if you don't specify begin transaction) then the data is removed from the pages. The pages are not delete but there is no longer any data on the page. This why the size of the database does not go down if you delete a lot of data.

If they still exist in the table doesn't this take up space in memory or in the drive?

It is empty pages that is taking up space.

How do you get rid of the this space or does it get re-used?

The database will reuse the pages. As Denny said above, you can shrink the database to remove the empty pages but that can cause added overhead when the database has to autogrow to add them back. So if space is not an issue just leave them.



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Nicly stated paul.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ptheriault]

Thanks Denny.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[TonyScarpelli]

Thanks, guys.

I'm so used to FoxPro tables where the rows are just marked as deleted and are still there, and every once in a while you have to pack the table to get rid of the rows. This then actually reduces the size of the table.

I'm not that familiar with how SQL Server works inside. I guess I have to do a little more research.

Thanks again.

CU


Tony Scarpelli
Clinical Engineering Dept.
Maine Medical Center
Portland, Maine 04102