Category: SQL Server

Reasons for Excessive Log Growth

There are several reasons for excessive log growth. Please go through the below possible reasons. Operating a database in FULL recovery model, without taking log backups Performing index maintenance Index maintenance operations are a very common cause of excessive transaction log usage and growth, especially in databases using the FULL recovery model. The amount of

Physical Architecture of Transaction Log

The physical architecture of the transaction log is shown in below image. It is split internally into smaller chunks called virtual log files (or VLFs). These are simply an aid to easier internal management of the transaction log. When a VLF becomes full, logging automatically proceeds to use the next VLF in the transaction log.

Finding Size of Index

Below Store Procedure Provides the Indexes and its sizes for the given table CREATE PROCEDURE getIndexSizes ( @tblName VARCHAR(500) ) AS EXECUTE AS CALLER; BEGIN SELECT OBJECT_NAME(i.object_id) AS TableName , AS IndexName , i.index_id AS IndexID , ( 8 * SUM(a.used_pages) ) / 1024 AS 'Indexsize(MB)' FROM sys.indexes AS i JOIN sys.partitions AS p

Search for an Object in all Databases

CREATE PROCEDURE [dbo].[SearchObject] ( @SearchString VARCHAR(255) , @NotContains VARCHAR(255) ) AS DECLARE @Text VARCHAR(1500) , @TextInit VARCHAR(1500); SET @TextInit = 'USE @Code INSERT INTO ##Temp2 SELECT ''@Code'' AS dbName, a.[Object Name], a.[Object Type] FROM (SELECT DISTINCT AS [Object Name], CASE WHEN sysobjects.xtype = ''C'' THEN ''CHECK constraint'' WHEN sysobjects.xtype = ''D'' THEN ''Default or