Looking deeper into the physical & logical architecture - Transaction Log File

Beyond all doubt, it is essential to have a good understanding of the Transaction Log (T-Log) so that we can diagnose unforeseen performance issues related to it and I am sure that almost everyone had at least one. The T-Log is basically a record of all transactions happening to the database. All these transactions are actually first written to the physical T-Log file, and then after a CHECKPOINT, is written to the Data File via the Lazy Writer process. Some of the uses of T-Log are: as a point in time recovery (full recovery model), to record the Start and End of each transaction, every data modification (insert, update, delete) including system SP's, DDL statements to any table including system tables, every extent and page allocation and de-allocation operation, and creation or drop of tables and indexes.

Getting useful information of data & log files for all databases

It is a common DBA task to check the unused space of all database files in order to make the decision of extending the file sizes and provide them with more disk hard space in the likely event of running out of it. Today I have two scripts I would like to share with you to get that info easily. The first one is to report some very important information about every data & log file for all databases such as name, file size in GB, space used in GB, free space in GB and percentage, growth settings in tandem with other information at database level such as collation, compatibility level, owner, and more. Here you are:

USE master
GO
IF EXISTS (SELECT name FROM master.sys.tables WHERE name='TmpFileSpace')
    DROP TABLE TmpFileSpace

CREATE TABLE [dbo].[TmpFileSpace](
    [DatabaseName] [nvarchar](128) NULL,
    [FileName] [sysname] NOT NULL,
    [FileSizeGB] [decimal](10, 2) NULL,
    [SpaceUsedGB] [decimal](10, 1) NULL,
    [SpaceFreeGB] [decimal](10, 1) NULL,
    [SpaceFree%] [decimal](10, 1) NULL
) ON [PRIMARY]
 
EXEC sp_MSforeachdb '
USE [?]
INSERT INTO master.dbo.[TmpFileSpace]([DatabaseName], [FileName], [FileSizeGB], [SpaceUsedGB], [SpaceFreeGB], [SpaceFree%])
SELECT DB_NAME() DatabaseName, name FileName, 
CAST(size/128.0/1024.0 AS DECIMAL(10,2)) SizeGB, 
CAST(FILEPROPERTY(name,''SpaceUsed'') /128.0/1024.0  AS DECIMAL(10,1)) SpaceUsedGB, 
CAST((size - FILEPROPERTY(name,''SpaceUsed'')) /128.0/1024.0 AS DECIMAL(10,1)) SpaceFreeGB, 
CAST(((size - FILEPROPERTY(name,''SpaceUsed''))/(size*1.0)) *100 AS DECIMAL(10,1)) [SpaceFree%]
FROM sys.database_files'

SELECT db.name DatabaseName,db.collation_name,db.compatibility_level, SUSER_SNAME(owner_sid) OwnerName,
db.page_verify_option_desc, db.is_auto_close_on, 
db.is_auto_create_stats_on,db.is_auto_shrink_on, db.is_auto_update_stats_on,
db.is_auto_update_stats_async_on,db.name DatabaseName,  fs.FileName,
fs.FileSizeGB , fs.SpaceUsedGB, fs.SpaceFreeGB, fs.[SpaceFree%],
  physical_name, cast(size/128.0/1024.0 as decimal(10,2)) FileSizeGB,
db.state_desc,max_size,growth,is_percent_growth 
FROM sys.master_files mf
INNER JOIN sys.databases db ON mf.database_id = db.database_id
INNER JOIN TmpFileSpace FS ON mf.database_id=db_id(Fs.DatabaseName) AND mf.name=fs.FileName

DROP TABLE [TmpFileSpace]

But if you only want to get information about the size in GB, space used in GB, free space in GB of all data & log files for a specific database you can use this:

USE [YourDatabaseName]
SELECT DB_NAME() DatabaseName, name FileName, 
CAST(size/128.0/1024.0 AS DECIMAL(10,2)) SizeGB, 
CAST(FILEPROPERTY(name,'SpaceUsed') /128.0/1024.0  AS DECIMAL(10,1)) SpaceUsedGB, 
CAST((size - FILEPROPERTY(name,'SpaceUsed') ) /128.0/1024.0 AS DECIMAL(10,1)) SpaceFreeGB, 
CAST(((size - FILEPROPERTY(name,'SpaceUsed') )/(size*1.0)) *100 AS DECIMAL(10,1)) [SpaceFree%]
FROM SYS.database_files

That is all for now. Let me know any remarks you may have. 

How to split the tempdb database into more files

Naturally, as DBAs we do know is mandatory to modify data and log properties of the tempdb database. Unless we do a customized configuration of it, SQL Server will create only one data file and log file by default. The reality is that we will mostly need to create more files on production environments. There are many recommendations not only about how to create them but also the quantity of files on OLTP environments. To be perfectly honest, I do not believe that the number of data files only depends on the number of cores, but it also depends on concurrency, tempdb contention issues, the workloads on your server and, clearly, the performance of your queries. So, there is no a rule for it. Moreover, depending on the SQL Server version we are working on, we may NOT need to split the tempdb into many data files. There are some situations where it will work splendidly with only one data file since each database environment is unique and, therefore, we need to determine the best for it.

Today we are not going to discuss more details about it. I just would like to suggest splitting your tempdb database into four data files (if you have four or eight core) or eight data files (for 16, 32, 64, or more cores) and ONLY one log file. Ideally, we also need to locate them in different drives RAID1, RAID5 or RAID10. Now having very clear the situation, I will show you one small script to split your default tempdb database into 8 data files and remaining the only one log file.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'templog', NEWNAME= N'tempdev_Log')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev', NEWNAME=N'tempdev_Data01')
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev_Log',  FILENAME= N'D:SQLTempDBtempdev_Log.ldf', SIZE = 2048MB , FILEGROWTH = 2048MB)
GO
ALTER DATABASE [tempdb] MODIFY FILE (NAME=N'tempdev_Data01',  FILENAME= N'D:SQLTempDBtempdev_Data01.mdf', SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data02', FILENAME = N'D:SQLTempDBtempdev_Data02.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data03', FILENAME = N'D:SQLTempDBtempdev_Data03.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data04', FILENAME = N'D:SQLTempDBtempdev_Data04.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data05', FILENAME = N'D:SQLTempDBtempdev_Data05.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data06', FILENAME = N'D:SQLTempDBtempdev_Data06.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data07', FILENAME = N'D:SQLTempDBtempdev_Data07.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev_Data08', FILENAME = N'D:SQLTempDBtempdev_Data08.ndf' , SIZE = 512MB , FILEGROWTH = 1024MB )

After having successfully executed, we must restart the database engine in order to get the new files created and all changes done. I hope you find this post interesting. Let me know any remark if you may have. Thanks for reading!