BI cooking

with a touch of SQL
posts - 5 , comments - 1 , trackbacks - 0

Wednesday, January 30, 2013

Change the Maxsize and Filegrowth of a datafile and a logfile

If you need to alter a database file and logfile to change their maxsize and filegrowth settings, here is a small script that might help you

----------------------------------------------------------------------------------------- spChangeMaxsizeandFilegrowth
----------------------------------------------------------------------------------------- Author: Remko de Boer
-- Created: 2013-01-30
-- Description: Change the Maxsize and filegrowth of database with a single
--  datafile and a single transaction logfile
---------------------------------------------------------------------------------------
-- Version: 1.0 Initial
---------------------------------------------------------------------------------------



-- Declare variables ------------------------------------------------------------------
declare @datafile varchar(250)
declare
@logfile varchar(250)
declare
@dbname varchar(250)
declare
@maxsize int
declare
@autogrowth int
declare
@sizesuffux char(2)
declare
@string nvarchar(4000)
---------------------------------------------------------------------------------------

-- Set Variables ----------------------------------------------------------------------

set @maxsize = 8192
set
@autogrowth = 64
set
@sizesuffux = 'MB'
---------------------------------------------------------------------------------------


-- automated variables setting --------------------------------------------------------
set @datafile = (select name from sys.database_files where type = 0)
set
@logfile = (select name from sys.database_files where type = 1)
set
@dbname = (select DB_NAME() as databasename)
---------------------------------------------------------------------------------------


-- Set the database file maxsize and filegrowth ---------------------------------------
set @string = N''
set
@string = @string + N'ALTER DATABASE [' + @dbname + '] '
set @string = @string
+ N'MODIFY FILE (NAME = [' + @datafile + '], '
set @string = @string + N'MAXSIZE = ' + cast(@maxsize as varchar) + @sizesuffux + ', '
set @string = @string + N'FILEGROWTH = ' + cast(@autogrowth as varchar) + @sizesuffux
set @string = @string + N' );'


exec
sp_executesql @string
---------------------------------------------------------------------------------------


-- set the logfile maxsize and filegrowth ---------------------------------------------
set @string = N''
set
@string = @string + N'ALTER DATABASE [' + @dbname + '] '
set
@string = @string + N'MODIFY FILE (NAME = [' + @logfile + '], '
set @string = @string + N'MAXSIZE = ' + cast(@maxsize as varchar) + @sizesuffux + ', '
set
@string = @string + N'FILEGROWTH = ' + cast(@autogrowth as varchar) + @sizesuffux
set @string = @string + N' );'


exec
sp_executesql @string
---------------------------------------------------------------------------------------

Posted On Wednesday, January 30, 2013 12:09 PM | Comments (0) |

Wednesday, January 16, 2013

Grouped Failover, a 2008R2 version of Availability groups

One of the new cool features in SQL 2012 is the SQL Server Availability groups. In other words being able to failover a group of databases which are logically connected. i.e. SharePoint databases. Well it is also possible to do that in SQL 2008 (R2). It’s called a Grouped Failover. Instead of configuring it in SQL mirroring, you have to administrate the connected databases yourself and with a single stored procedure you will be able to failover a group of databases. It also gives you the option to failover a group of mirrored databases when the principal is down.

How to implement this solution

First run the script and it will create a database, 3 tables and a stored procedure. Of the 3 tables 2 of them need to be filled with configuration you desire or need. In the table tblFailoverGroups you create 1 or more failover groups. In the table tblFailoverGroupDatabases you need to add the databases that are linked to the failover group you just created. You can also add databases that are not mirrored, but have a logical connection to the failover group. These database will not failover since they are not mirrored.

Script
----------------------------------------------------------------
-- Create grouped failover solution
----------------------------------------------------------------
-- Author:        Remko de Boer
-- Email:        remko@bittown.nl
-- Created:        16-01-2013
-- Description:    The grouped failover solution is an solution that
--                that empoweres the DBA to failover a number of databases
--                which are logically linked to eachother i.e. sharepoint
--                databases. The concept is based on 2 tables:
--                tblFailoverGroups and tblFailoverGroupDatabases
--                The tblFailoverGroups contains the group information en
--                description. It is possible to have multiple groups on a
--                single SQL Server Instance.
--                The tblFailoverGroupDatabases contains all databases linked
--                to a specific group. It is not possible for a database to be
--                in more then 1 group. 
--                It is possible to add databases to the group, even while these
--                databases are not being mirrored.
----------------------------------------------------------------
-- Version:        1.0 Initial
----------------------------------------------------------------
-- Installation
----------------------------------------------------------------
-- Run this script, and after a succesfull execution check if 
-- the following objects have been created:
-- database        SQL_Beheer
-- table        dbo.tblFailoverGroups
-- table        dbo.tblFailoverGroupDatabases
-- table        dbo.tblFailoverErrors
-- procedure    dbo.spFailoverGroup
-- If all these objects are installed correctly, 
-- the solution then needs to configured by adding groups 
-- and adding databases to these groups.
-- After configguring the groups and databases the grouped failover
-- can be executed by the use of spFailoverGroup.
-- Check the inline documentation of the spFailoverGroup procedure
-- to obtain the needed information on what values to use as parameters
----------------------------------------------------------------
 
 
-- Check if database already exsists ---------------------------
if (select count(1) from sys.databases where name = 'SQL_Beheer') > 0
    BEGIN
        print 'database already exists.'
    END
