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.