Hi,

I am the happy owner of a SQL express 2008 R2 holding my DB.

I have my DB primary data file at 7 GB

In DB properties, I have initial size of primary db at 7022 MB, autogrowth = by 1 MB, unlimited.

I query table sizes with :

select object_name(id) [Table Name],
[Table Size] = convert (varchar, dpages * 8 / 1024) + 'MB'
from sysindexes where indid in (0,1)
order by dpages desc

total size of all my tables is then 1171 MB

I do shrink / files / file type = data => available free space is 0.25 MB

I'm afraid it will hit the 10 GB size.

How do I reduce the DB size ? it is possible to change the  initial size of primary db ?

thanks

I query table sizes with :

No, you don't, you query only the size of heaps (indid=0) and clustered Indexes(indid=1), everything else is missing, see Detailed list of all tables and their size

You have 2 options
- Move old data to a second database
- Delete old data

Could you please paste the output of below query:

Use database

go

select
a.FILEID,
[FILE_SIZE_MB] = 
convert(decimal(12,2),round(a.size/128.000,2)),
[SPACE_USED_MB] =
convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
[FREE_SPACE_MB] =
convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) ,
NAME = left(a.NAME,15),
FILENAME = left(a.FILENAME,30)
from
dbo.sysfiles a

If available free space is 0.25 MB then you cant reduce anymore. You will have to purge data or move to other database or go for edition upgrade when you hit 10GB. Initial size is not a problem you can shrink only if you have free space.

Please note shrink db is not advisable.

• Edited by dave_gona Thursday, March 19, 2015 10:50 AM

I'm afraid it will hit the 10 GB size.

How do I reduce the DB size ? it is possible to change the  initial size of primary db ?

thanks

The initial size of database is not actually the  *initial size* it does not gives correct information. Below article would help you in understanding more

http://social.technet.microsoft.com/wiki/contents/articles/22661.sql-server-misleading-database-initial-size-label.aspx

Size does matter: 10 ways to reduce the database size and improve performance in SQL Server

http://aboutsqlserver.com/2014/12/02/size-does-matter-10-ways-to-reduce-the-database-size-and-improve-performance-in-sql-server/