Showing posts with label 2017 features. Show all posts
Showing posts with label 2017 features. Show all posts

Saturday, September 9, 2017

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

Thursday, September 7, 2017

SQL Server 2017 for Linux

As most of you already know, next version of SQL Server (currently SQL Server 2017) will have the ability to install the database engine on Linux systems.

But wait! you cannot install the engine in any distro, as for the latest available version (Release candidate 2) the supported systems are: Red Hat Enterprise 7.3, SUSE Enterprise V12 SP2 and Ubuntu 16.04 ( you can see the full list in the link below).

As we can see in the release notes, not all the features on the windows counterpart are available on the Linux one, so always plan ahead the pros/cons of this type of install (apart from the obvious pricing one), right now, the main difference is that you can only install the database engine component.

My advice to you: learning is always important, and even when i consider that the Linux version will not have a hard short-term impact on the market, you could be needed to install/administer one someday, so, learn the basics of Unix OS, security, disk management, etc... and most important: know the limitations of the product so you will be prepared (besides it will look nice in your résumé).

For the latest version and supported features/systems, please visit:

https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes

Thanks for reading!

 -Eduardo-

How I Built RAG for My Accounting App on Bedrock and pgvector

The first version of my accounting assistant could not find a single purchase of "comida" in a database full of lunches, misse...