SQL Server Performance Forum – Threads Archive

unused space is too large, how to shirnk it ?

Dear ALL, I have some database whose size is very big, up to 3GB. But I know the data and index is not so many. Run "sp_spaceused " on one of it "MRP_01", I get the return:
database_name database_size unallocated space
————————— —————— ——————
MRP_01 2244.94 MB 290.31 MB reserved data index_size unused
—————— —————— —————— ——————
2000256 KB 661352 KB 115088 KB 1223816 KB
so, the unused space "eat" a lot of space of reserved. I run "DBCC shinkdatabase " and "DBCC shinkfile", but no exciting result. Then I create a new database named "MRP_Test" with default paramater, and import all the objects & data from "MRP_01".
Run " sp_spaceused " on "MRP_Test", return the result:
database_name database_size unallocated space
—————- —————— ——————
MRP_Test 736.69 MB 61.30 MB reserved data index_size unused
————- —————— —————— ——–
675024 KB 577952 KB 92920 KB 4152 KB
The Database reserved space and database_size tone down. I want to know: 1.What can grow my database ? and why "unused" is too big?
2.What can I do to keep my database thin? Thanks.

Did you try using DBCC UPDATEUSAGE to see if that helps to change the space used shown in sp_spaceused?
Or did you fixed initial size of database file that much while creating database?

1) Follow as specified by Harsh and ensure reindex jobs are scheduled.
2) You can shrink the database by using DBCC SHRINKDATABASE if you feel the unused space is more and its required to OS for other applications. Take help from thishttp://vyaskn.tripod.com/track_sql_database_file_growth.htm&e=7415 link to tract the dB growth and take necessary action. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forums This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

harsh sr, thanks. Before run sp_spaceused I have run DBCC UPDATEUSAGE. and Now , I find a way: Create clustered index on all of the table, and then drop it . Then shrinkdatabase. any idea , thanks.

No need to create on all the tables, run DBREINDEX instead. And to get the optimum figure run DBCC UPDATEUSAGE then follow as suggested in books online to shrink the database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forums This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

OK, satya, thanks. As your said, that means INDEX cause the unused space? NOT DATA ? Thanks.

Yes it could be index and perform DBCC checks to see the results.
Also run SP_HELPDB to get other details. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi ya, Do you have a lot of delete or update activity happening on some tables? If so then check the space usage of these tables, you may be getting fragmentation as rows are removed or moved into bigger slots on a page, thus leaving holes in pages…? Cheers
Twan

hi, Twan I think it must be what you said. the unused space in every table is big too. TableName RowsCount Reserved(KB)Data(KB)Index_size(KB)Unused(KB)
INVLA 108,310 769,456 86,664 16,464666,328
MOCTE 44,334 322,32839,144 4,040 279,144
MOCTC 12,064 242,568 29,304 1,336 211,928
INVTB 18,642 143,552 18,832 696 124,024
MOCTG18,630 121,928 15,112 1,472 105,344
INVTA3,630 120,136 14,760 448 104,928
MOCTB37,093 121,304 14,160 5,376 101,768
INVLC21,280 13,584 11,376 1,512 696
MOCTD4,912 88,264 10,752 528 76,984
MOCTF5,777 84,496 10,200568 73,728
BOMMD13,212 30,864 9,760 2,168 18,936
MOCTA6,324 77,384 9,144 1,336 66,904
INVMF7,752 77,768 8,936 1,192 67,640
COPTH9,700 66,264 8,584 856 56,824
INVMB5,668 37,008 7,904 976 28,128
MOCTI7,112 61,400 7,120 1,592 52,688
COPTG2,183 51,672 6,440 360 44,872
INVLB28,303 10,832 6,008 1,576 3,248
INVTC28,283 8,016 5,272 688 2,056
INVTD21,985 16,016 5,040 1,536 9,440

Schedule the DBREINDEX and may choose to run DBCC INDEXDEFRAG to get rid of the fragmentation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

You may want to turn on a profiler trace recording for batch completed and RPC completed to get an idea of the type of statements on the database. Look for odd things like the application doing a delete then insert instead of update, etc. How long has it taken for the database to get this much unused space? Are there any clustered indexes on the tables? Each table should ideally have one, especially if it is update intensive. They will help keep tables under control as it transforms them to a b-tree rather than a heap. If there is no clustered index on a table, then as you’ve found out creating an idex and dropping it will ‘tidy up’ (i.e. recreate) the table and any non-clustered indexes. dbreindex won’t work in that circumstance since the problem is with the table which won’t get reorganised by dbreindex unless there is a clustered index. Cheers
Twan

Try running DBCC SHOWCONTIG on your tables. This will show you if your tables are heavily fragmented, and which are the worse. This is better indicator than sp_spaceused about ‘wasted’ space. Once you have that information, you can run dbcc reindex or indexdefrag as is needed. If you lookup dbcc showcontig in sql books online there is even a script there that will "Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment all the indexes in a database" BOL is a wonderful tool with some great hidden tidbits. Chris

]]>