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 |
|
johann77
Starting Member
2 Posts |
 Posted - 2012-11-21 : 16:13:36
|
| I'm new to SQL Server. DB Admin quit. Boss is asking to reduce size of a table (don't know what table yet) kept for the last 7 years with over 2 million records. Our weekly task compares against the table and is taking more than 5 hours to do so, and by reducing the table we expect to reduce processing time, so he wants me:1. Backup current table (just in case)2. Then only keep the last 6 months of records and delete rest.Using SQL Server 2008 Management Studio. I come from a Visual FoxPro background. Never have used SQL Server. Sounds easy, but I have absolutely no idea on how to even start. Problem is, they want this done by the end of this month. I started reading a bit and learned among other things that tables are kept in .mdf files. Did a search and found several .mdf files. Although filename.mdf does not match table name but does match Database name. I can tell Database holds a little over 90 tables. I only want to delete records from 1 table, leave the rest of the tables alone.Once I find out exactly what table I need to reduce, I guess this is not just a matter of copy and paste the <File.mdf>? Can I achieve this through SQL syntax?Thanks, |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-21 : 16:46:15
|
| If this is important data, you really shouldn't be messing around with this server until you get some training on SQL Server. 2M records is not that big, you could probably tune the process and keep the data, though an archival strategy is not a bad idea. You need to get some training though, or hire someone with SQL Experience.-Chad |
 |
|
|
srimami
Posting Yak Master
160 Posts |
Posted - 2012-11-22 : 02:15:45
|
| What is the size of the table you are referring to? Find out by running the following query and post the results. If this table is occupying too much of space, you can go with deleting records with approval sign of your manager. sp_spaceuse 'TableName'Note: Deleting records will not release space to the disk; you need to either alter the table or update statistics to improve the query performance. Take help of your DBA while performing these actions. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-11-22 : 14:51:48
|
| Some archiving strategy sounds good. Before you attempt anything do you have a backup?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
johann77
Starting Member
2 Posts |
Posted - 2012-11-29 : 14:29:28
|
| Sorry about not replying sooner, had a family emergency. Anyways, size is about 3 TB, about 2M records. From what I've read, it is that big for a SQL table, but the PC the table is on is a Pentium IV, and by that I mean kind of old. All the company wants is to backup (or make a copy of the existing table) somehow index it on the entry date, and reduce it to the last 6 months of data. Like I said, I have no experience with SQL, but shouldn't there be a simple procedure to just make a copy of the table with a COPY command, and then just to some record deletes based on SELECT and DELETE commands?We have no DBA in house anymore.(Note: Deleting records will not release space to the disk; you need to either alter the table or update statistics to improve the query performance.)So my question to srimami: So reducing the table will not improve performance, even though the table will be reduced from 2M records to approx 200000?Thanks. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-11-29 : 15:59:25
|
| 2M Record is not a big table. We have table which are lot more than that.I would suggest to look at proper indexing and updating statistics and index maintenance. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-11-29 : 23:46:23
|
| That is an average row size of about 1.5MB (Unless my math is off). What is the structure of the table and what indexes exist?-Chad |
|
|
|
johnson_ef
Starting Member
16 Posts |
Posted - 2012-11-30 : 04:13:34
|
| What I understood from the requirement is, need to purge a part of the table.Let me clear one thing, we don't have an option of backup and and restore of specific tables with a command. yes we have workarounds copying the table with different name (as archive or backup).what I also assume from the query is, space is not a constraint (not mentioned to save, but save time for process), then I would prefer to keep an duplicate table in the DB. this will also help you to compare old records in case.What I may follow1) Import\Export option to take the backup\duplicate Table or, you can also use SELECT INTO option to do the same.After you make sure the duplicate table is ready with equalant data, then you can move to next step2) Check for the table's columns where any specific column which has any date\time value stored. Filter out with this option (date <= so and so date), you can apply this where condition based on your requirement in the live table (not the duplicate). If the row counts are more, please do it in bulk of 10000 or 20000 records applying appropriate where condition with appropriate date range.This will also help the log growth control.Once you achieve the purging using DELETE with WHERE clause, you need to apply 'UPDATEUSAGE' command. This will make sure clean up and allocate free up space to DB file or storage.Now you have 2 tables, one with only the records which has only required records and the one which you created as backup\archive with whole record.Hope this will help you, if my understanding is wrong, please pardon me.-Johnson |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-12-02 : 02:00:07
|
quote:
Originally posted by johnson_ef
What I understood from the requirement is, need to purge a part of the table.Let me clear one thing, we don't have an option of backup and and restore of specific tables with a command. yes we have workarounds copying the table with different name (as archive or backup).what I also assume from the query is, space is not a constraint (not mentioned to save, but save time for process), then I would prefer to keep an duplicate table in the DB. this will also help you to compare old records in case.What I may follow1) Import\Export option to take the backup\duplicate Table or, you can also use SELECT INTO option to do the same.After you make sure the duplicate table is ready with equalant data, then you can move to next step2) Check for the table's columns where any specific column which has any date\time value stored. Filter out with this option (date <= so and so date), you can apply this where condition based on your requirement in the live table (not the duplicate). If the row counts are more, please do it in bulk of 10000 or 20000 records applying appropriate where condition with appropriate date range.This will also help the log growth control.Once you achieve the purging using DELETE with WHERE clause, you need to apply 'UPDATEUSAGE' command. This will make sure clean up and allocate free up space to DB file or storage.Now you have 2 tables, one with only the records which has only required records and the one which you created as backup\archive with whole record.Hope this will help you, if my understanding is wrong, please pardon me.-Johnson
You can move the table to it's own filegroup, and backup/restore that filegroup. So in essence, you can backup/restore a table.-Chad |
|
|
|
Howard43Willard
Starting Member
8 Posts |
Posted - 2012-12-02 : 20:22:08
|
you need to either alter the table or update statistics to improve the query performance      |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-12-03 : 07:34:58
|
| Copy table to a new table:SELECT *INTO MyNewTableNameFROM MyOldTableNamethat will need AS MUCH SPACE as the current table takes up so make sure you have disk space enough. It will create a large transaction (for the duration of the task), and if the table is big may take SOME TIME - so best done during quiet time."size is about 3 TB, about 2M records."If that is the SIZE FOR THAT ONE TABLE?? then the design is probably rubbish and you are in all sorts of trouble before you start.You probably have an unlimited "notes" column in the table which people have been adding to for years, and SELECT * in the application, and just shifting the data around is killing you.COPYING the table, and DELETING to the old, stale, rows will be slow and disruptive, and may fill the disk / mess up your day.If the only issue is performance you may be better off optimising the system - e.g. adding an Index to a column that is crucial to the slow query. If nothing else that would buy you more time to figure out the best way of improving the 2.5TB / 2M row table ..." DB Admin quit"High risk that you will be next when the fallout, from being asked to do this job with no experience, has settled |
|
|
|
|
|
|
|
|
How to reduce a table in size