SQL Server 2017: Some changes in transaction log monitoring

If your database is configured with the Full recovery model, the transaction log (.ldf files) stores all the transactions done to the database for recovery purposes.
If you fire certain DML commands, the transaction log can grow in matter of minutes and occupy all your precious disk space, that can lead to new commands not able to be executed against the database.

As a new feature in SQL Server 2017 we will have 2 new commands that can help us to monitor the log usage: sys.dm_db_log_stats and sys.dm_db_log_info

sys.dm_db_log_stats


Usage:

sys.dm_db_log_stats ( database_id ) 

This DMV returns a summary of the information for transaction log files. if used without parameter this will return information of the current database.
This can be helpful in a lot of database administration tasks, for example:
  • Using column log_since_last_log_backup_mb you can fire a tlog backup when the log reaches certain size.
  • With log_backup_time you can know the date of the last log backup.
  • Obtain the VLF count with active_vlf_count.
  • Know the log hold reason with log_truncation_holdup_reason, among other things. 

For a full list of columns returned, please visit the official documentation here 


sys.dm_db_log_info


Usage:

sys.dm_db_log_info ( database_id ) 

This DMV returns the VLF information for the database id specified, or the current database if no parameter is used.
It replaces the DBCC LOGINFO command with a more robust and now, documented option to manage VLF information, besides you can now use in custom query build joining it with other tables or views.

For the full column output an definition, please visit the official documentation here

Comments

Post a Comment

Popular posts from this blog

Checking Azure SQL DB service tier via T-SQL

Image
If you have to manage or work with an Azure SQL database and want to know what service tier the database is currently operating, but you don't have access to the Azure subscription or CLI. Or you want to know the status of the service tier after a scale up or scale down, directly from the database; you can do it via T-SQL Just query the sys.database_service_objectives DMO to obtain this information, this will give information about the service tier, and also will tell you if the database is part of an elastic pool or not. Basic usage (in the context of the database you need the information): SELECT * FROM sys.database_service_objectives; This will return the following information for the current database: I am running on a General Purpose tier with 1 VCore What if you want the information for all the databases created? Just change the context to the master database and execute the following query: SELECT D.name AS DB , D.create_date , SO.edition , SO.service_o
Read more

Install Python on Windows

Image
Python is the most famous development language today. This is because is open-source, scalable and robust. It can run in almost any device with a processor (or microprocessor, we will see that in coming posts) and is very simple to use and learn, even if you don't have any programming backgroud or experience. Start using it is very simple and you can learn the basics in the official site .   Installing Python For windows systems, you can download the latest version here . Once downloaded, open the executable file as administrator. Then, for the uses we will give on the articles on this site, please install it this way: Make sure you have selected the add Pyton to PATH option - This will allow us to run the Python engine from multiple sources (As is intended for use on multiple applications) .   Make sure you install Python for all users - This will allow usto run the script commands .   Select PIP feature - This is used to install adittional libraries and functionalities, one of
Read more

Quick tip: Zoom in Azure Data Studio

Image
If you finally have given a try t o Azure Data Studio , and if you use it on a regular basis, maybe you want to customize it to suit your needs. Among the huge customization options it has, you can control the text size in form of zoom. To do change it, just use the following keyboard combinations: (Ctrl + = ) For Zoom in. (Ctrl + - ) For Zoom out. (Ctrl + 0) For Zoom reset.  You can see it in action: Also, as with everything on this tool, you can access this functionality from command palette (Ctrl + Shift + P) , and type "zoom" (you can fine-control the editor zoom from here): If you haven't tried it yet, you can download Azure Data Studio here .
Read more