10 most popular sqlserver-dba posts for 2015

I've been meaning to publish the 10 most popular posts for sqlserver-dba.com for 2015 since the start of 2016. But I've had a lot of work and being writing about other topics. Mainly I write sqlserver-dba.com posts to document problems and to help me understand what I'm working on. One of the observations about this list is that I also revisit these posts regularly. I use the posts as reference and also edit based on feedback. I've listed them from No 10 down to the most popular. 10) SQL Server – LATCH_EX AND how to reduce it - SQL Server... Read more →

VM Snapshot Backup Review for databases

VM administrators like VM snapshots for recovery due to standardisation and ease of use. Lets look at some of the issues and complexities of relying on VM Snapshots for RDBMS servers The usual benefits cited of VM snapshots are : VMs can be restored quickly VMs can be restored on another server with compatible virtualisation platform All data is protected. It is not OS oriented or reliant on specific backup agents To make VM aware backups for SQL Server the Volume shadow-copy service (VSS) is required. VM Snapshot relies on Microsoft VSS to provide a consistent state for the SQL... Read more →

How to find sql transactions with large logs

There are quite a few solutions on the internet dealing with runaway transaction log files.During some sql server performance troubleshooting yesterday, I was reminded of how important analysing large transactions and the impact on transaction logs. The sys.dm_tran_database_transactions DMV returns transaction information at the database level. Creating a JOIN with the sys.dm_exec_requests DMV (which returns details of sql requests executing) , creates a resultset with granular information on transaction log usage per request. What’s the point of this information? What sort of problems can be solved using this information? Here are some examples : 1)Identifying very large deletes generating large... Read more →

How to deal with unexpectedly large transaction log files

A common problem faced by SQL server administrators is the unexpectedly large size to which transaction log files often grow. Large transaction log files pose many problems to the server itself such as sluggish performance, threat of data corruption, server downtime and server inaccessibility. It thus becomes a priority to deal with such huge files at the earliest before they can cause hindrances in the normal working of the server. In this article we’ll be focusing on this issue and detailing the causes behind this problem, ways to prevent it from happening (if possible) and tips to resolve it. Read... Read more →

Database has more than 1000 virtual log files which is excessive – informational message 9017

Question: I get the 9017 Informational Message when I start the SQL Server Instance Database Mydatabase has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files. What does it mean and what steps can I take to fix? Answer:The 9017 message appears because a threshold is met on the number of virtual log files for a database. Within a SQL Server Transaction Log file exist virtual log files (VLF) .... Read more →