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 |
|
TomD
Starting Member
3 Posts |
 Posted - 2010-02-07 : 22:54:58
|
| Hi All, I am new here and the question might look quite "simple" but I hope someone can shed some thorough light on the subjectI have truncated the content of an unsued table on a live (lots of connections open) database. Prior to that of course I took a backup :) My question is - is there any thread that this might affet the database. Is there any possibility that there will be any problems? As I mentioned that table was unused at that time. Thanks all for your input/assistance. Cheers, Tom |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-02-08 : 02:03:27
|
| A truncate just deallocates all the pages of a table, it's a fast delete of all rows. won't affect anything or anyone except someone who wants the data that you've deleted.--Gail ShawSQL Server MVP |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-08 : 03:41:23
|
| If the table going to have some more data added? or are you expecting that it is "unused"?If "unused" I would rename the table instead, and if noone "screams"!! in a month then DROP it.When renaming tables / files in this way I add a suffix "_NU_20100208" ("NU" = Not Used) so that I know how long it has been since I marked it as "not used", and then I can judge when to Drop / Delete it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-08 : 05:08:37
|
| We also follow the same strategy that Kristen suggest. Unless we created and own the table, we add it to candidate list and monitor whether anythings accessing it. After a period if we dont see any activity in table, we go ahead and drop it |
|
|
|
TomD
Starting Member
3 Posts |
Posted - 2010-02-08 : 17:29:59
|
| Thanks for your advise all :) Much appreciated. Yes Kristen, the data will be added to that table once the service which populates it is started. To be precise 'unused' means to me that the table is not being called by any process once the certain service is stopped. My task is to delete the pages from that table, run the service which should populate it. Should my troubleshooting fail I would then INSTERT the pages to that table using a backup.My lack of SQL KB started to ask questions. Would there be any negative effect to the DB of that excersise. Thanks again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-09 : 08:17:39
|
| If the Service is stopped and it does not expect to find any data when it starts up, then it sounds fine to "empty" the table's data.When the service starts to add data (assuming it adds more than, say, a few hundred rows) then the statistics for the table will need updating - and probably the indexes will need rebuilding - otherwise any queries may be inefficient. |
|
|
|
TomD
Starting Member
3 Posts |
Posted - 2010-02-09 : 21:59:10
|
| Thanks for that Kristen :) |
|
|
|
|
|
|
|
|
Truncating a table content from the live db