Focus on OLDEST_PAGE as a factor that can delay LOG truncation

How do I fix the Oldest_page wait type desc Read more →

What does LSN mean in SQL Server?

An auditor asked me how can prove a restore - LSN of course Read more →

The mysterious case of Pseudo SIMPLE RECOVERY model and the transaction log explosive growth

I created a database to host a data set for some sql performance testing. The default Recovery Model was set to FULL. The test data load insert was executed and it was expected to take about 40 mins. About 15 mins into the the test data load insert a scheduled full backup started. Up until the full backup started the transaction log growth rate was minimal. At the end of the FULL BACKUP - the transaction log started growing at a much quicker rate - with the same type of transactions. What is going on here? Initially I was scratching... Read more →

The transaction log for database is full due to LOG_BACKUP

Question: I'm getting the The transaction log for database is full due to LOG BACKUP Error 9002 Severity 17 State 2 error message . When I look at the Message associated with the error message the details are "One or more recovery units belonging to database 'database_name' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure" I can see clearly the disk space available on the drive is... Read more →

How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry. To read more about these options read SQL Server - Find Default Data location and Default Log Location .... For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and... Read more →