Solved

MS sql sever 2005 - In recovery

Posted on 2010-11-19
6
286 Views
Last Modified: 2012-05-10
Hi,
Running a large delete sql in taking sql server to auto recovery mode after which the DB isnt available for long time (hours).
My understanding the is that running large quries clutters the db buffer space and when reaching a certain point it goes on recovery mode (thus cleaning the buffer) - please correct
Issue is that we are running a web from the DB so cant affrod the down time.
What is an appropriate fix to this?
thanks.
0
Comment
Question by:SameerMirza
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
you must batch up your deletes and I won't go beyond 10000 rows per batch delete even if your table is partitioned.
deletes are affected by:

clustered index/pk on the table
foreign keys - pretty big impact
I/O
recovery mode of your DB - if possible simple would be prefered but if you run 24/7 web site against it this is unlikely to be available.

I need to do similar thing to purge expired data and for some tables (100million plus clients) the batch is no larger than 20-50 records for delets but it goes silent and pretty fast. also if possible include hints in your delete - I use ROWLOCK
0
 

Author Comment

by:SameerMirza
Comment Utility
Problem is that my log backup disk space is running out of memory.
I have 10z of millions of record to delete. How can u delete them in such short batches
If that's the best way then could you please paste link on how to? :)
Also about the rowlock
Thanks
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
This looks challenging indeed but let me ask a question:

Is this something like a purge process that you need to setup and the innitial volumes are high then once you catch up the delete volumes are smaller or
This is something you need to constantly do on such large volumes?

For the first option I can provide some batch example including a chekpoint and update stats in it so your log file may be somewhat under control due to the volumes to delete however you will need to test that CAUTIOSLY in your live environment for speed/impact in your web site UI due to locking/blocking. You could start the deletes from small to larger batch in a SQL job.

For the second option if you need to constantly delete such volumes I suggest/recommand partitioning then you could switch out unwanted large volumes faster with less impact - I use this method for archiving obsolite data by date range or inactive clients.

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:SameerMirza
Comment Utility
Disk space issue - Logs filling the disk space
0
 
LVL 39

Accepted Solution

by:
lcohan earned 500 total points
Comment Utility
Is it your db t-log where you do the deletes or tempdb t-log? I use the command below in all may batch deletes relying on temp tables and you could include an explicit CHECKPOINT (and even a "update statistics table_name") after a larger number of rows were deleted -like 100,000 or 1,000,000 rows:

EXEC sp_executesql N'USE TEMPDB; CHECKPOINT;'
0
 

Author Closing Comment

by:SameerMirza
Comment Utility
thanks
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

743 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

18 Experts available now in Live!

Get 1:1 Help Now