| Author |
Topic |
|
sqlpal2007
Posting Yak Master
200 Posts |
 Posted - 2013-01-10 : 05:11:29
|
| Hello All,My database size was very big so I truncated the audit tables and that means half of the data is gone from the database. Still the database size didn't reduce. Then I backed up the database. The backup file was way less than the previous day backup size. Then I restored that file but still it takes on the same size. The data file didn't get the free space from the data truncation operation.Can you please tell me how to reclaim that free space? I tried everyway but no luck.Thanks,pam |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-10 : 05:16:33
|
search for SHRINK
Too old to Rock'n'Roll too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-10 : 05:17:54
|
| http://blog.sqlauthority.com/2012/01/29/sql-server-reclaiming-space-back-from-database-quiz-puzzle-28-of-31/------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2013-01-10 : 05:35:49
|
| Thanks visakh. The there is no answer to my post. The audit data I truncated has no indexes on them. I don;t know how to gain that space back. |
|
|
|
sqlpal2007
Posting Yak Master
200 Posts |
Posted - 2013-01-10 : 10:47:32
|
| I could shrink the data files upto 50%. Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2013-01-10 : 13:01:22
|
welcome 
Too old to Rock'n'Roll too young to die. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-11 : 11:51:16
|
| Donot shrink the database. It will cause huge fragmentation and performance will go down. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-11 : 11:55:18
|
| If you do have to shrink the database, make sure to run index reorganization (or rebuild) on your tables afterward. Syntax is here: http://technet.microsoft.com/en-us/library/ms188388.aspxOr you can use Ola Hallengren's maintenance script:http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-11 : 12:09:32
|
| Either operation will grow the log, but the log file can be shrunk without fragmenting any data. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-11 : 14:18:53
|
| Yah that's true.I actually follow this one from Paul Randal instead of shrinking the db.http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-01-11 : 14:48:24
|
| I agree, the only downside with Paul's alternative is that it uses more disk space, and if you are truly out of disk it's not a viable option. And unless you have Enterprise Edition, your indexes and data may be inaccessible during the rebuild. |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-01-15 : 07:36:40
|
| Hi Pam,Any truncate/delete/drop on Sql Server doesn't release space back to disk. Truncate/Delete/Drop should follow DBCC Shrinkfile to reclaim the space back to disk.Note: Do not use UI interface, instead use DBCC Shrinkfile query.Thanks,Sri. |
|
|
|
lopez
Starting Member
8 Posts |
Posted - 2013-02-08 : 05:59:28
|
| Perform these operation 1. Defragment you database2. If in SQL server any object or file is taking large space switch it into a new filegroup3. Partitioning of the table & the database if requireOr try this http://www.sqlservermanagement.net/ |
|
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2013-02-08 : 14:08:19
|
| To be more precise; Drop/Truncate/Delete on Sql Server will not release space from Disk. You need to perform DBCC Shrinkfile when ever you do these operations to get space back to disk. |
|
|
|
|
Reclaim file space