Results 1 to 7 of 7

Thread: Page level locking

  1. #1
    Join Date
    Aug 2009
    Posts
    23

    Page level locking

    Hello All,

    My problem is that I want to allow page level locking for all the databases.


    For one database, I am using below query:


    sp_msforeachtable 'ALTER INDEX all ON ?
    SET (
    ALLOW_PAGE_LOCKS = ON
    )
    '

    this enables page level locking for all indexes on all tables for one particular database.

    How do I do it for all databases in one go.

    I tried using sp_msforeachdb but realised I can't do it. I tried a couple of other things. but nothing worked.

    Need help here!!!

    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    You try with cursor to get db name and run your code under each db.

  3. #3
    Join Date
    Aug 2009
    Posts
    23
    I already tried that and below is what I did :



    declare @Database_Name nVarchar(512)
    declare Database_Cursor cursor
    for

    select name from sys.databases
    where name not in ('master','model','msdb','tempdb')
    and state_desc like 'online'


    open Database_Cursor
    fetch next from Database_Cursor
    into @Database_Name
    while @@fetch_status = 0
    begin

    Use @Database_Name
    go;
    sp_msforeachtable 'ALTER INDEX all ON ?
    SET (
    ALLOW_PAGE_LOCKS = ON
    )
    '

    if @@error <> 0
    break

    fetch next from Database_Cursor
    into @Database_Name
    end
    close Database_Cursor
    deallocate Database_Cursor


    and this gives an error:

    Msg 102, Level 15, State 1, Line 17
    Incorrect syntax near '@Database_Name'.



    where am I wrong?? I am sorry but I am novice when it comes to all this..

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Can't use parameter in USE, need do it in dymanic sql.

  5. #5
    Join Date
    Feb 2011
    Posts
    1

    sp_msforeachdb

    @rmiao - you seem to have contradicted yourself:


    @bhosalenarayan, all you need to do is build a wrapper using sp_msforeachdb around your sp_msforeachtable script.

    You didn't mention the original script that fails, only your cursor script which was incorrectly prescribed.

  6. #6
    Join Date
    Mar 2014
    Posts
    1
    Just for the records...

    Code:
    DECLARE @Database_Name NVARCHAR(512) 
    DECLARE Database_Cursor CURSOR FOR
    	SELECT name FROM sys.databases 
    	WHERE name NOT IN ('master','model','msdb','tempdb')
    	AND state_desc LIKE 'online'
    OPEN Database_Cursor
    FETCH NEXT FROM Database_Cursor INTO @Database_Name 
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	EXECUTE('
    		USE ' + @Database_Name + '
    		PRINT ''Running on '' + DB_NAME() + ''...''
    		EXEC SP_MSFOREACHTABLE @command1= ''ALTER INDEX all ON ? SET ( ALLOW_PAGE_LOCKS = ON )''
    		PRINT ''OK!''
    	')
    
    	IF @@ERROR <> 0
    		BREAK
    
    	FETCH NEXT FROM Database_Cursor INTO @Database_Name 
    END
    
    CLOSE Database_Cursor
    DEALLOCATE Database_Cursor

  7. #7
    Join Date
    Mar 2016
    Posts
    2
    Quote Originally Posted by theo2f View Post
    Just for the records...

    Code:
    DECLARE @Database_Name NVARCHAR(512) 
    DECLARE Database_Cursor CURSOR FOR
    	SELECT name FROM sys.databases 
    	WHERE name NOT IN ('master','model','msdb','tempdb')
    	AND state_desc LIKE 'online'
    OPEN Database_Cursor
    FETCH NEXT FROM Database_Cursor INTO @Database_Name 
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	EXECUTE('
    		USE ' + @Database_Name + '
    		PRINT ''Running on '' + DB_NAME() + ''...''
    		EXEC SP_MSFOREACHTABLE @command1= ''ALTER INDEX all ON ? SET ( ALLOW_PAGE_LOCKS = ON )''
    		PRINT ''OK!''
    	')
    
    	IF @@ERROR <> 0
    		BREAK
    
    	FETCH NEXT FROM Database_Cursor INTO @Database_Name 
    END
    
    CLOSE Database_Cursor
    DEALLOCATE Database_Cursor
    Hi Theo2f, I tried your code to get page locking to work but it didn't seem to do anything. Maybe I'm putting the code in the wrong place. Where should I put this exactly?

    Thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •