SQL Server: Test Linked Server Connection

SQL Server: Test Linked Server Connection

If you want to have access to data stored in a different server, one of the ways to do so is to make that server as a Linked Server to the current server and then query using the linked server name. Let us assume that there is a server named test available over the network and you want to get some data from there. System stored procedure sp_addlinkedserver can be used to create a linked server and fire distributed queries.

The following code adds the server test as Linked server to the current server

EXEC sp_addlinkedserver test

If you want to know if the connection to the linked server is accessible, you can make use of system stored procedure sp_testlinkedserver as shown below

EXEC sp_testlinkedserver test

It simply checks if the connection is successful. If the result is "Command(s) completed successfully.", it means the connection is successful. This is the simplest way to check if the linked server is accessible

Also check Viewing Linked Server Information in SQL Server 2005/2008

List Empty Tables in SQL Server

List Empty Tables in SQL Server

I was recently doing a clean up of my website database. I remember creating some tables on my database but never adding any new rows to it.

Here’s a simple query to list all empty tables in your SQL Server database that uses aDynamic Management View called dm_db_partition_stats which returns page and row-count information for every partition in the current database.

;WITH EmptyRows AS
(
   SELECT SUM(row_count) AS [TotalRows],
          OBJECT_NAME(OBJECT_ID) AS TableName
   FROM sys.dm_db_partition_stats
   WHERE index_id = 0 OR index_id = 1
   GROUP BY OBJECT_ID
)
SELECT * FROM EmptyRows
WHERE [TotalRows] = 0

OUTPUT 
   


Note that the results from the view is only approximate. If there are any active transactions that are inserting or deleting rows, the count may not include it.

Working with Temporary Objects in SQL Server

Working with Temporary Objects in SQL Server

In this article, we will learn about Temporary objects like Temporary tables in SQL Server. We will also see the different scoping levels for Temporary objects.

So the first question that comes to mind is why should we learn about Temporary objects? There are various scenarios where you can take the advantage of temporary objects. Some of them are as follows – 

• The data which you don’t want to persist in the database for the future usage can be stored in temporary objects.
• You may have some Computed data which you want to use in stored procedures during business processing logic.
• Sometimes you may want to make data visible to a particular session only. In that case, you can store the data into temporary objects.
• You don’t want to clean-up the data explicitly, so you can store the temporary data into temporary objects.

Now let’s start with a demonstration by creating temporary objects. You can create various temporary objects like – 

• Temporary Tables.
• Temporary Stored Procedures.
• Temporary Variables.

