Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dwoolaver75
Starting Member
6 Posts |
 Posted - 2009-06-10 : 17:08:53
|
| Hello all, First let me apologize for making this post in the first place as I am sure it has been discussed again over and over. However, I have been searching the forum on and off for over an hour and still have not exactly what I looking for. I am running out of disk space on my server. My MDF file is about 50gig and ldf(log) file is 250MB. The database is set to simple recovery. (most posts about disk space seem to deal with out of hand ldf log files because mode is set to full and not simple). We have identified millions of rows stored in the DB that the business does not require to be kept. I am going to delete millions of rows from several tables. What is the best way to recover some disk space and leave the db stable after doing this? Is there any method of purging records that better than another?Thanks much! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-06-10 : 22:53:09
|
| You can purge data and shrink the data file to recover space. Make sure you rebuild indexes and room for log to grow(not much since you are keeping in simple recovery model). |
 |
|
|
dwoolaver75
Starting Member
6 Posts |
Posted - 2009-06-11 : 10:40:51
|
| OK thanks. So here's what I am thinking I will do. Purge the current log by doing:(Again, I am already in simple mode)BACKUP LOG "mydatabase" WITH TRUNCATE_ONLYdbcc shrinkfile ("mydatabase_log", 50)I will then do my data purges which if I use "delete from table where"will make the log file get huge. After finished removing unneeded data repeat the:BACKUP LOG "mydatabase" WITH TRUNCATE_ONLYdbcc shrinkfile ("mydatabase_log", 50)Can I repeat this after every sizable purge? If not I may run out of disk space. and then after purges do:dbcc shrinkfile ("mydatabase_data", ?)Is this the best way to go about this fairly simple task?If this is an example of my results from sp_spacedused what would go in the ? above?database_name......database_size.....unallocated spacemydatabase.........50813.13 MB.......17671.77 MB reserved........data............index_size......unused33695528 KB.....21972960 KB.....11679416 KB.....43152 KB |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-11 : 10:48:36
|
| probably want to do some sort of paged deletes.Delete in batches of 500 rows or similar at a time. Better performance.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dwoolaver75
Starting Member
6 Posts |
Posted - 2009-06-11 : 11:16:15
|
| Thanks Charlie! I found your post on how to do paged deletes and it is a little beyond me without actually stretching my brain. :) But that's ok, I'm not too concerned with time it take to do the purges. This is a very low use db at the moment. I will probably run deletes in batches manually. I obviously am not a DBA, but have to play the part of one when needed. I just want to make sure I don't leave the database in some "unstable" state. I have read many warnings against shrinking the database but I think I am ok with the method I propose above since I mass deleting data? |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-11 : 11:43:30
|
| Your outline sounds good.Just make sure you have a backup before you start issuing those DELETE commands!Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
dwoolaver75
Starting Member
6 Posts |
Posted - 2009-06-11 : 11:47:43
|
| Yep! Backup is taking place now. Thanks again. |
|
|
|
|
|
|
|
|
Big one time data purge need to recover disk space