| Author |
Topic |
|
PatMc
Starting Member
16 Posts |
 Posted - 2010-04-12 : 09:37:10
|
| Hi All,I have a table that is growing on a daily basis that i would like to 1. reduce the size of the table but regain the space so that it usable. 2. is there some way it can be done via a daily/weekly maintenance job.If this does not make sense i am sorry as my SQL skills are lacking. so if you need more info please ask.ThanksPat |
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-12 : 09:54:17
|
| Hi,Could we have a little more info?1) why is the table growing? What data is in the table and why do you store it if you don't need it?2) How much data are we talking about?3) what is the table structure? (maybe this would be a good case for partitioning)....Don't want to advise with the information given.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
 |
|
|
PatMc
Starting Member
16 Posts |
Posted - 2010-04-12 : 10:17:10
|
| HiThanks for taking the time to help me.1) why is the table growing? What data is in the table and why do you store it if you don't need it?The data in this table is from cold Fusion client variability. It is stored for the app but we don't need all that is in there. 2) How much data are we talking about?There are over 11 million rows with over 2.9 gigs of data. I know it not much,but we need to keep this small3) what is the table structure? (maybe this would be a good case for partitioning)....Table structure is3 columns called datatypeCFID charDATA textLVISIT datetime All are nullable not much else. I think the text in DATA might be what is taking all the room but I am not sureThanks |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-12 : 10:22:15
|
| Ok.Is there a PRIMARY KEY / CLUSTERED INDEX on the table? -- looks like this table could be a heap which wouldn't be good. How do you determine what date is old / not required? (the LVISIT column?) If there is a way to determine what isn't needed then you could set up a job to delete the rows that aren't required.What version of SQL SERVER do you have?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
PatMc
Starting Member
16 Posts |
Posted - 2010-04-12 : 10:32:58
|
| Hi,There is no key on this table not sure how to check if it is heap or cluster.SQL Server 2000 is the version |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-12 : 10:36:41
|
| if there's no key I'd be surprised if there was a CLUSTERED INDEX (you can check in management studio in the indexes view if you drill into the table in object explorer).The crux of your issue then is just working out which rows you can get rid of. How do you determine what is an unwanted row?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-04-12 : 10:39:44
|
quote:
Originally posted by Transact CharlieHow do you determine what date is old / not required?
That's the $64 million question. If you can't logically identify which records are unwanted, then there's not a lot you can do. Can you write a select statement that selects the records you want to delete? Also, is this a one time only job, or are new errant records continually being added to the table? If it's only a one time job, then might I make a suggestion - create a new database, and within it a table with the same structure as your table. Copy the records from your table to the new table before deleting them, and then backup the new database, and burn it to a DVD. Then you can drop the new database. That way, if you realise in a month's time that you've delete the wrong record, then at least you'll have a rollback plan that's reasonably easy to implement.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
PatMc
Starting Member
16 Posts |
Posted - 2010-04-12 : 13:20:02
|
| according to the developer who work the Cold fusion code we only need to keep the data in that table for 3 days. So I am thinking i can use lvisit date and just have the later stuff removed. My other question is if i just use a SQL query to remove the old data will the space then be reusable or will it be like white space that i can't use.Pat |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-04-12 : 13:33:05
|
The space left by the deleted data can be reused by that database.If that isn't what you want then you should shrink the database after deleteing the data.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-04-13 : 04:00:38
|
| one minor point. If the table has no clustered index then you'll maybe see a performance drop when you delete data. Can you add a clustered index to any of the columns? LVISIT seems like a good candidate if it always stored increasing dates.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
PatMc
Starting Member
16 Posts |
Posted - 2010-04-13 : 14:07:06
|
| Thanks everyone I was able to get this done with your help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-13 : 15:31:50
|
| If there is no candidate for a Clustered Index then add an IDENTITY column (which you are, otherwise, not using at all) and make that the Clustered Index - that way the space from deleted records will be reclaimed more efficiently, and REINDEX will clean up the space released by deletions ensuring that it is reused effectively (otherwise, with just a HEAP table, I reckon you will have a lot of fragmentation) |
|
|
|
|
reducing table size question