else
    begin
        Create database SQL_Beheer
 
        print 'database SQL_Beheer created.'
    end
----------------------------------------------------------------
GO
 
-- Switch to SQL_Beheer ----------------------------------------
USE SQL_Beheer
----------------------------------------------------------------
GO
 
-- check if tblFailoverGroups exists ---------------------------
if (select count(1) from sys.tables where name = 'tblFailoverGroups') > 0
    BEGIN
        print 'table tblFailoverGroups already exists.'
    END
ELSE
    BEGIN
        CREATE TABLE [dbo].[tblFailoverGroups]
        (
            [id] [int] IDENTITY(1,1) NOT NULL,
            [group_name] [varchar](250) NULL,
            [group_description] [varchar](max) NULL
        ) ON [PRIMARY]
 
        print 'table tblFailoverGroups created.'
    END
----------------------------------------------------------------
GO
 
-- check if tblFailoverGroupDatabases exists -------------------
if (select count(1) from sys.tables where name = 'tblFailoverGroupDatabases') > 0
    BEGIN
        print 'table tblFailoverGroupDatabases already exists.'
    END
ELSE
    BEGIN
    CREATE TABLE [dbo].[tblFailoverGroupDatabases]
        (
        [id] [int] IDENTITY(1,1) NOT NULL,
        [group_id] [int] NULL,
        [database_name] [varchar](250) NULL,
        [database_id] [int] NULL
        ) ON [PRIMARY]
 
        print 'table tblFailoverGroupDatabases created.'
    END
----------------------------------------------------------------
GO
 
-- check if tblFailoverErrors exists ---------------------------
if (select count(1) from sys.tables where name = 'tblFailoverErrors') > 0
    BEGIN
        print 'table tblFailoverErrors already exists.'
    END
ELSE
    BEGIN
        CREATE TABLE [dbo].[tblFailoverErrors]
            (
            [id] [int] IDENTITY(1,1) NOT NULL,
            [group_id] [int] NULL,
            [database_id] [int] NULL,
            [error_date] [date] NULL,
            [error_time] [time](7) NULL,
            [error_message] [varchar](1000) NULL
            ) ON [PRIMARY]
 
        print 'table tblFailoverErrors created.'
    END
----------------------------------------------------------------
GO
 
-- check if spFailoverGroup exists -----------------------------
if (select count(1) from sys.procedures where name = 'spFailoverGroup') > 0
    BEGIN
        print 'procedure spFailoverGroup already exists'
    END
