July 2, 2012 at 3:59 am
Dear All,
In our environment Due to one table Db is growing high.Due to this disk space is is high..We have truncated the table.But still disk is showing same size.
So we have shrieked the DB.But still it is showing same size.Kindly support me on this issue.
July 3, 2012 at 2:45 pm
Minto Minto(quendans) (7/2/2012)
Dear All,In our environment Due to one table Db is growing high.Due to this disk space is is high..We have truncated the table.But still disk is showing same size.
So we have shrieked the DB.But still it is showing same size.Kindly support me on this issue.
Wait a while. Truncating deallocates pages but as I understand it the space is not freed until the ghost cleanup process gets around to visiting each record on the page, and that does not happen on any discernable timeline in terms of estimating when all your records will be cleaned up. The cleanup task starts up every 5 seconds looking for ghosted records, but there is no guarantee all your records will be cleaned up in one pass. Read more here:
Inside the Storage Engine: Ghost cleanup in depth by Paul Randal
You can force all ghost records to be cleaned up by rebuilding all indexes on the table. If it's a heap you may need to rebuild the table, not sure how ghost records work with heaps.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 3, 2012 at 3:11 pm
opc.three (7/3/2012)
If it's a heap you may need to rebuild the table, not sure how ghost records work with heaps.
They don't. Ghost records only appear in indexes.
http://www.sqlskills.com/blogs/paul/post/ghost-cleanup-redux.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2012 at 8:17 am
GilaMonster (7/3/2012)
opc.three (7/3/2012)
If it's a heap you may need to rebuild the table, not sure how ghost records work with heaps.They don't. Ghost records only appear in indexes.
http://www.sqlskills.com/blogs/paul/post/ghost-cleanup-redux.aspx
Thanks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 4, 2012 at 9:24 am
You can shrink the specific file for that data, but make sure you leave some free space for maintenance operations (Reindexing) and data growth.
July 4, 2012 at 9:33 am
How much space is free in your data file? Use the below query (version-controlled copy here) to find out this information:
use YourDatabase;
go
;with DatabaseFileInfo as
(
select
db_name(database_id) as database_name,
name as db_file_name,
size * 8 as total_size_kb,
fileproperty(name, 'SpaceUsed') * 8 as space_used_kb
from sys.master_files
where database_id = db_id()
)
select
database_name,
db_file_name,
total_size_kb,
space_used_kb,
total_size_kb - space_used_kb as free_space_kb,
cast(space_used_kb * 1.0 / total_size_kb * 100.0 as decimal(5, 2))
as percent_used
from DatabaseFileInfo
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply