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…

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…

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…

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…

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],…

In SQL Server you can define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers. By specifying the context in which the module is executed, you can control which user account the…

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); DECLARE @dbid smallint; — Conditionally select tables…