Reclaim unused space from data file

Question

Post reply

Reclaim unused space from data file

Horatio

Mr or Mrs. 500

Points: 542
More actions
September 10, 2010 at 1:30 am

#223809

Hi all,
Hope you can help me. I was playing around in a test database with shrinking the overall size of the database (and thus, the backup file as well) by removing some old tables (tables that were renamed in a quick way to shrink a table) and old data no longer relevant. While the size of the backup has gone down considerably, the actual MDF file still uses the same amount of space as before the cleaning of the data.
Now, our client wants to decrease the size of the database on disk (and the backup size, as well as time it takes to make the backup) and I need to know if there is a way I can shrink the MDF file now that all the data is gone??
Any help/tips greatly appreciated!!
Thanks,
The H..................

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

did you deleted your data and table or just flagged them as 'to be deleted'?

You can use below query to check space consuming by tables:-

/*

Obtains spaced used data for ALL user tables in the database

*/

DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR

FOR

select [name]

from dbo.sysobjects

where OBJECTPROPERTY(id, N'IsUserTable') = 1

FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable

(

tableName varchar(100),

numberofRows int,

reservedSize varchar(50),

dataSize varchar(50),

indexSize varchar(50),

unusedSize varchar(50)

)

--Open the cursor

OPEN tableCursor

--Get the first table name from the cursor

FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch

WHILE (@@Fetch_Status >= 0)

BEGIN

--Dump the results of the sp_spaceused query to the temp table

INSERT #TempTable

EXEC sp_spaceused @TableName

--Get the next table name

FETCH NEXT FROM tableCursor INTO @TableName

END

--Get rid of the cursor

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT *

FROM #TempTable order by numberofrows desc

--Final cleanup!

--DROP TABLE #TempTable

GO

----------
Ashish

Horatio Mr or Mrs. 500 Points: 542 More actions · Answer 2

Hi,

I physically deleted the data from the database, I.E. ran the DELETE statement on the rows that were no longer relevant, and also dropped the old tables that were renamed.

Thanks,

crazy4sql SSCoach Points: 19590 More actions · Answer 3

have you run the above query to check table size to verify the space is being released from those tables?

----------
Ashish

Horatio Mr or Mrs. 500 Points: 542 More actions · Answer 4

Hi,

Yes, the sizes that show up are correct, and the tables removed do not appear on the list.

We're running SQL 2008 RTM on Win2K3 SP2 Ent R2

crazy4sql SSCoach Points: 19590 More actions · Answer 5

have a look of this post. Might help you.

http://www.sqlservercentral.com/Forums/Topic803424-146-1.aspx

Post your problem further if it still not get resolved

----------
Ashish