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.

|||

It could be spaced used for indexes, or white space within the files.

Check sp_spaceused and see how much space it shows as being used.

The white space is probably used for rebuilding indexes.

|||

One of the possible reasons might be that the datafile is allocated to 12GB size check it out

RegarDs,

Jacx

|||

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

-- Individual File Size query

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

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

FROMsys.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.

-- Declare variables

SETNOCOUNTON

DECLARE @.tablename VARCHAR(128)

DECLARE @.execstr VARCHAR(255)

DECLARE @.objectid INT

DECLARE @.indexid INT

DECLARE @.frag DECIMAL

DECLARE @.maxfrag DECIMAL

-- Decide on the maximum fragmentation to allow

SELECT @.maxfrag = 5.0

-- Declare cursor

DECLARE tables CURSORFOR

SELECT TABLE_NAME

FROMINFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE ='BASE TABLE'

-- Create the table

CREATETABLE #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 the cursor

OPEN tables

-- Loop through all the tables in the database

FETCHNEXT

FROM tables

INTO @.tablename

WHILE@.@.FETCH_STATUS= 0

BEGIN

-- Do the showcontig of all indexes of the table

INSERTINTO #fraglist

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

WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')

FETCHNEXT

FROM tables

INTO @.tablename

END

-- Close and deallocate the cursor

CLOSE tables

DEALLOCATE tables

-- Declare cursor for list of indexes to be defragged

DECLARE indexes CURSORFOR

SELECT ObjectName, ObjectId, IndexId, LogicalFrag

FROM #fraglist

WHERE LogicalFrag >= @.maxfrag

ANDINDEXPROPERTY(ObjectId, IndexName,'IndexDepth')> 0

-- Open the cursor

OPEN indexes

-- loop through the indexes

FETCHNEXT

FROM indexes

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

WHILE@.@.FETCH_STATUS= 0

BEGIN

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)

FETCHNEXT

FROM indexes

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

END

-- Close and deallocate the cursor

CLOSE indexes

DEALLOCATE indexes

-- Delete the temporary table

DROPTABLE #fraglist

GO

-- Runs UPDATE STATISTICS against all user-defined tables in the current database

EXECsp_updatestats

GO

|||

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