SQL SERVER – Behind the Scene of SQL Server Activity of – Transaction Log – Shrinking Log

Imran Mohammed continues to help community of SQL Server with his very enthusiastic writing and deep understanding of SQL Server architecture. Let us read what Imran has to say about how Transaction Log works and Shrinking of Log works.

Question from lauraV

Please help me understand. I am taking a full backup once a day, and transaction logs once every hour. Why is my LDF file not retaining a “normal” size? It continues to grow. I do not want to break the chain and use truncate only, though I have done this and it fixes the problem. I would very much like to understand the underlying problem.
thank you in advance

I suggest to all my readers to read the answer from Imran, this really explains what really goes on behind the scene of Transaction Log. I have highlighted some of the important keylines and keywords in his answer to lauraV.

Answer from Imran Mohammed


You did not mention which version of SQL Server you are using, Either 2000 or 2005, ( would be easy for us to narrow our answer to questions)

When ever you take transactional log backup, no matter if you scheduled it or if you take it manually, SQL Server by default will empty transactional log. Meaning after the transactional backup ( Be Careful NOT FULL BACKUP) SQL Server will remove inactive transactions from logfile.

Which means your transaction file is empty after you take transactional log backup, Empty doesnot mean your logfile became small, NO. the size of the log file will still be the same but it will be empty, all you have to do is shrink logfile, run below command,

USE database_name
DBCC shrinkfile (logfilename, 1)

and it will shrink the log file to its minimum size possible,

In SQL Server 2000:

Right click database name -> all task -> backup -> in the dialog box, select transactional backup, and click on options ( at the top left side of the box) you will see “remove inactive entries from Transactional log”.

This means when you take transactional Log backup, SQL Server is removing all inactive entries from SQL Server log file. This is default setting.

In SQL Server 2005:

Right Click database name->task ->Backup in the Dialog box, select backup type Transactional backup, and click options tab (at left side up) and under Transaction log section you will see “Truncate the Transaction log”.

Which mean when SQL Server 2005 performs transactional backup it truncates logfile. This is also default setting.

NO matter if you use SQL Server 2000 or SQL Server 2005, SQL Server by default (can be changed) will truncate log after performing Transactional backup.

So simple solution will be shrink log file after you take transactional log backup. You dont have to do it manually You can send this as a response to this transactional bacup job, when ever this job succeed, response to that would be run this script

USE databasename
DBCC shrinkfile ( logfile_name, 1)

That might work.

I will take one more minute to explain what is this active and inactive transaction in the logfile . Please correct me if I am wrong.

This is how SQL Server works,

Note from Pinal – Following 4 paragraph is the most interesting part of whole discussion.

When a transaction comes to SQL Server, it first comes to transactional log buffer, and then it is hardened to disk ( log file, .ldf ) and then it is written to data file ( .mdf). Then we say the transaction is committed or it is inactive, because the transaction performed all the actions that it should.

On the other hand, when a transaction comes to SQL Server and it is entered into log buffer and also in transactional log, but not yet entered in data file, its still in the process, then we will say this transaction as active transaction.

One important point to think is, all the inactive transactions in the log file ( transactions which completed their tasks and are entered in data file) are also present in data file, and SQL Server is smart enough to think, inactive transactions are already in data file and also, they have been backed up( by transactional log backup) hence it thinks, this is the time to get rid of this data and it removes all the inactive transactions.

But for Active transactions, which are either incomplete or could not complete because of disaster ( sudden power failure….) will be stored in transactional log and will be called acive transactions untill they are entered in data file.

This will be little bit confusing…

Read more about this in books online.

Hope this helps.

Reference : Pinal Dave (http://blog.SQLAuthority.com)


If you are DBA who are involved with Database Maintenance and file group maintenance, you must have experience that many times DBCC SHRINKFILE operations takes long time but any other operations with Database are relative quicker. Rebuilding index is quite resource intensive task but that happens faster than DBCC SHRINKFILE.

Well, answer to this is very simple. DBCC SHRINKFILE is a single threaded operation. A single threaded operation does not take advantage of multiple CPUs and have no effect how many RAM are available. Hyperthreaded CPU even provides worst performance.

If you rebuild indexes before you run DBCC SHRINKFILE operations, shrinking file operations will take relatively less time. Rebuilding Index operations takes advantage of multiple CPUs.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Clear SQL Server Memory Caches

If SQL Server is running slow and operations are throwing errors due to lack of memory, it is necessary to look into memory issue. If SQL Server is restarted all the cache memory is automatically cleaned up. In production server it is not possible to restart the server. In this scenario following three commands can be very useful.

When executed following three commands will free up memory for SQL Server by cleaning up its cache.


Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Find Current Identity of Table

Many times we need to know what is the current identity of the column. I have found one of my developer using aggregated function MAX() to find the current identity.

USE AdventureWorks
FROM Person.Address

identity1 SQL SERVER   Find Current Identity of Table

However, I prefer following DBCC command to figure out current identity.

USE AdventureWorks
DBCC CHECKIDENT ('Person.Address')

identity2 SQL SERVER   Find Current Identity of Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 – A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold

Just a day ago, I received email from regular reader Rajiv Kayasthy about a script which demonstrates the A Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold. He found this script on TechNet BOL and was attempting to run on SQL Server but was getting continuous error

Msg 2501, Level 16, State 45, Line 1
Cannot find a table or object with the name “TableName”. Check the system catalog.

After looking at the script provided on BOL I found that it has very small error. It was retrieving data without prefixing database schema. I have modified original script and corrected it to run on SQL Server 2005.

Following corrected script demonstrates Simple Way To Defragment All Indexes In A Database That Is Fragmented Above A Declared Threshold.

/* Originally created by Microsoft */
/* Error corrected by Pinal Dave (http://www.SQLAuthority.com) */
-- Specify your Database Name
USE AdventureWorks;
-- Declare variables
DECLARE @tablename VARCHAR(128);
DECLARE @execstr VARCHAR(255);
DECLARE @objectid INT;
DECLARE @indexid INT;
DECLARE @frag decimal;
DECLARE @maxfrag decimal;
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0;
-- Declare a cursor.
AS Table_Name
-- Create the table.
CREATE TABLE #fraglist (
ObjectName CHAR(255),
ObjectId INT,
IndexName CHAR(255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity decimal,
BestCount INT,
ActualCount INT,
LogicalFrag decimal,
ExtentFrag decimal);
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FROM tables
INTO @tablename;
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
FROM tables
INTO @tablename;
-- Close and deallocate the cursor.
CLOSE tables;
-- Declare the cursor for the list of indexes to be defragged.
ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0;
-- Open the cursor.
OPEN indexes;
-- Loop through the indexes.
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
+ RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(VARCHAR(15),@frag)) + '%';
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
+ RTRIM(@indexid) + ')';
EXEC (@execstr);
FROM indexes
INTO @tablename, @objectid, @indexid, @frag;
-- Close and deallocate the cursor.
CLOSE indexes;
-- Delete the temporary table.
DROP TABLE #fraglist;

Reference : Pinal Dave (http://blog.SQLAuthority.com), TechNet BOL

SQL SERVER – Msg: 2593 : There are ROWCOUNT rows in PAGECOUNT pages for object ‘OBJECT’.

There are ROWCOUNT rows in PAGECOUNT pages for object ‘OBJECT’.

This message is displayed when DBCC command is ran for any database. It is harmless and displayed for information purpose only. For each database DBCC commands displays number of rows and number of pages it is using. DBCC CHECKALLOC is exception for this messages.

dbccmsg SQL SERVER   Msg: 2593 : There are ROWCOUNT rows in PAGECOUNT pages for object OBJECT.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Reclaim Space After Dropping Variable-Length Columns Using DBCC CLEANTABLE

All DBA and Developers must have observed when any variable length column is dropped from table, it does not reduce the size of table. Table size stays the same till Indexes are reorganized or rebuild. There is also DBCC command DBCC CLEANTABLE, which can be used to reclaim any space previously occupied with variable length columns. Variable length columns include varchar, nvarchar, varchar(max), nvarchar(max), varbinary, varbinary(max), text, ntext, image, sql_variant, and xml. Space can be reclaimed when variable length column is also modified to lesser length.

DBCC command for reclaiming space is very simple. Following example is for AdventureWorks database and Person.Contact table.

DBCC CLEANTABLE ('AdventureWorks','Person.Contact', 0)

The result of DBCC is displayed below.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC is fully logged operation. It also does not affect temp tables and system tables.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL – DBCC CLEANTABLE

SQL SERVER – 2005 – Display Fragmentation Information of Data and Indexes of Database Table

One of my friend involved with large business of medical transcript invited me for SQL Server improvement talk last weekend. I had great time talking with group of DBA and developers. One of the topic which was discussed was how to find out Fragmentation Information for any table in one particular database. For SQL Server 2000 it was easy to find using DBCC SHOWCONTIG command. DBCC SHOWCONTIG has some limitation for SQL Server 2000.

SQL Server 2005 has sys.dm_db_index_physical_stats dynamic view which returns size and fragmentation information for the data and indexes of the specified table or view. You can run following T-SQL for any database to know detailed information of the database.
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('test_contig'), NULL, NULL , 'DETAILED')

