DECLARE @SQLServiceRestartDateTime AS VARCHAR(50), @ServerRestartDateTime AS VARCHAR(50) SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2 SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]
Wednesday, May 25, 2022
T-SQL to find Server and SQL restart date time
This script comes handy when you would like to find out the Server and SQL services restart times.
Friday, May 20, 2022
T-SQL to find Backup or Restore Progress
Here is a script that comes handy while performing a huge database Backup or Restore. This script provides the details on the progress of the Backup or Restore operation including the estimated finish time.
SELECT session_id AS SPID, command AS [Command], a.text AS Query, start_time AS [Start Time], percent_complete AS [Percent Complete], dateadd(second,estimated_completion_time/1000, getdate()) AS [Estimated Completion Time] FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a WHERE r.command like 'BACKUP%' OR r.command like 'RESTORE%')
Labels:
Backup,
Backup and Restore,
MSSQL,
Restore,
SQL Server,
SQL Server 2005,
SQL Server 2008,
SQL Server 2008 R2,
SQL Server 2011,
SQL Server 2012,
SQL SERVER 2014,
SQL Server 2016,
sql server 2019,
SQL Server Agent
Monday, May 16, 2022
T-SQL to find Navigation path in CMS
In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.
DECLARE @ServerName nvarchar(50) SET @ServerName='SQL01'; WITH CMSCTE AS ( --Anchor SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath FROM msdb.dbo.sysmanagement_shared_server_groups AS A WHERE parent_id IS NULL UNION ALL --Recursive Member SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath FROM msdb.dbo.sysmanagement_shared_server_groups AS B JOIN CMSCTE AS C ON B.parent_id = C.server_group_id ) SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description' FROM CMSCTE AS A INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B ON A.server_group_id=B.server_group_id WHERE B.name = @ServerName ORDER BY [Level] DESC
Labels:
Central Management Server,
CMS,
MSSQL,
SQL Server,
SQL Server 2012,
SQL Server 2016,
sql server 2019,
T-SQL
Friday, April 22, 2022
T-SQL to find Remote Server Name of the linked server
At times it happens that you might have named a linked server with a friendly name that the actual server name. As the time flies and people change in the team, it will become difficult to identify how or to where this is linked. You can find the information using the sp_linkedservers stored procedure but what if the linked server configured uses a entirely different DNS name than the actual server? The below query helps to get the Remote Server Name with few other details when run against a linked server.
DECLARE @linkedServerName varchar(100), @sql varchar(max) SET @linkedServerName='Your Linked Server Name' SET @sql = CONCAT('SELECT * FROM OPENQUERY([',@linkedServerName , '], ''SELECT ''''',@linkedServerName,''''' AS LinkedServerName, @@SERVERNAME AS RemoteServerName, SUSER_SNAME() AS ConnectedWith, DB_NAME() AS DefaultDB'')') EXEC (@sql)

Thursday, February 20, 2020
Disable Auto-Forwarding of e-Mails to external domain in Office 365
e-Mails are one of the most critical components and infrastructure of any business in the current day and age. e-Mails can bring in business as well as loss if misused. A lot of data theft can happens via e-Mails and it becomes most important to safegaurd the e-Mail infrastructure.
If a hacker gains access to a user's mailbox, they can auto-forward the user's email to an outside address and steal proprietary information.
One such step in safegaurding the data that leaks via emails is to disable the Auto-Forwarding of e-Mails to external domains.
To Disable Auto-Forwarding of e-Mails to External Domains in Office 365, Create a Transport Rule as below
If a hacker gains access to a user's mailbox, they can auto-forward the user's email to an outside address and steal proprietary information.
One such step in safegaurding the data that leaks via emails is to disable the Auto-Forwarding of e-Mails to external domains.
To Disable Auto-Forwarding of e-Mails to External Domains in Office 365, Create a Transport Rule as below
- From the Microsoft 365 admin center, Select Exchange --> Mail Flow --> Select the Rules tab --> Click on the plus sign and Choose "Create a new rule"
- Name your new rule and Select More options
- Click on the drop-down for "Apply this rule if", Select the "Recipient", and then "is external/internal"
- Select "Outside the organization" and click OK
- Click "add condition" and Click on the drop-down, Select "The message properties", then select "include the message type"
- Open the select message type drop-down, choose "Auto-forward" and click OK.
- Open the "Do the following" drop-down, select "Block the message", then choose "reject the message and include an explanation"
- Enter the message text for your explanation that you want to show to your users, then select OK.
- Scroll to the bottom and select Save
This newly created Transport Rule will now start blocking the Auto-Forwarding of e-Mails to external domains.
Subscribe to:
Posts (Atom)