| Author |
Topic |
|
srimami
Posting Yak Master
160 Posts |
 Posted - 2012-02-23 : 04:35:36
|
| Hi,Can anyone please let me know how to claim unused free space at table level. The database size is of 4.6TB, recently I deleted some data which is of almost 1.5TB but my unused space grown upto 2.5TB leaving me short of space on my drives. How do I claim this usused on to OS Disk space?Any quick help is hghly appreciated.Thanks,Sri. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-02-23 : 05:31:29
|
| There are no indexes created on this DB (any of the tables). Would it be of any help if I create Cluster Index and run my daily load normal? Will this re-arrange the pages and use unused henceforth? Thanks,Sri. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-02-28 : 09:13:47
|
| The tables are loaded using SSIS package and would drastically slow down my performance if I create any Index on these. However, can you please let me know if this worksCreate another DB and use insert into tables, rename this as old and delete the original DB. Will this work? Can I claim unused space if I implement this logic?Thanks,Sri. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-28 : 09:18:33
|
| All tables should have clustered indexes. You can drop nonclustered indexes for a data load and recreate them afterwards.As for the create new DB, that's major overkill at that size, it will take hours if not days.Heaps don't release pages properly, that's one reason to have a clustered index. Create a clustered index on an appropriate column and don't drop it afterwards.--Gail ShawSQL Server MVP |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-02-28 : 11:02:04
|
| I tried creating cluster index but it failed after running for 16 hours. So this option is ruled out to me, though it is tedious and time consuming process will creating new DB and moving tables help?Thanks,Sri. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-28 : 11:56:20
|
| Failed for what reason? Transaction log full? If so, expand the log (or switch to bulk-logged recovery) and create a clustered index. Copying tables has the same log impact as creating a clustered index.--Gail ShawSQL Server MVP |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-02-28 : 12:14:06
|
| Yes, expanded log files and DB files but we have no space left. Even this failed cluster index consumed 450GB which is the reason we are requesting the client to add few more drives so that we can start a fresh DB.Thanks |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-28 : 12:52:32
|
| If you can get more space, build a clustered index. Otherwise you'll be doing this again and again and again because of the way heaps reuse space.Oh, and a new DB and copying the data over will require about the same amount of space in the new DB's data and log files.--Gail ShawSQL Server MVP |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-03-02 : 04:09:19
|
| Hi Gail,Thank you for your response. I got many problems with the existing DB so wanted to create another database and design filegroups/files, create a cluster Index on the empty tables and load the data from old DB/tables.Will this solve my problem of unused space is I am concerned about?Thanks,Sri. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-03-02 : 04:38:43
|
| Yes, but it's not a free lunch, the log space required will be the size of the data if in full recovery, maybe even in the other recovery models too.--Gail ShawSQL Server MVP |
|
|
|
|
How to claim unused space from tables