Showing posts with label Log Files. Show all posts
Showing posts with label Log Files. Show all posts

Friday, 21 September 2012

Create Database Script

I always try to enforce standards across all the SQL Servers I manage, it makes tons of administrative tasks much easier to complete and ensures consistency across the estate.  One thing that really irritates me is existing “legacy” environments that do not conform to these standards.  For example I have server X which is running SQL Server 2005 on Windows Server 2003, this server was installed and configured long before I started working for my employer and has a disk configuration of;

C:\ - OS
D:\ - SQL Server files

With this non-standard drive configuration my standard create database script falls flat on its face as the Data and Log directories are hard coded (not brilliant I know but with consistency it works a treat :)  As my Standard installation guide specifies the Data and Log directory paths I know that any new servers will be consistent but the new script will also work for existing “legacy” servers with non-standard drive configurations.

Below is the modified script, It will create a database called dbLogging in the default data and log directories with a few other settings, which may or may not be what you require so get modding.  Yes it does assume that the database xabcx123xdbxdefaultxfilexpathx321xcbax does not exist, to be honest I don’t really care, if someone has a database with that name then I would love to know why.  If you do then you just need to do a find and replace to something more unconventional like say dbAdmin or dbSQLAdmin.

/*
      -----------------------------------------------------------------
      Create database with files in default the directories
      -----------------------------------------------------------------
   
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
    
      -----------------------------------------------------------------
*/
-- set database context
USE master;
GO

-- Create a temp database
CREATE DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax;

-- Declare variables
DECLARE @DefaultDataFilePath VARCHAR(512)
DECLARE @DefaultLogFilePath VARCHAR(512)
DECLARE @DatabaseName VARCHAR(512)
DECLARE @DataFileName VARCHAR(517)
DECLARE @LogFileName VARCHAR(517)
DECLARE @DataFileExtension VARCHAR(4)
DECLARE @LogFileExtension VARCHAR(4)
DECLARE @SQL VARCHAR(4000)

/*
      *** THIS IS ALL YOU NEED TO SPECIFY ***
*/
SET @DatabaseName = 'dbLogging'

-- Set variables
SET @DataFileName = @DatabaseName + '_Data'
SET @LogFileName = @DatabaseName + '_Log'
SET @DataFileExtension = '.mdf'
SET @LogFileExtension = '.ldf'

-- Get the default data path  
SELECT @DefaultDataFilePath =   
(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)
    FROM sys.master_files mf  
    INNER JOIN sys.databases d  
    ON mf.database_id = d.database_id  
    WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 0);

-- Get the default log path  
SELECT @DefaultLogFilePath =   
(   SELECT LEFT(physical_name,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1)  
    FROM sys.master_files mf  
    INNER JOIN sys.databases d  
    ON mf.database_id = d.database_id
    WHERE d.[name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' AND type = 1);

-- Drop the temp database
IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = 'xabcx123xdbxdefaultxfilexpathx321xcbax' )  
BEGIN 
    DROP DATABASE xabcx123xdbxdefaultxfilexpathx321xcbax  
END;

-- If the database already exists print message tro client
IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @DatabaseName) 
BEGIN
      -- Print message if database already exists
      PRINT 'Database ' + @DatabaseName + ' already exists on ' + @@SERVERNAME
END;

-- Build up SQL string to create database
IF NOT EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @DatabaseName)  
BEGIN
      SET @SQL = 'CREATE DATABASE ' + '[' + @DatabaseName + ']' +'
      ON
      ( NAME = ' + '[' + @DataFileName + ']' + ',
            FILENAME = '''+ @DefaultDataFilePath + @DataFileName + @DataFileExtension + ''',
            SIZE = 1024MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10% )
      LOG ON
      ( NAME = ' + '[' + @LogFileName + ']' + ',
            FILENAME = '''+ @DefaultLogFilePath + @LogFileName + @LogFileExtension + ''',
            SIZE = 1024MB,
            MAXSIZE = UNLIMITED,
            FILEGROWTH = 10% )'

-- Create the database
--Try Catch block to create database                 
      BEGIN TRY
            EXEC ( @SQL )
            PRINT @DatabaseName + ' has been created on ' + @@SERVERNAME
      END TRY
      BEGIN CATCH
            SELECT  @DatabaseName message_id,
                        severity,
                        [text],
                        @SQL
            FROM    sys.messages
            WHERE   message_id = @@ERROR
                        AND language_id = 1033 --British English
      END CATCH
END;
GO

Enjoy!

Chris

Saturday, 25 February 2012

A Script A Day - Day 20 - User Database VLF Count

Today’s script will execute DBCC LOGINFO for all user databases.  This is a script I use to monitor the number of Virtual Log Files (VLF’s).  Kimberly Tripp has a great article on VLF’s here.

/*
      -----------------------------------------------------------------
      User Database VLF Count
      -----------------------------------------------------------------
     
      For more SQL resources, check out SQLServer365.blogspot.co.uk

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.
 
      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"
     
      -----------------------------------------------------------------
*/
-- Set database context
USE master;
GO
-- Declare variables
DECLARE @DatabaseName VARCHAR(100)
DECLARE @MinDatabaseID INT
DECLARE @MaxDatabaseID INT
DECLARE @SQL VARCHAR(200)
           
-- Check for temporary table and drop it if it exists
IF OBJECT_ID('tempDB.dbo.#Database') IS NOT NULL
    DROP TABLE [#Database] ;

-- Create temporary table
CREATE TABLE #Database
    (
      ID INT IDENTITY(1, 1),
      DatabaseName VARCHAR(100)
    )
       
-- Check for existing user databases
IF EXISTS ( SELECT  name
            FROM    sys.databases
            WHERE   database_id > 4
                    AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',
                                      'distribution' ) )
    BEGIN
            -- Insert all database names into a temporary table
        INSERT  INTO #Database ( DatabaseName )
                SELECT  name
                FROM    sys.databases
                WHERE   database_id > 4
                        AND name NOT IN ( 'ReportServer', 'ReportServerTempDB',
                                          'distribution' )
                                                
            -- Set variables for the loop            
        SELECT  @MinDatabaseID = MIN(ID),
                @MaxDatabaseID = MAX(ID)
        FROM    #Database

            -- Begin loop
        WHILE @MinDatabaseID <= @MaxDatabaseID
            BEGIN
                        -- Get databaseName
                SELECT  @DatabaseName = DatabaseName
                FROM    #Database
                WHERE   ID = @MinDatabaseID
           
                        -- Build command
                SET @SQL = 'DBCC LOGINFO(' + '''' + @DatabaseName + ''''
                    + ');'
           
                        -- Try Catch block to execute SQL and handle errors              
                BEGIN TRY
                              -- Execute SQL
                    EXEC ( @SQL
                        )
                    PRINT 'DBCC LOGINFO RUN FOR ' + @DatabaseName
                END TRY
                BEGIN CATCH
                    SELECT  @DatabaseName,
                            message_id,
                            severity,
                            [text],
                            @SQL
                    FROM    sys.messages
                    WHERE   message_id = @@ERROR
                            AND language_id = 1033 -- British English
                END CATCH
           
                        --Get the next databasename ID
                SET @MinDatabaseID = @MinDatabaseID + 1    
                  -- End loop
            END
    END
GO

Enjoy!

Chris