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

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

Execute As Clause

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 Database Engine uses to validate permissions on objects that are referenced by the module. This