Above query returns lots of information, most of the time we only need to know Tablename, IndexName and Percentage of Fragmentation. Following query returns only three most important details mentioned earlier. I have added an extra condition where results are filtered where average fragmentation is greater than 20%.

i.name AS IndexName,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 20

The results will help DBA to make necessary reports.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – DBCC CHECKDB Introduction and Explanation – DBCC CHECKDB Errors Solution

DBCC CHECKDB checks the logical and physical integrity of all the objects in the specified database. If DBCC CHECKDB ran on database user should not run DBCC CHECKALLOC, DBCC CHECKTABLE, and DBCC CHECKCATALOG on database as DBCC CHECKDB includes all the three command. Usage of these included DBCC commands is listed below.

DBCC CHECKALLOC – Checks the consistency of disk space allocation structures for a specified database.

DBCC CHECKTABLE – Checks the integrity of all the pages and structures that make up the table or indexed view.

DBCC CHECKCATALOG – Checks for catalog consistency within the specified database. The database must be online.

Along with above three DBCC commands it also runs following two tasks to check the validity database (physical as well logical) i.e. validates the contents of every indexed view in the database and Validates the Service Broker data in the database.

If database DBCC check has returned any errors, the best solution is to RESTORE DATABASE from BACKUP. (Additional reading : SQL Backup and Restore). There is additional keyword REPAIR with DBCC CHECKDB which can be used to repair database but it is not recommended. I will write additional articles on this subject.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , DBCC CHECKDB

SQLAuthority News – Best Articles on SQLAuthority.com

SQL SERVER – Cursor to Kill All Process in Database

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure

SQL SERVER – Shrinking Truncate Log File – Log Full

SQL SERVER – Simple Example of Cursor

SQL SERVER – UDF – Function to Convert Text String to Title Case – Proper Case

SQL SERVER – Restore Database Backup using SQL Script (T-SQL)

SQL SERVER – T-SQL Script to find the CD key from Registry

SQL SERVER – Delete Duplicate Records – Rows


SQL SERVER – Union vs. Union All – Which is better for performance?

SQL SERVER – DBCC RESEED Table Identity Value – Reset Table Identity

SQL SERVER – @@IDENTITY vs SCOPE_IDENTITY() vs IDENT_CURRENT – Retrieve Last Inserted Identity of Record

SQL SERVER – Difference between DISTINCT and GROUP BY – Distinct vs Group By

SQL SERVER – Index Seek Vs. Index Scan (Table Scan)

SQL SERVER – TempDB is Full. Move TempDB from one drive to another drive

SQL SERVER – T-SQL Paging Query Technique Comparison – SQL 2000 vs SQL 2005

SQL SERVER – Performance Optimization of SQL Query and FileGroups


SQL SERVER – 2005 Explanation of TRY…CATCH and ERROR Handling

SQL SERVER – Script to find SQL Server on Network

SQL SERVER – Stored Procedures Advantages and Best Advantage

SQL SERVER – CASE Statement/Expression Examples and Explanation

SQL SERVER – Raid Configuration – RAID 10

