Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
peter_dtm
Starting Member
7 Posts |
 Posted - 2010-02-12 : 07:21:04
|
| [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table id: -1028542572)' in database 'BizWare' because the 'PRIMARY' filegroup is full.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.I have seen elsewhere the suggestion to turn off AutoShrink - (I have limited remote access to the server so can not check now) I am pretty sure this is already off.The error is generated after running the standard maintenance job as created by the wizard. It occurs here :2] Database BizWare: Index Rebuild (leaving 10%% free space)...Rebuilding indexes for table 'afBatchCatalogueBatchStarted' ...etc list of tables that are processed until :we get the following error :[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1105: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not allocate space for object '(SYSTEM table id: -1028542572)' in database 'BizWare' because the 'PRIMARY' filegroup is full.[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.any ideas ?- We used to include the master db - removed that from the job so at least the old trn & bak files are now deletedPete-not everything digital is better ! - |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 07:57:35
|
Check the size of MSDB database - that may be huge (and could be sorted out to make some space)Standard Maintenance plan will use REINDEX which will, in effect, take a complete copy of the index to a new part of the disk file - and then "release" the space from the original copy. This requires twice as much space as the index itself, of course.It is possible to defragment the index instead, which is "in situ" and thus less disk-hungry.Also, reindex will be putting a lot of data into the transaction log. So that probably means that your LDF file is sized to accommodate the whole Reindex; that can be eased by taking more frequent Tlog backups during maintenance window - we increase ours from every 15 minutes to every 2 minutes during maintenance. Defragging can be set up to only defrag indexes that are more than, say, 20% fragmented - that reduces the number of tables that are actually refreshed, and that reduced TLog requirement too.You could also rebuild indexes A-M on Mon, Wed and Fri and indexes N-Z on Tue, Thur and Sat (I'm only drawing an analogy here, I'm sure you get my meaning  )You may also have the Autogrow set to, say, 10%. If the DB is large then extending it by 10% may be a huge addition. Changing that to a fixed MB size is better (SQL 2000 grinds to a halt when making large file extensions if the database has any sort of load at the same time) |
 |
|
|
peter_dtm
Starting Member
7 Posts |
Posted - 2010-02-12 : 08:23:08
|
| Thanks Kristen; These tables are fairly small & there is plenty of room on the disk; most of the maintenance effort is manage the Tlog file & keep it small - we don't actually need to back up most of the data. The whole lot is a mere 500MB - though prior to our engagement the Tlog had grown to 5 GB over several years.I suspect that the Tlog is now too small to handle the index from what you have written; I have a remote visit booked Monday so I can have a good dig around then. Pete-not everything digital is better ! - |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-12 : 08:44:09
|
If the TLog is set to AutoGrow (AND there is disk space) then the Reindex should not raise any error (but file may extend occasionally).Error message looks like MDF data file is full, rather than LDF / TLog file.Make sure AutoShrink is off - that should only be used for the Devil's own work! It will cause fragmentation of the files which will wreck performance.Check LDF and MDF are set to AutoGrow by a sensible FIXED AMOUNT (NOT percentage)LDF should reasonably be up to, about, 120% of MDF size.Much bigger than that and something is usually wrong - a) it once grew due to massive, one-time, DELETE and was never re-shrunk or b) TLog backups are not being taken / not taken often enough (15 minutes is a good interval, once a day is a sign of a badly configured system!!).Smaller than that is rare - unless a) no REINDEX housekeeping or b) AutoShrink being usedCheck if MSDB has MDF/LDF files bigger and a few 10's of MB. If so it has no housekeeping to delete backup logs that were made years ago!! (Do NOT use the MS Provided SProc to clear those down, it will take weeks to run [I kid you not  ] - there are other solutions available) |
|
|
|
peter_dtm
Starting Member
7 Posts |
Posted - 2010-02-15 : 05:53:26
|
| Kristenthanks - I have been through & turned off all Autoshrinks (there were a couple) - I take it from your comment that in normal usage we should NEVER have that turned on ; and there is/are some specific needs where it would be needed; which hopefullu I'll never need ???msdb has this : unrestricted 27MBtrn unrestricted 3MBSimple Recovery ModeThe failing dbase turned out to be fixed size; & it was within less than 10% of the fixed size with 10% growth allowed : restrict growth is on to 14000MB current size 12853MB - growth by 10%trn 383mb 10% growth unrestrictedauto shrink was on - is now offmake mfd unrestricted growthYou recommend using absolute growth amounts rather than % - I can see why that would be a good idea; but how on earth do you get a decent estimate of how much ? - a lot of the SQL DBase we are seeing NEVER had any maintenance set up; so we are seeing truly massive trn (500MB dbase with 5 to 15 GIG trns); our developers/product managers/sales people think that SQL maintenance is 'someone else's problem'.....Pete-not everything digital is better ! - |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-15 : 06:49:56
|
| "I take it from your comment that in normal usage we should NEVER have that turned on ; and there is/are some specific needs where it would be needed; which hopefullu I'll never need ???"I can't think of any good usage.SQL finds it does not have enough space and extends the file.AutoShrink chops it back againSQL re-extend the file again when it does the same job tomorrow ...Results in file fragmentation, and also SQL 2000 takes a significant amount of time to extend that file [its better in SQL2005]However, if someone does a one-time massive DELETE, or somesuch, and that extends the files then a one-time shrink to get them back to reasonable size is good.The time to Restore a SQL 2000 database is proportional to the filesize. A TLog file that is massive (but unused) will dramatically increase the restore time - and you will have to have that much disk space available - there is NO way to force it to restore to a small filesize. Worse, if the TLog has not been backed up there may be a HUGE amount of time during restore working out what needs to be rolled back / forwards. We've had some clients who fell into that hole - even after we had advised them that it needed sorting out."how on earth do you get a decent estimate of how much"Well ... you don't really want it to need to extend more often than once a week - otherwise it extends with lots of small fragments. So you need something to monitor what the file sizes are, over time (including Mark One Eyeball if that is all you've got!), and then setting a reasonable size.We used to have performance issues with 100MB increase on a 1GB database, when the server was busy (i.e. using SQL 2000) - it would literally lock everyone out with Timeouts for 30 minutes!! A 10% increase on your 12GB database would be worse (assuming that the server was busy at the time ...)"our developers/product managers/sales people think that SQL maintenance is 'someone else's problem'.."Make them accountable for the cost of finding & fixing when it goes wrong, and the cost of customer dissatisfaction - do they even know if you have lost customers, or they have bad mouthed your product to potential customers, because it performs badly? - when just a simple maintenance routine would improve performance by 10 fold probably?We take a different view. We want to be responsible for database maintenance rather than leaving it in the hands of someone "with a little knowledge to the point that they are dangerous". Its a reasonable money earner, and means that we don't have emergency, interruptive, fixes - that we would have if clients found it necessary to scream! |
|
|
|
peter_dtm
Starting Member
7 Posts |
Posted - 2010-02-15 : 07:27:27
|
| Kristenthanks for all the help & comments; this tech support engineer really appreciates the help given. I suspect I will be back for more - unfortunately the company sees SQL as an underlying platform that is the responsibility of the client (client's IT) but doesn't bother having a way of TELLING the client that they have a can of worms - even where there are on the ball IT departments; they are not always aware of the 'new kid' that is not part of the IT infrastructure. Process & Automation engineers have fought long & hard to stop IT departments trashing production servers by treating them like 'ordinary' office environment machines ....any way - again thanks for the help - I'll now try & write this all up as a sensible TechNote our Field Service Engineers & Project engineers can follow & apply ...Pete-not everything digital is better ! - |
|
|
|
|
|
|
|
|
Could not allocate space for object ..