LOCK Table Necessary In Stored Procedure?

Hi,

I have some questions about locking tables in stored procedures. I got some excellent tips from my last post, but since it's sort of a different problem I figured I'd post it separately.

I have a large log table I need to do manual, periodic clean-up process on, which basically is purging unneccessary log-entries. The idea is to select out the 1-3% I need to another table, drop the old table, and rename the new table to the old one.

The problem is that I most likely will need to lock the entire table while I do all the clean-up stuff. If a client manages to add things inbetween this is going on, I could end up loosing data.


The table looks like this:
Logid PK
LogTypeID -- what category
LogValue --
LogTime -- when it occurred

My imaginary stored procedure looks something like this:

CREATE PROCEDURE ShrinkDB AS
-- 1)
"lock table log" -- do I have to do something like this?

-- 2)
select * into log_keep FROM Log where
(
logtypeid <> 2020 AND -- activity played
logtypeid <> 5020 AND -- database connected
-- ....etc et..... about 10 different things I don't need to keep
or logtime > dateadd(d, -1, getdate()) -- keep everything from last 24 hours
)
-- 3)
drop table log

-- 4)
EXEC sp_rename 'log_keep', 'log'
GO

I'm not able to figure out wether I need to run some sort of "Lock" command or not, or if everything inside a stored procedure automatically is locked. If so, I shouldn't worry about loosing any data I guess??


Hopefully it works that way, but if not I assume I'll run into these two problems:

- If a client logs immediately after the Selecet, could data be logged AFTER the select, but BEFORE the drop table-command? In which case I guess I would loose data?

- Immediately after the drop table log in step 3, there's no table named 'log' in my database. 'Log' will be "created" when I run step 4. This means I could perhaps loose data since the client for a brief moment can't log data to the 'log' table?


Hopefully someone can clearify this for me, I've read the documentation, but I don't feel too sure on this subject.... :-)