December 12, 2011 at 5:42 am
Hi Experts,
I have recently migrated my Navision database (340GB in size) from MSSQL 2005 Enterprise edition to 2008R2 slandered edition with the same compatibility as 2005.
I have the same disk configuration in both servers. There 7 data files for the db and each file is kept in 7 different disks with 50GB each.
We have a regular index rebuild job for tables and views that runs every weekend in both the servers. For some strange reason the rebuild index job failed in the 2008R2 version saying there is less disk space for the files to grow.
Executing the query "ALTER INDEX [$19] ON [dbo].[test$G_L Entry] REBUILD..." failed with the following error: "Could not allocate a new page for database 'NAVI_PROD' because of insufficient disk space in filegroup 'Data Filegroup 1'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
So i extended the disk space by 50GB in each drive and still the job failed with same error and there was no space left in each disk.
What do you think will be the issue?
Is there any difference index in 2008R2 and 2005 because of which the size not enough as in 2005?
Thanks in advance
/Eswin
Tanx 😀
December 12, 2011 at 5:51 am
Please verify the size of 'Data Filegroup 1' filegroup & its autogrowth.
Is it default filegroup for tables / indexes? Or setup accidently as default for tables / indexes during upgrade?
December 12, 2011 at 5:57 am
Do the files have a max size set? How big is the index in question? How much free space in the files belonging to 'Data Filegroup 1'?
What's your autogrowth settings for those files?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 12, 2011 at 7:42 am
Please find the attached screenshot of filegroup and autogrowth setting.
Its all default indexes present from 2005 database.
Tanx 😀
December 12, 2011 at 7:58 am
GilaMonster (12/12/2011)
How big is the index in question? How much free space in the files belonging to 'Data Filegroup 1'?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 7:20 am
Im running rebuild index from maintenance plan its not run for one index its for the entire database i dont know how to check how big the index is there are many index in question.
SP_SPACEUSED gives this result
database_name database_size unallocated space
----------------- ------------------ ------------------
GSS_NAVI_PROD 680544.00 MB 13679.69 MB
reserved ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,data,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,index_size,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,unused
628963712 KB,,,,,,,,,,,,,,,,,,,,,,,,,,377577240 KB,,,,,,,,,,,,,,,,,,,254032088 KB,,,,,,,,,,,,,,,,,,,,,,,,[-2645616 KB]
PFA attached for filesize and freespace
Tanx 😀
December 13, 2011 at 7:23 am
Before you waste any more time on this issue (which you can't really avoid).
December 13, 2011 at 7:25 am
And run DBCC UpdateUsage please. Negative free space is a bit silly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 13, 2011 at 4:43 pm
Could it be something to do with the fact that you migrated from SQL Server 2005 (Enterprise) to SQL Server 2008 R2 (Standard)?
For e.g. Index rebuilds with ONLINE are only allowed in Enterprise editions
December 14, 2011 at 1:45 am
Hi Anish,
Im not sure if its something to do with the edition.
Im running offline index rebuild in the 2008R2 Standard edition.
Tanx 😀
December 14, 2011 at 4:37 am
We're not talking because we still need this answer!
GilaMonster (12/12/2011)
GilaMonster (12/12/2011)
How big is the index in question? How much free space in the files belonging to 'Data Filegroup 1'?
December 15, 2011 at 12:54 am
Hi Gail ,
I ran DBCC UpdateUsage (0) againt the database but the result is the same.
Tanx 😀
December 15, 2011 at 1:01 am
Hi Gail/Ninja's_RGR'us
Im running rebuild index using the maintenance task against the entire databases and not a single index.
from sp_spaceused i get
index_size is 254032088 KB
and each file is having less than 100MB of free space.
Tanx 😀
December 15, 2011 at 4:48 am
Eswin (12/15/2011)
Hi Gail/Ninja's_RGR'usIm running rebuild index using the maintenance task against the entire databases and not a single index.
from sp_spaceused i get
index_size is 254032088 KB
and each file is having less than 100MB of free space.
And you wonder why it blows up?
Use the script I posted instead, it's a much smarter defrag. You still have to make enough room for your bigger indexes but at least the whole thing runs faster.
December 15, 2011 at 6:18 am
Actullay it is working fine in 2005 Enterprice edtion with the same cofiguration but its failing in 2008R2 standared edtion to which the database was migrated. The free space became less than 100MB after the rebuild failed.
Tanx 😀
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply