INSERT - issue

Question

INSERT - issue

AlanB

SSC Eights!

Points: 845
More actions
September 28, 2006 at 8:26 am

#92510

Any ideas with this problem would be appreciated.
Each day tables in a database are updated by truncating the tables, and inserting records from a corresponding tables on another server. This normally works fine because it is done at night when users are not accessing the data.
Occasionally things run late, and the server tries to run the truncate/insert when users are accessing the data. This can result in big delays, presumably because of locking.
Is there an easy way to temporarily make the table unavailable to users so that the insert can run? Or is there an easier way of doing this?
Thanks for any help.
AlanB

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Loner SSC-Insane Points: 21279 More actions · Answer 1

We have the same problem, so first before the update, we find out which user is using the database and kill the session. If all your users under one role name, then you can use

sp_droprolemember @rolename = 'role'

after the insert then

use

sp_addrolemember @rolename = 'role'

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

alter database x set restricted_user

should stop anyone who isn't dbo dbcreator or sysadmin getting in the database

MVDBA

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Make sure you are not using any of the above authentication users (dbo, dbcreator or sysadmin) as mentioned by Mike in your connection string for the application.

Prasad Bhogadi
www.inforaise.com

AlanB SSC Eights! Points: 845 More actions · Answer 4

Presumably whatever approach you take to renew the data (truncate/insert, copy database/table, replication etc.) it's dependant on dropping connections that would get in the way.

Thanks for your quick replies!

Alan

Dan Murray Right there with Babe Points: 779 More actions · Answer 5

Assuming that you really want to make this table unavailable to the users/system during this process you can simply rename the table and rename it back when done, i.e.:

EXEC

sp_rename N'FDR', N'JFK', N'Object';

and when done

EXEC sp_rename N'JFK', N'FDR', N'Object';

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

But he is running some scheduled process on the same tables, in which case he may have use the renamed tables for his processing aswell.

Prasad Bhogadi
www.inforaise.com

Dan Murray Right there with Babe Points: 779 More actions · Answer 7

Right, For this reason the scheduled process would have to be aware of the name change. They'd have to be written so that they ran against, JFK instead of FDR.

AlanB SSC Eights! Points: 845 More actions · Answer 8

Thanks for the suggestions guys!

Restricting access seems to be a complex issue, so I'm going to use the table/index renaming idea suggested above. It's worked well under test, and is fast as well.

AlanB