Jeff Moden (11/4/2013)

---

Yes, yes... I know... all of your character based columns are NVARCHAR(MAX) because someone believes that "Premature optimization is the root of all evil." The problem is...

Are you sure it failed because of the compat level of the publisher?

Possibly large amount of log being included in the backup. Guessing though.

Are you appending multiple backups to the same file? If you back up repeatedly to the same file that's the default, append.

You've got to set Veeam to not backup and truncate the transaction logs, that or stop log shipping and use something like mirroring or availability groups. There's got to be...

Backups don't include unallocated pages. Shrink will just re-fragment all your indexes.

Did you rebuild the clustered index for that table? Does it have a clustered index?

Locking isn't by default at the row level. It depends how much data you're retrieving. SQL can choose to lock at the row level, page level or table level.

Did you...

ROW_NUMBER() OVER (partition by ac_id order by Rev_no DESC) as RevisionNumber

Then filter for that = 1.

It's mostly cases where the parsing rules have been tightened. Don't know if there's a list. It's why you need to run upgrade advisor and run tests before upgrading anything.

For...

Why not use backup/restore? Far less hassle than a copy job, much less impact on production (you can just use the previous night's production backup) and probably faster.

If you want to make a copy of the DB, why not just take a backup and restore the backup as a new database?

Not necessarily, no. In most cases, but there are changes that don't fall under the compat modes

In simple recovery, the log is marked reusable by a checkpoint, not a commit. That's assuming the...

Could you be a little more specific about what you want to collect, when and how?

Check with the vendor. Some will say yes, some will refuse support if @@version doesn't match what they support. I've even seen one that checked the product major version and...