Author: Uma Mahesh

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

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 sysobjects.name 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
SiteLock