There are several reasons for excessive log growth. Please go through the below possible reasons.
SELECT name , recovery_model_desc , log_reuse_wait_desc FROM sys.databases
Log_reuse_wait_desc provides the reason for why the Log space was not reused. For possible reasons please navigate to https://msdn.microsoft.com/en-us/library/ms178534.aspx.
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. There is a misconception that eventually the transaction log will run out of space, but this is where the transaction log is so different from data files.
The transaction log is really a circular file—as long as the log records at the start of the transaction log have been truncated (or cleared). Then when logging reaches the end of the transaction log, it wraps around to the start again and begins overwriting what was there before.
A log record is no longer needed in the transaction log if all of the following are true:
A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. Every so often, the transaction log is checked to see whether all the log records in a full VLF are active or not; if they are all inactive, the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps). When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.
This process is called log truncation—not to be confused with actually shrinking the size of the transaction log. Log truncation never changes the physical size of the transaction log—only which portions of the transaction log are active or not. Below image shows the transaction log after truncation has occurred to the above image.
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 ON p.object_id = i.object_id AND p.index_id = i.index_id JOIN sys.allocation_units AS a ON a.container_id = p.partition_id WHERE OBJECT_NAME(i.object_id) = @tblName GROUP BY i.object_id , i.index_id , i.name ORDER BY OBJECT_NAME(i.object_id) , i.index_id; END;
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 a starting point for any database tuning. However, to my surprise, today this DMV held very few rows for the database I was attempting to tune.
A little bit of investigation revealed that my oldest plan in cache for this database was created just after the recovery model had been changed from FULL to SIMPLE. Now, I know that certain SQL Server operations can flush cache, for example changing the max degree of parallelism on a server – but I’d never come across this before..
I wonder how many DBAs are inadvertently flushing procedure cache if they are switching recovery models often – for example, to help with bulk-logged performance ?
Here’s a simple test script to demonstrate :-
1. Create a database
USE Master GO CREATE DATABASE PlanCacheTest
2. Confirm the recovery model is full.
SELECT name, recovery_model_desc FROM sys.databases
3 Create a test proc in the database.
USE PlanCacheTest CREATE PROC PlanCacheProc AS SELECT * FROM sysindexes ORDER BY dpages DESC
4. Run the proc and check there is an entry in the procedure cache now
EXEC PlanCacheProc SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = DB_ID('PlanCacheTest')
Will return 1 row.
5. Now change the recovery model
ALTER DATABASE PlanCacheTestSET RECOVERY SIMPLE
6. Now look in the procedure cache again, the plan for my new proc has gone…
SELECT * FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt WHERE qt.dbid = DB_ID('PlanCacheTest')
Will return 0 rows.
USE Master GO DROP DATABASE PlanCacheTest
I see the same behaviour switching between all 3 recovery models.
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 DEFAULT constraint'' WHEN sysobjects.xtype = ''F'' THEN ''Foreign Key'' WHEN sysobjects.xtype = ''FN'' THEN ''Scalar function'' WHEN sysobjects.xtype = ''P'' THEN ''Stored Procedure'' WHEN sysobjects.xtype = ''PK'' THEN ''PRIMARY KEY constraint'' WHEN sysobjects.xtype = ''S'' THEN ''System table'' WHEN sysobjects.xtype = ''TF'' THEN ''Function'' WHEN sysobjects.xtype = ''TR'' THEN ''Trigger'' WHEN sysobjects.xtype = ''U'' THEN ''User table'' WHEN sysobjects.xtype = ''UQ'' THEN ''UNIQUE constraint'' WHEN sysobjects.xtype = ''V'' THEN ''View'' WHEN sysobjects.xtype = ''X'' THEN ''Extended stored procedure'' END AS [Object Type] FROM sysobjects WHERE sysobjects.type IN (''C'', ''D'', ''F'', ''FN'', ''P'', ''K'', ''S'', ''TF'', ''TR'', ''U'', ''V'', ''X'') AND sysobjects.category = 0 AND CHARINDEX(''@SearchString'', sysobjects.name) > 0 AND ((CHARINDEX(''@NotContains'', sysobjects.name) = 0 –OR CHARINDEX(''@NotContains'', sysobjects.name) <> 0 ))) a'; SET @TextInit = REPLACE(@TextInit, '@SearchString', @SearchString); SET @TextInit = REPLACE(@TextInit, '@NotContains', @NotContains); SELECT name AS dbName , CAST(NULL AS VARCHAR(255)) AS ObjectName , CAST(NULL AS VARCHAR(255)) AS ObjectType INTO ##Temp1 FROM master.dbo.sysdatabases ORDER BY name; SELECT * INTO ##Temp2 FROM ##Temp1 WHERE 1 = 0; DECLARE @Code VARCHAR(255) , @Count INT , @Incrementer INT; SET @Count = ( SELECT COUNT(dbName) FROM ##Temp1 ); DECLARE c_k CURSOR LOCAL FAST_FORWARD FOR SELECT dbName FROM ##Temp1 ORDER BY dbName DESC; OPEN c_k; FETCH NEXT FROM c_k INTO @Code; SET @Incrementer = 1; WHILE ( @@fetch_status = 0 ) AND ( @Incrementer <= @Count ) BEGIN SET @Text = REPLACE(@TextInit, '@Code', @Code); PRINT @Text; EXEC (@Text); SET @Incrementer = @Incrementer + 1; DELETE FROM ##Temp1 WHERE dbName = @Code; FETCH NEXT FROM c_k INTO @Code; END; CLOSE c_k; DEALLOCATE c_k; SELECT * FROM ##Temp2 ORDER BY dbName , ObjectType; DROP TABLE ##Temp2; DROP TABLE ##Temp1;