USE MASTER; GO CREATE DATABASE trndb ON ( NAME = trndb_dat, FILENAME = 'c:\trndb.mdf', SIZE = 10MB, MAXSIZE = 300MB,FILEGROWTH = 5MB ) LOG ON ( NAME = trndb_log,FILENAME = 'c:\trndb.ldf', SIZE = 64MB, MAXSIZE = 248MB, FILEGROWTH = 5MB ) ; GO DBCC loginfo('trndb') ALTER DATABASE trndb MODIFY FILE ( NAME = trndb_Log,FILENAME = 'c:\trndb.ldf',SIZE = 1024MB) sp_helpdb 'trndb'
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.
The output is given below.
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
Now our transaction log will looks like below
Transaction Log with Example
–Reading Transaction Log in Details
USE [master]; GO CREATE DATABASE TranDB_READ; GO -- Create tables. USE TranDB_READ; GO CREATE TABLE [dbo].[USER_DETAILS]( [USER_ID] [int] NULL, [EMAIL_ADDR] [varchar](250) NULL, [USER_FIRST_NAME] [varchar](40) NULL, [USER_LAST_NAME] [varchar](40) NULL, [DESGINATION] [varchar](100) NULL, [LOCATION] [varchar](15) NULL, [CITY] [varchar](15) NULL ) ON [PRIMARY] INSERT INTO USER_DETAILS values ('101215','firstname.lastname@example.org','Tom','Refienger','DBA Manager','Emmanus','Allen Town') INSERT INTO USER_DETAILS values ('101216','email@example.com','Venkata','Sarath','Sr DBA','Bangalore','Karnataka') INSERT INTO USER_DETAILS values ('101217','Sai.firstname.lastname@example.org','Sai','Ram kumar','Dentist','Tirupati','Andhra Pradesh') INSERT INTO USER_DETAILS values ('101218','email@example.com','Sriram','chowdary','Mobile Engineer','Tirupati','Andhra Pradesh') INSERT INTO USER_DETAILS values ('101219','firstname.lastname@example.org','Rakesh','Singh','Sr Manager','Allen Town ','Pennsylvania') INSERT INTO USER_DETAILS values ('101220','email@example.com','Amith','kumar','Sr Manager','Hyderabad','Andhra Pradesh') INSERT INTO USER_DETAILS values ('101221','firstname.lastname@example.org','Umesh','kumar','Ass Operations Mgr','Hightstown','New Jerchy') INSERT INTO USER_DETAILS values ('101222','email@example.com','kapa','kishore','Sr Developer','Minneapolis','Minnesota') INSERT INTO USER_DETAILS values ('101223','firstname.lastname@example.org','kiran','kumar','BenchMark Engineer','Bangalore','Karnataka') INSERT INTO USER_DETAILS values ('101224','naveendhan.hmh.com','naveendhan','arurmugam','Sr DBA','Chennai','Tamilnadu') USE TranDB_READ; GO select COUNT(*) from fn_dblog(null,null) USE TranDB_READ; GO select [Current LSN], [Operation], [Transaction Name], [Transaction ID], [Transaction SID], [SPID], [Begin Time] FROM fn_dblog(null,null) select * from USER_DETAILS INSERT INTO USER_DETAILS values ('101224','naveendhan.hmh.com','naveendhan','arurmugam','Sr DBA','Chennai','Tamilnadu') delete from USER_DETAILS where USER_ID=101224 update USER_DETAILS set EMAIL_ADDRemail@example.com' where USER_ID='101222' SELECT COUNT(*) FROM fn_dblog(null,null) GO BACKUP DATABASE TranDB_READ TO DISK = 'c:\TranDB_Full.bak' GO SELECT COUNT(*) FROM fn_dblog(null,null) GO select [Current LSN],[Operation],[Transaction Name], [Transaction ID],[Transaction SID], [SPID],[Begin Time] FROM fn_dblog(null,null)
—-If Needed we can see –Page spliting
SELECT [Current LSN],[Transaction ID],[Operation],[Transaction Name],[CONTEXT], [AllocUnitName],[Page ID],[Slot ID],[Begin Time],[End Time],[Number of Locks], [Lock Information] FROM sys.fn_dblog(NULL,NULL) WHERE [Transaction ID]='0000:0000032d'