SQLProfi.Net  Es muss nicht jeder den gleichen Fehler machen
StartseiteKontaktImpressum

Scripts

Transaction Log auf 20GB kürzen

USE Northwind;

GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE Northwind

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 20000 MB.

DBCC SHRINKFILE (Log01, 20000);

GO

-- Reset the database recovery model.

ALTER DATABASE Northwind

SET RECOVERY FULL;

GO

 

Wenn sich z.B. ein Logfile nicht kürzen läßt

kann es am Status der Datenbank liegen. Über folgenden SQL Befehl,
kann der Status abgefragt werden.

select name, log_reuse_wait_desc from sys.databases;

 

Welche User sind länger als 6 Stunden online?

select sp.spid, RTRIM(sp.loginame)
from master..sysprocesses sp, master..syslogins sl
where sp.sid = sl.sid
and datediff(hour,sp.last_batch,getdate()) > 3
and datepart(yy, sp.last_batch) > 1900
and sl.isntgroup = 0
and sl.name <> 'Domain-User des SQLServers'
and sl.name <> 'sa'

Transaktion Log Backup Script mit eMail Benachrichtigung

/* Transact-SQL Programm Start */

/* variablen Definition */

/* Thomas Lauer (c) - 2010 */

 

declare @body1 varchar(100)

declare @dbtb varchar(100)

declare @dbrealname varchar(100)

declare @wd varchar(10)

declare @madd varchar(100)

declare @disk varchar(100)

declare @sub varchar(100)

 

/* variablen fuellen */

set @dbrealname = 'Northwind-LOG'

set @madd = 'support@glsh.net;

set @wd = CAST(Datepart(hour, getdate()) aS varchar)

set @dbtb = 'Northwind'

set @disk = '\\backupserver\sicherung\BackupSQL\Northwind\LogBack\'+ @dbtb + @wd +'.trn'

 

set @body1 = 'Server :'+@@servername+ char(10)+

                           'Datenbank: '+@dbrealname+ + char(10) +

                           'Datum: '+CONVERT(varchar,GETDATE(),104)

 

/* Backup durchführne */

BACKUP LOG @dbtb

TO  DISK = @disk WITH FORMAT, INIT, 

NAME = @dbrealname, SKIP, REWIND, NOUNLOAD,  STATS = 10

 

/* Backup erfolgreich */

IF @@error = 0

BEGIN

       set @sub = 'Datensicherung '+@dbrealname+' ERFOLGREICH'

       /* Erfolgreich eMail senden */

       EXEC msdb.dbo.sp_send_dbmail @recipients=@madd,

             @subject = @sub,

             @body = @body1,

             @body_format = 'TEXT' ,

             @profile_name = 'Standard';

       Print 'erfolgreich';

END

ELSE

BEGIN

       set @sub = 'Datensicherung '+ @dbrealname +' FEHLER'

       /* Error Mail senden */

       EXEC msdb.dbo.sp_send_dbmail @recipients=@madd,

             @subject = @sub,

             @body = @body1,

             @body_format = 'TEXT',

             @profile_name = 'Standard' ;

       Print 'Fehler';

END

/* Transact-SQL Programm stop */

 

 

 

 

 

 

 

 

 

 

Datenbank Backup Script mit eMail Benachrichtigung

 

 

 

/* Transact-SQL Programm Start */

/* variablen Definition */

/* Thomas Lauer (c) - 2010 */

 

declare @body1 varchar(100)

declare @dbtb varchar(100)

declare @dbrealname varchar(100)

declare @wd varchar(10)

declare @madd varchar(100)

declare @disk varchar(100)

declare @sub varchar(100)

 

/* variablen fuellen */

set @dbrealname = 'Northwind Datenbank'

set @madd = 'support@glsh.net'

set @wd = CAST(Datepart(weekday, getdate()) aS varchar)

set @dbtb = 'Northwind'

set @disk = '\\backupserver\sicherung\BackupSQL\Northwind\'+ @dbtb + @wd +'.bak'

 

set @body1 = 'Server :'+@@servername+ char(10)+

                           'Datenbank: '+@dbrealname+ + char(10) +

                           'Datum: '+CONVERT(varchar,GETDATE(),104)

 

/* Backup durchführne */

BACKUP DATABASE @dbtb

TO  DISK = @disk WITH FORMAT, INIT, 

NAME = @dbrealname, SKIP, REWIND, NOUNLOAD,  STATS = 10

 

/* Backup erfolgreich */

IF @@error = 0

BEGIN

       set @sub = 'Datensicherung '+@dbrealname+' ERFOLGREICH'

       /* Erfolgreich eMail senden */

       EXEC msdb.dbo.sp_send_dbmail @recipients=@madd,

             @subject = @sub,

             @body = @body1,

             @body_format = 'TEXT',

             @profile_name = 'Standard' ;

       Print 'erfolgreich';

END

ELSE

BEGIN

       set @sub = 'Datensicherung '+ @dbrealname +' FEHLER'

       /* Error Mail senden */

       EXEC msdb.dbo.sp_send_dbmail @recipients=@madd,

             @subject = @sub,

             @body = @body1,

             @body_format = 'TEXT' ,

             @profile_name = 'Standard';

       Print 'Fehler';

END

/* Transact-SQL Programm stop */

 

Statistik über Indexe der aktuellen Datenbank

SELECT DB.name AS DatabaseName, IU.database_id, object_name(IU.object_id) AS ObjectName, IU.object_id, IX.name AS IndexName, IX.index_id, user_seeks, user_scans, user_lookups, user_updates, last_user_seek, last_user_scan, last_user_lookup, last_user_update
FROM sys.dm_db_index_usage_stats IU
INNER JOIN master.dbo.sysdatabases AS DB on DB.dbid = IU.database_id
INNER JOIN sys.indexes IX ON IX.object_id = IU.object_id AND IX.index_id = IU.index_id
WHERE objectproperty(IU.object_id,'IsUserTable') = 1 AND IU.database_id = db_id ()
ORDER BY IU.database_id, ObjectName

Datenbank Check durchführen

USE [Northwind]

GO

DBCC CHECKDB WITH NO_INFOMSGS

 

fragmentierten Indexe - korrigieren

 

SELECT object_name(PS.object_id) AS ObjectName,PS.object_id,IX.name AS IndexName,PS.index_id,index_type_desc,avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats ([Nummer_der_Datenbank], NULL, NULL, NULL, NULL) AS PS

INNER JOIN sys.indexes IX ON IX.object_id = PS.object_id AND IX.index_id = PS.index_id

WHERE avg_fragmentation_in_percent > 10 ORDER BY avg_fragmentation_in_percent DESC

 

 

--Die Nummer der Datenbank erhalten Sie über

USE [Northwind]

GO

SELECT DB_ID()

GO

 

--Wann sollten die Index defragmentiert werden?

--Alle Indizes einer Tabelle defragmentieren (sollte lt. MS gemacht werden, wenn avg_fragmentation_in_percent value >= 5 && < 30 liegt, darüber wird REBUILD empfohlen)

--Das folgende Script baut abhänig von der Microsoft Empfehlung die Indexe neu auf:

 

 

 

 

USE [Northwind];

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SELECT

    object_id AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

 

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

 

-- Open the cursor.

OPEN partitions;

 

-- Loop through the partitions.

WHILE (1=1)

    BEGIN;

        FETCH NEXT

           FROM partitions

           INTO @objectid, @indexid, @partitionnum, @frag;

        IF @@FETCH_STATUS < 0 BREAK;

        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

        FROM sys.objects AS o

        JOIN sys.schemas as s ON s.schema_id = o.schema_id

        WHERE o.object_id = @objectid;

        SELECT @indexname = QUOTENAME(name)

        FROM sys.indexes

        WHERE  object_id = @objectid AND index_id = @indexid;

        SELECT @partitioncount = count (*)

        FROM sys.partitions

        WHERE object_id = @objectid AND index_id = @indexid;

 

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

        IF @frag < 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

        IF @frag >= 30.0

            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

        IF @partitioncount > 1

            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

        EXEC (@command);

        PRINT N'Executed: ' + @command;

    END;

 

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

 

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

 

 

Datenbank Statistik erstellen

SELECT Database_ID = DB_ID()

    , Database_Name = DB_NAME()

    , Schema_Name = a3.name

    , TableName = a2.name

    , TableSize_MB = (a1.reserved + ISNULL(a4.reserved,0)) / 128

    , RowCounts = a1.rows

    , DataSize_MB = a1.data / 128

    , IndexSize_MB = (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data

                        THEN (a1.used + ISNULL(a4.used,0)) - a1.data

                        ELSE 0

                    END) /128

    , Free_MB = (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used

                        THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used

                        ELSE 0

                    END) / 128

FROM (SELECT ps.object_id

            , [rows] = SUM(CASE

                                WHEN (ps.index_id < 2) THEN row_count

                                ELSE 0

                            END)

            , reserved = SUM(ps.reserved_page_count)

            , data = SUM(CASE

                            WHEN (ps.index_id < 2)

                                THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)

                            ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)

                        END)

            , used = SUM (ps.used_page_count)

        FROM sys.dm_db_partition_stats ps

        GROUP BY ps.object_id) AS a1

    INNER JOIN sys.all_objects a2  ON a1.object_id = a2.object_id

    INNER JOIN sys.schemas a3 ON a2.schema_id = a3.schema_id

    LEFT JOIN (SELECT it.parent_id

            , reserved = SUM(ps.reserved_page_count)

            , used = SUM(ps.used_page_count)

        FROM sys.dm_db_partition_stats ps

            INNER JOIN sys.internal_tables it ON it.object_id = ps.object_id

        WHERE it.internal_type IN (202,204)

        GROUP BY it.parent_id) AS a4 ON a4.parent_id = a1.object_id WHERE a2.type <> 'S' and a2.type <> 'IT'

    --AND a2.name IN ('spt_values')

ORDER BY a1.reserved desc