Post reply

Killing all queries on all databases

  • August 30, 2013 at 5:20 am

    #281745

    I'm trying to kill all queries on all user databases prior to starting an overnight batch. Running this code

    DECLARE @sql VARCHAR(MAX)

    SET @sql = ''

    SELECT @sql = @sql + 'USE master; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + CHAR(13) + CHAR(10) + 'GO ' + CHAR(13) + CHAR(10) + 'ALTER DATABASE [' + NAME +'] SET MULTI_USER; ' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    PRINT @sql

    EXEC (@SQL)

    is giving errors (incorrect syntax near GO). However copying the printed sql and running that works. Can anyone suggest why this would be the case?

    Thanks

  • August 30, 2013 at 5:33 am

    #1646299

    you are getting error because before statement

    FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    you haven't use SELECT statement and columns names which you required in your output

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 30, 2013 at 5:40 am

    #1646301

    Thanks but where would you put the SELECT in?

  • August 30, 2013 at 5:46 am

    #1646304

    magwitch (8/30/2013)

    Thanks but where would you put the SELECT in?

    Before FROM keyword....

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 30, 2013 at 5:49 am

    #1646305

    I thought thats where you meant, can you give an example? when i've added a SELECT name to just before the FROM it doesn't work.

    Thanks

  • August 30, 2013 at 5:53 am

    #1646308

    kapil_kk (8/30/2013)

    you are getting error because before statement

    FROM sys.databases WHERE name not in ('master','tempdb','model','msdb','ReportServer','ReportServerTempDB')

    you haven't use SELECT statement and columns names which you required in your output

    I apologize for my comments........

    I didn't read your query properly..You don't have to put any SELECT keywork before FROM

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 30, 2013 at 5:59 am

    #1646313

    Yes, i didn't think that was right. So do you have any idea as to why the @sql will not execute but by cutting, pasting and executing the printed @sql it does run?

    Thanks

  • August 30, 2013 at 6:25 am

    #1646322

    magwitch (8/30/2013)

    Yes, i didn't think that was right. So do you have any idea as to why the @sql will not execute but by cutting, pasting and executing the printed @sql it does run?

    Thanks

    Your query will generate PRINT statement when you Commented out

    EXEC(@sql)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 30, 2013 at 6:36 am

    #1646333

    You're getting errors because GO is not a T-SQL command. It's a batch-breaker, designates where the client tool breaks commands apart to send the batches to SQL Server.

    Take the GOs out of your dynamic SQL.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 30, 2013 at 6:39 am

    #1646336

    Thanks Gail, works perfectly now.

Viewing 10 posts - 1 through 10 (of 10 total)

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