July 24, 2009 at 10:11 am
I have a SQL2K5 (version 9.0.4035) on a Windows 2003 w/sp2 Server.
The database is a Vendor Application and consists of ONE MDF file, ONE LDF file and SEVEN NDF files.
Question: The NDF files grow (a lot), then when I look at the database properties in SSMS the SIZES looks like:
Size = 17662.25 MB
Space Available = 9101.09 MB
If the NDF database files grow, why is there so much FREE (unused) SPACE ?
Please help me understand. Thanks in advance.
July 24, 2009 at 11:09 am
rew (7/24/2009)
If the NDF database files grow, why is there so much FREE (unused) SPACE ?Please help me understand. Thanks in advance.
The database file grows at a specific percent or specific amount of size each time it falls short of space within the file. This doesn't mean all the space requested by the operating system will be consumed immediately. hence you see free space within the file which will be subsequently used as your database grows.
July 24, 2009 at 11:17 am
Thanks Pradeep.
But, 9GB of free space? That seems like a lot.
BTW: The database RECOVERY MODEL is set to SIMPLE. ALL database files are set to grow automatically by 10%. I'm trying to verify with the Vendor if these settings are accurate.
July 24, 2009 at 11:20 am
Choose one of the NDF file then post both Size and Available space for the last week, daily snapshots.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 24, 2009 at 11:46 am
The vendor may have created the database with a large initial size to give the database enough room to grow. For example: I could set the initial size of a database to 17GB and at first the database uses only 500 MB. The next day you notice the files have grown to 1GB you will still have 16GB free. The database will not grow until the data reaches 17GB.
July 24, 2009 at 12:37 pm
All MDF/NDF files were initially 500MB.
"dbcc showfilestats" info:
TotalExtents UsedExtents Name O/S Current SIZE
8000 4083 DataFile.MDF 500MB
8000 1833 NDF1_Data.NDF 500MB
82582 38219 NDf2_Data.NDF 5.04GB
8000 333 NDf3_Data.NDF 500MB
68847 31933 NDF4_Data.NDF 4.2GB
16400 9358 NDF5_Data.NDF 1GB
74767 44176 NDF6_Data.NDF 4.56GB
8000 1 NDF7_Data.NDF 500MB
"sp_spaceused" info
database_name database_size unallocated space
TESTDB 17662.25 MB 9042.82 MB
reserved data index_size unused
8314296 KB 5629808 KB 2679448 KB 5040 KB
July 24, 2009 at 12:41 pm
Have you or the application run a defrag on tables and or indexes?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 24, 2009 at 12:52 pm
Yes sir, once.
However, last night I did a database SHRINK (I know I'm not supposed to do this, but this system is still in development and I not given much space). Then I did a FULL Backup. This morning the database (NDF files) has grown again.
July 24, 2009 at 12:54 pm
PaulB (7/24/2009)
Choose one of the NDF file then post both Size and Available space for the last week, daily snapshots.
BTW: I'm new to SQL2K5 and don't know how to do this. Can you advise?
July 24, 2009 at 4:23 pm
Also, why are some of these NDF files so much larger than others? Is that due to the TABLES/INDEXES that are contained within them? Shouldn't they all be relatively the same size or is that a non issue?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply