dbatools is a free PowerShell module with over 200 SQL Server administration, best practice and migration commands included.

Please note that documentation and command names may be out of date while we work furiously towards 1.0


Test-DbaVirtualLogFile

Returns database virtual log file information for database files on a SQL instance. As you may know, having a TLog file with too many VLFs can hurt database performance.

Too many virtual log files can cause transaction log backups to slow down and can also slow down database recovery and, in extreme cases, even affect insert/update/delete performance.

If you’ve got a high number of VLFs, you can use Expand-SqlTLogResponsibly to reduce the number.

Screenshots

Test-DbaVirtualLogFile

Examples

To return all user database virtual log file counts for the sqlcluster instance

Test-DbaVirtualLogFile -SqlServer sqlcluster

To return user databases that have more than or equal to 50 VLFs

Test-DbaVirtualLogFile -SqlServer sqlserver | Where-Object {$_.Count -ge 50}

To return all database VLF information for all servers registered in sqlserver’s CMS. Processes data via the pipeline.

Get-SqlRegisteredServerName -SqlServer sqlserver | Test-DbaVirtualLogFile

To return VLF counts for the db1 and db2 databases on sqlcluster.

Test-DbaVirtualLogFile -SqlServer sqlcluster -Databases db1, db2

References

Transaction Log VLFs – too many or too few?

Too Many Virtual Log Files (VLFs) Can Cause Slow Database Recovery

Get-Help

From PowerShell, execute Get-Help Test-DbaVirtualLogFile -Detailed for more information on this function.

Source Code

Want to see the source code? View Test-DbaVirtualLogFile.ps1 on GitHub
 

Related commands