•  
  • Scripts (6)

Find the database free space sql server The transaction log for database is full

The transaction log for database is full. To find out why space in the log cannot be reused

OR

Could not allocate space for object because the filegroup is full

Reema:

Hello DBA it seems our application not working today. We got some error it looks like some problem with the database.

DBA:

This error mostly occurs because of insufficient space on the drive.

Have you checked the drive space?

Reema:

Yeah, I have checked the drive space in a server –> my computer. It looks I have plenty of space on the server.

DBA:

Can you check the database auto growth setting in the database?

Reema:

I have 30 databases on my server. How can I check it?

DBA:

Check the error log and find which database throwing error and right click the database check the auto growth setting.

Reema:

It seems I am not principally SQL DBA. Can you tell me the steps?

The conversion goes like this.

 

Use of this script:

  1. When you face an insufficient disk space problem you can add your drive letter to the “WHERE CLAUSE” and you can find which file is using more disk space.
    “WHERE A.PHYSICAL_NAME LIKE ‘E%’”

 

  1. In some case, The auto growth setting will be restricted. In this case You can easily find the auto growth setting for all databases and you can change the setting for the database (DB which is causing a problem).

 

  1. In some case, The database size is big but it will not use as much space but you will get an insufficient disk space error.  In this case you can easily identify the database by using second script and you can reclaim the space.

 

Check the drive free space:

XP_FIXEDDRIVES

 

For all Databases:

The following script will show the database current size, auto growth setting and file location.

SELECT DB_NAME(A.DATABASE_ID) AS DBNAME,A.NAME AS FILENAME ,

SIZE/128.0 AS CURRENTSIZE_MB,

B.RECOVERY_MODEL_DESC,A.TYPE_DESC ,

CASE WHEN IS_PERCENT_GROWTH = 0

THEN LTRIM(STR(A.GROWTH * 8.0 / 1024,10,1)) + ' MB, '

ELSE 'BY ' + CAST(A.GROWTH AS VARCHAR) + ' PERCENT, 'END +

CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'

ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'

END AS AUTOGROW,A.PHYSICAL_NAME

FROM SYS.MASTER_FILES A JOIN SYS.DATABASES B

ON A.DATABASE_ID =B.DATABASE_ID

--WHERE A.PHYSICAL_NAME LIKE 'E%'

-- AND A.FILE_ID =2

ORDER BY A.SIZE/128.0 DESC

 

For a particular Database:

The following script will give about the particular database current size, used space; free space and auto grow setting.

SELECT DB_NAME() AS DBNAME,

NAME AS FILENAME,

SIZE/128.0 AS CURRENTSIZE_MB,

SIZE/128.0 - ((SIZE/128.0) - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0)  AS USEDSPACE_MB,

SIZE/128.0 - CAST(FILEPROPERTY(NAME, 'SPACEUSED') AS INT)/128.0 AS FREESPACEMB,

PHYSICAL_NAME,DATABASEPROPERTYEX (DB_NAME(),'RECOVERY') AS RECOVERY_MODEL,TYPE_DESC,

CASE WHEN IS_PERCENT_GROWTH = 0

THEN LTRIM(STR(GROWTH * 8.0 / 1024,10,1)) + ' MB, '

ELSE 'BY ' + CAST(GROWTH AS VARCHAR) + ' PERCENT, 'END +

CASE WHEN MAX_SIZE = -1 THEN 'UNRESTRICTED GROWTH'

ELSE 'RESTRICTED GROWTH TO ' +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ' MB'

END AS AUTO_GROW

FROM SYS.MASTER_FILES

WHERE DATABASE_ID = DB_ID()

 

In most cases, The log file gets bigger when you are not maintained in your database properly. That is choosing the right recovery model and backup types it will help to minimize the log file size.
http://www.sqlserverblogforum.com/2011/06/sql-server-recovery-models-and-backup-types/

Bear in mind: Ask your customer about their RPO and RTO and scheduled the log backup regularly.

 

Additional Reading:

http://www.sqlserverblogforum.com/2011/04/shrinking-the-log-file-script-for-sql-server-database/

http://www.sqlserverblogforum.com/2011/03/difference-between-truncating-and-shrinking-the-transaction-log-file/

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Find the latest databse backup SQL server

by Muthukkumaran kaliyamoorthy Published on: March 5, 2012
Comments: 2 Comments
Tags: ,
Categories:Scripts

Use the simplest script to find the latest database backup and the location.

 

SELECT b.server_name ,b.database_name,b.user_name, f.physical_device_name,
b.backup_finish_date,b.backup_size /1024/1024 AS size_MB,b.type,b.recovery_model,
b.has_bulk_logged_data,b.is_copy_only,f.mirror
FROM MSDB.DBO.BACKUPMEDIAFAMILY F
JOIN MSDB.DBO.BACKUPSET B
ON (f.media_set_id=b.media_set_id)
WHERE database_name='test'
--AND B.type='L'
ORDER BY b.backup_finish_date DESC

 

You can add more conditions on where clause http://msdn.microsoft.com/en-us/library/ms186299.aspx.

Like backup Type

D = Full

I = Differential database

L = Log

F = File or filegroup

G =Differential file

P = Partial

Q = Differential partial

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

Shrinking the log file script for SQL server database

by Muthukkumaran kaliyamoorthy Published on: April 10, 2011
Comments: 15 Comments
Tags: ,
Categories:DBA, Scripts

Shrinking the log file script for SQL server database                               

This script mainly for a DBA’s, because most of the time the log file grow again and again and eats up all the disk space and finally an application goes down.

Errors from SQL server error log:

Error: 9002, Severity: 17, State: 2.

 

The transaction log for database ‘test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

If you got an error “Error: 9002, Severity: 17, State: 2.” like this go and check the log_reuse_wait_desc column in sys.databases by running below command and see what value it returns.

 

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases name='test'


Shrinking the files is not recommended but, anyway to prevent the database down we can shrink the log files at least some crucial situation. Try to avoid shrinking database files as much as possible. See my previous post How to avoid the shrinking.

--history propose
USE [master]
GO
CREATE TABLE [DBO].[tbl_get_logfiles_details](
            [dbname] [SYSNAME] NOT NULL,
            [backup_location] [SYSNAME]NOT NULL,
            [name] [SYSNAME] NOT NULL,
            [log_size] [INT] NULL,
            [log_usedsize] [INT] NULL,
            [log_usedsize%] [DECIMAL](18, 0) NULL,
            [log_reuse_wait_desc] [SYSNAME] NOT NULL,
            [freespace] [INT] NULL,
            [shrink_status] [SYSNAME] NULL,
            [date] [DATETIME] NULL
)

Note:

 

This procedure only works with the following criteria.

  • A backup disk can be a local disk.
  • The log backup has been done at least one time for the all databases.
  • The database recovery model does not simple.

This coding style has been taken from Support-Matrix Brent Ozar websites thanks to Brent.

The 1 GB log file size is rough calculation and you can alter the procedure whatever you want.

Important note: This procedure will not shrink the log file if it has an active transaction (VLF).

 

CREATE PROCEDURE Usp_dba_shrink_logfiles
/*
Summary:        Shrinking the log files to prevent the disk running out of space
Contact:        Muthukkumaran Kaliyamoorhty SQL DBA
Description:    This Sproc will take the each of the log files one by
one and check their size more than 1GB then this will do two kinds of work.
1. Check the log filesize >1GB AND log reuse wait type is not log backup then shrink
2. Check the log filesize >1GB AND log reuse wait type is log backup AND the
drive size greater than backup size then take a backup AND shrink it

ChangeLog:
Date                          Coder                                                    Description
2011-03-11                 Muthukkumaran Kaliyamoorhty               created
2011-04-18                 Muthukkumaran Kaliyamoorhty               added aditional part for make the VLF to inactive

*******************All the SQL keywords should be written in upper case*********************/
AS
  BEGIN
      SET nocount ON

      DECLARE @dbname SYSNAME
      DECLARE @minid INT
      DECLARE @maxid INT
      DECLARE @filename SYSNAME
      DECLARE @log_filesize INT
      DECLARE @sql SYSNAME
      DECLARE @sql1 SYSNAME
      DECLARE @sql2 SYSNAME
      DECLARE @sql3 SYSNAME
      DECLARE @sql4 SYSNAME
      DECLARE @size INT
      DECLARE @log_used_size INT
      DECLARE @bak_locatiON SYSNAME
      DECLARE @freespace INT
      DECLARE @logwait_type SYSNAME

      ---------------------------------------------------------------------------------------------
      --inserting the drive space.
      ---------------------------------------------------------------------------------------------
      CREATE TABLE #drive_size
        (
           drive     CHAR(1),
           freespace INT
        )

      INSERT INTO #drive_size
      EXEC MASTER..Xp_fixeddrives

      DECLARE @tbl_loop TABLE (
        id     INT IDENTITY,
        dbname SYSNAME)

      INSERT INTO @tbl_loop
      SELECT Db_name(f.database_id)
      FROM   MASTER.sys.databases d
             JOIN MASTER.sys.dm_os_performance_counters p
               ON ( d.name = p.instance_name )
             JOIN MASTER.sys.dm_os_performance_counters s
               ON ( d.name = s.instance_name )
             JOIN MASTER.sys.master_files f
               ON ( d.database_id = f.database_id )
             JOIN msdb.dbo.backupset bs
               ON ( Db_name(f.database_id) = bs.database_name )
             JOIN msdb.dbo.backupmediafamily bf
               ON ( bs.media_set_id = bf.media_set_id )
             JOIN #drive_size ds
               ON ( ds.drive = LEFT(bf.physical_device_name, 1) )
      WHERE  p.counter_name LIKE 'log file(s) used size (kb)%'
             AND s.counter_name LIKE 'log file(s) size (kb)%'
             AND f.type_desc = 'log'
             AND f.database_id NOT IN( 1, 2, 3, 4 )
             AND f.size / 128 > 1024
             AND bs.TYPE = 'l'
      GROUP  BY Db_name(f.database_id),
                f.name,
                s.cntr_value / 1024,
                p.cntr_value / 1024,
                CAST(CAST(p.cntr_value AS FLOAT) / CAST(s.cntr_value AS FLOAT)AS
                     DECIMAL(18, 2))
                * 100,
                d.log_reuse_wait_desc,
                ds.freespace
      ORDER  BY s.cntr_value / 1024

      SELECT @minid = MIN(id)
      FROM   @tbl_loop

      SELECT @maxid = MAX(id)
      FROM   @tbl_loop

      ---------------------------------------------------------------------------------------------
      --Archive the log file size for future reference
      ---------------------------------------------------------------------------------------------
      INSERT INTO MASTER.dbo.tbl_get_logfiles_details
      SELECT Db_name(f.database_id),
             MAX(bf.physical_device_name),
             f.name,
             s.cntr_value / 1024,
             p.cntr_value / 1024,
             CAST(CAST(p.cntr_value AS FLOAT) / CAST(s.cntr_value AS FLOAT)AS
                  DECIMAL(
                  18, 2))
             * 100,
             d.log_reuse_wait_desc,
             ds.freespace,
      'Shrinklog',
      Getdate()
      FROM   MASTER.sys.databases d
             JOIN MASTER.sys.dm_os_performance_counters p
               ON ( d.name = p.instance_name )
             JOIN MASTER.sys.dm_os_performance_counters s
               ON ( d.name = s.instance_name )
             JOIN MASTER.sys.master_files f
               ON ( d.database_id = f.database_id )
             JOIN @tbl_loop t
               ON ( Db_name(f.database_id) = t.dbname )
             JOIN msdb.dbo.backupset bs
               ON ( Db_name(f.database_id) = bs.database_name )
             JOIN msdb.dbo.backupmediafamily bf
               ON ( bs.media_set_id = bf.media_set_id )
             JOIN #drive_size ds
               ON ( ds.drive = LEFT(bf.physical_device_name, 1) )
      WHERE  p.counter_name LIKE 'log file(s) used size (kb)%'
             AND s.counter_name LIKE 'log file(s) size (kb)%'
             AND f.type_desc = 'log'
             AND f.database_id NOT IN( 1, 2, 3, 4 )
             AND f.size / 128 > 1024
             AND bs.TYPE = 'l'
      GROUP  BY Db_name(f.database_id),
                f.name,
                s.cntr_value / 1024,
                p.cntr_value / 1024,
                CAST(CAST(p.cntr_value AS FLOAT) / CAST(s.cntr_value AS FLOAT)AS
                     DECIMAL(18, 2))
                * 100,
                d.log_reuse_wait_desc,
                ds.freespace
      ORDER  BY s.cntr_value / 1024

      WHILE ( @minid <= @maxid )
        BEGIN
            SELECT @dbname = Db_name(f.database_id),
                   @bak_locatiON = MAX(bf.physical_device_name),
                   @filename = f.name,
                   @log_filesize = s.cntr_value / 1024,
                   @size = p.cntr_value / 1024,
                   @log_used_size = CAST(CAST(p.cntr_value AS FLOAT) / CAST(
                                         s.cntr_value AS FLOAT)
                                         AS DECIMAL(18, 2))
                                    * 100,
                   @logwait_type = d.log_reuse_wait_desc,
                   @freespace = ds.freespace
            FROM   MASTER.sys.databases d
                   JOIN MASTER.sys.dm_os_performance_counters p
                     ON ( d.name = p.instance_name )
                   JOIN MASTER.sys.dm_os_performance_counters s
                     ON ( d.name = s.instance_name )
                   JOIN MASTER.sys.master_files f
                     ON ( d.database_id = f.database_id )
                   JOIN @tbl_loop t
                     ON ( Db_name(f.database_id) = t.dbname )
                   JOIN msdb.dbo.backupset bs
                     ON ( Db_name(f.database_id) = bs.database_name )
                   JOIN msdb.dbo.backupmediafamily bf
                     ON ( bs.media_set_id = bf.media_set_id )
                   JOIN #drive_size ds
                     ON ( ds.drive = LEFT(bf.physical_device_name, 1) )
            WHERE  p.counter_name LIKE 'log file(s) used size (kb)%'
                   AND s.counter_name LIKE 'log file(s) size (kb)%'
                   AND f.type_desc = 'log'
                   AND f.database_id NOT IN( 1, 2, 3, 4 )
                   AND f.size / 128 > 1024
                   AND bs.TYPE = 'l'
                   AND t.id = @minid
            GROUP  BY Db_name(f.database_id),
                      f.name,
                      s.cntr_value / 1024,
                      p.cntr_value / 1024,
                      CAST(CAST(p.cntr_value AS FLOAT) / CAST(
                           s.cntr_value AS FLOAT)AS
                           DECIMAL(18, 2))
                      * 100,
                      d.log_reuse_wait_desc,
                      ds.freespace
            ORDER  BY s.cntr_value / 1024

            ---------------------------------------------------------------------------------------------
            --Check the log filesize >1GB AND log reuse wait type is not log backup then shrink.
            ---------------------------------------------------------------------------------------------
            IF( @log_filesize >= 1024
                AND @logwait_type <> 'log_backup' )
              BEGIN
                  CHECKPOINT

                  SET @sql='EXEC (''USE[' + @dbname + '];DBCC SHRINKFILE(' +
                           @filename
                           +
                           ')'')'

                  EXEC @sql
              END
            ---------------------------------------------------------------------------------------------
            --Check the log filesize >1GB AND log reuse wait type is log backup AND the drive size
            -- greater than backup size then take a backup AND shrink it.
            ---------------------------------------------------------------------------------------------
            ELSE
              IF( @log_filesize >= 1024
                  AND @logwait_type = 'log_backup'
                  AND @log_filesize < @freespace )
                BEGIN
                    SET @sql1='BACKUP LOG [' + @dbname + '] TO DISK=''' +
                              @bak_locatiON
                              +
                              ''''

                    EXEC @sql1

                    EXEC @sql1

                    SET @sql2='EXEC (''USE[' + @dbname + '];DBCC SHRINKFILE(' +
                              @filename +
                              ')'')'

                    EXEC @sql2

                    UPDATE MASTER.dbo.tbl_get_logfiles_details
                    SET    shrink_status = 'Shrink&Bak'
                END
              ---------------------------------------------------------------------------------------------
              --If the first two conditions are not reduce the log file size because of active VLF,
              --then take a log backup again and mark the active VLF to inactive VLF then shrink it.
              ---------------------------------------------------------------------------------------------
              ELSE
                IF( @log_filesize >= 1000
                    AND @log_filesize < @freespace )
                  BEGIN
                      SET @sql3='BACKUP LOG [' + @dbname + '] TO DISK=''' +
                                @bak_locatiON +
                                ''''

                      EXEC @sql3

                      EXEC @sql3

                      SET @sql4='EXEC (''USE[' + @dbname + '];DBCC SHRINKFILE('
                                +
                                @filename
                                +
                                ')'')'

                      EXEC @sql4

                      UPDATE MASTER.dbo.tbl_get_logfiles_details
                      SET    shrink_status = 'Shrink&Bak_3'
                  END

            SET @minid=@minid + 1
        END
  END

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

INDEX DEFRAGMENTATION SCRIPT for SQL server 2000

 

INDEX DEFRAGMENTATION SCRIPT SQL 2000

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

Use the DBCC SHOWCONTIG statement. It will display the fragmentation information.

 

Here I used 3 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild%

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

How to get the SQL server critical errors via mail using native T-SQL Script

by Muthukkumaran kaliyamoorthy Published on: February 6, 2011
Comments: 13 Comments
Tags: ,
Categories:DBA, Scripts, T-SQL

 

T-SQL Tuesday #015: Automation:

 

 

     

I am very much interested to participate in the T-SQL Tuesday event party. I have seen the T-SQL Tuesday logo and article in one of my favorite SQL server blog Gail Shaw.

I found the Adam Machanic ( Blog | Twitter ) via Google but, I did not find the required information so, I wrote an email to him and he has sent me the link to topic http://sqlasylum.wordpress.com/2011/02/01/invitation-to-t-sql-tuesday-15-automation-in-sql-server.

Thanks to Adam Machanicfor doing this wonderful job. This is my first T-SQL Tuesday post. I have referred some of T-SQL Tuesday blog post.

This month Pat Wright is hosting and has given us the topic of Automation.


This Month Topic is Automation:

It’s a really great topic for DBA’s because, most of the DBA work is to automate everything (I mean 95% of DBA works).

What most of us think about DBAs?

They don’t have any work they are always enjoying their life. I can say if you don’t have work you are a good DBA 🙂 because you automated/protected everything (No performance issues, No data loss, No application downtime …)

In this article I am going to write about  “How to get the SQL server critical error via mail using native T-SQL”.

While doing DBmail test using a Gmail account on my laptop, I got number of errors So, I thought to write an article how to setup the DBmail using Google mail server account. 

 

This is my first version. (I will post it an effective manner at my second version)

What I made in this procedure?

  • I have collected all the critical errors from system stored procedure “EXEC DBA_test. DBO. SP_READERRORLOG”
  • I have inserted the critical errors to “dba_all_errorlog_details” table.
  • Create second stored procedure  “usp_dba_critical_error_DBmail” to send an e-mail to DBA team.(Every two hours)

Note: You can insert the critical errors whatever, you thing as critical.

 

USE [DBA_test]
GO
/****** Object:  Table [dbo].[DBA_ALL_ERRORLOG_DETAILS]    Script Date: 02/05/2011 12:57:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dba_all_errorlog_details](
            [id] [INT] IDENTITY(1,1) NOT NULL,
            [date] [DATETIME] NULL,
            [processinfo] [SYSNAME] NOT NULL,
            [text] [SYSNAME] NOT NULL
) ON [PRIMARY]

USE [DBA_test]
GO
/****** Object:  StoredProcedure [dbo].[usp_dba_critical_error]    Script Date: 02/05/2011 12:57:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author                       : Muthukkumaran Kaliyamoorthy
-- Create date    : 06/02/2011
-- Description    : populate the critical errors to the table
-- =============================================
CREATE PROC [dbo].[usp_dba_critical_error]
AS
BEGIN 

--TRUNCATE TABLE DBA_test.DBO.dba_all_errorlog_details 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,
'Setting database option OFFLINE' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'killed by' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'alter database' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'setting database option recovery' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'account is currently locked out' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'sql server is terminating due to'  

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'deadlock' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'the log is out of space' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'error: 9002'

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 13'

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 17' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 18' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 19' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 20' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 21' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 22' 

INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 23' 

--SELECT * FROM DBA_test.DBO.dba_all_errorlog_details
END 

-- Exec [usp_dba_critical_error_DBmail]

USE [DBA_test]
GO
/****** Object:  StoredProcedure [dbo].[usp_dba_critical_error_DBmail]    Script Date: 02/05/2011 12:57:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author                       : Muthukkumaran Kaliyamoorthy
-- Create date    : 06/02/2011
-- Description    : Send the critical errors to a DBA email account
-- =============================================
CREATE PROC [dbo].[usp_dba_critical_error_DBmail]
AS
BEGIN
EXEC[DBA_test].[DBO].[usp_dba_critical_error]
--truncate table dba_all_errorlog_details
IF EXISTS(SELECT 1 FROM DBA_test.DBO.dba_all_errorlog_details )
BEGIN
DECLARE @processinfo VARCHAR(500)
DECLARE @text VARCHAR(5000)
DECLARE @date VARCHAR(200)
DECLARE @dateadd VARCHAR(200)
DECLARE @maxid int
DECLARE @minid int
SET @dateadd = REPLACE(CONVERT(CHAR(8),DATEADD(HH,-2,GETDATE()),108),':','')
select @minid=MIN(id) from DBA_test.DBO.dba_all_errorlog_details
select @maxid=MAX(id) from DBA_test.DBO.dba_all_errorlog_details
WHILE (@minid<=@maxid)
BEGIN
SELECT @date=[date],@processinfo=[processinfo],@text=[text] FROM DBA_test.DBO.dba_all_errorlog_details
WHERE id=@minid and REPLACE(CONVERT(CHAR(8),DATEADD(hh,-2,date),108),':','') < @dateadd
set @minid=@minid+1
END
DECLARE @body1 VARCHAR(2000)
SET @body1= 'server :following messagees aer crictical errors '+ CHAR(13)
+CHAR(13)
SET @body1= @body1 +
'DATE: '+@date+CHAR(9)+
'PROCESSINFO: '+@processinfo+ CHAR(13)+
'TEXT:'+@text+ CHAR(13)+CHAR(13)
EXEC MSDB.DBO.SP_SEND_DBMAIL @recipients='muthukumark1986@gmail.com',
    @subject = 'server :following messagees aer crictical errors',
    @body = @body1,
    @body_format = 'text' ,@profile_name='test';
END
END

 


The results from SProc

 

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

INDEX DEFRAGMENTATION SCRIPT

 

INDEX DEFRAGMENTATION SCRIPT for 2008 / 2005

I dedicate this article to my senior DBA “Roshan Joe Joseph” who has helped me to write a script.

I have posted the updated INDEX DEFRAGMENTATION script here, which is already published in sqlservercentral (SSC).

For VLDB – Please use Ola Hallengren’s script https://ola.hallengren.com/, Which has many parameters.

Script changes:

·         I used while loop instead of a cursor.
·         I used to rebuild the index if the page level locking is disabled.

Msg 2552, Level 16, State 2, Line 1
The index “index name” (partition 1) on table “table name” cannot be reorganized because page level locking is disabled

 

Index Defragmentation is one of the most important DBA tasks. This will significantly improve query performance. When you perform any DML operation (INSERT, UPDATE, or DELETE statements) table fragmentation can occur. If you use predefined maintenance plan it will take much server resource and time. Here is a custom stored procedure.

If you want to determine the level of fragmentation, you can use the SYS.DM_DB_INDEX_PHYSICAL_STATS statement. The SYS.DM_DB_INDEX_PHYSICAL_STATS DMV displays fragmentation information for the data and indexes of the specified object.

Here I used 4 conditions:

1. Fragmentation >=30 AND PAGES>1000 then rebuild
2. Fragmentation between 15 to 29 AND PAGES>1000 then reorganize & update statistics
3. If the page level locking is disabled then rebuild
4. If the above conditions are false and statistics are outdated more than 10 days then update the statistics

Before you run the procedure create the tables provided for history propose

Read Gail Shaw’s post ‘What is fragmentation’ and how it will affect the performance http://sqlinthewild.co.za/index.php/2008/10/20/what-is-fragmentation/

 

-- For SQL-2005/2008
USE MSDB;
go
CREATE TABLE [dbo].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[table_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[action_taken] [VARCHAR](35) NULL,
[last_stats_date] [datetime] NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go
--Archive the data's in master DB
USE MASTER;
go
CREATE TABLE [dbo].[dba_defrag_maintenance_history]
(
[db_name] [SYSNAME] NOT NULL,
[table_name] [SYSNAME] NOT NULL,
[index_name] [SYSNAME] NOT NULL,
[frag] [FLOAT] NULL,
[page] [INT] NULL,
[action_taken] [VARCHAR](35) NULL,
[last_stats_date] [datetime] NULL,
[date] [DATETIME] NULL DEFAULT (GETDATE())
)
go

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[indexdefragmentation]    Script Date: 09/07/2012 05:18:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--drop proc [indexdefragmentation]
CREATE PROC [dbo].[indexdefragmentation]@p_dbname SYSNAME
/*
Summary:     Remove the Index Fragmentation to improve the query performance
Contact:        Muthukkumaran Kaliyamoorhty SQL DBA
Description:   This Sproc will take the fragmentation details and do four kinds of work.

1. Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
2. Check the fragmentation between 5% to 30% and pages greater than 1000 then reorganize & update stats
3. Check the fragmentation between 5% to 30% and pages greater than 1000 and page level lock disabled then rebuild
4. Update the statistics if the above conditions are false
/*statistics will be updated if its gets 10 days older */
ChangeLog:
Date              Coder                                                             Description
2012-sep-7        Muthukkumaran Kaliyamoorhty                        created
2012-sep-8        Muthukkumaran Kaliyamoorhty                        updates                Stats date    
*************************All the SQL keywords should be written in upper case*************************
*/
AS
BEGIN
SET NOCOUNT ON
DECLARE
@db_name SYSNAME,
@tab_name SYSNAME,
@ind_name VARCHAR(500),
@schema_name SYSNAME,
@frag FLOAT,
@pages INT,
@min_id INT,
@max_id INT,
@stats_date VARCHAR(8)

SET @db_name=@p_dbname

--------------------------------------------------------------------------------------------------------------------------------------
--inserting the Fragmentation details
--------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE #tempfrag
(
id INT IDENTITY,
table_name SYSNAME,
index_name VARCHAR(500),
frag FLOAT,
pages INT,
schemaName SYSNAME,
statsDate VARCHAR(8)
)            

EXEC ('USE ['+@db_name+'];
INSERT INTO #tempfrag (table_name,index_name,frag,pages,schemaName,statsDate)
SELECT OBJECT_NAME(F.OBJECT_ID) obj,i.name ind,
f.avg_fragmentation_in_percent,
f.page_count,OBJECT_SCHEMA_NAME(f.object_id),
CONVERT(VARCHAR(8),STATS_DATE(I.OBJECT_ID,I.index_id),112)as statsdate
FROM SYS.DM_DB_INDEX_PHYSICAL_STATS (DB_ID(),NULL,NULL,NULL,NULL) F
JOIN SYS.INDEXES I
ON(F.OBJECT_ID=I.OBJECT_ID)AND i.index_id=f.index_id
JOIN INFORMATION_SCHEMA.TABLES S
ON (s.table_name=OBJECT_NAME(F.OBJECT_ID))
AND f.database_id=DB_ID()
AND OBJECTPROPERTY(I.OBJECT_ID,''ISSYSTEMTABLE'')=0
AND alloc_unit_type_desc = ''IN_ROW_DATA''
AND is_disabled = 0
'

)
SELECT @min_id=MIN(ID)FROM #tempfrag
SELECT @max_id=MAX(ID)FROM #tempfrag
TRUNCATE TABLE msdb.dbo.dba_defrag_maintenance_history

--SELECT * FROM #tempfrag

WHILE (@min_id<=@max_id)
/*While loop begin*/
BEGIN
SELECT
@tab_name=table_name,
@schema_name=schemaname,
@ind_name=index_name ,
@frag=frag ,
@pages=pages,
@stats_date=statsDate
FROM #tempfrag WHERE id = @min_id

--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation greater than 30% and pages greater than 1000 then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF (@ind_name IS NOT NULL)
/*First if condition*/
BEGIN
IF (@frag>30 AND @pages>1000)
/*Sub if condition 1st */
BEGIN
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REBUILD',@stats_date,GETDATE())
END
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 5% to 30% and pages greater than 1000 then reorganize
--------------------------------------------------------------------------------------------------------------------------------------
ELSE IF((@frag BETWEEN 5 AND 30) AND @pages>1000  )
/*Sub if condition 2nd */
BEGIN
BEGIN TRY
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REORGANIZE ')
IF (@stats_date <CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) ' )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'REORGANIZE & UPDATESTATS',@stats_date,GETDATE())
END
END TRY
BEGIN CATCH
--------------------------------------------------------------------------------------------------------------------------------------
--Check the fragmentation between 15% to 29% and pages greater than 1000 and page level
--lock disabled then rebuild
--------------------------------------------------------------------------------------------------------------------------------------
IF ERROR_NUMBER()=2552
EXEC ('USE ['+@db_name+'];ALTER INDEX ['+@ind_name+'] ON ['+@schema_name+'].['+@tab_name +'] REBUILD ')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'PLLD_REBUILD',@stats_date,GETDATE())
END CATCH
END

--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics  for all indexes if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
/*Sub if condition 3rd*/
ELSE
BEGIN
IF (@stats_date < CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+'] (['+@ind_name+']) '  )
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,@ind_name,@frag,@pages,'UPDATESTATS',@stats_date,GETDATE())
END
END
END
ELSE

BEGIN
IF (@stats_date < CONVERT(VARCHAR(8),DATEADD(HH,1,GETDATE()-10),112))
BEGIN
--------------------------------------------------------------------------------------------------------------------------------------
--Update the statistics  for all tables if the first three conditions is false
--------------------------------------------------------------------------------------------------------------------------------------
EXEC ('USE ['+@db_name+'];UPDATE STATISTICS ['+@schema_name+'].['+@tab_name+']')
INSERT INTO msdb.dbo.dba_defrag_maintenance_history
VALUES (@db_name,@tab_name,'HEAP',@frag,@pages,'UPDATESTATS',@stats_date,GETDATE())
END
/*End of the first if condition*/
END

SET @min_id=@min_id+1
/*While loop end*/
END

DROP TABLE #tempfrag
INSERT INTO master.dbo.dba_defrag_maintenance_history
SELECT * FROM msdb.dbo.dba_defrag_maintenance_history
END
GO

Muthukkumaran kaliyamoorthy

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

More Posts - Website

page 1 of 1

Welcome , today is Tuesday, May 23, 2017