February 11, 2015 at 1:12 pm
Hi Sql Gurus,
I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.
If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger!!! I think I can shrink the log file and the data file. Is this the best way to do it? what are the best ways? I would appreciate if someone guide me with steps.
February 11, 2015 at 1:18 pm
Instead of deleting 5 years worth all at once, try deleting a smaller date range at a time, say one month at a time.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 11, 2015 at 1:19 pm
Set db to simple recovery and delete data in a loop in small chunks. If you can't set it to simple, run backup log after every delete.
But if it will be ongoing task, the best advice is to partition it.
February 11, 2015 at 1:24 pm
Thank you Alvin Ramard and SQL 1. How do I claim the space after the delete.
February 11, 2015 at 1:55 pm
dbcc shrinkfile(file_name, 12345) - target size in MB.
Again, do it by small chunks
February 11, 2015 at 2:47 pm
And make sure you rebuild your indexes on that table if you shrink the file, they will horribly fragmented.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 2, 2015 at 8:21 am
Little confused. Should I shrink database or shrink files (got one .mdf and two .ndf)??
March 2, 2015 at 8:31 am
Shrink database will shrink all the files, shrink file let's you specify the file you want to shrink. Unless you really need to reclaim drive space there's not too much value in shrinking a file that will just grow again.
March 9, 2015 at 5:52 am
Thanks Zzartin,
I am trying to RECLAIM the drive space, so should I go with shrink database or shrink file option? which one is safe and best practice?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply