SQL Server Performance Forum – Threads Archive
Shrink mdf file
I have a database that accumulates about 1 Gb per month. Since the beginningof the year, it has accumulated about 5 Gb of data. I recently archived the
first 3 months of data, but the database is still showing 3.4 Gb of data if
I use sp_spaceused @updateusage = ‘true’ I tried the dbcc shrinkfile, but the database is not shrinking anywhere near
what I expect. I figure for April and May, the data should only be about
1.5 Gb at most. Can anyone tell me why the stored procedure is reporting the large amount of
data? Is there any way to get it to compact to what I expect? I have used DBCC DBREINDEX and INDEXDEFRAG, which helped a little bit with the size,
but nowhere near what I expected. It did help greatly with the fragmentation
however.
Does the database have AUTO_GROW option enabled?
You can use DBCC SHRINKDB to shrink the database and refer to books online for the shrinking information. What was that SP reporting large data? _________
Satya SKJ
The SP was sp_spaceused @updateusage = ‘true’ that I referred to at the beginning of the post. I have used DBCC SHRINKDATABASE and SHRINKFILE but neither have produced the results I have expected, probably because the results of the SP show a large percentage of data even after the DBREINDEX. Maybe I’m using the wrong fill factor. I’m using 70%.
How about the DB option referred above? _________
Satya SKJ
Sorry…AUTO_GROW is turned on.
Then remove it and try DBCC SHRINKDB the result will be required. _________
Satya SKJ
I’ll try it….what does that mean, "the result will be required"?
Whatever the values you specify in the SHRINKDB statement. _________
Satya SKJ
Well, I’ve tried the DBCC SHRINKDATABASE, as well as the DBCC SHRINKFILE again, as I’ve been doing, but this time turning off the AUTO GROW. It didn’t make a difference, as the database still thinks there is a larger amount of data than I think there should be. I neglected to stated I’m using SQL 2000, so SHRINKDB is outdated.
What parameters did you use in your DBCC statements and What is the reported DB size now ?
At the same time replace SHRINKDB with SHRINKDATABASE statement which functions in similar, if you want take auto shrink advantage then enable AUTO_SHRINK option to the database. _________
Satya SKJ
AUTO SHRINK puts a lot of overhead on the database. Usually, I optimize when performing complete backups. As for the previous question from VBKenya, I am using DBCC SHRINKFILE (dbname, 2000) – 2000 being 2 Gb, whereas the db size is reserved data index_size used
4559776 KB4215232 KB337560 KB6984 KB Since the database has only data from April and May at this point, I would expect it to have about 2 Gb worth of data, because from December through March the total size was about 4 Gb. I don’t understand why it reports 4.2 Gb worth of data.
DBCC SHOWCONTIG scanning ‘Section’ table…
Table: ‘Section’ (1637580872); index ID: 1, database ID: 8
TABLE level scan performed.
– Pages Scanned…………………………..: 111094
– Extents Scanned…………………………: 13942
– Extent Switches…………………………: 17330
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 80.13% [13887:17331]
– Logical Scan Fragmentation ………………: 8.05%
– Extent Scan Fragmentation ……………….: 62.50%
– Avg. Bytes Free per Page…………………: 1575.6
– Avg. Page Density (full)…………………: 80.53%
What is the fillfactor % which u are using for the database Rushendra
]]>