ELSE
    BEGIN
        declare @string nvarchar(max)
 
        set @string = N''
 
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- dbo.spFailoverGroup <@groupid> <@actionid>' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Author:        Remko de Boer' + char(13) + char(10)
        set @string = @string + N'-- Created:        16-01-2013' + char(13) + char(10)
        set @string = @string + N'-- Description:    This procedure will failover mirrored databases as groups,' + char(13) + char(10)
        set @string = @string + N'--                based on the entries in the tblFailoverGroups table and the' + char(13) + char(10)
        set @string = @string + N'--                tblFailOverGroupDatabases table.' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Parameters:    @groupid,' + char(13) + char(10)
        set @string = @string + N'--                This is the id of the group which needs a failover.' + char(13) + char(10)
        set @string = @string + N'--                @action id,' + char(13) + char(10)
        set @string = @string + N'--                This switch gives the option to either do a normal' + char(13) + char(10)
        set @string = @string + N'--                controlled failover without dataloss or an emergency' + char(13) + char(10)
        set @string = @string + N'--                failover with possible dataloss. This emergency failover' + char(13) + char(10)
        set @string = @string + N'--                is only needed when the principal is no longer operating' + char(13) + char(10)
        set @string = @string + N'--                properly and there is no automated failover configgured.' + char(13) + char(10)
        set @string = @string + N'--                Swith Options:' + char(13) + char(10)
        set @string = @string + N'--                0 = Normal failover' + char(13) + char(10)
        set @string = @string + N'--                1 = forced failover on mirror, principal not available' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Version:        1.0 Initial Version' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'create proc spFailoverGroup' + char(13) + char(10)
        set @string = @string + N'@groupid int,' + char(13) + char(10)
        set @string = @string + N'@actionid int' + char(13) + char(10)
        set @string = @string + N'AS' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- DEBUG --------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'--declare @groupid int' + char(13) + char(10)
        set @string = @string + N'--declare @actionid int' + char(13) + char(10)
        set @string = @string + N'' + char(13) + char(10)
        set @string = @string + N'--set @groupid = 1' + char(13) + char(10)
        set @string = @string + N'--set @actionid = 0' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Database state:' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- 0 = ONLINE ' + char(13) + char(10)
        set @string = @string + N'-- 1 = RESTORING' + char(13) + char(10)
        set @string = @string + N'-- 2 = RECOVERING' + char(13) + char(10)
        set @string = @string + N'-- 3 = RECOVERY_PENDING' + char(13) + char(10)
        set @string = @string + N'-- 4 = SUSPECT' + char(13) + char(10)
        set @string = @string + N'-- 5 = EMERGENCY' + char(13) + char(10)
        set @string = @string + N'-- 6 = OFFLINE' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Mirroring state:' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- 0 = Suspended' + char(13) + char(10)
        set @string = @string + N'-- 1 = Disconnected from the other partner' + char(13) + char(10)
        set @string = @string + N'-- 2 = Synchronizing ' + char(13) + char(10)
        set @string = @string + N'-- 3 = Pending Failover' + char(13) + char(10)
        set @string = @string + N'-- 4 = Synchronized' + char(13) + char(10)
        set @string = @string + N'-- 5 = The partners are not synchronized. Failover is not possible now.' + char(13) + char(10)
        set @string = @string + N'-- 6 = The partners are synchronized. Failover is potentially possible.' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Mirroring Role:' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- 1 = Principal' + char(13) + char(10)
        set @string = @string + N'-- 2 = Mirror' + char(13) + char(10)
        set @string = @string + N'-- NULL = Database is inaccessible or is not mirrored. ' + char(13) + char(10)
        set @string = @string + N'----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Drop temp table(s) -------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'IF object_id(''tempdb..#worklist'') IS NOT NULL' + char(13) + char(10)
        set @string = @string + N'BEGIN' + char(13) + char(10)
        set @string = @string + N'   DROP TABLE #worklist' + char(13) + char(10)
        set @string = @string + N'END' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Create temp table(s) -----------------------------------------------' + char(13) + char(10)
        set @string = @string + N'create table #worklist' + char(13) + char(10)
        set @string = @string + N'(' + char(13) + char(10)
        set @string = @string + N'id int identity (1,1) not null' + char(13) + char(10)
        set @string = @string + N',database_id int null' + char(13) + char(10)
        set @string = @string + N',database_name varchar(250) null' + char(13) + char(10)
        set @string = @string + N')' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Create worklist ----------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'insert into #worklist' + char(13) + char(10)
        set @string = @string + N'(' + char(13) + char(10)
        set @string = @string + N'database_id,' + char(13) + char(10)
        set @string = @string + N'database_name' + char(13) + char(10)
        set @string = @string + N')' + char(13) + char(10)
        set @string = @string + N'select ' + char(13) + char(10)
        set @string = @string + N'    database_id,' + char(13) + char(10)
        set @string = @string + N'    database_name' + char(13) + char(10)
        set @string = @string + N'from' + char(13) + char(10)
        set @string = @string + N'    tblFailoverGroupDatabases' + char(13) + char(10)
        set @string = @string + N'where' + char(13) + char(10)
        set @string = @string + N'    group_id = @groupid' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Declare Variables --------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'declare @database_name varchar(250) ' + char(13) + char(10)
        set @string = @string + N'declare @database_id int' + char(13) + char(10)
        set @string = @string + N'declare @database_status int' + char(13) + char(10)
        set @string = @string + N'declare @database_role int' + char(13) + char(10)
        set @string = @string + N'declare @mirror_status int' + char(13) + char(10)
        set @string = @string + N'declare @error_message varchar(2000)' + char(13) + char(10)
        set @string = @string + N'-----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Create cursor ------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'declare failover_worklist cursor for' + char(13) + char(10)
        set @string = @string + N'select database_id, database_name from #worklist order by database_name' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'open failover_worklist' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'FETCH NEXT FROM failover_worklist' + char(13) + char(10)
        set @string = @string + N'INTO @database_id, @database_name' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'-- Check @@FETCH_STATUS to see if there are any more rows to fetch.' + char(13) + char(10)
        set @string = @string + N'WHILE @@FETCH_STATUS = 0' + char(13) + char(10)
        set @string = @string + N'BEGIN' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    -- check if normal failover needs to be ' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    -- @actionid' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    -- 0 = Normal Failover' + char(13) + char(10)
        set @string = @string + N'    -- 1 = Emergency Mode ### Potential Dataloss when used ! ###' + char(13) + char(10)
        set @string = @string + N'    -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'    if @actionid = 0 -- Normal Failover --' + char(13) + char(10)
        set @string = @string + N'    BEGIN' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        -- check if the database is mirrored' + char(13) + char(10)
        set @string = @string + N'        -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        -- 1    = Principal' + char(13) + char(10)
        set @string = @string + N'        -- 2    = Mirror' + char(13) + char(10)
        set @string = @string + N'        -- NULL = Database is inaccessible or is not mirrored.' + char(13) + char(10)
        set @string = @string + N'        -----------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        set @database_role = (select mirroring_role from sys.database_mirroring where database_id = @database_id)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        if @database_role is null' + char(13) + char(10)
        set @string = @string + N'        BEGIN' + char(13) + char(10)
        set @string = @string + N'                set @error_message = ''Database is not configured for mirroring''' + char(13) + char(10)
        set @string = @string + N'        END ' + char(13) + char(10)
        set @string = @string + N'        else' + char(13) + char(10)
        set @string = @string + N'        BEGIN' + char(13) + char(10)
        set @string = @string + N'                ------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'                -- check the mirroring state' + char(13) + char(10)
        set @string = @string + N'                ------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'                -- 0 = Suspended' + char(13) + char(10)
        set @string = @string + N'                -- 1 = Disconnected from the other partner' + char(13) + char(10)
        set @string = @string + N'                -- 2 = Synchronizing ' + char(13) + char(10)
        set @string = @string + N'                -- 3 = Pending Failover' + char(13) + char(10)
        set @string = @string + N'                -- 4 = Synchronized' + char(13) + char(10)
        set @string = @string + N'                -- 5 = The partners are not synchronized. Failover is not possible now.' + char(13) + char(10)
        set @string = @string + N'                -- 6 = The partners are synchronized. Failover is potentially possible.' + char(13) + char(10)
        set @string = @string + N'                -----------------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'                set @mirror_status = (select mirroring_state from sys.database_mirroring where database_id = @database_id)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                if @mirror_status = 4 or @mirror_status = 6' + char(13) + char(10)
        set @string = @string + N'                    BEGIN' + char(13) + char(10)
        set @string = @string + N'                        declare @string nvarchar(4000)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                        set @string = N'''' ' + char(13) + char(10)
        set @string = @string + N'                        set @string = N''ALTER DATABASE '' + CAST(@database_name as varchar) + '' SET PARTNER FAILOVER''' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                        exec sp_executesql @string' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                        set @error_message = ''Manual failover executed.''' + char(13) + char(10)
        set @string = @string + N'                    END' + char(13) + char(10)
        set @string = @string + N'                else' + char(13) + char(10)
        set @string = @string + N'                    BEGIN' + char(13) + char(10)
        set @string = @string + N'                        set @error_message = ''In current mirroring state a normal synchronised failover is not possible.''' + char(13) + char(10)
        set @string = @string + N'                    END' + char(13) + char(10)
        set @string = @string + N'            END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'    END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'if @actionid = 1 -- EMERGENCY MODE --' + char(13) + char(10)
        set @string = @string + N'    BEGIN' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        -------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        -- check the mirroring state' + char(13) + char(10)
        set @string = @string + N'        -------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'        set @mirror_status = (select mirroring_state from sys.database_mirroring where database_id = @database_id)' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'        if @mirror_status <> 4' + char(13) + char(10)
        set @string = @string + N'            BEGIN' + char(13) + char(10)
        set @string = @string + N'                set @string = N''''' + char(13) + char(10)
        set @string = @string + N'                set @string = N''ALTER DATABASE '' + CAST(@database_name as varchar) + '' SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS''' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                exec sp_executesql @string' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'                set @error_message = ''Forced failover with possible data loss. Principal was unavailable.''' + char(13) + char(10)
        set @string = @string + N'            END' + char(13) + char(10)
        set @string = @string + N'        else' + char(13) + char(10)
        set @string = @string + N'            BEGIN' + char(13) + char(10)
        set @string = @string + N'                set @error_message = ''In current mirroring state a normal synchronised failover is not possible.''' + char(13) + char(10)
        set @string = @string + N'            END    ' + char(13) + char(10)
        set @string = @string + N'    END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'---------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N'-- Log error message to logging table' + char(13) + char(10)
        set @string = @string + N'---------------------------------------------------------------' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'    INSERT INTO [dbo].[tblFailoverErrors]' + char(13) + char(10)
        set @string = @string + N'    (' + char(13) + char(10)
        set @string = @string + N'    [group_id]' + char(13) + char(10)
        set @string = @string + N'    ,[database_id]' + char(13) + char(10)
        set @string = @string + N'    ,[error_date]' + char(13) + char(10)
        set @string = @string + N'    ,[error_time]' + char(13) + char(10)
        set @string = @string + N'    ,[error_message]' + char(13) + char(10)
        set @string = @string + N'    )' + char(13) + char(10)
        set @string = @string + N'    VALUES' + char(13) + char(10)
        set @string = @string + N'    (' + char(13) + char(10)
        set @string = @string + N'@groupid' + char(13) + char(10)
        set @string = @string + N'    ,@database_id' + char(13) + char(10)
        set @string = @string + N'    ,convert(date,GETDATE(),120)' + char(13) + char(10)
        set @string = @string + N'    ,convert(time,getdate(),0)' + char(13) + char(10)
        set @string = @string + N'    ,@error_message' + char(13) + char(10)
        set @string = @string + N'    )' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'       FETCH NEXT FROM failover_worklist' + char(13) + char(10)
        set @string = @string + N'       INTO @database_id, @database_name' + char(13) + char(10)
        set @string = @string + N'    END' + char(13) + char(10)
        set @string = @string + N' ' + char(13) + char(10)
        set @string = @string + N'CLOSE failover_worklist' + char(13) + char(10)
        set @string = @string + N'    DEALLOCATE failover_worklist' + char(13) + char(10)
 
        exec sp_executesql @string        
 
    END
----------------------------------------------------------------
GO
 
----------------------------------------------------------------
-- End of Script
----------------------------------------------------------------

Posted On Wednesday, January 16, 2013 11:31 PM | Comments (0) |

Force shrink of transaction log after a full backup

After installing vCenter and connecting it to a SQL Server 2008R2 database server and making it high available with the help of database mirroring, somthing anoying started to take place. After a full backup the transaction log does NOT get 'truncated' and shrunk. So within a day or 5 my transaction log drive was full and vCenter refused to operate. After a bit of research it turns out that vCenter keeps an open transaction to the tailend of the transaction. Even during a full backup. So this open transaction stops any truncation and or shrinking of the transaction log. The VMWare white paper states that a succesfull backup of the database will only happen if you stop the vCenter service, make a backup and then start the vCenter service again. Due to several reasons, this is not a preffered way of ensuring a proper backup with a shrunk transaction log. The following code gives you the option to force a shrink of the transaction log.

Only use this solution if the possible dataloss is acceptable.

---------------------------------------------------------------------
-- Backup_Tran
---------------------------------------------------------------------
-- Author: Remko de Boer
-- Created: 2012-06-18
-- Description: Create a transaction log backup for all databases that are 
-- mirrored and in a synchronised state
---------------------------------------------------------------------
-- Version: 1.0 initial
---------------------------------------------------------------------
-- Database state:
-- 0 = ONLINE 
-- 1 = RESTORING
-- 2 = RECOVERING
-- 3 = RECOVERY_PENDING
-- 4 = SUSPECT
-- 5 = EMERGENCY
-- 6 = OFFLINE
--
-- Mirroring state:
-- 0 = Suspended
-- 1 = Disconnected from the other partner
-- 2 = Synchronizing 
-- 3 = Pending Failover
-- 4 = Synchronized
-- 5 = The partners are not synchronized. Failover is not possible now.
-- 6 = The partners are synchronized. Failover is potentially possible.
--
-- Shrink enabled
-- 0 = No
-- 1 = Yes
--
-- Overwrite backup
-- 0 = No, backup files will be appended
-- 1 = Yes, previous backups will be overwritten
---------------------------------------------------------------------
ALTER proc [dbo].[spBackup_tran]
AS
-- Declare Variables ------------------------------------------------
declare @name varchar(150)
declare @id int
declare @backup_root varchar(500)
declare @backup_path varchar(250)
declare @backup_file varchar(250)
declare @mirror_state int
declare @database_state int
declare @shrink_enabled int
declare @overwrite_backup int
declare @backup_date varchar(20)
declare @kill int
declare @killstring nvarchar(1000)
---------------------------------------------------------------------
 
-- Set Variables ----------------------------------------------------
set @backup_root = 'D:\Beheer\SQL_Backup\'
set @mirror_state = 4
set @database_state = 0
set @shrink_enabled = 1
set @overwrite_backup = 1
set @kill = 0
---------------------------------------------------------------------
 
---------------------------------------------------------------------
set @backup_path = @backup_root + CAST(@@SERVERNAME as varchar) + '\'
set @backup_date = replace(replace(replace(convert(varchar,CONVERT(datetime,getdate(),120),120),'-',''),' ',''),':','')
--select @backup_date
---------------------------------------------------------------------
 
declare transactie cursor for
select
db.name,
db.database_id
from 
sys.databases db
inner join sys.database_mirroring dbm on db.database_id = dbm.database_id
where 
db.[state] = @database_state
and db.recovery_model = 1
and dbm.mirroring_state = @mirror_state
open transactie
FETCH NEXT FROM transactie
INTO @name, @id
WHILE @@FETCH_STATUS = 0
BEGIN
-- set database to single user -------------------------------------------
if @kill = 1
BEGIN
set @killstring = N'ALTER DATABASE [' + @name + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
 
exec sp_executesql @killstring
END
--------------------------------------------------------------------------
 
 
-- Backup transactielog --------------------------------------------------
set @backup_file = ''
 
-- DEBUG -------------
-- select @backup_file
----------------------
 
 
if @overwrite_backup = 0
BEGIN
set @backup_file = @backup_path + @name + '_' + @backup_date + '.trn'
 
BACKUP LOG @name TO DISK = @backup_file WITH NOINIT
END
 
if @overwrite_backup = 1
BEGIN
set @backup_file = @backup_path + @name + '.trn'
 
BACKUP LOG @name TO DISK = @backup_file WITH INIT
END
--------------------------------------------------------------------------
 
 
-- shrink transactielog --------------------------------------------------
if @shrink_enabled = 1
BEGIN
declare @string nvarchar(500)
declare @log_name varchar(250)
DECLARE @ParmDefinition nvarchar(500)
declare @string1 nvarchar(500)
set @string = N'select @log= name from ' + @name + '.sys.database_files where type = 1'
SET @ParmDefinition = N'@log varchar(250) OUTPUT';
execute sp_executesql @string, @ParmDefinition, @log = @log_name output
set @string1 = 'use ' + @name + ' DBCC SHRINKFILE ([' + @log_name + '],1, TRUNCATEONLY)'
-- DEBUG ----------
--select @log_name
--select @string1
-------------------
 
execute sp_executesql @string1
 
 
END
--------------------------------------------------------------------------
 
-- set database to multi user -------------------------------------------
if @kill = 1
BEGIN
set @killstring = 'ALTER DATABASE [' + @name + '] SET MULTI_USER WITH ROLLBACK IMMEDIATE'
 
execute sp_Executesql @killstring 
END
--------------------------------------------------------------------------
 
 
FETCH NEXT FROM transactie
INTO @name, @id
END
CLOSE transactie
DEALLOCATE transactie

Posted On Wednesday, January 16, 2013 3:07 PM | Comments (0) |

Saturday, May 21, 2011

Reporting Services 2008 R2 Report Parts : the poor man’s version for a template

Ok you have been playing around with SSRS 2008 R2 as did I and while I can’t wait to get my hands on CTP 2 of Denali here is some tips to get your reports a bit more standardized. The concept is called Report Parts. Report Parts are a SSRS 2008 R2 only feature. Basically it allows you to publish elements of a report as a separate objects to the report server. These elements can be any element of a report, as long as there is no custom code in them. So how do you make a report part? Well, that’s simple. Here is a basic description on how to create a report part.

Report Part Creation

Create a report and drag an element onto it and you have done the basics of creating a report part.  The only thing you now need to do is to tag the element as a report part. In report builder go ‘start’ and click ‘Publish Report Parts’

image

You then will get a question if you want to ‘publish all report parts with default settings’ or you want to ‘review and modify report parts before publishing’. Choose the second option and you will see a window with elements of the report that can become report parts.

image

In the demo I created I only made a header, hence there is only 1 element in the list. So the header element is the object that I want to make a report part, so I have that element checked. So to complete the meta data of  the report part for further use click on the arrow next to the soon to be published report part and enter a  description of the report part.

image

When you are happy with design and working of the part(s) then press publish. By default the report part will be publish in the folder ‘Report Parts’ on your reporting server.

image

 

Report Part Usage

Ok now that we have created an published a report part, anybody that has rights to view the part is able to use the report part in the creation of a new report. In report builder, after creating a new report, go the ‘insert’ ribbon an d click the ‘report parts’ button.

image

A search task pane called ‘Report Part Gallery’ will appear and then press search. All available report parts will appear in the report part gallery

image

Drag the part you need into the report and your are done.

Maintaining and updating Report Parts

Is the report part read only? No, you can edit the part, but beware as the original author of the report part has made changes to the part and he has published them and you update your report parts, your modifications will be be lost. So how you check for updates? Go to the start button and click ‘Check for Updates’. If report parts have been updated, you will get a notification, and you will be able to accept the changes. Due to the fact that modifications to the report parts will be lost when they are updated, I strongly recommend that you keep your report parts as a standard as possible. Use well known expressions like the default field Report Name  as the title of your report and the report description as your subtitle. In this way your header is flexible and custom modifications will be kept to a minimum. You off course can create several report headers, just to give the user / report creator a choice. But remember custom code is not allowed. Have fun coding.

Posted On Saturday, May 21, 2011 12:29 AM | Comments (1) |

Thursday, May 19, 2011

SQL Agent Job Queue proxy

Ok, so you have locked your SQL down. No users allowed. But then there is this information worker for whom the IT department made a special SSIS package. And they loaded the package in to the SQL server and created a SQL Agent job for it. Well no problem so far. But now the user wants to be able to start the job when ever he needs to. Hmmm, huge problem. Because you need to have SA equivalent rights. What?? Yes, you read it right. SA equivalent !! Hell no! Ok a sql geek will now say, that's wrong, there is the operator role for that. That is true, BUT in my experience that role doesn't cut it for several reasons.

So here is the solution. The SQL script here will create a database, a couple a tables and a stored proc. It also will create a sql agent job that will loop. Why do you ask. Well the idea is to have a low level security object which a user can use to request job executions. In this case it is a stored proc. The stored proc itself inserts a record into a queue which is being monitored by the sql agent loop job. Once the job sees an new entry in the queue it will check if the requested job isn’t already requested. If it isn’t requested before, it will then check if the requesting user has rights in the userjob mapping table.

Current version does not support user enabled scheduling. I am working on that. Also a job monitoring function will be added in future version

 

 

----------------------------------------------------------------------------------------------------------------------------------------------
--     Job Queue Proxy 
----------------------------------------------------------------------------------------------------------------------------------------------
--     Author:      Remko de Boer, Triennium Rivium
--     Created:     2011-04-01
--     Database:    New database
--     Schema:      dbo
--     Type:        script
--     Purpose:     Create a low level / high level security proxy to handle user requests to start sql server agent jobs. 
--                    Concept is based on the idea that users only should be allowed to have read and write access to table 
--                    by using stored procedures. The job request insert stored procedure is a low level security & risk,
--                    which all alllowed users should have access to and the right to execute this procedure.
--                    The Job that checks the queue runs on a high security level and therefor is able to start the requested sql agent jobs.
--
--                    Many thanks to Theo Ekelmans for his aid and tips for this solution
----------------------------------------------------------------------------------------------------------------------------------------------
--    Install Instruction
--
--    Edit the @mdfpath and @logpath parameters to the correct filepaths
--
--    Execute the script
--
--    To obtain available jobnames for the UserJobMapping table:
--    select * from msdb.dbo.sysjobs
--
--    To obtain user_id's for the UserJobMapping table:
--    select * from sys.server_principals
--
--    With these 2 queries you should be able to set the correct user to job mappings.
--
----------------------------------------------------------------------------------------------------------------------------------------------
--     Version:     Date:        Change:
--     1.0          2011-04-11   Initial
----------------------------------------------------------------------------------------------------------------------------------------------
USE [master]
go


declare @mdfpath nvarchar(250)
declare @logpath nvarchar(250)
declare @createstring nvarchar(max)

set @mdfpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' -- Edit to the correct file path
set @logpath = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' -- Edit to the correct file path

-- NO Editing past this point ----------------------------------------------------------------------------------------------------------------

-- Database creation --
set @mdfpath = @mdfpath + N'JobExecution.mdf'
set @logpath = @logpath + N'JobExecution_log.ldf'

set @createstring = 'CREATE DATABASE [JobExecution] ON  PRIMARY '
set @createstring = @createstring + '( NAME = N''JobExecution'', FILENAME =N''' + @mdfpath + ''' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) '
set @createstring = @createstring + ' LOG ON '
set @createstring = @createstring + '( NAME = N''JobExecution_log'', FILENAME = N''' + @logpath + ''' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) '

exec sp_executesql  @createstring
GO

ALTER DATABASE [JobExecution] SET COMPATIBILITY_LEVEL = 100
GO

USE [JobExecution]
GO

-- JobQueue table creation --
CREATE TABLE [dbo].[JobQueue](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [created] [datetime] NULL,
       [job_type] [int] NULL,
       [inprocess] [int] NOT NULL,
       [finished] [int] NOT NULL,
       [job_name] [varchar](250) NULL,
       [prio] [int] NULL,
       [user_id] [int] NULL,
       [runat] [datetime] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[JobQueue] ADD  CONSTRAINT [DF_JobQueue_inprocess]  DEFAULT ((0)) FOR [inprocess]
GO
ALTER TABLE [dbo].[JobQueue] ADD  CONSTRAINT [DF_JobQueue_finished]  DEFAULT ((0)) FOR [finished]
GO
ALTER TABLE [dbo].[JobQueue] ADD  CONSTRAINT [DF_JobQueue_prio]  DEFAULT ((0)) FOR [prio]
GO




-- UserJobMapping table creation
CREATE TABLE [dbo].[UserJobMapping](
       [id] [int] IDENTITY(1,1) NOT NULL,
       [user_id] [int] NULL,
       [job_id] [uniqueidentifier] NULL
) ON [PRIMARY]
GO

--------------------------------------------------------------------------------------------
--     Queue control 
--------------------------------------------------------------------------------------------
--     Author:      Remko de Boer, Triennium Rivium & Theo Ekelmans
--     Created:     2011-04-01
--     Database:    JobExecution
--     Schema:      dbo
--     Type:        Stored Procedure
--     Purpose:     Check if there is a job in the queue and execute based on set rights and prevent hammering
--------------------------------------------------------------------------------------------
--     Version:     Date:        Change:
--     1.0          2011-04-11   Initial
--------------------------------------------------------------------------------------------
create proc [dbo].[spQueueCheck]

as

declare @count int
declare @id int
declare @jobid uniqueidentifier
declare @right int
declare @hammering int

set @count = (select COUNT(1) from JobQueue where [inprocess] = 0)

if @count > 0
BEGIN

set @id = (select top 1 id from JobQueue where [inprocess] = 0)

set @hammering = (select COUNT(1) from JobQueue where job_name = (select job_name from JobQueue where ID = @id) and [inprocess] = 0)

if @hammering < 1

       BEGIN

       set @jobid = (select job_id from msdb.dbo.sysjobs where name = (select job_id from JobQueue where ID = (select id from JobQueue where [inprocess] = 0)))

       set @right = (select COUNT(1) from userjobmapping ujm inner join JobQueue w on ujm.user_id = w.user_id and ujm.job_id = @jobid)

       if @right > 0
             BEGIN
             
             EXEC msdb.dbo.sp_start_job @jobid
             
             END
       
       END

END
GO
--------------------------------------------------------------------------------------------
--     Insert Job into Queue 
--------------------------------------------------------------------------------------------
--     Author:      Remko de Boer, Triennium Rivium & Theo Ekelmans
--     Created:     2011-04-01
--     Database:    JobExecution
--     Schema:      dbo
--     Type:        Stored Procedure
--     Purpose:     Insert a job into the queue
--------------------------------------------------------------------------------------------
--     Version:     Date:        Change:
--     1.0                 2011-04-11   Initial
--------------------------------------------------------------------------------------------
CREATE proc [dbo].[spInsertJobExecution]
@job_type int
,@job_name varchar(250)
,@prio int
,@runat datetime

AS
       BEGIN
            if @runat > GETDATE()
       

             INSERT INTO [JobExecution].[dbo].[JobQueue]
                    (
                    [created]
                    ,[job_type]
                    ,[job_name]
                    ,[prio]
                    ,[user_id]
                    )
             VALUES
                    (
                    GETDATE(),
                    @job_type,
                    @job_name,
                    @prio,
                    SYSTEM_USER
                    )
       END
GO

-- Create Job
USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'JobExecutionJob', 
             @enabled=1, 
             @notify_level_eventlog=2, 
             @notify_level_email=0, 
             @notify_level_netsend=0, 
             @notify_level_page=0, 
             @delete_level=0, 
             @description=N'SQL Loop job to check if there is a job request in the queue', 
             @category_name=N'[Uncategorized (Local)]', 
             @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Stap 1 : Controle]    Script Date: 04/11/2011 16:37:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 1 : Check', 
             @step_id=1, 
             @cmdexec_success_code=0, 
             @on_success_action=4, 
             @on_success_step_id=2, 
             @on_fail_action=2, 
             @on_fail_step_id=0, 
             @retry_attempts=0, 
             @retry_interval=0, 
             @os_run_priority=0, @subsystem=N'TSQL', 
             @command=N'exec spQueueCheck', 
             @database_name=N'JobExecution', 
             @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Stap 2 : Loop]    Script Date: 04/11/2011 16:37:51 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step 2 : Loop', 
             @step_id=2, 
             @cmdexec_success_code=0, 
             @on_success_action=4, 
             @on_success_step_id=1, 
             @on_fail_action=2, 
             @on_fail_step_id=0, 
             @retry_attempts=0, 
             @retry_interval=0, 
             @os_run_priority=0, @subsystem=N'TSQL', 
             @command=N'WAITFOR DELAY ''00:00:05''


', 
             @database_name=N'master', 
             @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

exec msdb.dbo.sp_start_job 'JobExecutionJob'
go



Posted On Thursday, May 19, 2011 6:32 PM | Comments (0) |

Powered by: