SQL Server Performance

Database compatibility level

Discussion in 'SQL Server 2008 General DBA Questions' started by darkangelBDF, Sep 17, 2009.

  1. darkangelBDF Member

    Hi there all. I've been tasked with writing a script where I iterate through sys.sysdatabases to find all databases with a compatibility level not equal to 100. Those databases need to then have their compatibility levels changed to 100 in the same process. Can anyone give me any ideas on how the script would look? I'm new to writing T-SQL statements and don't want to do it wrong... This is what I've got so far:declare
    @dbname nvarchar(30);
    set @dbname =
    (
    select name
    from sys.sysdatabases
    );while
    exists
    (
    select *
    from sys.sysdatabases
    where cmptlevel <> 100
    )alter
    database @dbname
    set compatibility_level = 100;
    I know the last part won't work but I don't know how to do it...
    darkangelBDF, Sep 17, 2009
    #2

  2. darkangelBDF Member

    Upon some more investigation I came up with this:/******************************************************
    Procedure
    ChangeCmptLevel.sql
    Purpose
    This procedure changes the compability level for all
    databases on a given SQL Server Instance
    Revision History
    CR Number:
    Revision 1: Developer_Name
    Date: 17 September 2009
    ******************************************************/SET ANSI_NULLS ON
    GO
    SET
    QUOTED_IDENTIFIER ON
    GOCREATE
    PROCEDURE [dbo].[uspChangeCmptLevel]
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Find all databases which do not have a compatibility
    -- level of 100 (which is SQL Server 2008)
    WHILE EXISTS
    (
    SELECT *
    FROM sys.sysdatabases
    WHERE cmptlevel <> 100
    )
    -- For each of these databases, change the compatibility
    -- level to 100
    EXEC sp_MSforeachdb
    'ALTER DATABASE [?]
    SET COMPATIBILITY_LEVEL = 100';
    END
    GO
    Would this work?
    darkangelBDF, Sep 17, 2009
    #3

  3. darkangelBDF Member

    They have now added another small complicatoin. There are servers which have read-only databases. I need to cater for those databases as well.
    darkangelBDF, Sep 17, 2009
    #4

  4. Adriaan New Member

    That's the whole point of them being read-only, you can't make changes to them. So you'd need to remove the read-only status, make the change, and make them read-only again.
    Not sure that you should be doing this in dynamic SQL on a production server ...
    Adriaan, Sep 17, 2009
    #5

  5. darkangelBDF Member

    What I meant to say was that the read-only databases should be excluded from the compatibility update.
    darkangelBDF, Sep 17, 2009
    #6

  6. FrankKalis Moderator

    Untested...
    DECLARE @t TABLE (DBName sysname);
    DECLARE @DB sysname;
    DECLARE @sql nvarchar(2000);

    INSERT INTO @t
    (DBName)
    SELECT
    D.[name]
    FROM
    sys.databases D
    WHERE
    D.is_read_only = 0 AND
    D.compatibility_level <> 100;

    SELECT
    @DB = MIN(DBName)
    FROM
    @t;

    WHILE @DB IS NOT NULL
    BEGIN
    SET @sql = 'ALTER DATABASE ' + @DB + ' SET READ_ONLY';

    EXEC sp_ExecuteSQL @sql;

    SET @DB = NULL;

    DELETE @t
    WHERE
    DBName = @DB;

    SELECT
    @DB = MIN(DBName)
    FROM
    @t;
    END
    Edit: Typos
    FrankKalis, Sep 17, 2009
    #7

  7. FrankKalis Moderator

    Haha, obviously you need to change these lines

    DELETE @t
    WHERE
    DBName = @DB;

    SET @DB = NULL;

    SELECT
    @DB = MIN(DBName)
    FROM
    @t;
    and probably put in a SET NOCOUNT ON as well.
    FrankKalis, Sep 17, 2009
    #8

  8. FrankKalis Moderator

    All good things are three. You'd want to exclude system databases as well:
    SET NOCOUNT ON;

    DECLARE @t TABLE (DBName sysname);
    DECLARE @DB sysname;
    DECLARE @sql nvarchar(2000);

    INSERT INTO @t
    (DBName)
    SELECT
    D.[name]
    FROM
    sys.databases D
    WHERE
    D.database_id > 4 AND
    D.is_read_only = 0 AND
    D.compatibility_level = 100;

    SELECT
    @DB = MIN(DBName)
    FROM
    @t;

    WHILE @DB IS NOT NULL
    BEGIN
    SET @sql = 'ALTER DATABASE ' + @DB + ' SET READ_ONLY';

    EXEC sp_ExecuteSQL @sql;

    DELETE @t
    WHERE
    DBName = @DB;

    SET @DB = NULL;

    SELECT
    @DB = MIN(DBName)
    FROM
    @t;
    END
    FrankKalis, Sep 17, 2009
    #9

  9. darkangelBDF Member

    I think this is exactly what I was looking for! Right now you might be a total stranger, but to me you are a hero! I will test this and give feedback.
    darkangelBDF, Sep 17, 2009
    #10

  10. Madhivanan Moderator

    I am confused by Frank's posts [:)]
    Is this you want?DECLARE
    @sql nvarchar(2000);set
    @sql=''SELECT
    @sql=@sql+'ALTER DATABASE ' + D.[name] + ' SET COMPATIBILITY_LEVEL = 100; ' FROM
    sys.databases DWHERED
    .database_id > 4 ANDD
    .is_read_only = 0 ANDD
    .compatibility_level <> 100;EXEC
    sp_ExecuteSQL @sql;
    Madhivanan, Sep 17, 2009
    #11

  11. FrankKalis Moderator

    Just ignore my first 2 posts....
    Yours is another way of doing things. Except that you rely on the undocumented concatenation behaviour....
    scnr [:)]
    FrankKalis, Sep 17, 2009
    #12

  12. darkangelBDF Member

    Thanks to both of you. I'll be saving both and have managed to, with the help of some helpful folks, impress my boss and change the levels on our test servers. If all goes well, I'll be using it next week on production. Once again, THANKS!!!
    darkangelBDF, Sep 18, 2009
    #13

  13. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>Just ignore my first 2 posts....</P><P>Yours is another way of doing things. Except that you rely on the undocumented concatenation behaviour....</P><P>scnr <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"> <BR>[/quote]</P><P>Well. Actually both our methods do the different things<BR>Yours just set the database mode to read only and mine set compatibility to 100<BR></P>
    Madhivanan, Sep 18, 2009
    #14

  14. FrankKalis Moderator

    <p>[quote user="Madhivanan"]</p><p>[quote user="FrankKalis"] </p><p>Just ignore my first 2 posts....</p><p>Yours is another way of doing things. Except that you rely on the undocumented concatenation behaviour....</p><p>scnr <img src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" mce_src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif" alt="Smile"> <br>[/quote]</p><p>Well. Actually both our methods do the different things<br>Yours just set the database mode to read only and mine set compatibility to 100<br></p><p>[/quote]&nbsp;</p><p>Oops, good catch! </p><p>Think I confused myself as well... [:)] <br></p>
    FrankKalis, Sep 18, 2009
    #15

  15. Madhivanan Moderator

    <P mce_keep="true">[quote user="FrankKalis"] <P>[quote user="Madhivanan"]</P><P>[quote user="FrankKalis"] </P><P>Just ignore my first 2 posts....</P><P>Yours is another way of doing things. Except that you rely on the undocumented concatenation behaviour....</P><P>scnr <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"> <BR>[/quote]</P><P>Well. Actually both our methods do the different things<BR>Yours just set the database mode to read only and mine set compatibility to 100<BR></P><P>[/quote]&nbsp;</P><P>Oops, good catch! </P><P>Think I confused myself as well... <IMG alt=Smile src="http://sql-server-performance.com/Community/emoticons/emotion-1.gif"> <BR></P><P>[/quote]</P><P>And I think OP wanted to Set the compatibility to 100 for all accessible DBs [:)]</P>
    Madhivanan, Sep 18, 2009
    #16

  16. FrankKalis Moderator

    Yes, you're right.
    FrankKalis, Sep 18, 2009
    #17

Share This Page