Music :- Shakka & Wretch 32

Background

If I am going to have to work weekends, I might as well look for new music.

It is most likely not new for you, but here is a new voice for me.

Music

  1. Shakka
    • Shakka – Take Our Time
      Published On :- 2011-Dec 22nd
      Link
  2. Wretch 32
    • Wretch 32 – ‘6 Words’ (Official Video) (Out Now)
      Published On :- 2014-Oct-14th
      Link
    • Wretch 32 ft Shakka – ‘Blackout’ (Official Video)
      Published On :- 2103-May-28th
      Link
    • Wretch 32 ft Ed Sheeran – ‘Hush Little Baby’
      Published On :- 2012-April-26th
      Link

SQL Server Compare – sy.configurations – Using Linked Server

Background

Needed a quick code to compare SQL Instance configurations across two SQL SQL Server Instances.

Prerequisite

Please create a linked server named Secondary to the other SQL Server Instance.

Sample Code

Code

Here is a sample code for comparing SQL Server configuration’s setting set via sp_configure.

 

select 
		  [basedOn] = cast (serverproperty('servername') as sysname)
		, [name] = tblSCSrc.name collate Latin1_General_100_BIN2
		, [valuePrimary] = tblSCSrc.value
		, [valueSecondary] = tblSCDes.value
		, [missing]
			= case 
					when ( tblSCDes.[value_in_use] is null) then 1
					else 0
			 end

from   [master].sys.configurations tblSCSrc

left outer join [SECONDARY].[master].sys.configurations tblSCDes


	on tblSCSrc.[name] = tblSCDes.[name] collate Latin1_General_100_BIN2

where  (

			(
					   ( tblSCSrc.[value_in_use] != tblSCDes.[value_in_use] )
					or (tblSCDes.[value_in_use] is null )
			)

		)

union

select 
		  [basedOn] = tblSCDes.[basedOn]
		, tblSCSrc.name collate Latin1_General_100_BIN2
		, [valuePrimary] = tblSCSrc.value
		, [valueSecondary] = tblSCDes.value
		, [missing]
			= case 
					when ( tblSCDes.[value_in_use] is null) then 1
					else 0
			 end

from   [master].sys.configurations tblSCSrc

right outer join
(
	select *
	from   openquery
	(
	  	   [SECONDARY]
		 , 'select 
				      [basedOn]
						 = cast (serverproperty(''servername'') as sysname)

					, tblSC.* 
			from [master].sys.configurations tblSC 
			'
	)
)
		 tblSCDes

	on tblSCSrc.[name] = tblSCDes.[name] collate database_default

where  (

			(

				   (tblSCSrc.[value_in_use] is null )

			)

		)

Output

comparesqlserversettings_20170225_0426

 

Sermons & Discussions for 2017-March

 

Sermons

  1. Paris Reidhead
    • Satanic Strategy By Paris Reidhead
      Published On :- 2014-Jan-5th
      Link
  2. Damon Thompson
    • Damon Thompson- Discerning Transition and Receiving Direction
      Published On: 2013-Feb-3rd ( Super Bowl Sunday 2013 )
      Added On :- 2017-Feb-27th
      Link
  3. Live Your Song. | Jon Foreman | TEDxUniversityofNevada
    Published On:- 2016-Feb-8th
    Added On:- 2017-Feb-27th
    Link

 

NFL Players Trip to Israel

Background

Over the weekend I became aware of a story that tracked a planned visit of NFL players to Israel.

Story

Here is a good take of the story penned by Allison Kaplan Sommer:

Haaretz – Israel News
Link

What was supposed to be a high-profile image-buffing visit by a delegation of 13 NFL players to Israel ended up failing to draw enough players to field a team.
In the end, only five of the players originally slated on the seven-day trip designed to make them “ambassadors of good will” for Israel ended up arriving to tour the Holy Land and seeing the sights, after a public relations fumble caused the majority to pull out.

