How to check if SQL Server Agent is running using t-sql

Question: I want to check if SQL Server Agent is running using t-sql. I know I can check through the services.msc or other methods such as Powershell scripts to report on the SQL Server Agent status , but the requirement is to extract this information via t-sql. Do you have a script using t-sql to get the SQL Server Agent Status? Answer: If you check the SQL generic Refresher program in the sysprocesses list , it is possible to establish the SQL Server Agent status through t-sql. The SQL Generic Refresher is an app program. When the SQL Server Agent... Read more →

How to check if Puppet is reboots a windows server with Powershell

If you want a Powershell script to quickly check if Puppet has initiated a reboot on a Windows server , use this script . It uses the Powershell cmdlet -Get-WinEvent. Typically - a Puppet maintenance window is set per server. If a reboot is required , due to a pending patching change - than the reboot will occur during the designated window. A server owner may experience an unexpected outage - and will want to investigate the root cause. Part of the root cause may include analysing the Event viewer logs for clues. The Windows Event Logs stores loads of... Read more →

How To Use Transparent Data Encryption To Increase Your Security

In addition to a general database policy and practices of securing databases such as : Security architecture, Asset encryption ,Firewalls & Regular Audits , there is an option to apply Transparent Data Encryption (TDE) to a SQL Server database - aka "data at rest encryption". TDE is about securing data in the situation of backup device\drive theft or adhoc backup file protection. Setting up TDE in SQL Server is in summary Create a master key. Create or obtain a certificate protected by the master key. Create a database encryption key and protect it by using the certificate. Set the database... Read more →

Quick Fix: How to display hours minutes seconds between two dates

Question: I need to present the difference between two dates - returned by GETDATE() , which are using the datetime data type, as hours , minutes, seconds i.e hh:mm:ss , not as columns - but as one string . For example , if its 1 hr, 10 min, 15 seconds - than it should be : 01:10:15 . How can this be done? Answer: There's a few different ways you manage this result . For example - you could use the DATEDIFF method - comparing two different dates and returning the datepart defined between the two dates. In your case... Read more →

Use Get-ADPrincipalGroupMembership with other Active Directory domains

Question: I use the Powershell cmdlet Get-ADPrincipalGroupMembership to get AD group membership of IDs within the current domain, but how can I use Get-ADPrincipalGroupMembership to check the membership of an ID in another domain? Answer: Yes , The Get-ADPrincipalGroupMembership cmdlet will return the group membership of the specified user , in the current domain of the logon executing this cmdlet . To access the group membership of a specific ID , you'll need to use the -server switch . Example Get-ADPrincipalGroupMembership 'ID1' -Server 'anotherdoamin' | select name If you want to read on how to access the group membership of... Read more →

On-Demand Instant File Initialization status with sys.dm_server_services

Configuring a SQL Server with Instant file initialization is a fairly standard part of SQL Server Installation Checklists. There are substantial benefits to the duration taken during a SQL Server database restore. If you want to read more on how to set up instant file initialization and the technical benefits read SQL Server faster restores with instant file initialisation When I first started using instant file initialization I used to manually check using the gpedit.msc console - and checking the Windows Settings | Security settings | Local Policies | User Rights Assignments. Eventually I wrote a script to check as... Read more →

Error Message:The UPDATE statement conflicted with the REFERENCE constraint

During an UPDATE statement on a particular row on a SQL Server table , this error message appeared: Error Message:The UPDATE statement conflicted with the REFERENCE constraint "FK_My_Constraint". The conflict occured in database "MyDB",table "dbo.MyTable". The statement has been terminated The initial input value was incorrect , so I needed to update the value - but there was a foreign key constraint in place to retain integrity between the tables. The way to fix this issue is to temporarily disable the FOREIGN KEY constraint - change the values and the set the FOREIGN KEY constraint again. SQL Server has a... Read more →