SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Reclaim file space
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlpal2007
Posting Yak Master

184 Posts

Posted - 01/10/2013 :  05:11:29  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/10/2013 :  05:16:33  Show Profile  Visit webfred's Homepage  Reply with Quote
search for SHRINK


Too old to Rock'n'Roll too young to die.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/10/2013 :  05:17:54  Show Profile  Reply with Quote
http://blog.sqlauthority.com/2012/01/29/sql-server-reclaiming-space-back-from-database-quiz-puzzle-28-of-31/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sqlpal2007
Posting Yak Master

184 Posts

Posted - 01/10/2013 :  05:35:49  Show Profile  Reply with Quote
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.
Go to Top of Page

sqlpal2007
Posting Yak Master

184 Posts

Posted - 01/10/2013 :  10:47:32  Show Profile  Reply with Quote
I could shrink the data files upto 50%. Thanks
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 01/10/2013 :  13:01:22  Show Profile  Visit webfred's Homepage  Reply with Quote
welcome


Too old to Rock'n'Roll too young to die.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/11/2013 :  11:51:16  Show Profile  Reply with Quote
Donot shrink the database. It will cause huge fragmentation and performance will go down.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 01/11/2013 :  11:55:18  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.aspx

Or you can use Ola Hallengren's maintenance script:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/11/2013 :  11:59:38  Show Profile  Reply with Quote
quote:
Originally posted by robvolk

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.aspx

Or you can use Ola Hallengren's maintenance script:

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html



From my experience I have seen that REBUILDing index on huge databases after shrinking, log file will grow huge(which can be minimal with simple recovery model though) and data files also. It takes large number of hrs to complete it.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 01/11/2013 :  12:09:32  Show Profile  Visit robvolk's Homepage  Reply with Quote
Either operation will grow the log, but the log file can be shrunk without fragmenting any data.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/11/2013 :  14:18:53  Show Profile  Reply with Quote
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/
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 01/11/2013 :  14:48:24  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 01/15/2013 :  07:36:40  Show Profile  Reply with Quote
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.
Go to Top of Page

lopez
Starting Member

USA
8 Posts

Posted - 02/08/2013 :  05:59:28  Show Profile  Reply with Quote
Perform these operation
1. Defragment you database
2. If in SQL server any object or file is taking large space switch it into a new filegroup
3. Partitioning of the table & the database if require
Or try this
http://www.sqlservermanagement.net/
Go to Top of Page

srimami
Posting Yak Master

160 Posts

Posted - 02/08/2013 :  14:08:19  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000