The players who made the trip were Arizona Cardinals defensive end Calais Campbell, Oakland Raiders defensive tackle Dan Williams, New Orleans Saints defensive end Cameron Jordan, Tennessee Titans tight end Delanie Walker and Philadelphia Eagles linebacker Mychal Kendricks.

The decision of the other participants not to come on the trip took place after the visit was publicized by Strategic Affairs and Public Diplomacy Minister Gilad Erdan on February 5, with a press release describing the trip as being part of an “intensive fight against the delegitimization and BDS campaigns against Israel, and part of this struggle includes hosting influencers and opinion-formers of international standing in different fields.” The football players would get “a balanced picture of Israel, the opposite from the false incitement campaign that is being waged against Israel around the world,” he said.

Move follows ‘open letter’ from Walker, Belafonte, Glover

The move was followed by the publishing of an “open letter” urging the players not to go, sponsored by pro-Palestinian activist groups and signed by high-profile activists and celebrities like Alice Walker, Harry Belafonte and Danny Glover. At the very beginning of the letter, Erdan’s words are cited as evidence that Israel was “aiming to use your fame to advance their own agenda: an agenda that comes at the expense of the Palestinian people.”

The decision by the other eight players not to travel to Israel was led by Seattle Seahawks defensive end Michael Bennett, who signaled he was pulling out of the trip by tweeting a photograph of Martin Luther King Jr. and a statement expressing resentment at what he saw as a manipulation by Israel’s government and declaring he wouldn’t “be used in such a manner.”
Since Bennett’s post, the ministry that had unveiled the trip with great fanfare went silent. After promising various updates on the delegation, Revital Yakin-Karkovsky, executive director for communications and strategy in the ministry, told the Associated Press that it would not comment on the visit.

The Tourism Ministry and the nonprofit America’s Voices in Israel organization, which were also involved in the planning, have also distanced themselves.

According to ESPN, the players who canceled their participation include Bennett’s brother Martellus, a tight end for the Super Bowl champion New England Patriots, along with Seattle Seahawks defensive end Cliff Avril, Miami Dolphins wide receiver Kenny Stills, San Francisco 49ers running back Carlos Hyde and Denver Broncos running back Justin Forsett, along with retired NFL linebacker Kirk Morrison.

The five players’ government-sponsored trip began Tuesday in the north of the country with a visit to Rambam Hospital, where the five players were given a tour and shown a presentation of a device developed by one of Rambam’s researchers that detects concussions in real time. Later, the players were set to include Yad Vashem and the Hebrew Israelite community in the southern city of Dimona.

Two of the visiting players, Jordan and Kendricks, have kept their fans updated on their travels by posting their adventures on social media.

My Take

Personally, my take is that all of us should do good to the extent that we can.

Carry One Another’s Burdens
…Let us not grow weary in well-doing, for in due time we will reap a harvest, if we do not give up.
Therefore, as we have opportunity, let us do good to everyone, and especially to the family of faith.
( Galatians 6:9-10)

