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 →

How to trace SQL Server Backup

Question: I'm trying to understand for educational purposes , what are the exact steps the SQL Server Backup is taking to progress through a SQL Server backup. Is there a way or method to trace the steps taken to complete a backup? Answer: Before I progress with outlining a method - I want to set a WARNING that this trace flag should only be used in guidance with experienced support staff - and only on non production systems. Thoroughly test on non-production first and ensure consulting is involved Using the 3004 and 3605 DBCC TRACEON (3004 , 3605 , -1)... Read more →

How to fix error - The database cannot be opened because it is version 852. This server supports version 706 and earlier

Question:A vendor sent me some detached MDF files from a SQL Server and asked me to attach. I attempted the ATTACH without a log file option and got this error The database 'mydb' cannot be opened because it is version 852. This server supports version 706 and earlier. A downgrade path is not supported. The error message details are obvious i.e attempt to attach a database with a version level higher than the current version of the current SQL Server. The SQL error message says the current server is version 706 and attempting to connect a database of version 852.... Read more →

How to troubleshoot Alter Database Failed SQLSTATE 42000 Error 3023

Question: I received an alert for a regular backup failure . A closer inspection of the sql server agent error logs displayed this error message: Executed as user: myUser. Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). Answer: There are different reasons this error message is generated. The error message says that "ALTER DATABASE ADD FILE and encryption changes must be serialized". This... Read more →

How to troubleshoot Avamar backup issues with avsql command line

It is possible when there is either a SQL Version upgrade or Avamar plug-in changes backup issues are encountered. avsql is the command line interface for Avamar and SQL Server. The command browses SQL Server plug-in data for SQL Server backup and restores . These are the different operation available via the avsql command line interface . avsql --operation={browse | backup | restore} [options] The browse operation reports on client data available for backup. This example returns client data for SQL Server instances on the current host avsql --operation=browse --verbose There are plenty of other browsing options right down to... Read more →