Table too large

Question

Table too large

SkyBVI

SSCoach

Points: 15128
More actions
December 24, 2010 at 12:18 pm

#144262

Hi,
I found out that a table in a production database is too large (23GB) and I wanted to delete some date from it. It contains data from 2001 -2010 and has 23 million rows. When I try to do a select * ...the sql server hangs (not responding)...or hte query goes on and on.
When I go into the table and select indexes reorganize or rebuild or update statisitics, then also the sql server hangs (not responding).
How can I delete some unuseful rows(2001-2009).
Any alternatives?
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

It would be of assistance to those who wish to help if you can you post the table definition, and all indexes on that table, a limited amount of data which is representative of the data you desire to delete as well as the T-SQL statement you used in your attempts to delete unwanted rows, along with that statement's estimated execution plan.

Further questions:

1. Is your estimate of the number of rows that you are attempting to delete, is it 10 percent, 20 percent, 50 percent or greater than the total number of rows in the table?

2. Have you attempted to insert the number of rows which are to be retained into a new table, dropping the old table and then renaming the new table with the old table's name. (Be sure to have a valid backup before attempting this on a production database.)

3. Have you considered perfoming batch deletes? Refer to:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91179

to see sample code for batchhing deletes.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

homebrew01 SSC Guru Points: 55539 More actions · Answer 2

Are you trying to rebuild the indexes through the GUI ? You will have better performance if you script out the code, then run it as a job. You might want to put t-log backups in between some of the rebuilds to manage log growth. And 23G isn't huge. I have one that's about 600G in a 1.2T database

Dugi SSCoach Points: 17998 More actions · Answer 3

Why you are trying to SELECT all the records!?

While you are using the SQL Server 2005 you have possibility to do the partition table ...

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

sjimmo SSChampion Points: 11139 More actions · Answer 4

Please provide the schema, all indexes and the select that is hanging.

I have databases with tables much greater than this and do not have issues.

Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

I agree with the others. Knowing structure and queries in use for this will go miles in helping to resolve it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

SKYBVI (12/24/2010)
Hi,
I found out that a table in a production database is too large (23GB) and I wanted to delete some date from it. It contains data from 2001 -2010 and has 23 million rows. When I try to do a select * ...the sql server hangs (not responding)...or hte query goes on and on.
When I go into the table and select indexes reorganize or rebuild or update statisitics, then also the sql server hangs (not responding).
How can I delete some unuseful rows(2001-2009).
Any alternatives?
Regards,
Sushant

Have you ever actually previously done any database maintenance and integrity checks on this database?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SkyBVI SSCoach Points: 15128 More actions · Answer 7

I successfully deleted the unuseful rows from the table.

I used sp_spacesused 'tablename' to see how much space was freed up , and it was 10 gb.

The earlier size (of database) was 28 GB and now it should be around 18 GB.

But when I see, the mdf file is 28 GB now also.

I tried to go into dbname -->prop--> initial size was 28 gb, i changed it into 18 gb.

But it didnt change, it again came back to 28 gb..

The whole purpose of deleting the unuseful rows was free up some space, which is not acheived..

How can it be done??

HAPPY NEW YEAR

Regards,

Sushant

DBA

West Indies.

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 8

Is there any way other than shrinking??

as I want to avoid it.

HAPPY NEW YEAR AHEAD.

Regards,

Sushant

DBA

West Indies

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

SKYBVI (12/31/2010)
The whole purpose of deleting the unuseful rows was free up some space, which is not acheived..

You have freed up space. You've freed up space within the data file for SQL to reuse. If you want some of that space released to the file system, you need to shrink the data file. Don't forget to rebuild indexes after.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass