search

olahallengren / sql-server-maintenance-solution

SQL Server Maintenance Solution
https://ola.hallengren.com
MIT License
2.87k stars 737 forks source link

[dbo].[DatabaseBackup] Invalid column name 'modified_extent_page_count'. #190

Closed MyKarpenko closed 5 years ago

MyKarpenko commented 5 years ago

Hi Ola, Probably somebody has already submitted a ticket... It is similar to https://dba.stackexchange.com/questions/220843/error-with-ola-hallengren-backup-job-after-upgrading-sql-server-2016-to-sp2?newreg=f8d3c98783d5484d85e71b7bf803be8f

I have 6 SQL Server 2016 SP 2 instances on the same box Just one after I installed it and updated to

giving me an error EXECUTE [dbo].[DatabaseBackup] @Databases = 'USER_DATABASES', @Directory = N'\\my path', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 120, @CheckSum = 'Y', @LogToTable = 'Y'

Date and time: 2019-01-10 16:06:16 Server: xxx\yyyy Version: 13.0.5153.0 Edition: Enterprise Edition: Core-based Licensing (64-bit) Platform: Windows Procedure: [master].[dbo].[DatabaseBackup] Parameters: @Databases = 'USER_DATABASES', @Directory = '\my path', @BackupType = 'LOG', @Verify = 'Y', @CleanupTime = 120, @CleanupMode = 'AFTER_BACKUP', @Compress = NULL, @CopyOnly = 'N', @ChangeBackupType = 'N', @BackupSoftware = NULL, @CheckSum = 'Y', @BlockSize = NULL, @BufferCount = NULL, @MaxTransferSize = NULL, @NumberOfFiles = NULL, @CompressionLevel = NULL, @Description = NULL, @Threads = NULL, @Throttle = NULL, @Encrypt = 'N', @EncryptionAlgorithm = NULL, @ServerCertificate = NULL, @ServerAsymmetricKey = NULL, @EncryptionKey = NULL, @ReadWriteFileGroups = 'N', @OverrideBackupPreference = 'N', @NoRecovery = 'N', @URL = NULL, @Credential = NULL, @MirrorDirectory = NULL, @MirrorCleanupTime = NULL, @MirrorCleanupMode = 'AFTERBACKUP', @MirrorURL = NULL, @AvailabilityGroups = NULL, @Updateability = 'ALL', @AdaptiveCompression = NULL, @ModificationLevel = NULL, @LogSizeSinceLastLogBackup = NULL, @TimeSinceLastLogBackup = NULL, @DataDomainBoostHost = NULL, @DataDomainBoostUser = NULL, @DataDomainBoostDevicePath = NULL, @DataDomainBoostLockboxPath = NULL, @DirectoryStructure = '{ServerName}${InstanceName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @AvailabilityGroupDirectoryStructure = '{ClusterName}${AvailabilityGroupName}{DirectorySeparator}{DatabaseName}{DirectorySeparator}{BackupType}{Partial}{CopyOnly}', @FileName = '{ServerName}${InstanceName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}{FileNumber}.{FileExtension}', @AvailabilityGroupFileName = '{ClusterName}${AvailabilityGroupName}{DatabaseName}{BackupType}{Partial}{CopyOnly}{Year}{Month}{Day}{Hour}{Minute}{Second}_{FileNumber}.{FileExtension}', @FileExtensionFull = NULL, @FileExtensionDiff = NULL, @FileExtensionLog = NULL, @Init = 'N', @DatabaseOrder = NULL, @DatabasesInParallel = 'N', @LogToTable = 'Y', @Execute = 'Y' Version: 2018-07-16 18:32:21 Source: https://ola.hallengren.com

Msg 207, Level 16, State 1, Line 1 Invalid column name 'modified_extent_page_count'. Msg 208, Level 16, State 1, Procedure DatabaseBackup, Line 1761 [Batch Start Line 0] Invalid object name 'sys.dm_db_log_stats'.

Please let me know if i can provide anything else useful

Thank you.

Regards, Michael

MyKarpenko commented 5 years ago

Installed SPs

SQLServer2016SP2-KB4052908-x64-ENU SQLServer2016-KB4340355-x64_SP2_CU2

MyKarpenko commented 5 years ago

Update.

Machine reboot has fixed the issue.

Sorry for the false alarm

olahallengren commented 5 years ago

It looks like the version number had been updated, but the new / updated DMVs were not available yet.

olahallengren commented 5 years ago

I would like to understand this a bit better. Was all the SP2 and CU2 update successful? How did you do it? GUI? Command Line? Please describe your exact procedure.

MyKarpenko commented 5 years ago

Hi Ola,

I had 2 Always On nodes (our test server) with 4 instances on each node already running for months. All were patched to SP2 Cu2 with no issue.

I installed 2 more instances. One per node. Then I installed Backup script and confirmed that everything works fine.

Then I installed SP2 + Cu2 to every new instance with an option skip reboot check ( /SkipRules=RebootRequiredCheck) After that 1 node instance started to fail on "User Database Transaction Log backup " job. However the second one was working fine..

I reboted node 1 and got it fixred.

Regards, Michael

MyKarpenko commented 5 years ago

one more point. Depending on my Always on backup preferred node settings, it was failing from the 2-nd node acting as a primary but backup preferred secondary. it was not failing if i performed a log backup from 2-nd as a primary and preferred backup primary

MyKarpenko commented 5 years ago

Then I installed SP2 + Cu2

i run setup.exe /SkipRules=RebootRequiredCheck

olahallengren commented 5 years ago

Then I installed SP2 + Cu2 to every new instance with an option skip reboot check ( /SkipRules=RebootRequiredCheck)

So you installed first SP2 and then CU2? Both with /SkipRules=RebootRequiredCheck? Were all of them successful?

MyKarpenko commented 5 years ago

Yeah. Both were installed with the option with no visible issues. The server instance does not have any workload for now. Just a few databases restored. Log backup failure was first issue I noticed. I think that that node was somehow a problem before the installation.

olahallengren commented 5 years ago

Thank you for the detailed information.

allanhirt commented 5 years ago

What you're saying is you loaded the shotgun, aimed it at your foot and blew it off. You told the SQL Server installer not to notify you if a reboot was required.

SKIPRULES was put in for customers to be directed by support to skip something, not for you to decide you don't want to run a test. It's not documented in the official documentation for a reason. https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-from-the-command-prompt?view=sql-server-2017