August 20, 2010 at 7:19 am
Hi
I have a table in my DB that is 30GB in size.
Once I drop/truncate the table, how can I reclaim the space used on the HD.
Here is the table def.
CREATE TABLE [dbo].[Table_TEMP](
[UNIQ_KEY] [nvarchar](2000) NULL,
[User_profile] [nvarchar](10) NULL,
[User_details] [nvarchar](50) NULL,
[File_name] [nvarchar](10) NULL,
[Date_time] [nvarchar](50) NULL,
[Program] [nvarchar](500) NULL,
[Change] [nvarchar](1000) NULL
) ON [PRIMARY]
Any help will be appreciated.
Thanks
August 20, 2010 at 7:49 am
DBCC SHRINKFILE
August 20, 2010 at 7:54 am
Fragmentation?
August 20, 2010 at 8:51 am
Using truncateonly will shrink the file to the last used extent
DBCC SHRINKFILE (LogicalFileName, truncateonly)
Specifying a size will shrink and re arrange the file to free potentially more space (2GB in this case)
DBCC SHRINKFILE (LogicalFileName, 2048)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 5, 2011 at 3:57 pm
How do you suggest reclaiming the space for a specific table? Is there any way or some steps we can do?
If I do the following, would there be any adverse effect and how would that impact on indexing?
Assume that the table is current in file 1 and I will move that to file 2.
step 1: Move a table (of size say 30 GB) to a specific file
Step 2: Truncate / drop that table
Step 3: Perform the shrink for that logical file
Would this free up the space from both the file group?
March 5, 2011 at 4:34 pm
Anamt (3/5/2011)
How do you suggest reclaiming the space for a specific table? Is there any way or some steps we can do?
Most of the time, you don't. You'd have had to have had the table stored in a dedicated filegroup to do this.
If I do the following, would there be any adverse effect and how would that impact on indexing?
Assume that the table is current in file 1 and I will move that to file 2.
step 1: Move a table (of size say 30 GB) to a specific file
Step 2: Truncate / drop that table
Step 3: Perform the shrink for that logical file
Would this free up the space from both the file group?
Well, if you generated a specific filegroup to move the table to, you would have emptied space out (note, not shrunk, merely emptied) from the primary when the table shifts to the secondary. You could then shrink the primary, truncate the table, and remove the secondary filegroup altogether.
You could do the exact same thing in the primary without the usage of a secondary. A table that large won't share many extents. A shrink is usually a one-off operation and you want to make sure you do reorganizations/reindexes afterwards to remove fragmentation caused by it. Unless the space is exorbinant compared to the rest of the database, this is usually an unwise process.
Are you seeing something specific you need to deal with, or are you just curious about what you can do?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 5, 2011 at 5:05 pm
Let me throw in a warning here. If you shrink the MDF file, you will frag the world because of the way that shrinkfile works. You absolutely MUST rebuild all indexes if you shrink the MDF file with anything other than truncate only. Rebuilding the indexes WILL cause the database to grow again. By how much depends on how large the other tables are.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply