May 20, 2010 at 8:09 am
I have SQLServer 2005 database with Recovery Model Set to Simple. This database has 18 user tables. I looked for the size of each table and didn't find any huge table. Still the MDF file of this database is 67GB.
What could be causing the database file (MDF) to be this big?
This is what I got when I ran the SQL to get each table size info.
Name,Rows,Reserved,data,index_size,unused
-----------------------------------------
COUNTRY,236 ,32 KB,16 KB,16 KB,0 KB
STATE,66 ,16 KB,8 KB,8 KB,0 KB
ORDERINFO,2 ,24 KB,8 KB,16 KB,0 KB
D_ENTRY,4 ,16 KB,8 KB,8 KB,0 KB
SAL_DELVR,4 ,16 KB,8 KB,8 KB,0 KB
SAL_FMDWL,2 ,16 KB,8 KB,8 KB,0 KB
dtslog,0 ,0 KB,0 KB,0 KB,0 KB
SAL_OP,2 ,16 KB,8 KB,8 KB,0 KB
SAL_RG,23 ,16 KB,8 KB,8 KB,0 KB
SAL_PP,3 ,48 KB,16 KB,32 KB,0 KB
sysds,1 ,72 KB,48 KB,24 KB,0 KB
V_ADDRESS,1274 ,208 KB,152 KB,8 KB,48 KB
PAID_FEE,2614 ,528 KB,472 KB,8 KB,48 KB
SAL_AD,21 ,16 KB,8 KB,8 KB,0 KB
SAL_ADVL,2 ,16 KB,8 KB,8 KB,0 KB
CASE_ON,7000 ,2064 KB,2024 KB,8 KB,32 KB
WH_RCL,386953 ,346200 KB,344080 KB,1896 KB,224 KB
ST_AD,0 ,24 KB,8 KB,16 KB,0 KB
May 20, 2010 at 8:54 am
You can make the data file any size you want, but then it will consist mostly of free space. Right click the database and check the properties. That's an easy way to see the amount of free space.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 20, 2010 at 9:13 am
RKumar-320442 (5/20/2010)
What could be causing the database file (MDF) to be this big?
It would be better if you can find it from the DBA who created the database.
Without any knowledge about what's that database does it's hard to say why.
Was there a huge chunck of data deleted in the recent past?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 20, 2010 at 9:21 am
also have a look at the auto grow size. Maybe someone set it way to big.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 20, 2010 at 9:22 am
Keep in mind that a db should NOT be the data size. There should be free space in there to allow for growth.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply