Excessive unused space
-
I am currently testing a data import program which deletes records from files in a SQL 2000 database, and then inserts new ones. The database has over 2000 tables, my program affects about 30 of them and there are over one million records that get deleted and reinserted. During repeated testing, I noticed two events: (1) that the database would expand to fill the space allocated for it, and (2) that after several runs I would start getting SQL timeouts. I also noticed that these two events seem to be linked – when I increased the allocated space, the timeouts would disappear until such point that the database had filled the allocated space again. When I investigated further (using ‘sp_spaceused’) I found the following: 07.80 Gb Data 01.70 Gb Index 27.10 Gb Unused 36.60 Gb Reserved 01.00 Gb Unallocated 38.60 Gb Database Size It seems to me that the culprit is that ‘unused’ space. If I could eliminate that, I should be able to get the database down to a manageable size … about 10.5 Gb. I spent some time researching how to do that. I tried various procedures, including DBCC SHRINKFILE, SHRINKDATABASE, UPDATEUSAGE and DBREINDEX scripts, but nothing works. I ran a third-party SQL defrag utility but, although it reported that some tables/indexes could benefit by defragging, after I ran the defrag routine I noticed that nothing actually got defragged. (This could be because it is an eval version - even tho its supposed to be fully functioning, or it could be symptomatic of a larger problem with the database). At this point I'm stymied. Can anyone help me? Ian Dennis Business Analyst, Assets HANSEN
-
I am currently testing a data import program which deletes records from files in a SQL 2000 database, and then inserts new ones. The database has over 2000 tables, my program affects about 30 of them and there are over one million records that get deleted and reinserted. During repeated testing, I noticed two events: (1) that the database would expand to fill the space allocated for it, and (2) that after several runs I would start getting SQL timeouts. I also noticed that these two events seem to be linked – when I increased the allocated space, the timeouts would disappear until such point that the database had filled the allocated space again. When I investigated further (using ‘sp_spaceused’) I found the following: 07.80 Gb Data 01.70 Gb Index 27.10 Gb Unused 36.60 Gb Reserved 01.00 Gb Unallocated 38.60 Gb Database Size It seems to me that the culprit is that ‘unused’ space. If I could eliminate that, I should be able to get the database down to a manageable size … about 10.5 Gb. I spent some time researching how to do that. I tried various procedures, including DBCC SHRINKFILE, SHRINKDATABASE, UPDATEUSAGE and DBREINDEX scripts, but nothing works. I ran a third-party SQL defrag utility but, although it reported that some tables/indexes could benefit by defragging, after I ran the defrag routine I noticed that nothing actually got defragged. (This could be because it is an eval version - even tho its supposed to be fully functioning, or it could be symptomatic of a larger problem with the database). At this point I'm stymied. Can anyone help me? Ian Dennis Business Analyst, Assets HANSEN
What recovery model (simple, full or bulk-logged) is your database using?
-
What recovery model (simple, full or bulk-logged) is your database using?
I'm using a simple recovery model (I've just discovered!) but I need to state that it is the data file (mdf) that is growing out of control, not the log file (ldf).
-
I'm using a simple recovery model (I've just discovered!) but I need to state that it is the data file (mdf) that is growing out of control, not the log file (ldf).
1. The
sp_spaceusedprocedure has an@updateusageparameter - try to runEXEC sp_spaceused @updateusage = N'TRUE'and check if the results will not change. 2. If you have time and some free disk space, make a full backup of the database and check its size - is it around 10GB? -
1. The
sp_spaceusedprocedure has an@updateusageparameter - try to runEXEC sp_spaceused @updateusage = N'TRUE'and check if the results will not change. 2. If you have time and some free disk space, make a full backup of the database and check its size - is it around 10GB?I've used @updateusage = 'TRUE' and it doesn't make any difference. I've totally killed the database, reloaded it, and started testing my program again. It's growing already ... Reload database - DbSize:06,033, Unalloc:0,515, Reserved:05,341, Data:3,786, Index:1,493, Unused:0,061 Run conversion. - DbSize:18,558, Unalloc:3,511, Reserved:14,869, Data:5,049, Index:1,815, Unused:8,006 (sizes in Mb)
-
I've used @updateusage = 'TRUE' and it doesn't make any difference. I've totally killed the database, reloaded it, and started testing my program again. It's growing already ... Reload database - DbSize:06,033, Unalloc:0,515, Reserved:05,341, Data:3,786, Index:1,493, Unused:0,061 Run conversion. - DbSize:18,558, Unalloc:3,511, Reserved:14,869, Data:5,049, Index:1,815, Unused:8,006 (sizes in Mb)
When you used
DBCC SHRINKFILE, did you specifytarget_sizeparameter to force pages reorganization before releasing unused space? From BOL[^]: A. Shrinking a data file to a specified target size The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.USE UserDB; GO DBCC SHRINKFILE (DataFile1, 7); GO -
When you used
DBCC SHRINKFILE, did you specifytarget_sizeparameter to force pages reorganization before releasing unused space? From BOL[^]: A. Shrinking a data file to a specified target size The following example shrinks the size of a data file named DataFile1 in the UserDB user database to 7 MB.USE UserDB; GO DBCC SHRINKFILE (DataFile1, 7); GOI don't remember now. I've got some more info which I'll post in a new message in this same thread so, please view that. Thanks
-
I am currently testing a data import program which deletes records from files in a SQL 2000 database, and then inserts new ones. The database has over 2000 tables, my program affects about 30 of them and there are over one million records that get deleted and reinserted. During repeated testing, I noticed two events: (1) that the database would expand to fill the space allocated for it, and (2) that after several runs I would start getting SQL timeouts. I also noticed that these two events seem to be linked – when I increased the allocated space, the timeouts would disappear until such point that the database had filled the allocated space again. When I investigated further (using ‘sp_spaceused’) I found the following: 07.80 Gb Data 01.70 Gb Index 27.10 Gb Unused 36.60 Gb Reserved 01.00 Gb Unallocated 38.60 Gb Database Size It seems to me that the culprit is that ‘unused’ space. If I could eliminate that, I should be able to get the database down to a manageable size … about 10.5 Gb. I spent some time researching how to do that. I tried various procedures, including DBCC SHRINKFILE, SHRINKDATABASE, UPDATEUSAGE and DBREINDEX scripts, but nothing works. I ran a third-party SQL defrag utility but, although it reported that some tables/indexes could benefit by defragging, after I ran the defrag routine I noticed that nothing actually got defragged. (This could be because it is an eval version - even tho its supposed to be fully functioning, or it could be symptomatic of a larger problem with the database). At this point I'm stymied. Can anyone help me? Ian Dennis Business Analyst, Assets HANSEN
I've been running a series of further tests. My first thought is that my problem was connected with deleting records, as that is something I'm doing that is, perhaps, unusual. For instance, I delete 25,000 sewer records out of a table and insert 25,000 new sewer records, then I delete 27,000 water records out of the same table and insert 27,000 new water records, etc. But I'm checking the progress of my program with
sp_spaceused @updateusage='true'and it shows the unused area of my database growing when I am inserting records only ... i.e., no deleting is taking place!!! Basically, what I am seeing is the unallocated size diminishing at the same speed as the unused area grows, but when the unallocated size gets to zero, it just increases the datafile size (at which point, the unallocated space gets big again). It never releases any of the unused area, nor can I claw any of that back from any of the maintenance routines I've described above. The only thing to add is the application is written in VB.Net. I've recently learnt that a colleague wrote another VB.Net conversion program which exhibited the same symptoms, even although his was operating on an Oracle database, not a SQL Server one. Does that make any lightbulbs go off in anyone's brain? -
I've been running a series of further tests. My first thought is that my problem was connected with deleting records, as that is something I'm doing that is, perhaps, unusual. For instance, I delete 25,000 sewer records out of a table and insert 25,000 new sewer records, then I delete 27,000 water records out of the same table and insert 27,000 new water records, etc. But I'm checking the progress of my program with
sp_spaceused @updateusage='true'and it shows the unused area of my database growing when I am inserting records only ... i.e., no deleting is taking place!!! Basically, what I am seeing is the unallocated size diminishing at the same speed as the unused area grows, but when the unallocated size gets to zero, it just increases the datafile size (at which point, the unallocated space gets big again). It never releases any of the unused area, nor can I claw any of that back from any of the maintenance routines I've described above. The only thing to add is the application is written in VB.Net. I've recently learnt that a colleague wrote another VB.Net conversion program which exhibited the same symptoms, even although his was operating on an Oracle database, not a SQL Server one. Does that make any lightbulbs go off in anyone's brain?Could it be this problem[^]?
-
I am currently testing a data import program which deletes records from files in a SQL 2000 database, and then inserts new ones. The database has over 2000 tables, my program affects about 30 of them and there are over one million records that get deleted and reinserted. During repeated testing, I noticed two events: (1) that the database would expand to fill the space allocated for it, and (2) that after several runs I would start getting SQL timeouts. I also noticed that these two events seem to be linked – when I increased the allocated space, the timeouts would disappear until such point that the database had filled the allocated space again. When I investigated further (using ‘sp_spaceused’) I found the following: 07.80 Gb Data 01.70 Gb Index 27.10 Gb Unused 36.60 Gb Reserved 01.00 Gb Unallocated 38.60 Gb Database Size It seems to me that the culprit is that ‘unused’ space. If I could eliminate that, I should be able to get the database down to a manageable size … about 10.5 Gb. I spent some time researching how to do that. I tried various procedures, including DBCC SHRINKFILE, SHRINKDATABASE, UPDATEUSAGE and DBREINDEX scripts, but nothing works. I ran a third-party SQL defrag utility but, although it reported that some tables/indexes could benefit by defragging, after I ran the defrag routine I noticed that nothing actually got defragged. (This could be because it is an eval version - even tho its supposed to be fully functioning, or it could be symptomatic of a larger problem with the database). At this point I'm stymied. Can anyone help me? Ian Dennis Business Analyst, Assets HANSEN
The problem seems to be centered around bugs reported by Microsoft as articles 934378 and 924947. Additionally, someone had set the database autogrowth setting to 10% and so towards the end of the conversion run the autogrow was taking longer and longer (resizing by 10% of 39Gb takes a lot longer than 10% of 6Gb) and therefore my inserts were timing out - to add misery to confusion. My eventual solution was: (1) Initially size the database to 40Gb. This solved the problem mentioned above (2) Make sure each table inserted into had at least one clustered index. This worked around the bug mentioned in article 924947 (3) When the conversion was finished, run a routine to rebuild ALL the indexes for ALL the tables. This worked around the bug mentioned in article 934378. (4) Finally, do a DBCC SHRINKDATABASE Without these fixes, the conversion left database = 39Gb, unused = 27Gb. With these fixes, the conversion left database = 12Gb, unused = 4Gb. It took me a long time to find (each run of the conversion takes 10 hours) but I ended up learning a lot about SQL Server!
-
Could it be this problem[^]?
I've finally figured it out. See my other post in this thread. Thanks for all your help!