How do I reduce DB Size - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

How do I reduce DB Size

Author

Topic

jarobert27
Starting Member

2 Posts

Posted - 2011-02-16 : 16:58:14

I'm a developer. Not a DBA.

I have a DB that is currently around 10.9 GB and in order to get this DB restored at my hosting provider I need to get it under 10GB.

I have already used the Shrink task so it currently reads 0% savings available.

What is my next step? I used the MS Database Tuning Advisor to recommend indexes and I have a suspicion that its the indexes that have grown the DB but I wouldn't know where to begin to pare them down. And the performance is finally where I need it to be.

Any other options? Are transaction logs relevant?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-02-16 : 19:02:50

Run this script from your database to see how big the various files are in your database and to see how much unused space there is in each file. I suspect,from the message the 0% saving message, that you may not have much room to maneuver.[CODE]create table #DB_FILE_INFO (
Server sysname,
db_name sysname,
logical_name sysname,
physical_name varchar(260),
Size_MB int,
Used_MB int,
Unused_MB int,
MaxSize_MB varchar(10),
Growth int,
Units varchar(2),
fType nvarchar(60),
compatibility tinyint,
recovery_model nvarchar(60)
)

insert into #DB_FILE_INFO
select
@@ServerName,
DB_Name(),
df.name logical_name,
df.physical_name,

(cast(df.size as bigint) * 8 + 1023) / 1024 Size_MB,
convert(int,round(fileproperty(df.name, 'SpaceUsed')/128.000,0)) Used_MB,
convert(int,round((df.size - fileproperty(df.name, 'SpaceUsed'))/128.000,0)) Unused_MB,

case
when df.growth = 0 then 'No Growth'
when df.max_size = -1 then 'Unlimited'
else
case
when len(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))) <= 3 then cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))
when len(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))) <= 6
then left(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20)), len(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))) - 3) +
',' + right(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20)), 3)
when len(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))) <= 9
then left(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20)), len(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))) - 6) +
',' +
substring(
cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20)),
len(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20))) - 6,
3) + ',' + right(cast((cast(df.max_size as bigint) * 8 + 1023) / 1024 as varchar(20)), 3)
end
end MaxSize_MB,

case
when df.is_percent_growth = 1 then df.Growth
when df.is_percent_growth = 0 then (df.Growth * 8 + 1023) / 1024
else -1
end Growth,

case
when df.is_percent_growth = 1 then '%'
when df.is_percent_growth = 0 then 'MB'
else '??'
end Units,

df.type_desc,

d.compatibility_level,
d.recovery_model_desc
from
sys.databases d
inner join
sys.database_files df
on
d.database_id = db_id();

select *
from
#DB_FILE_INFO
order by
left(physical_name, 1),
DB_Name(),
fType;[/CODE]If the size if your database is larger than the target machine's resources, your choices are 1) Get rid of indexes 2) Get rid of data or 3) Get more resources on the target machine.

The transaction Log file does, of course, take up space. The above script may show yours as being largely empty. This could be a red herring since a well administered log would be empty for a "quiet" database. The log would grow to be large when there is activity in the system. How big should it be? Guess and test!

HTH

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

Sachin.Nand

2937 Posts

Posted - 2011-02-17 : 00:50:33

quote:

I have a DB that is currently around 10.9 GB and in order to get this DB restored at my hosting provider I need to get it under 10GB.

If your data is going to increase the size is also going to increase.There is no way you can stop that.

Any particular reason you want to keep it under 10GB?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources