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
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.
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 , i.name 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
Did you know that when you change the recovery model of a database – plans for that database are removed from cache ? I didn’t… SQL 2012 :- I was doing some performance tuning today, using DMV sys.dm_exec_query_stats to look at summary information for database performance. I normally find this a very useful DMV as
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 sysobjects.name AS [Object Name], CASE WHEN sysobjects.xtype = ''C'' THEN ''CHECK constraint'' WHEN sysobjects.xtype = ''D'' THEN ''Default or