Solved

MS SQL 2005 purging old data question

Posted on 2010-09-23
12
531 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

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…
Giving access to ESXi shell console is always an issue for IT departments to other Teams, or Projects. We need to find a way so that teams can use ESXTOP for their POCs, or tests without giving them the access to ESXi host shell console with a root …
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:
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 …

733 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