I use the following script to gather data from my database wich is SQL Server on Azure.
-- Script to run against database to gather metrics
CREATE TABLE #SpaceUsed (name sysname,rows bigint,reserved sysname,data sysname,index_size sysname,unused sysname)
DECLARE @Counter int
DECLARE @Max int
DECLARE @Table sysname
SELECT name, IDENTITY(int,1,1) ROWID
INTO #TableCollection
FROM sysobjects
WHERE xtype = 'U'
ORDER BY lower(name)
SET @Counter = 1
SET @Max = (SELECT Max(ROWID) FROM #TableCollection)
WHILE (@Counter <= @Max)
BEGIN
SET @Table = (SELECT name FROM #TableCollection WHERE ROWID = @Counter)
INSERT INTO #SpaceUsed
EXECUTE sp_spaceused @Table
SET @Counter = @Counter + 1
END
SELECT * FROM #SpaceUsed
DROP TABLE #TableCollection
DROP TABLE #SpaceUsed
One of my tables is 53 GB in size
, with 38 GB in data
, 14 GB in unused
. The indexes are 11 MB in size
. How do I go about reclaiming that 14 GB that is unused
? As this is in Azure the extra space is costing money.
After reading various articles I have tried the following to reduce the size of the table.
DBCC DBREINDEX ('myTableName', ' ')
DBCC SHRINKDATABASE (myDatabaseName, 10);
However, after these complete, the size of the table remains unchanged. How can I go about reducing the size of the table and database in order to reclaim unused space?
UPDATE: There are 24 columns in the table with 9 of the fields being varchar of large length (> 4000) or varchar(MAX). Another 8 columns are unique-identifier types.