Before accepting that your database is big because it needs all the data inside it and buy extra storage to overcome the potential space problem, you can find out if the existing space can be reduced by removing unused objects, indexes.
First find out what are the biggest tables:
DECLARE @query varchar(100) DECLARE @schema as sysname DECLARE @objectname as sysname IF (object_id('tempdb..#Table_Size') IS NOT Null) drop table [dbo].[#Table_Size] CREATE TABLE #Table_Size ( TableName varchar(50), [RowCount] int, Table_Size varchar(50), Data_Space_Used varchar(50), Index_Space_Used varchar(50), Unused_Space varchar(50) ) DECLARE Object_Cursor CURSOR FOR SELECT s.name, o.name FROM sysobjects o inner join sys.schemas s ON o.uid = s.schema_id WHERE xtype='U' OPEN Object_Cursor FETCH NEXT FROM Object_Cursor INTO @schema, @objectname WHILE @@FETCH_STATUS = 0 BEGIN set @query = 'sp_spaceused ''[' + @schema + '].[' + @objectname + ']''' INSERT INTO #Table_Size EXEC(@query) FETCH NEXT FROM Object_Cursor INTO @schema, @objectname END CLOSE Object_Cursor DEALLOCATE Object_Cursor select TableName,[RowCount], convert(NUMERIC(12,2) ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2))/1024 [Table_Size (MB)], convert(NUMERIC(12,2) ,left(ltrim(rtrim(Data_Space_Used)),len(ltrim(rtrim(Data_Space_Used)))-2))/1024 [Data_Space_Used (MB)], convert(NUMERIC(12,2) ,left(ltrim(rtrim(Index_Space_Used)),len(ltrim(rtrim(Index_Space_Used)))-2))/1024 [Index_Space_Used (MB)], convert(NUMERIC(12,2) ,left(ltrim(rtrim(Unused_Space)),len(ltrim(rtrim(Unused_Space)))-2))/1024 [Unused_Space (MB)] from #Table_Size order by convert(int ,left(ltrim(rtrim(Table_Size)),len(ltrim(rtrim(Table_Size)))-2)) desc
After you have a list of biggest tables, check their Indexes. During my DBA work I have found many times unused indexes, doubled with same definition which not only occupied the disk space for nothing but also made worse the performance of any DML query as these should have been updated. So for a single Insert imagine updating 4 indexes. Sometimes these can be re-designed.
DECLARE @query varchar(100) DECLARE @index_id INT DECLARE @index_size BIGINT SET @index_size = 0 DECLARE @name as NVARCHAR(500) DECLARE @indextype as NVARCHAR(256) DECLARE @TableName AS NVARCHAR(256) SET @TableName = 'TABLENAME' IF (object_id('tempdb..#Index_Table') IS NOT Null) drop table [dbo].[#Index_Table] CREATE TABLE #Index_Table ( Table_Name varchar(250), Index_ID varchar(250), Index_Name varchar(250), IndexType varchar(250), Index_Size_MB INT ) DECLARE Index_Table CURSOR FOR SELECT index_id, name FROM sys.indexes WHERE object_id = OBJECT_ID(@TableName) OPEN Index_Table FETCH NEXT FROM Index_Table INTO @index_id, @name WHILE @@FETCH_STATUS = 0 BEGIN SELECT @index_size = @index_size + (avg_record_size_in_bytes * record_count) FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED') set @index_size = @index_size / 1048576 select @indextype = (index_type_desc) from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(@TableName), @index_id , NULL, 'DETAILED') insert into #Index_Table values (@TableName, @index_id, @name, @indextype, convert(int ,@index_size)) FETCH NEXT FROM Index_Table INTO @index_id, @name END CLOSE Index_Table DEALLOCATE Index_Table select * from #Index_Table order by Index_Size_MB desc