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 |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2009-06-03 : 12:49:09
|
| I had a database grow too much for a local backup to fit onthe server's disk drives. Long-story-short ... I did a shrink database ( dbname, 0 ) to get the database small enough that a full backup could be taken. That worked, but now (I assume) I have no free space in the database. I am worried about insert performance and want to put the free space back into the pages. I did some tests on a test machine. Maintenance Plan "optimizations" wants to shrink the file. I wantto INCREASE the free space. I put 99% as a test. This did, in fact double (or more) the "pages used" as reported by shrinkdatabase(dbname,tablular) on MOST of the .mdf files. But it only slightlyincreased the pages used on the DEFAULT filegroup file.Altering the file MAXSIZE did not affect the result. Alteringthe SIZE of the file did give a large filesize, but the PAGES USED value is the same.some stats on the test (display in courier will be easier to read):shrinkfile(dbname,tablular) -- before running Maint Plandbid fileid currSz minSz pgsUsed EstPgs7 1 317160 128 293464 2934567 3 128 128 8 87 4 128 128 0 07 5 128 128 16 167 6 128 128 8 8RUN MAINT PLAN 99%FREEAfter:dbid fileid currSz minSz pgsUsed EstPgs7 1 322816 128 293600 2936007 3 128 128 16 167 4 128 128 24 247 5 128 128 64 647 6 128 128 40 40 (fileid #2 not reported. that's the log)Any tips on how to put more free space into the data pages? |
|
|
|
|
|
Add Freespace SQL 2000