How to check the transaction log size in SQL Server

Question : Is the Log Size on DBCC SQLPERF(LOGSPACE) the max size the log file has ever been or an amount of reserved space? Answer: It is the current size allocated to the transaction log. It isn’t the max size. You can get the max size from the sys.database_files max_size column. You have 3 main variables to manipulate at the CREATE DATABASE or ALTER DATABASE Size Growth rate Max size There are some considerations in setting the MAX SIZE. Let’s say you’re in the middle of a transaction and the log file limit is reached , the in-flight transaction will... Read more →

How to shrink SQL Server database transaction log file initial size

Question: I received a database monitoring alert for a disk space full on a server hosting SQL Server. On closer inspection , the drive was full because of an excessively sized transaction log which had consumed the drive space. This also had a knock-on effect of not allowing any of the other database transaction log files to grow, causing jobs to fail. I'd like direction on a sqlserver shrink log file How can I shrink down the physical size of the sqlserver database log file? The database is in Full Recovery mode, with only a FULL BACKUP issued every day.... Read more →

Is a LOG BACKUP allowed on a SIMPLE RECOVERY database

I received a question from the backup engineers configuring a new backup system. They are working with different technology owners to set up the different schedules. As part of this checklist they asked me whether they should trigger a transaction log backup after the FULL BACKUP of databases in the SIMPLE RECOVERY model? My response to this question included a brief explanation about how SQL Server transaction logs are managed under the SIMPLE RECOVERY model. When a SQL Server database is configured with SIMPLE RECOVERY – a DBA needs to understand that transaction logs will be immediately truncated. The purpose... Read more →

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 →