Other Do Gooders

  1. Obama
    • Governor Bill Richardson
      • Gov. Richardson endorses Obama
        March 21st, 2008
        Link
        He drew laughs with a story about how Obama had bailed him out when a moderator called on him unexpectedly during a Democratic debate.

        “I was about to ask the moderator to repeat the question when Barack whispered to me ‘Katrina, Katrina.’ And I gave my Katrina answer. He could have thrown me under the bus, but he stood behind me.”
        Obama had earlier praised Richardson.
        “Whether it’s fighting to end the Iraq war or stop the genocide in Darfur or prevent nuclear weapons from falling into the hands of terrorists, Gov. Richardson has been a powerful voice on issues of global security, peace and justice,” Obama said in a statement released before the endorsement.
    • John McCain
      • McCain: Obama not an Arab, crowd boos
        By JONATHAN MARTIN and AMIE PARNES
        Dated :- 2008-Oct-10th
        Link
        McCain passed his wireless microphone to one woman who said, “I can’t trust Obama. I have read about him and he’s not, he’s not uh — he’s an Arab. He’s not — ” before McCain retook the microphone and replied:“No, ma’am. He’s a decent family man [and] citizen that I just happen to have disagreements with on fundamental issues and that’s what this campaign’s all about. He’s not [an Arab].”
  2. Brad Paisley
    • Flashback: Brad Paisley Salutes President Obama
      Singer visits the White House in 2009 to perform “Welcome to the Future” for Barack and Michelle Obama
      By Stephen L. Betts
      Date :-  2016-Nov-8th
      Link
      Paisley was a supporter of Obama’s candidacy, not an easy road for a performer in the predominantly conservative landscape of country music. But, as he told CNN in 2009, “On November 4th, I felt an emotion like I haven’t felt in my entire life. I think whoever you voted for, you had to be moved.”

      “Welcome to the Future,” penned with Chris DuBois, acknowledged the troubling history that made Obama’s victory even more significant, with Paisley recalling true events such as a classmate having a cross burned on his lawn because he had asked out the homecoming queen and referencing the brave actions of protester Rosa Parks and civil-rights leader Martin Luther King.Paisley, who had broken down in tears while running through the song during soundcheck earlier that afternoon, recalled doing “everything I could do not to lose it. I had to close my eyes, or I would completely break down.” After the performance, he tipped his hat to the president, thanked him and left the stage. Paisley recalled that once he was offstage he “just started bawling,” taking in the enormity of the moment. Paisley would again perform for the President and First Lady Michelle Obama on July 4th, 2012, and in January 2013 at the inauguration concert celebrating his second term.
    • Exclusive: Brad Paisley shares his night at the White House
      Link
      But this time, the phone call was essentially, “We would like to know if Brad would be interested in performing at the White House in the East Room.”
      It was as simple as that. It’s like, “Yes! More than yes. I’m floored. Why?!”
      And I could never get the answer why. They just wanted me to play.

      Ironically, I read this story just last week on CNN about Michelle Obama’s great-great grandfather, Jim Robinson, who was a slave in South Carolina. It’s insane to think about that.

      How the world has changed.On November 4th, I felt an emotion like I haven’t felt in my entire life. I think whoever you voted for, you had to be moved.My grandfather was in the Philippines fighting against the Japanese during World War II — and now I record for Sony and played Japan twice on tour, and loved every minute.If you’d go back in time and tell my grandfather during air raid sirens, “Hang on there. Your grandson — they’re gonna love him,” he never would have believed it.

Conclusion

Times are tough.

We all need help.

Sometimes it comes through friendship and actually working together.

At other times it comes through gracious and symbolic acts.

I am finding out that people sometimes view us different than we view ourselves.

In their lenses, we could have done more than we are doing.

We could have clarified something.

And, when they give us time to do so, and we choose not to do so, they will often move on.

But, that does not mean they forget or worse forgive.

I wish upon each of us “continuance in choosing good leadership“.

A while ago, we had a production problem and I needed help with instructions on how to get the system back operational.

One of seniors in the group explained to me how we got here and I was just staring and wondering if he thought an history is what I needed most of all.

I needed encouragement, access and privilege to the system, and an easy to understand guide.

A good friend shared with me a few years ago something to the effect that “sometimes people think or assume there needs are different than others“.

Closing Scripture

Paul Before the Areopagus ( Council or Court of Justice )
Nor is He served by human hands, as if He needed anything, because He Himself gives all men life and breath and everything else.

From one man He made every nation of men, to inhabit the whole earth; and He determined their appointed times and the boundaries of their lands.
God intended that they would seek Him and perhaps reach out for Him and find Him, though He is not far from each one of us.…
( Acts 17:25-27 )

SQL Server Management Studio ( SSMS ) – Non-Printable Characters

 

Background

Through the use of a Microsoft LightSwitch application that we are working on, I have been eating my dog food.

 

Data Entry