The temporary objects can be created as Local objects (using # sign) or global objects (using ## sign). The local temporary objects are available at session level where as the global temporary objects are available across all the sessions.

For this demonstration, I am using SQL Server 2012 with the demo database Northwind. Let’s open SQL Server Management and start creating the temporary objects as described below – 

· We will first of all create a temporary table and insert the data in the table from existing table. The script is as shown below – 



Now save the query file with the name TempObjects.sql and close the query window. After this, open the query window and then try executing the select statement

SELECT * FROM #CustomerCopy

You will get an error as shown below – .



The error says that object does not exist. If you see the script, we have created a temporary table using (#) sign. The single ‘#’ denotes that you have created a Local Temporary table. The life time of the local temporary table is till the session is on. Since we closed the query window, we lost the session.

Now lets rerun the above script and try to access the temporary object in a new query window by using the same SELECT command we just used -



The same error occurs as we lost the current session when we opened a new query window.

Global Temporary Table in SQL Server

Now lets create the same demonstration using Global (##) temporary table and analyze the life time of the table. In the above script, while creating a temporary table, use ‘##’ sign and create the table. The rest of the script will remain same except the table name with ‘##’ sign during insertion and selection. The table script will look like below – 



Run the Select statement into another query pad. You will see the following result – 



Now let’s see some examples on Select * INTO. Start by writing the following queries – 



The above query will create a temporary table which will copy all the rows from Customers table into our CustomerCopy table. If you need only schema of the table, you will have to apply a false condition at the end of the query. For example – WHERE 1=2. This condition is always set to false which will result into a copy of the table without any data.

Try viewing the temporary table metadata. For the non-temporary tables, we will make use of sp_help stored procedure. But for the temporary tables, we will have to make use of TempDB database and make use of sp_help stored procedure to view the metadata as below – 

Temporary Table with User Data Types

Let’s use the User Data Type in our Temporary table. Write the following script – 



When you try to execute the above script, you will get an error as described below – 



The above error says it cannot find the type “CustomerAddress”. But we have already created the type. Well, if you want to use User Types into Temporary tables, you will have to create them into TempDB database. So, let’s create a User Type into our TempDB and create temporary table into our Northwind database as shown below – 



The above query will get executed successfully and your temporary table will get created. You can use sp_help stored procedure to check the data types of the above created table.

Temporary Table using Dynamic Queries

Let’s now create temporary tables using dynamic query. There are couple of restrictions which you will have to consider while creating temporary tables using dynamic SQL as described below – 

• When you create temporary tables using Dynamic SQL, you cannot just first create the table and perform insert/update/delete/ select statements after creation. You will have to create dynamic SQL to create a table, then insert rows in the table and then select them and update them as per our requirements. All this has to be done using dynamic SQL query only.
• The other option could be to first create a temporary table and then use dynamic SQL to insert the data. Then use simple select statements and again use the dynamic SQL for updating the data of temporary tables. This will work. Because the temporary table is available in the Dynamic SQL.

You will have to make a clever choice of how do you want to create temporary object while working with dynamic SQL. Let’s see some examples of the same.



Now try writing a Select or Update Statement after creating the dynamic SQL and you will receive the following error.



The error shown in the above result is for the select statement which I have written after the dynamic SQL.

Create Temporary Table using Constraints

Now let’s take a look at how to create temporary tables using constraints. For this demonstration, we will create two tables as shown below – 



The above queries make uses of Primary key, Not Null and Check constraint. Now let’s try taking the reference of #Exception1 table into #Exception2 table using foreign key. The query and result of the query is as shown below – 



The above query execution tells that the Foreign key constraint cannot be enforced on local or global temporary tables. When you are creating temporary tables, you may want to create the indexes on the table for faster data retrieval operations. You can create an index for example Clustered Index, Non Clustered Index or Unique index on temporary tables. For example –

 

The above index query will create a non-clustered index on Name column of our #Exception1 table. Likewise, while creating a temporary table, you can apply identity to the column to auto generate the values, as well as you can also perform transactions with the temporary tables.

Conclusion

In this article, we have seen how to create temporary objects like temporary tables which we can use to store data which we don’t want to persist in the database. 

In the next article, we will touch base on Temporary Table Variables and also see some limitations while working with table variables compared to temporary tables.

Restore backup of database (.bak) using Sql Server 2005

Restore backup of database (.bak) using Sql Server 2005

If you intend to create a back up of your database in Server A and restore it on a Server B, here's how to go about it. Create a .bak file (Open SSMS > Database > Right Click your database > Tasks > BackUp > Specify the destination).

To do it using script, check my blog over here.

Once the .bak is created, copy this file from Server A to a Server B. We will assume that the file has been copied at the location ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\" at Server B. It can any location that you desire.

How to restore the .bak file

Use this query:

RESTORE DATABASE [SouthWind]
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\South.BAK'
WITH REPLACE,
MOVE 'SouthWind_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Data.MDF',
MOVE 'SouthWind_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\SouthWind_Log.LDF'

where FromDisk is where the .bak file is kept on Server B and MOVE specifies the location of the data and log file.

Change the Default Language for SQL Server

Change the Default Language for SQL Server

I sometimes get questions like – How do I change the default language of SQL Server 2005/2008 from English to Spanish, so as to display the SQL Server system messages in that language.

The answer is that if you have installed a Localized version of SQL Server (say in Spanish), then you get the system messages in two languages – US English and Spanish. In this case, you just have to make sure that the Default Language selected is Spanish.

There are 3 common ways to do so: Using SSMS, T-SQL and PowerShell. We will use SSMS and T-SQL in this article.

USING SSMS

To do so, open SQL Server Management Studio (SSMS) > Right click Server in Object Explorer > Properties > Advanced > Check the ‘Default Language’ property and make sure it is set to Spanish.

Select the language from the DropDown and click OK. Then Stop and Start SQL Server for the new language settings to take effect.

USING T-SQL

If you want to change the default language of SQL Server to a language of your choice using T-SQL, follow these steps:

1. Look up the sys.messages catalog view to check if SQL Server supports a message in your local language.

SELECT msg.language_id, lang.langid, alias 
FROMsys.messages AS msg
JOINsyslanguages AS lang
ON lang.msglangid = msg.language_id
GROUP BY  msg.language_id, lang.langid, alias

You get only 12 rows, which means SQL Server supports system messages and user-defined messages in only 12 local languages. (FEEDBACK: SQL Server should support more local languages!! By the way, check the sp_addmessage which offers some respite)

2. Once you know the LanguageID for your local language, for Spanish it is 5, just use this command to change the default language of SQL Server

EXEC sp_configure "default language", 5
RECONFIGURE WITH OVERRIDE

Start and Stop SQL Server. You can now verify if the local language has changed using the following command

sp_configure 'default language'

If the config_value shows 5, then you are good to go!

Alternatively, if you do not have a localized version of SQL Server, then you can use the SELECT @@language command

SET Language Spanish
SELECT @@language, @@langid

Strangely, if you do not explicitly say ‘SET Language Spanish’ and just run the @@language, @@langid command, the message still shows US_English, even if you have followed the steps I had shown above. That’s why i have explicitly used SET Language Spanish option.

Update: Please note that the contents of this post is valid if you have installed a Localized version of SQL Server, in our case Spanish.

If you have not installed the localized version of SQL Server, the default language is US English. If you need to change the default language on this machine, then you will have to change the default language for individual logins, as doing it on a server level won't work. Also as Geri Reshef points out in the comments section, you will have to use SET LANGUAGE to change the language to Spanish.

SQL Server: All About SQL Server Statistics : How to Detect outdated Statistics ?

SQL Server: All About SQL Server Statistics : How to Detect outdated Statistics ?

In the last post, we have discussed about the auto update statistics threshold and concluded that auto updated statistics threshold are good enough to get optimal performance in some workload. In many cases,a manual update of statistics will help to obtain better performance. In this post let us discuss, how to detect the outdated statistics.

In SQL server 2005 onwards, SQL server uses the ColModCtr to keep track of the changes in the leading column of the statistics. Unfortunatly that is not exposed through any DMV or system view in SQL server 2005 or SQL server 2008. In SQL server 2008 R2 (SP2) onwards, sys.dm_db_stats_properties will give the details of statistics along with the changes in the leading column of the statistics.

For older versions of SQL server users, we need to depends on rowmodctr available in the sys.sysindexes. As per BOL : In SQL Server 2005 and later, rowmodctr is not fully compatible with earlier versions.In earlier versions of SQL Server, the Database Engine maintained row-level modification counters. Such counters are now maintained at the column level. Therefore, the rowmodctr column is calculated and produces results that are similar to the results in earlier versions, but are not exact.

The below query will give an estimate of changes in the statistics.

SELECT 
     TableName=OBJECT_NAME(i.OBJECT_ID)
    ,ObjectType=o.type_desc
    ,StatisticsName=i.[name]
    ,statisticsUpdateDate = STATS_DATE(i.OBJECT_ID, i.index_id)
    ,RecordModified=si.rowmodctr
    ,NumberofRecords=si.rowcnt
FROM sys.indexes i 
JOIN sys.objects o ON    i.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON    i.OBJECT_ID=si.id
    AND i.index_id=si.indid 
WHERE  o.TYPE <> 'S' AND STATS_DATE(i.OBJECT_ID, i.index_id) IS NOT NULL
UNION ALL
SELECT 
     TableName=OBJECT_NAME(o.OBJECT_ID)
    ,ObjectType=o.type_desc
    ,StatisticsName=s.name
    ,statisticsUpdateDate= STATS_DATE(o.OBJECT_ID, s.stats_id)
    ,RecordModified=si.rowmodctr
    ,NumberofRecords=ir.rowcnt
FROM sys.stats s INNER JOIN sys.objects o ON s.OBJECT_ID=o.OBJECT_ID
JOIN sys.sysindexes si ON s.OBJECT_ID=si.id AND s.stats_id= si.indid
INNER JOIN (SELECT id,rowcnt FROM sys.sysindexes WHERE indid IN (0,1)) IR
ON IR.id=o.OBJECT_ID  WHERE  o.TYPE <> 'S'  
AND (s.auto_created=1 OR s.user_created=1)AND STATS_DATE(o.OBJECT_ID, s.stats_id) IS NOT NULL




By keeping this as a base, based on the workload/types of query running, we can schedule the statistics update at appropriate interval instead of blindly updating all statistics.

SQL Server: Performance Tuning :Understanding Set Statistics Time output

SQL Server: Performance Tuning :Understanding Set Statistics Time output

In the last post we have discussed about Set Statistics IO and how it will help us in the performance tuning. In this post we will discuss about the Set Statistics Time which will give the statistics of time taken to execute a query.

Let us start with a example.

USE AdventureWorks2008
GO
            DBCC dropcleanbuffers
            DBCC freeproccache

GO
SET STATISTICS TIME ON
GO
SELECT *
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID
    WHERE ProductID BETWEEN 700
        AND 800
GO
SELECT *
    FROM Sales.SalesOrderHeader SOH INNER JOIN  Sales.SalesOrderDetail SOD ON
            SOH.SalesOrderID=SOD.SalesOrderID
    WHERE ProductID BETWEEN 700
        AND 800




            
















There aretwo select statement in the example .The first one is executed after clearing the buffer. Let us look into the output.

SQL Server parse and Compile time : When we submit a query to SQL server to execute,it has to parse and compile for any syntax error and optimizer has to produce the optimal plan for the execution. SQL Server parse and Compile time refers to the time taken to complete this pre -execute steps.If you look into the output of second execution, the CPU time and elapsed time are 0 in the SQL Server parse and Compile time section. That shows that SQL server did not spend any time in parsing and compiling the query as the execution plan was readily available in the cache. CPU time refers to the actual time spend on CPU and elapsed time refers to the total time taken for the completion of the parse and compile. The difference between the CPU time and elapsed time might wait time in the queue to get the CPU cycle or it was waiting for the IO completion. This does not have much significance in performance tuning as the value will vary from execution to execution. If you are getting consistent value in this section, probably you will be running the procedure with recompile option.

SQL Server Execution Time: This refers to the time taken by SQL server to complete the execution of the compiled plan. CPU time refers to the actual time spend on CPU where as the elapsed time is the total time to complete the execution which includes signal wait time, wait time to complete the IO operation and time taken to transfer the output to the client.The CPU time can be used to baseline the performance tuning. This value will not vary much from execution to execution unless you modify the query or data. The load on the server will not impact much on this value. Please note that time shown is in milliseconds. The value of CPU time might vary from execution to execution for the same query with same data but it will be only in 100's which is only part of a second. The elapsed time will depend on many factor, like load on the server, IO load ,network bandwidth between server and client. So always use the CPU time as baseline while doing the performance tuning.

SQL Server : Understanding Bulk Change Map (BCM) Page and Bulk Logged Recovery Model

SQL Server : Understanding Bulk Change Map (BCM) Page and Bulk Logged Recovery Model

In the last post , we have discussed about the Differential Change Map pages and how it is utilized in the differential backup process.In this post, let us try to understand the bulk change map page and how the bulk logged recovery model works.

Bulk Change Map(BCM): SQL Server uses Bulk Change Map (BCM) page to track extent modified by bulk logged operation since last backup log operation. BCM page is the 7th page in the data file.BCM has a bit for every extent it tracks. If the bit is 1, the corresponding extent is modified after the last log backup due to bulk logged operation, if the bit is 0, there is no change in the corresponding extent due to bulk logged operation after the last log backup.A BCM page can hold information of around 64000 extents. Precisely BCM pages repeat after every 511232 pages .One BCM page can track the changes of exactly 63904 extents.The second BCM page will be 511239th page.

You can see the BCM pages in all the databases, but they are only relevant when the database is in the bulk recovery model.In this recovery model, while taking log backup, SQL server scans the BCM page and include the extents that are marked as changed in the log backup along with transaction log.This helps SQL server to make bulk logged operation to be recoverable if the database is restored from database backup and a sequence of transaction log backups.In the bulk logged recovery model, bulk logged operation like BCP,Bulkinsert,Select Into are minimally logged in the transaction log and modified extents are tracked through the BCM pages.This helps SQL server to have optimized performance for bulk operations.

BCM pages are not relevant in simple recovery and full recovery model. In simple and full recovery model, the bulk logged operation are treated as fully logged operation. In short, in simple and bulk recovery model, BCM page will not keep track of the changes happened through bulk logged operations.

Let us try to understand with a sample.

CREATE DATABASE Mydb

GO

ALTER DATABASE Mydb SET RECOVERY BULK_LOGGED

BACKUP DATABASE Mydb TO DISK='D:\mydb.bak'

GO

DBCC TRACEON(3604)

DBCC PAGE('MYDB',1,7,3)

From the output ,it is clear that none of the extents are changed due to bulk logged operations. Let us try to perform a bulk logged operation.

SELECT * INTO mydb..SalesOrderDetail FROM AdventureWorks2008.sales.SalesOrderDetail

DBCC PAGE('MYDB',1,6,3)

Now in the output, you can see two entries marked as MIN_LOGGED.The first one is due to the change in the IAM page and second one is the actual data inserted as part of Select Into operation.Now when we take a log backup, SQL server backups the transaction log changes along with extent marked as changed in the BCM file. After the log backup, the bits in the BCM pages are flipped to mark there is no extent changed due to the bulk logged operation. Let us take the log backup.

BACKUP LOG Mydb TO DISK='D:\mydblog.trn'

GO

DBCC PAGE('MYDB',1,7,3)

From the output, we can see that SQL server flipped the bit in BCM page and none of the extents are marked as changed due to the bulk logged operation. Note that BCM pages are not cleared by taking a full backup or differential backup.The base of the transaction log backups is the previous transaction log backup. SQL server should know what changes are happened between two transnational log backup.If a differential or full backup cleared the BCM page,next transaction log backup can not track/recover the changes between the previous log backup and full/differential backup. In short a full backup or differential backup will not break the transaction log backup chain. 

SQL Server :Part 1 : Architecture of Transaction Log

SQL Server :Part 1 : Architecture of Transaction Log

In our earlier post, we have discussed in detail about the architecture of the data file and different types of pages. In this post, we will be discussing about the architecture of log file.

Logging is a mechanism used in RDBMS to support various ACID (Atomicity,Consistency,Isolation and Durability) properties of transactions. A transaction log is a physical file in which SQL server stores the details of all transactions and data modifications performed on the database.In the event of of disaster, that causes SQL server to shutdown unexpectedly (Power failure/hardware failure), the transaction log is used to bring the database in a consistent state while restarting the server.On restarting the server, database goes through the recovery process.During this recovery process , the transaction log is used to make sure that all committed transactions are written to respective data pages (rolled forward) and revert the uncommitted transaction that were written to data pages.

Logically transaction log is a set of log records.Each records is identified by a log sequence number (LSN). The new log records is always written at the logical end of log file with a LSN which is greater than the previous one.Each LSN will be associated with a transaction id , which can be used to group the log records of a transaction. As log file store the log records in the sequential order as it happens, It is not necessary that, log records of a transaction are always available in sequence in the log file.Each log records will have the LSN of previous log as a backward pointer and that will help in rollback of transaction.

Transaction log will store separate log entries for each operation.For example, while inserting a record into a table, transaction log will store separate log entry for inserting into clustered index and other non clustered index. In the same way, if a single update statement is updating 10 records, transaction log will capture 10 separate log entries.For data modification, transaction log store either the logical operation performed or the before and after image of the record.

To understand it better, let us look into the transaction log using the sys.fn_dblog. It is an undocumented function which will help us to read the active portion of the log. we are using the below query to create two tables and insert some records into that.We will delete the records from these table to keep the table clean. This insert and delete operation is to make sure that the pages are allocated to the table and we will not get unnecessary entries in the transaction log while examining it.We have issued a manual checkpoint to force the SQL server to write the log information to data file and clear the log. Note that, one table is heap table and other one is clustered index table.

USE Mydb

GO

CREATE TABLE LoginfoHeap

(
   id INT)


GO

CREATE TABLE LoginfoCI

(
   id INT PRIMARY KEY)


INSERT INTO LoginfoHeap VALUES(1),(2)

INSERT INTO LoginfoCI VALUES(2),(4)

GO

DELETE FROM LoginfoHeap 

DELETE FROM LoginfoCI

GO

CHECKPOINT

GO

SELECT 
[Current LSN], 
Operation  ,Context    ,
[Transaction ID],
[Previous LSN],AllocUnitName,[
Previous Page LSN],
[Page ID],[XACT ID],SUSER_SNAME(CONVERT(VARBINARY,[Transaction SID])) AS 'Login',
[Begin Time],[End Time]
FROM sys.fn_dblog (NULL, NULL)







From the output it is very clear that , we have only two active log entries.First one is written ,when the checkpoint started. The second one is written,  when the checkpoint completed the process.

Now we will insert,update and delete records to these tables through two session 

---SESSION I
   BEGIN TRAN
   INSERT INTO LoginfoCI VALUES(2)

--SESSION 2
   BEGIN TRAN
   INSERT INTO LoginfoHeap VALUES(1),(2)

---SESSION I
   INSERT INTO LoginfoCI VALUES(4)

--SESSION 2
   UPDATE LoginfoHeap   SET id =8 WHERE id=1

---SESSION I
   UPDATE LoginfoCI  SET id =6 WHERE id=2

--SESSION 2
   DELETE FROM LoginfoHeap    WHERE id=2

---SESSION I
   DELETE FROM LoginfoCI   WHERE id=4
   SELECT * FROM sys.dm_tran_current_transaction
   COMMIT

--SESSION 2
   SELECT * FROM sys.dm_tran_current_transaction
   COMMIT

The DMV sys.dm_tran_current_transcation returns a single row that displays the state information of the current transaction in the current session.We are interested only in the transaction_id, which will help us to filter the output of sys.fn_dblog. Let us see the output of sys.fn_dblog.

SELECT 
[Current LSN], 
Operation  ,
Context    ,
[Transaction ID],
[Previous LSN],
AllocUnitName,
[Previous Page LSN],
[Page ID],[XACT ID],
SUSER_SNAME(CONVERT(VARBINARY,[Transaction SID])) AS 'Login',
[Begin Time],
[End Time]
FROM sys.fn_dblog (NULL, NULL) 
WHERE [Transaction ID] IN 
(
   SELECT [Transaction ID] FROM sys.fn_dblog (NULL, NULL) 
   WHERE [XACT ID] IN (856960,856981)
)

The values 856960 and 856981 are the transaction_id returned from sys.dm_tran_current_transaction.We have filter the output to get only the relevant rows in which we are interested.

In our script, we have opened two transaction and all our transaction log entries are grouped to 
one of these transaction_id marked in red and green.Let us analyse what we did and how it is captured in the transaction log.

In the session 1, we have started the transaction and inserted a single record.The first records in the output map to the BEGIN TRAN command. This is the starting point of the transaction and created a new transaction_id.The previous LSNcolumn value is 0 as this is the first log entry in this transaction.In the same log records,it stores the XACT_ID,login and transaction start time.The second record represent the insert into the clustered table.The transaction_id is used to group the entries associated with a transaction. The previouse LSN column, is a pointer to the previous log entry in the same transaction which help SQL server to move backwards in case of rollback.Page id column refer the the page number where this LSN made the change.Previous Page LSN column refer the last log sequence number(LSN) which modify this page.When LSN modify a page, it will also update the corresponding LSN number in the page header (m_lsn field in the header. For more detail refer this post)

In the session 2, we have opened another transaction and inserted two records through single insert statement to the heap table. You can map these operations to row number 3,4, and 5 in the transaction log output. Third row represent the Begin tran command. Even if we inserted two records in single insert statement , SQL server recorded two separate entry in the transaction log. 

As a next step, in session 1 we have added 1 record to the clustered index table.We can map this operation to the 6th record in the transaction log output.

In the next statement , we have modified a record in heap table through Session 2. You can map this to the 7th record in the transaction log output.If you look into the previous LSN column , it will be current LSN column value of the last record associated with this transaction.

In the same way, as a next statement we have modified a record in the clustered table through session 1. We can map the 8th and 9th records in the transaction log output to the update operation on the clustered table. You might have noticed that, when we modified a record in the heap table, transaction log recorded operation in a single row. Where as the same operation in a clustered table has two record in the transaction log. One for delete and other one for insert. When you modify the clustered index key, SQL server internally delete the existing record and insert a new record. This is because, the record need to be stored in the new location based on the modified value(based on the order of clustered index column). The easiest way for SQL server to achieve this is , delete the existing record and insert it as new records with modified clustered column value.

In the next two statement, we are deleting one record from heap table and clustered table.This can be mapped to the 10th and 11th records in the output.Finally we have issued the commit statement in both sessions.12th and 13th record in the transaction log output can be mapped to the commit operation.The Previous LSN column refer the Current LSN column of corresponding  begin tran statement. It will also capture the transaction end time in the End time column.

Understanding the VLF(Virtual Log File)

A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs  in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.

Growth upto 64 MB          = 4  VLF

From 64 MB to 1 GB       = 8   VLF

Larger than 1 GB             = 16 VLF

Let us create a database with 64 MB initial log size and later increase it to 1 GB. As per above calculation the log file should have 12 VLFs.  4 VLF based on initial size and 8 VLF due to changing the log size to 1 GB.

USE MASTER;
GO
CREATE DATABASE Mydb
ON 
(      NAME = MyDb_dat,  FILENAME = 'D:\MyDb\Mydb.mdf',
       SIZE = 10MB, MAXSIZE = 3072MB,   FILEGROWTH = 5MB )

LOG ON ( NAME = MyDb_log,FILENAME = 'D:\MyDb\MyDB.ldf',
    SIZE = 64MB, MAXSIZE = 2048MB, FILEGROWTH = 5MB ) ;
GO

ALTER DATABASE Mydb 
MODIFY FILE ( NAME = MyDb_Log,FILENAME = 'D:\MyDb\MyDB.ldf',    SIZE = 1024MB)

Now Let us see how many VLF got created. To find out the number of VLF in database log file, we can make use of DBCC Loginfo.

DBCC loginfo('mydb')

The output is given below.















There are 12  records in the output each represent a VLF.Let us try to understand the result

FileId: This is the file id of the log file and will be same for all 12 records as we have only one log file.If we have multiple log file , we can multiple numbers here

FileSize: This is the size of the VLF. If you look into the first four, have same size except the fourth one. This because first 8KB of the log file is used for file header. If you add filesize value of first four records along with 8192(8KB) , you will get 64MB which is the initial size of the log file.

16711680+16711680+16711680+16965632 =67100672+8192 =67108864bytes =64MB

In the same if you add the last 8 records it will account the 960 MB (1024-64) , the growth happened due to the alter statement.

StartOffSet: This values is also in bytes, and is the sort column of the output. The first VLF alwasy start from 8192, which is the number of bytes in a page.As mentioned above, the first 8KB is used for file header and will not store any log.

FSeqNo: The file sequence number indicates the order of usage of the VLFs. The row with the highest FSeqNo value is the VLF where current log records are being written.FSeqNo values are not consistent. It will keep changing each time when VLF are getting reused. We will discuss more about this later in this post. A value of 0 in this column means that this VLF has never been used at all. That is the reason we have 0 for all records except one where it is currently logging.

Status: Status has two possible values : 0 and 2. A value of 2 means the VLF is not reusable and a value 0 means it can be reused.It will be more clear as we go further.

Parity: Parity has three possible values 0 ,64 and 128. If the VLF is not used yet, it will have a value 0 and will be set to 64 on first use.Every time a VLF is reused, the parity value is switched between 64 and 128.

CreateLSN: The value indicates when the VLF is created or to group the VLF based on the creation. A values 0 indicates, those VLFs are created as part of database creation. In our case first four records has a value 0 which indicate these VLFs are created as part of database creation with 64MB log size. The remaining 8 records has the same value. These VLF are created as part of our alter database statement to increase the size of the log file from 64 MB to 1024MB

The above output description is referred from Kalen Delaney Blog Post

Now our transaction log will looks like below

Now we have learned about the LSN and VLF. we will discuss more about transaction log in the next post.