The postings on this site are my own and do not represent my Employer's positions, advice or strategies.

LifeAsBob - Blog

 

Home

No Ads ever, except search!
Wednesday, July 28, 2021 Login
Public

Loblolly Pine Trees7/13/2021 5:06:26 AM
Trying to grow some Loblolly Pine Trees in Missouri.
Seedling sourced from my Dad in South Carolina, and transplanted to larger containers, once they are big enough I'm going to plan them in a line and make a boundary at the back of my property.




Sidewalk replacement7/10/2021 8:44:09 AM
The sidewalk below my front step had collapsed down about 3 inches.  This leads to water running under the steps and that's bad as it puts water close to the foundation of the house, and even worse, snakes had decided to live under there (look in the pics and you'll see the snake eggs).


Plenty of Beer 6/12/2021 8:33:46 AM
Found this letter I wrote to my Grandfather from Okinawa in 1991 / 92.

There was water rationing at the time, but the "money" quote.

"But that's OK, there's always Beer and Plenty of it.".


EJ Horkay Class of 20215/22/2021 5:27:24 AM
Graduation May 13, 2021





Unable to drop Statistics3/26/2021 10:00:29 AM
Recently ran into an issue where we were unable to drop a column statistic.

We were trying to modify a column and received the following error.

ALTER TABLE [schema].[table] ALTER COLUMN [column1] VARCHAR(MAX)
go

Msg 5074, Level 16, State 1, Line 1
The statistics 'Stat_52ead462d2bc49009bded731bf1417a8' is dependent on column 'column1'.
ALTER TABLE ALTER COLUMN column1 failed because one or more objects access this column.
Operation cancelled by user.

Notice the odd name of stat_{guid}, this is usually when sql server is auto generating a system name.

Searched for that statistic name and could not find it.

select *

from sys.stats so

where so.name = 'Stat_6c8147e7477849589b585591f65b5843'




I ended up guessing that i needed to drop the statistic on the column, so ran this query to find the statistic on that column and then manually dropped the statistic.

select s.name as statistics_name

,c.name as column_name

, sc.stats_column_id

from sys.stats as s

inner join sys.stats_columns as sc

       on s.object_id = sc.object_id and s.stats_id = sc.stats_id

inner join sys.columns as c

       on sc.object_id = c.object_id and c.column_id = sc.column_id

where s.object_id = object_id('stg.td_click')

From Here i dropped that statistic and then was able to proceed, notice it is not named like the error, but it worked and allowed me to then alter the column.

drop statistics stg.td_click._WA_Sys_00000010_29D8AC17

Found one similar issue.


Monitoring read-only PAAS SQL node2/7/2021 7:39:34 PM

If you connect to your Business Critical Azure SQL instance with ADS or SMSS and in the Additional Connection Parameters you specify ApplicationIntent=ReadOnly, you are then connected via your admin account to that read only replica and can do some performance monitoring via database scoped DMVs:


Monitoring and troubleshooting read-only replicas

When connected to a read-only replica, Dynamic Management Views (DMVs) reflect the state of the replica, and can be queried for monitoring and troubleshooting purposes. The database engine provides multiple views to expose a wide variety of monitoring data.

Commonly used views are:

MONITORING AND TROUBLESHOOTING READ-ONLY REPLICAS

Name

Purpose

sys.dm_db_resource_stats

Provides resource utilization metrics for the last hour, including CPU, data IO, and log write utilization relative to service objective limits.

sys.dm_os_wait_stats

Provides aggregate wait statistics for the database engine instance.

sys.dm_database_replica_states

Provides replica health state and synchronization statistics. Redo queue size and redo rate serve as indicators of data latency on the read-only replica.

sys.dm_os_performance_counters

Provides database engine performance counters.

sys.dm_exec_query_stats

Provides per-query execution statistics such as number of executions, CPU time used, etc.

sys.dm_exec_query_plan()

Provides cached query plans.

sys.dm_exec_sql_text()

Provides query text for a cached query plan.

sys.dm_exec_query_profiles

Provides real time query progress while queries are in execution.

sys.dm_exec_query_plan_stats()

Provides the last known actual execution plan including runtime statistics for a query.

sys.dm_io_virtual_file_stats()

Provides storage IOPS, throughput, and latency statistics for all database files.

  

You can also set up xevent sessions : Read queries on replicas - Azure SQL Database | Microsoft Docs

 


SSRS Missing URL parameter IterationId2/5/2021 1:00:35 PM
SQL Server Reporting Services SSRS Error, Missing URL parameter:  IterationId.

Missing URL parameter: IterationId


I began receiving this error when i imported and tried to modify a report from SQL Server performance Dashboard that i was trying to make work with Azure PAAS SQL. I found this was not an uncommon issue. 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b44a0452-d71a-4e69-b7e0-af74b9f6bde7/missing-url-parameter-iterationid

 I deleted the Line from the report and problem was fixed. 

I was getting this error message myself, and it was due to the Line control. For whatever reason, the Line control tries to include a standard blank image right after it, but the URL is missing that parameter in FireFox and Chrome (but not IE, as far as I can tell). When I remove all the Line controls, the error goes away. Make sure you don't have any Lines in your footer (or anywhere else on your report, for that matter.)