database size issue

What can be the reason for a database to have abnormal size.

for example: i have a database with size 12 GB which is not in according to the amount of data it has (theoratically it should be around 3 GB). To verify this, i create another db and then import all the tables from first db to second, and as expected the second db size is about 2.5 GB. What can be the reason for additional 9 - 10 GB in first db.

It could be that the database data file has a lot of empty space in it. You can check in SSMS by going to Properties, Tasks, Shrink, Files, and check the available free space.

You could also have heavily fragmented indexes that are taking up extra space.

Try this to see how much space you have within the database file(s):

SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'

FROM sys.database_files;

thanks for the info

the result of above query is

filename physical name total size available space

a_Data F:\a_Data.MDF 11411 3218.812500
a_Log F:\a_Log.LDF 1956 1935.687500

and the result of sp_spaceused

db name db size unallocated space

abc 13367 mb 3216 mb

reserved data index_size unused

8391672 kb 7487104 kb 862912 kb 41656 kb

so the reserved size seems huge: why its so and how to solve it

You probably have a lot of index fragmentation. You can try running this script to defragment all the indexes in that database. It will also call sp_UpdateStats.

This should free up quite a bit of space inside the data file. If you want to, you can run a database shrink after that to make the data file smaller, but unless you are really worried about disk space, I would not bother doing that.

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @maxfrag DECIMAL

SELECT @maxfrag = 5.0

DECLARE tables CURSOR FOR

SELECT TABLE_NAME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE'

CREATE TABLE #fraglist (

ObjectName CHAR (255),

ObjectId INT,

IndexName CHAR (255),

IndexId INT,

Lvl INT,

CountPages INT,

CountRows INT,

MinRecSize INT,

MaxRecSize INT,

AvgRecSize INT,

ForRecCount INT,

Extents INT,

ExtentSwitches INT,

AvgFreeBytes INT,

AvgPageDensity INT,

ScanDensity DECIMAL,

BestCount INT,

ActualCount INT,

LogicalFrag DECIMAL,

ExtentFrag DECIMAL)

OPEN tables

FETCH NEXT

FROM tables

INTO @tablename

WHILE @@FETCH_STATUS = 0

INSERT INTO #fraglist

EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')

WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCH NEXT

FROM tables

INTO @tablename

CLOSE tables

DEALLOCATE tables

DECLARE indexes CURSOR FOR

SELECT ObjectName, ObjectId, IndexId, LogicalFrag

FROM #fraglist

WHERE LogicalFrag >= @maxfrag

AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0

OPEN indexes

FETCH NEXT

FROM indexes

INTO @tablename, @objectid, @indexid, @frag

WHILE @@FETCH_STATUS = 0

PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',

' + RTRIM(@indexid) + ') - fragmentation currently '

+ RTRIM(CONVERT(varchar(15),@frag)) + '%'

SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',

' + RTRIM(@indexid) + ')'

EXEC (@execstr)

FETCH NEXT

FROM indexes

INTO @tablename, @objectid, @indexid, @frag

CLOSE indexes

DEALLOCATE indexes

DROP TABLE #fraglist

EXEC sp_updatestats

thanks for the help.

Now my real concern. We have similar stand alone database in each branch of company with the same application and configuration.

Performance of appication in other branches is good, but in this branch its very very slow. since application / hardware / configuration is same in all branches, one of the possible reason can be a problem in database, and hence i am looking deep into it.

Firstly, why this particular db size has grown so much (and why the empty spaces comes up). Even a size of 12 gb ( with most of it empty) should not be considered big performance blocker. So what can be the reasons for low performance.

First, if our answers are helpful, please mark them as "Helpful" so we get credit for them. Second, you should start a fresh thread, with this entirely new question. Otherwise it will not get as much attention. You also need to be more specific about your issue.

In the meantime, what symptoms are you seeing (besides increased database size) at the one branch

My blog post is a starting point:

http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!549.entry

This whitepaper has a lot of good performance troubleshooting information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx