September 21, 2006 at 5:23 am
Hi folks,
I have a problem with my database. Last night I ran these scripts to reduce database file size:
dbcc shrinkdatabase (dbname)
exec dbname..sp_MSforeachtable 'dbcc dbreindex ("?")'
after running script for table size information, I got this result:
tablenamerowcount:0,Tablesize:1774008 KB, Data_space_used:0 KB, Index_space_used:1455304 KB, Unused_space:318704 KB
script for table size information:
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
Micheal Soelter
1/24/03
DESCRIPTION
Returns Table Size Information
SORTING USAGE
@sort bit values
0 = Alphabetically by table name
1 = Sorted by total space used by table
*/
DECLARE @cmdstr varchar(100)
DECLARE @sort bit
SELECT @sort = 0 /* Edit this value for sorting options */
/* DO NOT EDIT ANY CODE BELOW THIS LINE */
--Create Temporary Table
CREATE TABLE #TempTable
([Table_Name] varchar(50),
Row_Count int,
Table_Size varchar(50),
Data_Space_Used varchar(50),
Index_Space_Used varchar(50),
Unused_Space varchar(50)
)
--Create Stored Procedure String
SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''
--Populate Tempoary Table
INSERT INTO #TempTable EXEC(@cmdstr)
--Determine sorting method
IF @sort = 0
BEGIN
--Retrieve Table Data and Sort Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve Table Data and Sort by the size of the Table*/
SELECT * FROM #TempTable ORDER BY Table_Size DESC
END
--Delete Temporay Table
DROP TABLE #TempTable
How can I free up all unused index space ???
I tryed to drop indexes from table and recreate them again, but with no result !
thanx
September 21, 2006 at 5:33 am
Why do you feel the need to shrink your database - it will cause fragmentation and degrade performance. Leave your database at a size where it has working space to work and grow. So how big is your database - in the terrabyte size?
You need to read up on sql internals, try Ken Henderson's Guru's Guide or inside sql 2000 for an explanation.
I expect you also have bad ntfs fragmentation. If you want to free up index space remove the indexes.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 21, 2006 at 5:45 am
hmm, actually I need to shrink db after large deletion of junk data. Before any changes was made, db was at 25 GB with transaction log, now has 4,8 GB. But I don't understand after running status script, why is index space allocation so large, and data space 0 MB ?
I thought, that 0 MB of data in table should be related with index space. And when the shrink frees up all unused allocated blank space, why is index space still allocated ?
September 21, 2006 at 5:53 am
there's a number of reasons, make sure you do a dbcc updateusage too.
So your shrink worked I assume, so if you rebuild all the indexes, stats and updateusage, it should all sort its self out. Was some of the data you removed text or blob columns? if so then that's different. You could run a check alloc and look at the balance of mixed and full extents. Also a showcontig will indicate if you have any fragmentation, use all indexes and table results ( check BOL ) to get all the information.
sp_spaceused is generally reagrded as not being too accurate - I'm not sure as I rarely ever use it. I'm sure ms had a kb with an improved version of this proc, perhaps another member will be able to help?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
September 22, 2006 at 12:05 am
uf, thanks for your advice !
The command "dbcc updateusage" helped solve this problem !
Actually the database was shrinked as well as indexes, but only stupid statistics was not updated !!!
funny
September 25, 2006 at 8:48 am
yeah most seem to miss out dbcc updateusage but it's quite important to run and I never found it did any harm. glad it resolved your problem.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply