December 29, 2008 at 1:11 am
Hi,
we are using Sql Server 2000 standard edition on windows 2003 enterprise edition. We got the following errors in the error log and after that the weekly Optimization job failed.
spid1b SQL Server has encountered 1 occurence(s) of IO request taking longer than 15 seconds to complete on file [D:\MSSQL\DATA\abc.mdf] in database [abc] (9). The OS file handle is 0x0000053c. The offset of the latest long IO is:0x0000014edd4000
spid1b SQL Server has encountered 1 occurence(s) of IO request taking longer than 15 seconds to complete on file [D:\MSSQL\DATA\xyz.mdf] in database [xyz] (8). The OS file handle is 0x00000544. The offset of the latest long IO is:0x0000012eb64000
spid76 error: 1105, sevirity: 17, state: 2
spid 76 could not allocate space for object processcontrol in database 'system' because the 'PRIMARY' filegroup is full..
I went and check the SP_SPACEUSED in system database, and we have the Available space has 406.91MB But still the error shows 'could not allocate space for object processcontrol in database 'system' because the 'PRIMARY' filegroup is full..'
sp_spaceused:
dbname database size unallocated space
system 2346.91 MB 406.91MB
reserved data index_size unused
644504 KB 572000 KB 69928 KB 2576 KB
Could you plz suggest me what are the immediate steps i need to take.
thanks
Mani
December 29, 2008 at 1:43 am
The IO warnings are just saying that you're overloading the IO system and the latency is getting very, very high.
Is the second sp_spaceused that you posted from the processcontrol table? If so, the table is over 600 MB in size. To rebuild the cluster of that, SQL needs 600 or so MB free. You only have 400. Hence there's not enough space to rebuild that index.
Can you increase the size of the DB 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 29, 2008 at 11:23 am
thank you
Previously, I ran the sp_spaceused in the database 'system'. After running sp_spaced for the table processcontrol I got below results
Use system
GO
EXEC sp_spaceused N'dbo.ProcessControl'
GO
name rows reserved data Index_size unused
ProcessControl2179852 440504 KB392680 KB 47840 KB -16 KB
Plz advice me
December 29, 2008 at 11:27 am
Please run DBCC updateusage and then run the sp_spaceused again for both the database and the table.
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 29, 2008 at 11:56 am
thanks Gail,
Results after running DBCC Updateusage and sp_spaceused
sp_spaceused:in database 'system'
dbname database size unallocated space
system 2346.56 MB 399.68 MB
reserved data index_size unused
651912 KB 572000 KB 7068 KB 3208 KB
sp_spaceused: for the table processcontrol
Use system
GO
EXEC sp_spaceused N'dbo.ProcessControl'
GO
name rows reserved data Index_size unused
ProcessControl 2184089 442128 KB 394096 KB 48000 KB 32 KB
Thank you
Mani
December 29, 2008 at 12:22 pm
It looks like there's not enough space in the data file. To rebuild a clustered index, SQL needs at least as much space as the clustered index currently takes up. For the table in question, that's at least 395 MB. There's only 400MB space free in the data file.
Can you increase the size of the data file?
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 29, 2008 at 12:30 pm
thanks Gail..I will go head and increase the datafile size..
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply