SQL Admin Script Collection


Decided to consolidate my last blog posts into a single script collection.

Script to empty and remove a File from a SQL Database

Alter Database AdventureWorksDW2014 set multi_user with rollback immediate

USE [AdventureWorksDW2014]
GO
DBCC SHRINKFILE (N'fileneu' , EMPTYFILE)
GO

USE [AdventureWorksDW2014]
GO
ALTER DATABASE [AdventureWorksDW2014]  REMOVE FILE [fileneu]
GO

 

Script to defragment SQL Server Log Files – improves DML and Backup/Restore performance

 

--Speichere aktuelle Logfile größe in Temporärer Tabelle:

use master

SELECT instance_name AS DatabaseName,

       [Data File(s) Size (KB)] as 'dbfilekb',

       [LOG File(s) Size (KB)] as 'logfilesizekb',

       [Log File(s) Used Size (KB)] as 'logfileusage',

       [Percent Log Used]

          into LogUsage

FROM

(

   SELECT *

   FROM sys.dm_os_performance_counters

   WHERE counter_name IN

   (

       'Data File(s) Size (KB)',

       'Log File(s) Size (KB)',

       'Log File(s) Used Size (KB)',

       'Percent Log Used'

   )

     AND instance_name != '_Total'

) AS Src

PIVOT

(

   MAX(cntr_value)

   FOR counter_name IN

   (

       [Data File(s) Size (KB)],

       [LOG File(s) Size (KB)],

       [Log File(s) Used Size (KB)],

       [Percent Log Used]

   )

) AS pvt

 

--Generiere Script um Logfiles zu shrinken

declare @DatabaseName varchar(255)

declare @logfilename varchar(255)

declare c cursor for Select DatabaseName,m.name

from LogUsage l join sys.master_files m on l.DatabaseName = DB_NAME(m.database_id) where type_desc = 'LOG'

and Databasename not in ('master','model','msdb','tempdb')

open c

fetch next from c into @DatabaseName,@logfilename

while @@FETCH_STATUS = 0

begin

       print 'USE [' + RTRIM(LTRIM(@DatabaseName)) + ']'

       print 'GO'

       print 'DBCC SHRINKFILE (N'''+@logfilename+''', 0, TRUNCATEONLY)'

       print 'GO'

  fetch next from c into @DatabaseName,@logfilename

end

close c

deallocate c

 

--Generiere Script um die Logfiles neu zu sizen:

 

declare @DatabaseName varchar(255)

declare @logfilename varchar(255)

declare @newsize varchar(255)

declare c cursor for Select DatabaseName,m.name,logfilesizekb*1.2

from LogUsage l join sys.master_files m on l.DatabaseName = DB_NAME(m.database_id) where type_desc = 'LOG'

and Databasename not in ('master','model','msdb','tempdb')

open c

fetch next from c into @DatabaseName,@logfilename,@newsize

while @@FETCH_STATUS = 0

begin

print 'USE [' + RTRIM(LTRIM(@DatabaseName)) + ']'

print 'GO'

print 'ALTER DATABASE ['+RTRIM(LTRIM(@DatabaseName)) +'] MODIFY FILE ( NAME = N'''+@logfilename+''', SIZE = '+@newsize+'KB )'

print 'GO'   

  fetch next from c into @DatabaseName,@logfilename,@newsize

end

close c

deallocate c

 

-- Danke für diesen Korrekturkommentar:

Klaus

Mon, Oct 12 2015 9:32 AM

#

Hallo,

danke für das Script!

Es hat aber m.E. das Problem, dass durch die Multiplikation der neuen Logfilegröße mit 1.2 die entstehenden Kommastellen an den SIZE Parameter von ALTER DATABASE übergeben werden, was nicht erlaubt ist. Verzichtet man auf diese Multiplikation, funkioniert alles.

Oder man schneidet die entstehenden Kommastellen per STR-Funktion ab: LTRIM(STR(logfilesizekb, 255, 0))

Möchte man die Angabe der neuen Log file size dort in MB oder GB haben, hat man das gleiche Problem, wenn man nicht aufpasst.

Man müsste dann für die Angabe von MB LTRIM(STR(logfilesizekb / 1024.0, 255, 0)) rechnen und im ALTER DATABASE natürlich MB oder GB angeben.

 

SQL Script to set all Log and Database files to fixed size absolute growth values

 

EXEC sp_MSForEachDB 'Use [?];

declare @filename nvarchar(4000)

declare @filetype int

declare @cmd nvarchar(4000)

declare @DB varchar(255)

SELECT @DB = ''?''

if (@DB <> ''master'' AND @DB <> ''tempdb'' AND @DB <> ''msdb'' AND @DB <> ''model'') --DBEXCLUSIONLIST

begin

       declare c_files cursor for Select [NAME],[TYPE] from sys.database_files

       open c_files

       fetch next from c_files into @filename, @filetype

       while @@FETCH_STATUS = 0

       begin

             if (@filetype = 0)

             begin

                    set @cmd = ''ALTER DATABASE [?] MODIFY FILE ( NAME = N''''''+@filename+'''''',MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )''

                    print @cmd

                    EXEC sp_executesql @cmd

                    print ''Successfully Changed Data Filegrowth!''

             end

             else if (@filetype = 1)

             begin

                    set @cmd = ''ALTER DATABASE [?] MODIFY FILE ( NAME = N''''''+@filename+'''''',MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )''

                    print @cmd

                    EXEC sp_executesql @cmd

                    print ''Successfully Changed Log Filegrowth!''

             end   

             fetch next from c_files into @filename, @filetype

       end

close c_files

deallocate c_files

end';

SQL Script to migrate SQL Server Server Role Membership

Select 'EXEC sp_addsrvrolemember ''' + p2.name + ''',''' + p.name + ''';' from sys.server_role_members sr join sys.server_principals p on sr.role_principal_id = p.principal_id
join sys.server_principals p2 on sr.member_principal_id = p2.principal_id

 

SQL script to create a restore script to restore a long list of databases and move log and mdf files.

declare @DB varchar(255)
set @DB = ''
declare @DBnext varchar(255)
declare @filename varchar(255)
declare @filetype varchar(255)
declare c cursor for SELECT db_name(database_id) as 'DB',name,type_desc FROM sys.master_files where db_name(database_id) not in ('msdb','model','tempdb','master') order by database_id,type_desc
open c
fetch next from c into @DBnext,@filename,@filetype
while @@FETCH_STATUS = 0
begin
    if (@DB != @DBnext)
    begin
        set @DB = @DBnext
        print 'Restore Database ' + @DB + ' from disk = ''C:\temp\' +@DB +'.bak'' with '
    end
    if (@filetype = 'LOG')
       print 'move ''' + @filename + ''' to ''F:\temp\' + @DB + '.ldf'','
    else if (@filetype = 'ROWS')
       print 'move ''' +@filename + ''' to ''D:\temp\' + @DB + '.mdf'''
    fetch next from c into @DBnext,@filename,@filetype
end
close c
deallocate c

it works fine for databases with 2 files only. However it will not create the right file extension for .ndf files.


Comments (0)

Skip to main content