November 21, 2008 at 12:29 pm
Hello, I am going to delete about 20 GB of data on a 120 GB database. After this I wanted to do dbcc shrink data file with NOTRUNCATE option because I do not want to release space into the system. Is there a way to actually make sure that the command worked except for this message. How to see if dbcc shrink file with NOTRUNCATE actually worked. Shouldn't used space value decrease and free space value increase? I tried to do it on test database and couldn't see any changes in data file stats.
November 21, 2008 at 1:38 pm
Don't shrink. Even with the notruncate, you will badly fragment your indexes. There's little gain in moving the free space to the end of the file.
There will be no change in used space, because you're no changing how much space is free, just where it is in the file
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
November 22, 2008 at 4:20 pm
thank you for your reply. For now I just did index defrag for some of the tables in db and see that used space isn't changed but free space grew for about 500MB.. But if I do shrink data file which is 60 GB now, will I also get increase in free space inside it?
November 22, 2008 at 5:42 pm
llokshin (11/22/2008)
For now I just did index defrag for some of the tables in db and see that used space isn't changed but free space grew for about 500MB.
Probably because the rebuild required the file to grow.
But if I do shrink data file which is 60 GB now, will I also get increase in free space inside it?
No. You're not deleting any data when doing a shrink, so why should the free space go up?
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
November 23, 2008 at 7:10 pm
HI, Gail, maybe I didn't make myself clear. I wanted to delete from some tables about 16 GB of data. I do it in small transactions, not all at once. like 1000 rows every hour. I wanted to do shrink file to try to reorganize data file and get all freed space at the end of the data file.
Lilia
November 23, 2008 at 10:42 pm
Hi Lilia,
I'm not sure what you are trying to achieve with this effort.
If the table in question has no clustered index, then the data pages will not be contiguous. However deletion of records won't cause pages to be deallocated. You would need to use a tablock hint when performing the delete. (search MSDN for "Deleting Rows from a Heap" to read more on this.)
If it has a clustered index, then the data is in the leaf nodes of this index. I wouldn't normally expect the index pages to be contiguous. There may be benefit in rebuilding the clustered index once you have done a significant amount of deletion so that the index is balanced and has minimal fragmentation.
After you've done your cleanup you'll have a certain number of free pages in the database file. These don't need to be contiguous to be useful or high performing. When SQL needs to use a new page, it will go to the Index Allocation Map and use any page marked as free.
What benefits do you believe will be obtained by shifting the free space around?
Regards,
Scott Pettman
November 25, 2008 at 9:29 am
llokshin (11/23/2008)
HI, Gail, maybe I didn't make myself clear. I wanted to delete from some tables about 16 GB of data. I do it in small transactions, not all at once. like 1000 rows every hour. I wanted to do shrink file to try to reorganize data file and get all freed space at the end of the data file.
You made yourself very clear. What I don't understand is what you're trying to gain. There is no advantage of having all the free space at the end of the file. SQL doesn't care where the space is.
All you're going to achieve by this is fragmenting your indexes, badly. There will be no space gain from having the space at the end of the file and no advantage in doing so.
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
November 25, 2008 at 9:32 am
Thank you Gail, at the end I didn't do it, just ran indexdefrag. Lilia
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply