Solved

MS SQL 2005 purging old data question

Posted on 2010-09-23
12
527 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 250 total points
Comment Utility
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
Comment Utility
2b ... and if the log file cannot grow, then the delete will roll back.
0
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
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
 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
-- 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
Comment Utility
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
Comment Utility
DANG, knightEknight....

I should have posted my answer in batches!! :)
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 33

Expert Comment

by:knightEknight
Comment Utility
Great minds think alike!  

(and so do ours)

:)
0
 
LVL 17

Author Comment

by:Tiras25
Comment Utility
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
Comment Utility
Yes, since no transaction has been committed, it will all roll back.
0
 
LVL 17

Author Closing Comment

by:Tiras25
Comment Utility
!
0
 
LVL 7

Expert Comment

by:mmr159
Comment Utility
How did it go?  Much better broken down?
0
 
LVL 17

Author Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

It Is not possible to enable LLDP in vSwitch(at least is not supported by VMware), so in this article we will enable this, and also go trough how to enabled CDP and how to get this information in vSwitches and also in vDS.
In this step by step tutorial with screenshots, we will show you HOW TO: Enable SSH Remote Access on a VMware vSphere Hypervisor 6.5 (ESXi 6.5). This is important if you need to enable SSH remote access for additional troubleshooting of the ESXi hos…
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:
This video shows you how to use a vSphere client to connect to your ESX host as the root user. Demonstrates the basic connection of bypassing certification set up. Demonstrates how to access the traditional view to begin managing your virtual mac…

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now