| Author |
Topic |
|
rupeshjs
Starting Member
5 Posts |
 Posted - 2007-05-16 : 09:17:03
|
| I'm new to SQL Server Maintainance. I need some disk space now. In my database, I have some table that has 7 year old data and I can get rid of that without any conequences. Could you please tell me the best course of action ?? I was thinking about:1. Dropping those tables.2. Recreating them again3. Running ShrinkDB on entire DB.However, this solution does not sound right or elegant to me. Could you please help me with that ? Thanks |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 09:53:35
|
| You can truncate data from those tables instead of dropping and recreating. |
 |
|
|
rupeshjs
Starting Member
5 Posts |
Posted - 2007-05-16 : 09:56:20
|
| But How do I table to delete all the rows older than certain time or may be reduce the size of Table by 90% deleting all the data ? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-16 : 10:10:56
|
| Remember, Truncate Table won't work if your tables are involved in foreign key relationships.Additionally, you may want to take a look at this discussion:[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084[/url]Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 10:37:01
|
| Need delete statement to remote certain rows. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-05-16 : 10:44:41
|
| DELETE FROM <your table here> WHERE <datecolumn> <= '20010101'Or some such. Where is my coffee.....[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
rupeshjs
Starting Member
5 Posts |
Posted - 2007-05-16 : 10:52:43
|
| 1. Do you think Delete will release all Hard Drive Space ?2. One of my table has more than 14 Million rows and I'm afraid it will take forever for SQL to delete those rows.3. One of my table does not have Date Column in it ? How do I deal with that table ?Thanks |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2007-05-16 : 11:29:22
|
| Do a forum search on deleting large sets of data. There have been some very good discussions on how to do this.[Signature]For fast help, follow this link:http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxLearn SQLhttp://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-05-16 : 12:19:21
|
| This might also help you..? [url]https://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx[/url] |
|
|
|
rupeshjs
Starting Member
5 Posts |
Posted - 2007-05-16 : 13:08:08
|
| I started DBCC SHRINKDATBASE on my machine. I hope to reduce size from 100 GB to 22 GB. (Data is only 20 GB and I shrinked to DB to 10%).Its been going on for an hour now. I'm afraid it may take lot longer. So, If I log-out/shut-down my client machine/connection , would it adversely affect ongoing operation of SHRINKDB ?? Thanks a ton for all your help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 13:09:36
|
| Deleting rows doesn't free up disk space, still need to shrink db afterwards. |
|
|
|
rupeshjs
Starting Member
5 Posts |
Posted - 2007-05-16 : 13:53:30
|
| I started DBCC SHRINKDATBASE on my machine. I hope to reduce size from 100 GB to 22 GB. (Data is only 20 GB and I shrinked to DB to 10%).Its been going on for an hour now. I'm afraid it may take lot longer. So, If I log-out/shut-down my client machine/connection , would it adversely affect ongoing operation of SHRINKDB ?? Thanks a ton for all your help. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-16 : 15:40:36
|
| It'll take a while to shrink 100gb db. |
|
|
|
|
Reduce DB Size