?
Solved

MS SQL 2005 purging old data question

Posted on 2010-09-23
12
Medium Priority
?
533 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 1000 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
Create the perfect environment for any meeting

You might have a modern environment with all sorts of high-tech equipment, but what makes it worthwhile is how you seamlessly bring together the presentation with audio, video and lighting. The ATEN Control System provides integrated control and system automation.

 
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 1000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

HOW TO: Upload an ISO image to a VMware datastore for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5) using the vSphere Host Client, and checking its MD5 checksum signature is correct.  It's a good idea to compare checksums, because many installat…
Ransomware is a malware that is again in the list of security  concerns. Not only for companies, but also for Government security and  even at personal use. IT departments should be aware and have the right  knowledge to how to fight it.
Teach the user how to configure vSphere clusters to support the VMware FT feature Open vSphere Web Client: Verify vSphere HA is enabled: Verify netowrking for vMotion and FT Logging is in place or create it: Turn On FT for a virtual machine: Verify …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

771 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