How to enable SQL Server Service Broker

1) SQL Server Service Broker is enabled per database , so If you need to enable Service Broker on multiple databases , some sort of dynamic level sql is required , as there is no Server level command to activate all databases . 2) When enabling Service Broker on a database consider that if you recover and restore from a copy that does not have Service Broker enabled you'll need to have a process to enable Service Broker. 3) Attempt to complete the enabling of Service Broker at a non-critical time. Service Broker Activation requires an exclusive lock on the... Read more →

How to open Task Scheduler by cmd

Question: I'm on a Windows 2016 trying to open Windows Task Scheduler by cmdline - but it's not opening?Could you lete me know the cmd line reference for opening Windows Task Scheduler? Answer: To start Windows Task Scheduler on Windows 2016 use this cmd from the cmdline function: control schedtasks This will open a GUI similar to this one: Read More on Windows Administrative tasks Windows Server 2012 empty recycle bin (SQL Server DBA) Find Windows pagefile location through command line and How to write to a sql error to windows server event viewer (SQL ... Read more →

Troubleshooting SQL Server Database Mail not sending emails

Question: The Database Mail component is not send emails. The emails are registering – but remain in the “unsent” status. I’m running SQL Server 2016 . How can I troubleshoot this issue Answer: Here are some techniques to assist you in gathering information. I’ve experienced this problem a few times – always different reasons. --check database mail status sysmail_help_status_sp --delete mailitems DECLARE @GETDATE datetime SET @GETDATE = GETDATE(); EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @GETDATE; --stop start database mail EXEC msdb.dbo.sysmail_stop_sp; EXEC msdb.dbo.sysmail_start_sp; --check status EXECUTE dbo.sysmail_help_status_sp --check items SELECT * FROM sysmail_allitems One problem I’ve experienced is a missing .Net 3.5... Read more →

How to change SQL Server 2016 Database default locations for data and log files

The best way to set the database default locations for data and logs is when you first install the SQL Server Instance. They can be set through the configuration.ini file. ; Default directory for the Database Engine user databases. SQLUSERDBDIR="E:\MSSQLSERVER\MSSQL$MYINST\Data" ; Default directory for the Database Engine user database logs. SQLUSERDBLOGDIR="F:\MSSQLSERVER\MSSQL$MYINST\Data" But there are occasions where there’s a requirement to adjust the default locations after the SQL Server Instance is built. You can either write out the code , which requires references to xp_instance_regwrite or you can use the GUI to generate the code for you . This will save... Read more →

How to find SQL Server Instance Default Path and Instance Default Log Path

Finding the SQL Server Instance Default Path and the Instance Default Log Path in the old days (pre SQL Server 2012) was generally limited to either checking SSMS for the Instance Default Path or Log Path , and you could also use xp_regread to extract the information from the Registry. To read more about these options read SQL Server - Find Default Data location and Default Log Location .... For the last few versions of SQL Server similar information can be extracted using the SERVERPROPERTY functionality. This has introduced the benefit of being able to programmatically extract the details and... Read more →