July 31, 2007 at 8:45 am
Hi,
I hope that someome could help me with this issue and give me some hints on how resolve it.
The issue is with the job optimization. first, let me give you some settings :
I have an application that have a db in SQLServer 2000. The application have created a mainteance plan that does the folowing :
- Optimization :
* Reoganize data and indexes pages
** change free space per page percentage to : 10%
* Remove unused space from database file
** Shrink database when it grows beyond : 50 MB
** Amount of free space to remain after shrink : 10%
For information, xp_fixeddrives gives me :
C : 5331
D : 174932 -> all dbs files are located here
E : 3817
S : 33044
The db itself have the folowing size, according to the taskpad view :
* Space allocated
** Data : 110785 MB (106753 used, 4032 free)
** Log : 175.99 MB (14.19 used, 161.8 free)
Also, the data and log system files are on the D: drive
For the data file settings :
* Space allocated for data : 110787 MB (PRIMARY)
* Auto grow file is set, file growth in MB : 256
* Max file size, restrict file grow : 110880
For the Log file settings :
* Space allocated for data : 176 MB (PRIMARY)
* Auto grow file is set, file growth in MB : 128
* Max file size, restrict file grow : 22176
The tempdb now,
* Space allocated
** Data : 8 MB (1.12 used, 6.88 free)
** Log : 0.74 MB (0.34 used, 0.4 free)
Also, the data and log system files are on the D: drive
For the data file settings :
* Space allocated for data : 8 MB (PRIMARY)
* Auto grow file is set, by percent : 10
* unrestricted file growth
For the Log file settings :
* Space allocated for data : 1 MB (PRIMARY)
* Auto grow file is set, by percent : 10
* unrestricted file growth
Now, for the issue itself, everytime the optimization job is launch, the error log gives me :
Could not allocate space for object '(SYSTEM table id: -637536758)' in database 'DB' because the 'PRIMARY' filegroup is full..
I have cheked on the internet without finding the right way to solve this issue and I am not an expert in SQLServer 2000.
I suspect that when the job is launch, the reindexing or the remove spave operation is filling the DB space to the max but I not sure of this...
What setting should I need to modify in order to the job to complete without failure? I am pretty sure that some settings ar e not correct or that I need to add more space to the DB, but what and where is the question...
The only drive that I can use is the D drive whitch has as you can see plenty of free space.
Please, give me any hints to tricks that could help me (and others).
If you need more details, feel free to ask.
Thanks.
July 31, 2007 at 10:35 am
If DBCC DBREINDEX is used in the plan, you're probably right that it's generating a lot of growth in the transaction log. I noticed that you have restricted log file growth on the database, so it's hitting that limit and giving you the error message you see in the error log.
Try removing the growth restriction on the T-log. You could also look into using DBCC INDEXDEFRAG which is performed in short transactions rather than a single large transaction like DBCC DBREINDEX.
Greg
Greg
July 31, 2007 at 10:46 am
Were you aware that the optimization process for a table and its associated indexes makes a complete new copy ?
The total amount of free space needs to be based whatever table has the highest value for the sum of:
a. the table size divied by the fill factor
b. the clustered index size time 1.2 divided by the fill factor
c. for each non-clustered index, the average index column plus the length of clustering key times the number of rows divided by the fill factor
As a rule of thumb for free space, take the table that uses the most space and multiple by 1.2
You will find that shrinking database files is counter productive as each time the optimization process runs, the file sizes will need to be increased.
Also, the transaction log size must be at least the size calculated above (largest table) and, if the database is not in simple mode, you must have some method of delaying the optimization process to allow time for the transaction backup to complete.
SQL = Scarcely Qualifies as a Language
July 31, 2007 at 12:36 pm
Thanks Gerg,
Do you know a way to have the option to use DBCC INDEXDEFRAG instead of DBCC DBREINDEX in the maintenance plan?
July 31, 2007 at 12:41 pm
Thanks Carl,
So you tell me that:
- It is better for me to remove the shrinking option (the Remove unused space from database file option in fact)
- And then find the biggest table in my db (max number of rows), calculate its size, multiple by 1.2 and use this value as the size for the transaction log.
Am I right?
August 1, 2007 at 5:31 pm
Carl is correct. Change the maintenance plan to NOT shrink the database.
Make sure you have 1.2 times the largest table's free space in the data file to re-index the tables. That should solve your problems.
August 2, 2007 at 10:06 am
Romain,
You don't get a choice in a maintenance plan. You'd have to create your own job.
Greg
Greg
August 7, 2007 at 7:46 am
Hi,
I have applied some settings to the database and maintenance plan according to the advices you gave me :
- For the database, I have set the transaction log to 'unrestricted growth'
- I have removed the option 'Removed unused space from database files' and only have 'Reorganize data and index pages/Change free space per page percentage to : 10%.
But the optimization job still failing...
The log gives me the info :
------------
Rebuilding indexes for table 'ACL'
...
Rebuilding indexes for table 'SensorData1'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table id: -607234322)' in database 'RealSecureDB' because the 'PRIMARY' filegroup is full.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
-----------
According to the taskpad view, this table have the following size :
dbo.SensorData1 : rows = 73708159; size = 25802392 KB, Index size = 4932152
Can you help me to solve this issue?
The log says that the primary filegroup is full.. ok but how can I add more free space and where? Should I create another filegroup and if yes, how?
Thanks for all good advices that you could give me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply