| Author |
Topic |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2010-02-21 : 23:29:08
|
| I'm doing some massive deletes and it is causing the transaction logs to grow like crazy. Can I turn them off, run the deletes, then turn them back on? I tried changing recover mode to simple but that didn't help.Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-02-21 : 23:37:40
|
| Delete in Batches. |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 01:05:41
|
| If the deletes are one-off you can shrink the log back to its original "necessary" size afterwards.If the deletes are routine housekeeping then as Sodeep says: Delete in batches and launch a TLog backup after each batch.If you were able to turn the transactions off and then you had a power cut, or you had a deadlock with another process, or ... a whole range of things, then your database would be junk ... its the transaction log that enables a transaction to be rolled back if some error state occurs.If you run your database in Simple Recovery Model then the space for each transaction will be marked for reuse after it is committed (actually it isn't immediate, but that would stop the TLog growing indefinitely) - but if you do one massive delete that is one transaction and will still use up the same amount of space - multiple batches would have multiple, smaller, transactions.If you use Full Recovery Model then you need to use TLog backup to mark the transaction space as available for reuse.If you use Simple Recovery model then you can only restore to your last full backup. With Full Recovery Model you can restore up to any point in time up to your last TLog backup (and in many circumstances you can deliberately take a Tail/Final Tlog backup from the live database before starting the restore). This provides significantly better ability to recover from corruption caused by hardware failure - as well as human error - accidental deletion, for example. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-02-22 : 12:01:53
|
| Hi,I did it in batches as recommended and it worked fine. I have a related question. So I backed up our main database, restored it to a new database, deleted all of the clients in it except one, and now I want to send this "cleaned" version of the database to a client who wants to host this database themselves. The database is still 5 gigs in size even though I cleaned out almost all of the data. I think it is the transaction logs from the original backup. How do I get rid of those logs. I tried "shrink" but it didn't seem to do anything.Thanks,Criag |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 12:49:31
|
YOU need to Backup the TLog (which is probably uber-large with all the data you deleted  Then use SHRINK with the "Move pages to front of file" optionHopefully that will reduce the size of both MDF and LDF.If not have a look at the Sizes report here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762that should enable you to find out which tables have lots of rows etc., and that may guide you to some more data you can cull. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-02-22 : 14:10:31
|
quote:
Originally posted by Kristen
YOU need to Backup the TLog (which is probably uber-large with all the data you deleted Then use SHRINK with the "Move pages to front of file" optionHopefully that will reduce the size of both MDF and LDF.If not have a look at the Sizes report here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61762that should enable you to find out which tables have lots of rows etc., and that may guide you to some more data you can cull.
Hi Kristen,Actually, I just checked and the tlogs are only 8 megs. The database is showing as 5 gigs. But I deleted almost all of the data from the database so it can't be 5 gigs. I tried using the various "shrink" commands and it didn't help. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-02-22 : 14:12:00
|
| I ran the query you provided and got this...FileSizeMB UsedSpaceMB UnusedSpaceMB Type DBFileName5.06 1.45 3.61 Log absorb_log5082.31 4885.50 196.81 Data absorb5087.37 4886.95 200.42 *** Total for all files *** |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-02-22 : 14:15:39
|
| And this...TABLE_NAME TABLE_ROWS RESERVED DATA INDEX_SIZE UNUSED USED_MB USED_GBdbo.Activity 7506 2625568 2479704 70552 75312 2564.0313 2.5039What is up with that "Reserved" number? That seems to be the problem. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-22 : 14:55:51
|
| Does a SELECT COUNT(*) FROM MyTableNameindicate that there are still rows in each of those tables (which maybe you thought were deleted)? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-22 : 15:12:15
|
| Have you rebuilt the indexes (mainly the clustered index) after deleting?Never mind the reserved, the query shows 2.6 million rows in that table.--Gail ShawSQL Server MVP |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-22 : 15:14:47
|
| what is the output of this?SELECT * FROM sysfilesAlso, know that DBCC ShrinkDB cannot shrink it smaller than the original file size. DBCC ShrinkFile can though |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-02-22 : 15:16:39
|
| Hi. Thanks for the help guys.I deleted ALL the rows from the Activity table. I rebuilt the clustered index (and the non-clustered even though that is automatic). I then did a SHRINK and it did reduce the size a bit but still one gig for that table without any rows. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2010-02-22 : 15:17:42
|
quote:
Originally posted by russell
what is the output of this?SELECT * FROM sysfilesAlso, know that DBCC ShrinkDB cannot shrink it smaller than the original file size. DBCC ShrinkFile can though
fileid groupid size maxsize growth status perf name filename1 1 267248 -1 10 1048578 0 absorb S:\SQL Data\AbsorbRelease.mdf2 0 352 -1 10 1048642 0 absorb_log S:\SQL Logs\AbsorbRelease_1.ldf |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-23 : 02:42:44
|
Ferrethouse: You can put [code] tags around your figures - that will make them easier to read![code]fileid groupid size maxsize growth status perf name filename1 1 267248 -1 10 1048578 0 absorb S:\SQL Data\AbsorbRelease.mdf2 0 352 -1 10 1048642 0 absorb_log S:\SQL Logs\AbsorbRelease_1.ldf[/code] |
|
|
|
|
Turn off transaction logs?