SQL SERVER – Understanding new Index Type of SQL Server 2005 Included Column Index along with Clustered Index and Non-clustered Index

SQL SERVER – Query to Find Seed Values, Increment Values and Current Identity Column value of the table

SQL SERVER – Six Properties of Relational Tables


SQL SERVER – Difference between Unique Index vs Unique Constraint

SQL SERVER – 2005 Locking Hints and Examples

SQL SERVER – Good, Better and Best Programming Techniques

SQL SERVER – Random Number Generator Script – SQL Query

SQL SERVER – 2005 TOP Improvements/Enhancements

SQL SERVER – 2005/2000 Examples and Explanation for GOTO

SQL SERVER – Explanation SQL Commando GO

SQL SERVER – 2005 – List all the database

SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String

SQL SERVER – Disable Index – Enable Index – ALTER Index

SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator

SQL SERVER – SQL Code Formatter Tools

SQL SERVER – 2005 Comparison EXCEPT operator vs. NOT IN

SQL SERVER – 2005 NorthWind Database or AdventureWorks Database – Samples Databases

SQL SERVER – 2005 Find Table without Clustered Index – Find Table with no Primary Key

SQL SERVER – 2005 Limiting Result Sets by Using TABLESAMPLE – Examples

SQL SERVER – 2005 Change Database Compatible Level – Backward Compatibility

SQL SERVER – 2005 Constraint on VARCHAR(MAX) Field To Limit It Certain Length

SQL SERVER Database Coding Standards and Guidelines Complete List Download

SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL

SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice

SQL SERVER – 2005 T-SQL Paging Query Technique Comparison (OVER and ROW_NUMBER()) – CTE vs. Derived Table


SQL SERVER – 2005 – UDF – User Defined Function to Strip HTML – Parse HTML – No Regular Expression

SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER – Explanation and Comparison of NULLIF and ISNULL

SQL SERVER – 2005 Row Overflow Data Explanation

SQL SERVER – Comparison Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

SQL SERVER – 2005 Best Practices Analyzer Tutorial – Sample Example

SQL SERVER – 2005 – List All Stored Procedure Modified in Last N Days

SQL SERVER – Count Duplicate Records – Rows

SQL SERVER – CASE Statement in ORDER BY Clause – ORDER BY using Variable

SQL SERVER – Restore Database Without or With Backup – Everything About Restore and Backup

SQL SERVER – UDF – Function to Get Previous And Next Work Day – Exclude Saturday and Sunday

SQL SERVER – One Thing All DBA Must Know

SQL SERVER – 2005 – List Tables in Database Without Primary Key

SQL SERVER – 2005 – Find Stored Procedure Create Date and Modified Date

SQL SERVER – UDF – Validate Integer Function

SQL SERVER – What is SQL? How to pronounce SQL?

SQL SERVER – 2005 – Difference and Similarity Between NEWSEQUENTIALID() and NEWID()

SQL SERVER – 2005 – Explanation and Script for Online Index Operations – Create, Rebuild, Drop

SQL SERVER – Find Last Day of Any Month – Current Previous Next

SQL SERVER – T-SQL Script to Insert Carriage Return and New Line Feed in Code

SQL SERVER – 2005 – T-SQL Script to Attach and Detach Database

SQL SERVER – Actual Execution Plan vs. Estimated Execution Plan

SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

SQL SERVER – 2005 – Introduction and Explanation to sqlcmd

SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

SQL SERVER – 2005 – Start Stop Restart SQL Server From Command Prompt

SQL SERVER – 2005 – List All The Constraint of Database – Find Primary Key and Foreign Key Constraint in Database

SQL SERVER – UDF – Validate Positive Integer Function – Validate Natural Integer Function

SQL SERVER – Effect of TRANSACTION on Local Variable – After ROLLBACK and After COMMIT

SQL SERVER – 2005 – OUTPUT Clause Example and Explanation with INSERT, UPDATE, DELETE

SQL SERVER – 2005 – Sample Example of RANKING Functions – ROW_NUMBER, RANK, DENSE_RANK, NTILE

SQL SERVER – Three T-SQL Script to Create Primary Keys on Table

Reference : Pinal Dave (http://blog.SQLAuthority.com)