As one who hates the sheer drudgery of data entry, I end up doing quite a bit of copy and paste.

I copy and paste data from the original Excel file into the LightSwitch application.

And, things have been mostly OK; as the data that needs to be copied over is minimal.

But, recently I started noticing little errors cropping up.

I launched SQL Server Profiler and noticed the errors are due to referential integrity.

The error was traced to trying to place data into secondary tables whereas those same data are not in our primary data.

 

TroubleShooting

 

Query Data

Launched Sql Server Management Studio ( SSMS ), and queried for the data and they appear to be in the Primary table.

 

Issue

After a bit of head scratching traced the problem back to white spaces at the end of the primary key fields.

 

Remediation

As always took to the Net to determine fixes and workarounds.

Outline

Here are the workarounds that we found thus far:

  1. Using Transact SQL
    • Identify data that have non-printable characters ( CRLF )
    • Strip out the Non-Printable characters

 

Code

Using Transact SQL

Here the functions that I found on the Net

Sourced

  1. dbo.fn_ShowWhiteSpace
  2. dbo.fn_nonPrintableStringClean

 

Code Snippet

 
use master
go
 

 
if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_ShowWhiteSpace]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_ShowWhiteSpace]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER FUNCTION [npc].[fn_ShowWhiteSpace]
(
    @str varchar(8000)
)
RETURNS varchar(8000)
AS
BEGIN
    /*
        Michael Riley - AKA Gunny
        <a href="http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq">http://stackoverflow.com/questions/8655909/whats-the-best-way-to-identify-hidden-characters-in-the-result-of-a-query-in-sq</a>
 
        Michael Riley - AKA Gunny - Profile
        <a href="http://stackoverflow.com/users/195983/michael-riley-aka-gunny">http://stackoverflow.com/users/195983/michael-riley-aka-gunny</a>
 
    */
 
     DECLARE @ShowWhiteSpace varchar(8000);
     DECLARE @expanded bit
 
     set @expanded = 1
 
     SET @ShowWhiteSpace = @str
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(32), '[?]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(13), '[CR]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(10), '[LF]')
     SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(9),  '[TAB]')
 
     if (@expanded = 1)
     begin
 
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(1),  '[SOH]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(2),  '[STX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(3),  '[ETX]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(4),  '[EOT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(5),  '[ENQ]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(6),  '[ACK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(7),  '[BEL]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(8),  '[BS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(11), '[VT]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(12), '[FF]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(14), '[SO]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(15), '[SI]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(16), '[DLE]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(17), '[DC1]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(18), '[DC2]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(19), '[DC3]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(20), '[DC4]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(21), '[NAK]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(22), '[SYN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(23), '[ETB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(24), '[CAN]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(25), '[EM]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(26), '[SUB]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(27), '[ESC]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(28), '[FS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(29), '[GS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(30), '[RS]')
       SET @ShowWhiteSpace = REPLACE( @ShowWhiteSpace, CHAR(31), '[US]')
 
    end --if (@expanded = 1)
 
    RETURN(@ShowWhiteSpace)
 
END
go
 
grant execute on [npc].[fn_ShowWhiteSpace] to public
go

 

 

 

 
use [master]
go
 
set ansi_nulls on
go
set quoted_identifier on
go
 

if schema_id('npc') is null
begin
 
    exec('create schema [npc] authorization [dbo];');
 
end
go
 
if object_id('[npc].[fn_StringClean]') is null
begin
 
    exec('CREATE FUNCTION [npc].[fn_StringClean]() RETURNS varchar(8000) AS BEGIN return 1/0 END')
 
end
go
 
ALTER function [npc].[fn_StringClean] 
(
 @strIn as varchar(8000)
)
returns varchar(8000)
as
begin
 
    /*
        Blog
        How to remove unprintable characters from various fields in table
        <a href="https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql">https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1bf3e072-f194-4bff-87ee-07376927a7f8/how-to-remove-unprintable-characters-from-various-fields-in-table?forum=transactsql</a>
 
        Profile:
        NaveenCR
        <a href="https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini">https://social.msdn.microsoft.com/profile/naveencr/?ws=usercard-mini</a>
    */
 
    declare @iPtr as int
 
    set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
    while @iPtr > 0 
    begin
    
      set @strIn = replace(@strIn COLLATE LATIN1_GENERAL_BIN, substring(@strIn, @iPtr, 1), '')
 
      set @iPtr = patindex('%[^ -~0-9A-Z]%', @strIn COLLATE LATIN1_GENERAL_BIN)
 
     end
 
     return ( @strIn )
 
end
go
 
grant execute on [npc].[fn_StringClean] to public
go

 

 

Lab

Code
 

 

    select 
 
              tblLS.[serverName]
 
            , [serverName_Len] 
                = len(tblLS.[serverName])
 
            , [serverNameTrim]
                 = ltrim(rtrim(tblLS.[serverName]))
 
            , [serverName_Trim(Len]
                 = len(ltrim(rtrim(tblLS.[serverName])))
 
            , [ShowWhiteSpace]
                = [master].dbo.[fn_ShowWhiteSpace](tblLS.servername)
 
            , [serverName_NPClean]
                 = [master].[npc].[fn_StringClean]  (tblLS.[serverName])
 
            , [serverName_NPClean_Len] 
                = datalength([master].[npc].[fn_StringClean]  (tblLS.[serverName]))
 
    from   [DBLAB].dbo.[listofServers.2017022] tblLS
 
    where  (
                (
                    (
                         tblLS.servername 
                            != [master].[npc].[fn_StringClean] (tblLS.servername) 
                    )
                )
          )    

 

 

 

Output

dbo_listofservers_20170222_1036pm

 

Explanation
  1. Here are the columns that are being shown
    1. serverName
      • The actual Server Name
      • The length of the Server Name
    2. serverName Trimmed
      • Using ltrim and rtrim we trim the Server Name
      • We get the length of the trimmed column
    3. Show whitespace
      • Show the serverName and display Non-Printable characters
    4. Show “Cleaned” serverName
      • Show cleaned Server Name
      • Show length of cleaned Server Name

 

Microsoft Connect Items

  1. SQl Server Management Studio should show new lines in records. – by Michael Freidgeim
    • Submitted By :- Michael Freidgeim
    • Item ID :-381955
    • Date Submitted :- 2008-Nov-14th 4 AM
    • Link
    • Description:-
      • 1. If there are newLines characters in the record data, SQl Server Management Studio in Grid view shows the record as one string, without indication that newlines are present.
        It causes confusion to the user, invalid interpretation of the data and even data loss.See scenario in my post
        http://geekswithblogs.net/mnf/archive/2008/11/13/sql-server-management-studio-doesnt-show-new-lines-in-records.aspx
        It will be good if newLines will be shown as actual new lines(consistent with SQL Server 2000 Enterprize Manager) or as some special character(e.g \n or |) to inform user.
        Ideally method on new line presentation could be configured in Tools/Options/Query Results/SQL Server/Results to Grid.
      • 2. By the way, separate suggestion: make Results to Text Maximum Length of the output default  to 8192  (rather than 256) to avoid truncations, that are not obvious.
    • Microsoft Feedback
      • Posted by Seshagiri ( Microsoft ) on 2011-May-31st 7:33 AM
        • Hi Michael,
          This is related to the connect item 381955. We evaluated the feedback carefully and are able to reproduce the cases you listed. However because of the following reasons we are unfortunately not able to attend to this request:

          • 1. new lines in grid
            When using the results to grid option, we use the standard Windows grid control to display the results. This grid control treats each cell value as a plain text and hence the new line characters are ignored. The Save as function when executed from the results section just takes the content from the grid as it is and hence the newline is not found in the new file created from the grid. However if you use the results to text option or the results to file the new line characters are retained. SSMS is not really intended to be a reporting tool, so we are unable to spend much time on the result formatting, especially if some workarounds exist like mentioned above.
          • 2. Max characters when using results to text
            The number specified here is used to preallocate memory for the text column data. A larger value for default could mean lot of unnecessary memory being allocated but not used. Hence it is left to the user to increase the value based on their needs.
            I hope the above sounds reasonable. I am currently closing the issue as a wont-fix. However in case there is anything not mentioned previously please let us know and we will surely take a relook.
          • Thank you
            Seshagiri
            PM, SSMS

SQL Server – SQL Server Agent – Job “Job History (OptimizePlan.Subplan_1)” Failed

Background

Reviewing SQL Server Agent jobs found ones that consistency fails.

One of them is a Maintenance Plan that Organizes Indexes.

 

Error Message

SQL Server Agent

Image

jobhistory-optimizeplan-subplan-20170222-0245pm-cropped-up

 

Textual

Executed as user: DBLAB\sql. … Version 11.0.6020.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:00:40 PM Progress: 2017-02-22 15:00:41.05 Source: {04448243-3A2E-4299-BC33-5780CE3F7DEC} Executing query “DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp…”.: 100% complete End Progress Progress: 2017-02-22 15:00:43.82 Source: Progress Progress: 2017-02-22 15:00:43.86 Source: Reorganize Index Task Execut… The package execution fa… The step failed.

 

TroubleShooting

Maintenance Plan

Review the Maintenance Plan and here is what things look like.

OptimizePlan

Image

maintenanceplan_optimizeplan_20170222_0249pm-cropped-up

 

OptimizePlan

Reporting and Logging

Reporting and Logging ( Default )

Here is the default setting with “Log extended information” off.

reportingandlogging_20170222_0250pm

Reporting and Logging ( Post Changes )

Here is what happens when we set “Log extended information” on.

reportingandlogging_20170222_0259pm

 

Logfiles

Went to the identified Log Folder and sought for files matching the maintenance Plan we are trying to dig into.

optimizeplan_folder_20170222_0251pm

 

Logfile Contents

Image

content_20170222_0449pm

Text


USE [LNCD]
GO
ALTER INDEX [NCD_PK] ON [dbo].[NCD] REORGANIZE WITH ( LOB_COMPACTION = ON )
GO
USE [LNCD]
GO
ALTER INDEX [RelatedCase_PK] ON [dbo].[RelatedCase] REORGANIZE WITH ( LOB_COMPACTION = ON )

GO

Reorganize index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Object: Tables and views
Compact large objects
Task start: 2017-02-19T00:00:12.
Task end: 2017-02-19T00:01:56.
Failed:(-1073548784) Executing the query "ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]...." failed with the following error:
"The index "IX_FullNCDCategories_NCDID" on table "FullNCDCategories" cannot be reorganized because page level locking is disabled.".
Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Command:
GO

GO

Explanation

  1. The error Message has the following content
    • USE LNCD
      • Changing to database LNCD
    • Failed:(-1073548784) Executing the query “ALTER INDEX [IX_FullNCDCategories_NCDID] ON [dbo]….” failed with the following error
      • The name of the index is IX_FULLNCCategories
    • “The index “IX_FullNCDCategories_NCDID” on table “FullNCDCategories” cannot be reorganized because page level locking is disabled.”.
      • The targeted table is FULLNCDCategories
    • The Index can not reorganized because page level locking is disabled on the targeted table

 

Remediation

Identify impacted tables

Identify Impacted tables on current database

Code


SELECT 
		  [database] = db_name()

		, [schema]   = schema_name(tblSO.schema_id)

		, [object]   = tblSO.[name]

		, [index]    = tblSI.[name]

		, [allowRowLocks]
			= case ( tblSI.[allow_row_locks])
				when 1 then 'Yes'
				else 'No'
			  end

		, [allowPageLocks]
			= case ( tblSI.[allow_page_locks] )
				when 1 then 'Yes'
				else 'No'
			  end

FROM sys.objects tblSO 

inner join sys.indexes tblSI 

	on tblSO.object_id = tblSI.object_id 

WHERE tblSO.[type] = 'U' 

and ( 
		   ( tblSI.[allow_row_locks] = 0)  
		or ( tblSI.[allow_page_locks] = 0 ) 
	) 

and  tblSO.[is_ms_shipped] = 0 

ORDER BY 
		tblSO.[name]



Image

identifyimpactedtablesoncurrentdb_20170222_0518pm

 

Identify Impacted tables on all databases

Code


DECLARE @commandPLLD	varchar(1000) 

declare @tblObjectPageLevelLockingDisabled TABLE
(
	  [database]			sysname
	, [schema]				sysname
	, [object]				sysname
	, [index]				sysname  null
	, [allow_row_locks]		int null
	, [allow_page_locks]	int null

	, [sqlStatementPreserve] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = '
				+ case([allow_page_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end
				+ '		 , ALLOW_ROW_LOCKS = '
				+ case([allow_row_locks])
						when 1 then ' ON '
						when 0 then ' OFF '
				  end

				+ '		) '


	, [sqlStatementRevise] 
			as 
				  'use ' + quoteName([database]) + ';' 
				+ '  '
				+ 'ALTER INDEX '
				+ QuoteName([index])
				+ ' ON '
				+ QuoteName([schema])
				+ '.'
				+ QuoteName([object])
				+ ' '
				+ '	SET (  '
				+ '		   ALLOW_PAGE_LOCKS = ON  '
				+ '		 , ALLOW_ROW_LOCKS = ON  '
				+ '		) '

)

SELECT @commandPLLD = 'USE [?]; SELECT [database] = db_name(), schema_name(tblSO.schema_id), tblSO.name, tblSI.[name],  tblSI.[allow_row_locks]	, tblSI.[allow_page_locks] FROM sys.objects tblSO inner join sys.indexes tblSI on tblSO.object_id = tblSI.object_id WHERE tblSO.type = ''U'' and ( ( tblSI.allow_row_locks = 0)  or ( tblSI.allow_page_locks = 0 ) ) and  tblSO.[is_ms_shipped] = 0 ORDER BY tblSO.name ' 

insert into @tblObjectPageLevelLockingDisabled
(
	  [database]
	, [schema]
	, [object]
	, [index]
	, [allow_row_locks]		
	, [allow_page_locks]	
)
EXEC sp_MSforeachdb @commandPLLD


select *
		
from   @tblObjectPageLevelLockingDisabled tblPLLD




Explanation

The code snippet above does the following:

  1. Uses sp_MSforeachdb to run the same simple discovery code across all databases
  2. Queries the sys.indexes table looking for
    • allow_page_locks equal to 0
  3. When found it captures the
    • Current SQL for preserving the current Index State
    • The SQL to revise the current state unto set allow_page_lcoks to 1 & allow_page_locks to 1

Output

identifyimpactedtables_20170222_0506pm-croppedup

 

Ran

Captured the script from the sqlStatementRevise column.

Image

jobhistory-optimizeplan-subplan-20170222-0524pm-croppedup

Explanation

All of twenty-seven minutes later, I and we are good.

 

Microsoft -LightSwitch – Error – “Request Failed with Status Code ‘500’ and Status Text ‘Internal Status Error.'”

Background

From within Visual Studio, launched an application that I am developing using LightSwitch and received one of those hard to decipher error.

Error Message

Image

requestfailedwithstatuscode500

Text

Request failed with status code ‘500’ and status text ‘Internal Server Error’.

TroubleShooting

As I know so little about LightSwitch, I get petrified anytime I see an error.

Event Viewer

Took to the system’s event viewer and found the obvious problem.

Sender Information: System.ServiceModel.Activation.HostedHttpRequestAsyncResult – System.ServiceModel.ServiceActivationException

Image

taskcategory_webhost_2017021_0445pm-cropped-up

Textual

WebHost failed to process a request.
Sender Information: System.ServiceModel.Activation.HostedHttpRequestAsyncResult/35567111
Exception: System.ServiceModel.ServiceActivationException: The service ‘/DBDiagData.svc’ cannot be activated due to an exception during compilation. The exception message is: Memory gates checking failed because the free memory (196927488 bytes) is less than 5% of total memory. As a result, the service will not be available for incoming requests. To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.. —> System.InsufficientMemoryException: Memory gates checking failed because the free memory (196927488 bytes) is less than 5% of total memory. As a result, the service will not be available for incoming requests. To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.
at System.ServiceModel.Activation.ServiceMemoryGates.Check(Int32 minFreeMemoryPercentage, Boolean throwOnLowMemory, UInt64& availableMemoryBytes)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CheckMemoryCloseIdleServices(EventTraceActivity eventTraceActivity)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath, EventTraceActivity eventTraceActivity)
— End of inner exception stack trace —
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.End(IAsyncResult result)
Process Name: iisexpress
Process ID: 2756

Tabulate

Product Version File Version
 Log Name Application
 Source System.ServiceModel 4.0.0.0
 Event ID  3
 Task Category  WebHost
 Level  Error
 Process Name  iisexpress

 

 

ASP.NET 4.0.30319.0 –  InsufficientMemoryException

Image

taskcategory_aspnet_2017021_0616pm-croppedup

Textual

Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 2/21/2017 4:15:03 PM
Event time (UTC): 2/22/2017 12:15:03 AM
Event ID: a9e5abe2978940499d84b3ed7b2bc5a1
Event sequence: 34
Event occurrence: 1
Event detail code: 0

Application information:
Application domain: /LM/W3SVC/2/ROOT-1-131321960967478001
Trust level: Full
Application Virtual Path: /
Application Path: C:\EDiag\bin\Debug\
Machine name: DADENIJI

Process information:
Process ID: 2756
Process name: iisexpress.exe
Account name: dadeniji

Exception information:
Exception type: InsufficientMemoryException
Exception message: Memory gates checking failed because the free memory (179412992 bytes) is less than 5% of total memory. As a result, the service will not be available for incoming requests. To resolve this, either reduce the load on the machine or adjust the value of minFreeMemoryPercentageToActivateService on the serviceHostingEnvironment config element.
at System.ServiceModel.Activation.ServiceMemoryGates.Check(Int32 minFreeMemoryPercentage, Boolean throwOnLowMemory, UInt64& availableMemoryBytes)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CheckMemoryCloseIdleServices(EventTraceActivity eventTraceActivity)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath, EventTraceActivity eventTraceActivity)

Request information:
Request URL: http://localhost:62496/Services/Microsoft-LightSwitch-Security-ServerGenerated-Implementation-AuthenticationService.svc/binary/GetAuthenticationInfo
Request path: /Services/Microsoft-LightSwitch-Security-ServerGenerated-Implementation-AuthenticationService.svc/binary/GetAuthenticationInfo
User host address: 127.0.0.1
User: dadeniji
Is authenticated: True
Authentication Type: Negotiate
Thread account name: dadeniji

Thread information:
Thread ID: 11
Thread account name: dadeniji
Is impersonating: False
Stack trace: at System.ServiceModel.Activation.ServiceMemoryGates.Check(Int32 minFreeMemoryPercentage, Boolean throwOnLowMemory, UInt64& availableMemoryBytes)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CheckMemoryCloseIdleServices(EventTraceActivity eventTraceActivity)
at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath, EventTraceActivity eventTraceActivity)

 

Tabulate

Product Version File Version
 Log Name Application
 Source ASP.NET 4.0.30319.0
 Event ID  1309
 Task Category  Web Event
 Level  Warning
 Process Name  iisexpress.exe

 

 

Remediation

Using Microsoft’s Task Manager close some of the Applications and Processes that are sagging memory.

Our target is to get more than 5% memory available.