Solved

MS SQL 2005 purging old data question

Posted on 2010-09-23
12
532 Views
Last Modified: 2012-05-10
I have two question regarding this process:

1 - My vcenter db is 20GB and I am currently deleting data older than 20 days.  Previosly 180 days.  It's going on for 4 hours already.  If not going to complete by the end of busienss tonight.  Can I stop it?  What's the ramification for stopping and restarting again? How can I shorten this process?

2 - I see the log is rasing to 67% of log space used.   What will happen when it'll hit 100%?  Will it crash?  Can I clean it somehow?
vcdb      912.4922       67.37798       0

Thanks in advance!!
0
Comment
Question by:Tiras25
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 250 total points
ID: 33749265
1.  If you stop it, the delete will roll back.  If  you are deleting the rows all at once, they will all roll back.

2.  When the log file is full it will attempt to grow (if it is allowed to do so by it's properties, and if there is disk space).  If it can grow sufficiently, you are better off letting it run IMO.  But in the future delete the rows in batches ... first delete rows older than 180 days, then older than 150, then 120, etc.


0
 
LVL 33

Expert Comment

by:knightEknight
ID: 33749278
2b ... and if the log file cannot grow, then the delete will roll back.
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 33749292
Actually, in the future if you only want to keep 20 days of data, you may consider first copying the data  you want to keep to a new table for holding, then truncating the original table, then copying the data back from the holding table into the original table.  This will be faster and far less resource intensive than deleting the rows.
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 33

Expert Comment

by:knightEknight
ID: 33749311
-- example:

select *
into my_holding_table
from my_table (nolock)
where mydate > '2010-09-01'
go

-- verify the data in the new table is correct, then do this:

truncate my_table
go

insert into my_table
select * from my_holding_table (nolock)

-- done
0
 
LVL 7

Expert Comment

by:mmr159
ID: 33749313
Are any of the server resources pegged (CPU/RAM/DISK)?

The ramification for stopping should be nothing other than lost time... assuming no transactions have been committed.  Restarting will probably not help you much.

When your log hits 100%, it will either
- grow if it is set to grow
- error if it is not
- error regardless of grow/no grow and you are out of physical disk space

Often is the case that you can do large operations in several smaller steps faster than in one big step.

You went from pruning > 180 days to > 20... 160 days could be a big difference from the norm (if you run it once per day... you're used to seeing 1 day of deletion)

I recommend you try an dividing up the delete job.
0
 
LVL 7

Expert Comment

by:mmr159
ID: 33749331
DANG, knightEknight....

I should have posted my answer in batches!! :)
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 33749337
Great minds think alike!  

(and so do ours)

:)
0
 
LVL 17

Author Comment

by:Tiras25
ID: 33749359
Yes the RAM pegged by sqlsvr.exe process because the db size is 20GB.  That's why I'm trying to shrink it.  

So if I stop the purge process all the deleted filed will roll back like nothing happened at all?

Thank you for recommendation in batches!  Now I know.
0
 
LVL 7

Assisted Solution

by:mmr159
mmr159 earned 250 total points
ID: 33750021
Yes, since no transaction has been committed, it will all roll back.
0
 
LVL 17

Author Closing Comment

by:Tiras25
ID: 33758043
!
0
 
LVL 7

Expert Comment

by:mmr159
ID: 33758063
How did it go?  Much better broken down?
0
 
LVL 17

Author Comment

by:Tiras25
ID: 33758323
Nope.  Truncating and then shriking worked.  Purging did not help and never finished..
0

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article outlines why you need to choose a backup solution that protects your entire environment – including your VMware ESXi and Microsoft Hyper-V virtualization hosts – not just your virtual machines.
This article provides a convenient collection of links to Microsoft provided Security Patches for operating systems that have reached their End of Life support cycle. Included operating systems covered by this article are Windows XP,  Windows Server…
Teach the user how to install ESXi 5.5 and configure the management network System Requirements: ESXi Installation:  Management Network Configuration: Management Network Testing:
Teach the user how to edit .vmx files to add advanced configuration options Open vSphere Web Client: Edit Settings for a VM: Choose VM Options -> Advanced: Add Configuration Parameters:

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question