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... :-)Hi,

Why drop the table? why not delete the data you have just taken, then you will never lose any data|||...simply because it takes such an insane amount of time. A regular "delete from log where logtypeId = stuff-I-don't-need" takes forever. :( The complete post is here, if you want the details: http://www.dbforums.com/showthread.php?threadid=979910|||You can open a transaction, and then do SELECT * FROM TBL WITH (TABLOCK) WHERE 1=2 as you first statement. Then, instead of moving 1-3% of data to a different permanent table, you can put it into a temptable (whether # or @., doesn't matter,) then truncate the original (instead of dropping it,) and putting the data from your temptable back into the original.|||Even truncate takes quite a few seconds, allthough I guess that's related to some locking issues and not the truncate process in it self.

The method involving dropping the table takes about 3-5 seconds, and is much faster than select-away-and-truncate. It sort of works, the problem with that solution is that PK, indexes, rights and what not are gone too. This can of course be re-created with a number of SQL sentences, but it's not exactly ideal......

But I cannot understand why deleting takes so much time? I'm assuming the cause of this problem is that about 50 clients are constantly logging into the table while I'm deleting.

I'm not an expert sql-stored procedure maker, anyone who whould take a crack at setting up something for me with locking (and whatever else) that would work? Would help me a lot!! :-)|||I can send you a contract, and upon signing you'll never have to deal with it again :cool:sql

Localized MSDE 2000

Hi,
Can anyone tell me why localized MSDEs are necessary? Does
it have anything to do with storing localized strings?
Would the English version of an MSDE instance installed on
a - let's say - Chinese version of Windows OS, have
problems storing and working with Chinese characters?
hi Lee,
"Lee" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:19cdd01c4225d$c8ee9b80$a601280a@.phx.gbl...
> Hi,
> Can anyone tell me why localized MSDEs are necessary? Does
> it have anything to do with storing localized strings?
> Would the English version of an MSDE instance installed on
> a - let's say - Chinese version of Windows OS, have
> problems storing and working with Chinese characters?
nope... Localized versions have nothing to do with data storage...
Localized versions are only versions with localized strings contained in the
localized resource libraries...
All localized versions can store unicode data and set sort order and
collations as needed...
further info about collation can be found starting at
http://msdn.microsoft.com/library/de...ar_da_1pwz.asp
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thank you very much for your answer.
Lee

>--Original Message--
>hi Lee,
>"Lee" <anonymous@.discussions.microsoft.com> ha scritto
nel messaggio[vbcol=seagreen]
>news:19cdd01c4225d$c8ee9b80$a601280a@.phx.gbl...
Does[vbcol=seagreen]
on
>nope... Localized versions have nothing to do with data
storage...
>Localized versions are only versions with localized
strings contained in the
>localized resource libraries...
>All localized versions can store unicode data and set
sort order and
>collations as needed...
>further info about collation can be found starting at
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/architec/8_ar_da_1pwz.asp
>hth
>--
>Andrea Montanari (Microsoft MVP - SQL Server)
>http://www.asql.biz/DbaMgr.shtm
http://italy.mvps.org
>DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
>(my vb6+sql-dmo little try to provide MS MSDE 1.0 and
MSDE 2000 a visual
>interface)
>-- remove DMO to reply
>.
>

Local Admin group necessary for DBA's?

Assume that the DBA's windows login is in the sysadmin fixed server role, if
a DBA uses Windows Authentication to manage a SQL Server Enteriprise
(6.5,7.0,200), are there any problems with removing the DBA's account from
the local administrators group of each SQL Server?
Any supporting documentation or articles for removing the DBA from the admin
Group?
Any expected annoyances for the DBA?Hi,
You can't restrict the OS administrators fully, because they have full
rights on all folders and registry keys inwhich SQL server resides.
But, you can restrict them to an extend by removing "System Admin" role
from BUILTIN/ADMINISTRATORS account.
" I had problems in the below when I removed "Syadmin role" from
BuildIN/Administrators. So I have given back the sysadmin role to solve the
issue.
1. FULL Text Indexing
2. Maintenance Plans
So do a test in test server for couple of weeks and then implement in
Production server.
Known issues after removal , Some things to be aware of:
Q237604 PRB: SQL Server Agent Does Not Start and Displays Error 18456
Q295034 FIX: MSSearch Takes 100% CPU if BUILTIN\Administrators Removed
Q317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs "
Did i answer ur question?
Thanks
Hari
SQL Server MVP
"Johnnie Scott" <JohnnieScott@.discussions.microsoft.com> wrote in message
news:84A82B7C-6244-45C1-94A2-C64DE85C31FD@.microsoft.com...
> Assume that the DBA's windows login is in the sysadmin fixed server role,
> if
> a DBA uses Windows Authentication to manage a SQL Server Enteriprise
> (6.5,7.0,200), are there any problems with removing the DBA's account from
> the local administrators group of each SQL Server?
> Any supporting documentation or articles for removing the DBA from the
> admin
> Group?
> Any expected annoyances for the DBA?|||No, the question I'm really trying to answer is regarding removing the DBA's
windows account from the Local Administrators Group on the the server.
"Hari Prasad" wrote:

> Hi,
> You can't restrict the OS administrators fully, because they have full
> rights on all folders and registry keys inwhich SQL server resides.
> But, you can restrict them to an extend by removing "System Admin" role
> from BUILTIN/ADMINISTRATORS account.
>
> " I had problems in the below when I removed "Syadmin role" from
> BuildIN/Administrators. So I have given back the sysadmin role to solve t
he
> issue.
> 1. FULL Text Indexing
> 2. Maintenance Plans
> So do a test in test server for couple of weeks and then implement in
> Production server.
> Known issues after removal , Some things to be aware of:
> Q237604 PRB: SQL Server Agent Does Not Start and Displays Error 18456
> Q295034 FIX: MSSearch Takes 100% CPU if BUILTIN\Administrators Removed
> Q317746 PRB: SQL Server Full-Text Search Does Not Populate Catalogs "
> Did i answer ur question?
> --
> Thanks
> Hari
> SQL Server MVP
> "Johnnie Scott" <JohnnieScott@.discussions.microsoft.com> wrote in message
> news:84A82B7C-6244-45C1-94A2-C64DE85C31FD@.microsoft.com...
>
>|||We have this situation on some servers and it works to varying degree's.
There are "annoyances" - you have to work closely with the Windows admins to
define the required shares so that the DBA's can manage database
files,backups,logs etc. Management of a server "by committee" is tricky so
make sure you have the processes in place for the windows + sql admins to be
able to do their jobs. And remember that if the SQL Server Service account
is a member of the local admins group on the server so are the DBA's (via
xp_cmdshell) regardless of whether they are in the local admins group or not
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Johnnie Scott" <JohnnieScott@.discussions.microsoft.com> wrote in message
news:84A82B7C-6244-45C1-94A2-C64DE85C31FD@.microsoft.com...
> Assume that the DBA's windows login is in the sysadmin fixed server role,
> if
> a DBA uses Windows Authentication to manage a SQL Server Enteriprise
> (6.5,7.0,200), are there any problems with removing the DBA's account from
> the local administrators group of each SQL Server?
> Any supporting documentation or articles for removing the DBA from the
> admin
> Group?
> Any expected annoyances for the DBA?