I will explain how you can use sp_MSforeachdb stored procedure against READ_WRITE database only.
There are numerous articles on the web about excluding databases using database id or name. Recently I needed a script to run against databases that are part of Always On Availability Groups and in READ_WRITE status. I used the ‘Updateability’ properties of DATABASEPROPERTYEX function.
Here is how I implemented this.
SET NOCOUNT ON EXEC sp_MSForEachDb ' USE [?] IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'') IF ((SELECT DATABASEPROPERTYEX(''?'', ''Updateability''))=''read_write'' ) BEGIN ..... ..... END '
I would like to know if you implemented same …
About two years ago, I was working on an update statistics solution for 15 TB OLTP database. First thing I needed to know, is the status of statistics. With a google search I landed into this blog post by Erin Stellato (blog | Twitter) from SQLskills. Which gave me a perfect head start.
I expanded that query to add more things in the result set.
I observed this behavior only in following version of SQL Server while using Always On Availability Group feature.
Version: Microsoft SQL Server 2012 (SP3) (KB3072779) – 11.0.6020.0 (X64) Oct 20 2015 15:36:27 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )
Once I update my statistics with fullscan, with in 10~20 seconds some of the statistics on the same table are getting update on secondary with a sample pecent of rows. Meaning my best statistics are being …
There was a tweet came last night from Lohit about “need guidance on how to enable a set of jobs on secondary AG as soon as failover happen from pri to sec AG”. I have implemented a solution for most (I am using “most” literally because I have not done this for all jobs) of the agent jobs so jobs will run always on primary node. For example, I want to run DBCC CHECKDB, recycle error log on primary and all readable secondaries. That way for the jobs that I want to run on primary node only I do not …