With this query, you can see whitch database that is using most memory
SELECT COUNT(*) AS cached_pages_count ,
( COUNT(*) * 8.0 ) / 1024 AS MB ,
CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE DB_NAME(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY database_id
Whit this code, you can see how much of query in percent at this moment is reading from the cache/buffer.
DECLARE @value numeric(25, 2), @basevalue numeric(25, 2)
declare @Cachehitratio real
set @Cachehitratio = null
--Cache hit ratio
SELECT @value = cntr_value FROM master..sysperfinfo (nolock)
WHERE counter_name = 'Buffer cache hit ratio'
SELECT @basevalue = cntr_value FROM master..sysperfinfo (nolock)
WHERE counter_name = 'Buffer cache hit ratio base'
set @Cachehitratio= (@value / @basevalue) *100
select @Cachehitratio
When taking backup of a SQL server i got this error in my event viewer, every times the backup is running:
Event Viewer Error:
Cryptographic Services failed while processing the OnIdentity() call in the System Writer Object.
Details:
AddLegacyDriverFiles: Unable to back up image of binary Microsoft Link-Layer Discovery Protocol.
System Error:
Access is denied.
.
Solution:
During backup a VSS process running under NETWORK_SERVICE account calls cryptcatsvc!CSystemWriter::AddLegacyDriverFiles(), which enumerates all the drivers records in Service Control Manager database and tries opening each one of them. , The function fails on MSLLDP record with “Access Denied” error.
Turned out it fails because MSLLDP driver’s security permissions do not allow NETWORK_SERVICE to access the driver record.
The binary security descriptor for the record is located here:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MsLldp\Security
It should be modified, I used SC.EXE and Sysinternals’ ACCESSCHK.EXE to fix it.
The original security descriptor looked like below:
>accesschk.exe -c mslldp
mslldp
RW NT AUTHORITY\SYSTEM
RW BUILTIN\Administrators
RW S-1-5-32-549 <- these are server operators
R NT SERVICE\NlaSvc
No service account is allowed to access MSLLDP driver
The security descriptor for the drivers that were processed successfully looked this way:
>accesschk.exe -c mup
mup
RW NT AUTHORITY\SYSTEM
RW BUILTIN\Administrators
R NT AUTHORITY\INTERACTIVE
R NT AUTHORITY\SERVICE <- this gives access to services
How to add access rights for NT AUTHORITY\SERVICE to MSLLDP service:
1. Run: SC sdshow MSLLDP
You’ll get something like below (SDDL language is documented on MSDN):
D:(D;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BG)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)(A;;CCDCLCSWRPDTLOCRSDRCWDWO;;;BA)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SO)(A;;LCRPWP;;;S-1-5-80-3141615172-2057878085-1754447212-2405740020-3916490453)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
2. Run: SC sdshow MUP
You’ll get:
D:(A;;CCLCSWRPWPDTLOCRRC;;;SY)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BA)(A;;CCLCSWLOCRRC;;;IU)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
3. Take NT AUTHORITY\ SERVICE entry, which is (A;;CCLCSWLOCRRC;;;SU) and add it to the original MSLLDP security descriptor properly, right before the last S:(AU… group.
4. Apply the new security descriptor to MSLLDP service :
sc sdset MSLLDP D:(D;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;BG)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SY)(A;;CCDCLCSWRPDTLOCRSDRCWDWO;;;BA)(A;;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;SO)(A;;LCRPWP;;;S-1-5-80-3141615172-2057878085-1754447212-2405740020-3916490453)(A;;CCLCSWLOCRRC;;;SU)S:(AU;FA;CCDCLCSWRPWPDTLOCRSDRCWDWO;;;WD)
5. Check the result:
>accesschk.exe -c mslldp
mslldp
RW NT AUTHORITY\SYSTEM
RW BUILTIN\Administrators
RW S-1-5-32-549
R NT SERVICE\NlaSvc
R NT AUTHORITY\SERVICE
6. Run you backup app, the error is gone for my Home Server backup.
!!! Do not forget to use your security descriptor for MSLLDP driver since I guess there can be some rare cases when its different for your machine. Do not copy my SDDL descriptions, just in case. And backup the old descriptor just in case !!!
Thanks to szz743
https://answers.microsoft.com/en-us/windows/forum/windows8_1-hardware/cryptographic-services-failed-while-processing-the/c4274af3-79fb-4412-8ca5-cee721bda112
Today i have a problem that an exchange will not send mails though TLS, no matter what i do.
I find out that, somehow this exchange servers where creating new send connector, with forcehelo = True.
Witch mean that the Exchange server is using the OLD HELO instead of the EHLO, when talking to other SMTP servers. Setting this to false help. Now the Exchange server send with TLS.
So FORCEHELO=$TRUE breaks TLS.
To see if this is the problem, you can type:
get-sendconnector | select id, forceheloTo set it to false, for the send connector “Default_OUT” you can run this powershell command:
get-sendconnector -id Default_OUT | set-sendconnector -forcehelo $falseAlso you might need to set the TLS certificate to the right certificate. Remember that the certificate need to have the hostname in the certificate. Se more on this link:
For some reason RemoteFX USB Redirection is not enabled standard in Windows.
So what to do with a non domain joined computer, you can change GPO from the Active Directory.
That is not a problem, you can run GPEDIT from the client.
Right click on start, and select Run
And click OK
Now browse to Computer Configuration -> Administrative Templates -> Windows Components -> Remote Desktop Services -> Remote Desktop Connection Client -> RemoteFX USB Device Redirection
Dobble click on “Allow RDP redirection of other supported RemoteFX USB desvices from this computer”
And change the settings to Enabled, and choice Adminstrators and Users.
Click ok
Now restart you computer, and you should now have enabled RemoteFX.
If you have Installed Skype for Business and have enabled RemoteFX.
You might still find that Skype for Business is saying that it using Remote Audio, even if you have redirected you USB headset.
This can be changed, by running this command on in a Administrator Powershell.
Set-RDSessionCollectionConfiguration -CollectionName "YourCollectionName" -CustomRdpProperty "use re
direction server name:i:1 `n usbdevicestoredirect:s:* `n audiocapturemode:i:1 `n audiomode:i:1"After this go to yours RDWeb page, and download a new RDP fil, and connect with you new RDP file.
After that Skype should say that is using you USB Headset.
Remember to select that you will redirect you USB Headset.
If you have a RemoteAPP collection, and you will like to have allow you user, to access a full Desktop.
This can be changed by logging into yours Remote Desktop Broker, and go to the Regedit, and browse to this page:
HKLM\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Terminal Server\CentralPublishedResources\PublishedFarms\<collection>\RemoteDesktops\<collection>
And set the value of ShowInPortal to 1
After this you will have the desktop in RDWeb
Today I have this problem, that no matter what I do. I could get RemoteFX to work.
The strange things is it where working yesterday, and no one will admit that the have changed anything.
And no windows update have been installed.
I was going into debugging, and for some reason I so this registry was set to 0
HKLM -> Software -> Policies -> Microsoft -> Windows NT -> Terminal Services -> fDisableCam
Normally it is not configure on a RDS Session Host server.
So I deleted it, rebooted the server and RemoteFX is now working again.
Run this command to see if any database on the SQL Server is using enterprise features
IF OBJECT_ID('tempdb.dbo.##enterprise_features') IS NOT NULL
DROP TABLE ##enterprise_features
CREATE TABLE ##enterprise_features
(
dbname SYSNAME,
feature_name VARCHAR(100),
feature_id INT
)
EXEC sp_msforeachdb
N' USE [?]
IF (SELECT COUNT(*) FROM sys.dm_db_persisted_sku_features) >0
BEGIN
INSERT INTO ##enterprise_features
SELECT dbname=DB_NAME(),feature_name,feature_id
FROM sys.dm_db_persisted_sku_features
END '
SELECT *
FROM ##enterprise_features
If you got an error about compression, when trying to attach the database from an enterprise version to a standard version. You will have to attach the database to an enterprise database, and remove the compression first.
The error:
sg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. Msg 909, Level 21, State 1, Line 2 Database 'abc' cannot be started in this edition of SQL Server because part or all of object 'def' is enabled with data compression or vardecimal storage format. Data compression and vardecimal storage format are only supported on SQL Server Enterprise Edition. Msg 933, Level 21, State 1, Line 2 Database 'abc' cannot be started because some of the database functionality is not available in the current edition of SQL Server.
The SQL Command to run to disabled compression. It should be run on a query on the database with compression.
(Baware of the database will then fill something extra on the storage, so keep in mind that you need to have space available for that.)
SELECT DISTINCT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.partitions p join sys.objects o on p.object_id = o.object_id WHERE o.TYPE = 'u' and data_compression_desc != 'NONE' UNION SELECT 'ALTER INDEX ALL ON [' + SCHEMA_NAME(schema_id) + '].[' + NAME + '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);' FROM sys.partitions p join sys.objects o on p.object_id = o.object_id WHERE o.TYPE = 'u' and data_compression_desc != 'NONE'
If the database have compression it will output with multiple SQL commands. Run these command against you database aswell
Example: (You should run what you rnu of the script output)
ALTER INDEX ALL ON [dbo].[MSSBatchHistory] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSChangeLogCookies] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlDeletedURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlDeletedURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlHistoryLocal] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlReportCrawlErrors] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSCrawlURLReport] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSDocIDsAvailable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSDocIDsDeleted] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER INDEX ALL ON [dbo].[MSSNextDocID] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSBatchHistory] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSChangeLogCookies] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlDeletedURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlDeletedURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlHistoryLocal] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlReportCrawlErrors] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlURL] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlURLLog] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSCrawlURLReport] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSDocIDsAvailable] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSDocIDsDeleted] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE); ALTER TABLE [dbo].[MSSNextDocID] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);