After installation of these update, you can have problem booting you server. with this warning:
The digital signature for this file couldn’t be verified. file:\windows\system32\winload.efi status 0xc0000428
You can fix it by booting you windows operation on the installation disc, and go to a Recovery command prompt and type these commands:
c:\> bcdedit
This screenshot is taken from a running Windows Server, in a recovery boot the identifier under the second Windows Boot Loader, will be named {default} instead of {current}
c:\> bcdedit -set {default} -nointegritychecks 1
Replace {default} with the one from the bcdedit output.
Normal this have been resolved by installation and updated version of KB4474419, but there have not been an updated version to Windows server 2008 yet. But to Windows 7 and Windows server 2008 R2, you can download an updated version here: http://www.catalog.update.microsoft.com/search.aspx?q=kb4474419
I have this problems, that if a schedule inside a Agent job, have not been started for some reason, there will be no errors about this problem.
So Í have created this script, that will give the Status of 1, if the job, haven’t been run for the last two hours. I used PRTG to check the status.
USE msdb
GO
DECLARE @JobName VARCHAR(1000)
DECLARE @LastRunTime VARCHAR(1000)
DECLARE @LastRunStatus VARCHAR(1000)
DECLARE @CurrentTime VARCHAR(20)
SET @JobName = 'JOBNAME'
SET @CurrentTime = LEFT(REPLACE(CONVERT (time, DATEADD(HOUR, - 2, GETDATE())),':',''),6)
SELECT DISTINCT @LastRunTime = SJH.run_time
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJ.[name] = @JobName AND SJH.job_id = SJ.job_id and SJH.run_date =
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
IF @LastRunTime >= @CurrentTime
SELECT '0' AS Status
ELSE
SELECT '1' AS Status
In line 10, replace – 2 with the number of hours, you want to go back
Below is the same script, just for daily jobs, so it will will check if the job haven’t been run in the last 25 hours (My Schedule is set to run every 24 hours, and i give it an hour to be finish, before i got an alarm.
DECLARE @JobName VARCHAR(1000)
DECLARE @LastRunDate VARCHAR(1000)
DECLARE @LastRunStatus VARCHAR(1000)
DECLARE @CurrentDate VARCHAR(20) -- used for file name
SET @JobName = 'JOBNAME'
SET @CurrentDate = REPLACE(CONVERT (date, DATEADD(HOUR, -25, GETDATE())),'-','')
SELECT DISTINCT @LastRunDate = SJH.run_date
FROM sysjobhistory SJH, sysjobs SJ
WHERE SJ.[name] = 'Fibia optimering daglig (Batch 2)' AND SJH.job_id = SJ.job_id and SJH.run_date =
(SELECT MAX(SJH1.run_date) FROM sysjobhistory SJH1 WHERE SJH.job_id = SJH1.job_id)
ORDER BY SJH.run_date desc
IF @LastRunDate >= @CurrentDate
SELECT '0' as Status
ELSE
SELECT '1' as Status
In line 10, replace – 25 with the number of hours, you want to go back
The function I use fn_hadr_group_is_primary from this post is a UDF (User Defined Function) that we create in the master database. The function gets an Availability Group name as an input parameter and returns a Boolean value indicating weather this instance is a primary replica.
-- fn_hadr_group_is_primary
USE master;
GO
IF OBJECT_ID('dbo.fn_hadr_group_is_primary', 'FN') IS NOT NULL
DROP FUNCTION dbo.fn_hadr_group_is_primary;
GO
CREATE FUNCTION dbo.fn_hadr_group_is_primary (@AGName sysname)
RETURNS bit
AS
BEGIN;
DECLARE @PrimaryReplica sysname;
SELECT
@PrimaryReplica = hags.primary_replica
FROM sys.dm_hadr_availability_group_states hags
INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
WHERE ag.name = @AGName;
IF UPPER(@PrimaryReplica) = UPPER(@@SERVERNAME)
RETURN 1; -- primary
RETURN 0; -- not primary
END;
Use the fn_hadr_group_is_primary function within a new job step to find if this sql instance is a Primary replica. If this is not a primary replica we issue a stop job request while identifying the current job name using SQL Server Agent Tokens
-- Detect if this instance's role is a Primary Replica.
-- If this instance's role is NOT a Primary Replica stop the job so that it does not go on to the next job step
DECLARE @rc int;
EXEC @rc = master.dbo.fn_hadr_group_is_primary N'my-ag';
IF @rc = 0
BEGIN;
DECLARE @name sysname;
SELECT @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))'));
EXEC msdb.dbo.sp_stop_job @job_name = @name;
PRINT 'Stopped the job since this is not a Primary Replica';
END;
The script is very cool, as it will tell you the backup information, from a database. All you need to do is change this ‘<DATABASENAME>’ to yours database name, example ‘MASTER’. It will also include if a job where COPY ONLY, and where the backup is save.
You can see in the screenshot below, that someone have run a full backup and save it on the D:\ drive, but it where a copy only so the backup is not broken.
/* ==================================================================
Author......: hot2use
Date........: 25.04.2018
Version.....: 0.1
Server......: localhost (first created for)
Database....: msdb
Owner.......: -
Table.......: various
Type........: Script
Name........: ADMIN_Retrieve_Backup_History_Information.sql
Description.: Retrieve backup history information from msdb database
............
............
............
History.....: 0.1 h2u First created
............
............
================================================================== */
SELECT /* Columns for retrieving information */
-- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
-- msdb.dbo.backupset.expiration_date,
CASE msdb.dbo.backupset.type
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Diff'
WHEN 'L' THEN 'Log'
END AS backup_type,
-- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
msdb.dbo.backupmediafamily.logical_device_name,
msdb.dbo.backupmediafamily.physical_device_name,
-- msdb.dbo.backupset.name AS backupset_name,
-- msdb.dbo.backupset.description,
msdb.dbo.backupset.is_copy_only,
msdb.dbo.backupset.is_snapshot,
msdb.dbo.backupset.checkpoint_lsn,
msdb.dbo.backupset.database_backup_lsn,
msdb.dbo.backupset.differential_base_lsn,
msdb.dbo.backupset.first_lsn,
msdb.dbo.backupset.fork_point_lsn,
msdb.dbo.backupset.last_lsn
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset
ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
/* ----------------------------------------------------------------------------
Generic WHERE statement to simplify selection of more WHEREs
-------------------------------------------------------------------------------*/
WHERE 1 = 1
/* ----------------------------------------------------------------------------
WHERE statement to find Device Backups with '{' and date n days back
------------------------------------------------------------------------------- */
-- AND physical_device_name LIKE '{%'
/* -------------------------------------------------------------------------------
WHERE statement to find Backups saved in standard directories, msdb.dbo.backupfile AS b
---------------------------------------------------------------------------------- */
-- AND physical_device_name LIKE '[fF]:%' -- STANDARD F: Backup Directory
-- AND physical_device_name NOT LIKE '[nN]:%' -- STANDARD N: Backup Directory
-- AND physical_device_name NOT LIKE '{%' -- Outstanding Analysis
-- AND physical_device_name NOT LIKE '%$\Sharepoint$\%' ESCAPE '$' -- Sharepoint Backs up to Share
-- AND backupset_name NOT LIKE '%Galaxy%' -- CommVault Sympana Backup
/* -------------------------------------------------------------------------------
WHERE Statement to find backup information for a certain period of time, msdb.dbo.backupset AS b
----------------------------------------------------------------------------------
AND (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) -- 7 days old or younger
AND (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) <= GETDATE()) -- n days old or older
*/
/* -------------------------------------------------------------------------------
WHERE Statement to find backup information for (a) given database(s)
---------------------------------------------------------------------------------- */
AND database_name IN ('<DATABASENAME>') -- database names
-- AND database_name IN ('rtc') -- database names
/* -------------------------------------------------------------------------------
ORDER Clause for other statements
---------------------------------------------------------------------------------- */
--ORDER BY msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_finish_date -- order clause
---WHERE msdb..backupset.type = 'I' OR msdb..backupset.type = 'D'
ORDER BY
--2,
2 DESC,
3 DESC
#You can run these commands, to check for old MoveRequest or all mailbox in Office365, just remove the hash. #get-moverequest #get-mailbox
get-mailbox -id MAILBOX | New-MoveRequest -OutBound -RemoteTargetDatabase DB01 -RemoteHostName owa.domain.prefix -RemoteCredential $ONPREMCREDS -TargetDeliveryDomain ‘InternalAdDomain.prefix’ #Replace MAILBOX with the mailbox # Replace owa.domain.prefix with the public owa address # Replace InternalAdDomain.prefix with the internal ad domain name
#You can then run the following commands to check the mailbox moverequest status Get-MoveRequest | Get-MoveRequestStatistics
Today i have a customer that got an Bad Request Error 400, when he try to logon to ADFS from inside their own network, with domain connected computers. The error is only showing up in Internet Explorer, witch lead me to it might be a kerberos thing.
The service account that run the ADFS services, is “ita_service_admin”.
So looking at that account with setspn i can see witch SPN is register to ita_service_admin account.
setspn -l XXX\ita_service_admin
The where no SPN for the account. The ADFS url is: https://fs.YYY.dk so i added the following SPN to ita_service_Admin account:
Find the SPN, and delete it from the account, i find the account under Administrator, so it looks like someone have change the service account for ADFS service from Administrator to ITA_SERVICE_ADMIN account in the past, without moving the SPN.
So delete the SPN from administrator account
setspn -D HOST/FS.YYY.DK XXX\administrator
Then try to add it again to ITA_Service_Admin account.
setspn -S HOST/FS.YYY.DK XXX\ITA_SERVICE_ADMIN
Restart the ADFS service, and the ADFS is working again also from Internet Explorer.
How fast is data leving the cache, when it not access again.
With this query, you can see how fast data will get expire, and overwritten by new data in the cache.
SELECT @@servername AS INSTANCE
,[object_name]
,[counter_name]
, UPTIME_MIN = CASE WHEN[counter_name]= 'Page life expectancy'
THEN (SELECT DATEDIFF(MI, MAX(login_time),GETDATE())
FROM master.sys.sysprocesses
WHERE cmd='LAZY WRITER')
ELSE ''
END
, [cntr_value] AS PLE_SECS
,[cntr_value]/ 60 AS PLE_MINS
,[cntr_value]/ 3600 AS PLE_HOURS
,[cntr_value]/ 86400 AS PLE_DAYS
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND[counter_name] = 'Page life expectancy'
Witch the query you can see how long time data will be in the cache/buffer.