SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Reduce DB Size
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rupeshjs
Starting Member

5 Posts

Posted - 05/16/2007 :  09:17:03  Show Profile  Reply with Quote
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 again
3. 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
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/16/2007 :  09:53:35  Show Profile  Reply with Quote
You can truncate data from those tables instead of dropping and recreating.
Go to Top of Page

rupeshjs
Starting Member

5 Posts

Posted - 05/16/2007 :  09:56:20  Show Profile  Reply with Quote
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 ?
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5581 Posts

Posted - 05/16/2007 :  10:10:56  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77084

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Edited by - harsh_athalye on 05/16/2007 10:12:01
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/16/2007 :  10:37:01  Show Profile  Reply with Quote
Need delete statement to remote certain rows.
Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 05/16/2007 :  10:44:41  Show Profile  Reply with Quote
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.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

rupeshjs
Starting Member

5 Posts

Posted - 05/16/2007 :  10:52:43  Show Profile  Reply with Quote
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

Go to Top of Page

DonAtWork
Flowing Fount of Yak Knowledge

2161 Posts

Posted - 05/16/2007 :  11:29:22  Show Profile  Reply with Quote
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.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 05/16/2007 :  12:19:21  Show Profile  Reply with Quote
This might also help you..? https://blogs.msdn.com/sqlserverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx
Go to Top of Page

rupeshjs
Starting Member

5 Posts

Posted - 05/16/2007 :  13:08:08  Show Profile  Reply with Quote
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.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/16/2007 :  13:09:36  Show Profile  Reply with Quote
Deleting rows doesn't free up disk space, still need to shrink db afterwards.
Go to Top of Page

rupeshjs
Starting Member

5 Posts

Posted - 05/16/2007 :  13:53:30  Show Profile  Reply with Quote
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.
Go to Top of Page

rmiao
Flowing Fount of Yak Knowledge

USA
7266 Posts

Posted - 05/16/2007 :  15:40:36  Show Profile  Reply with Quote
It'll take a while to shrink 100gb db.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000