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 →

How to write a conditional substring in sql

Question: I have a SELECT statement returning a recordset . Before presenting the recordset , I want to strip out some of the substrings within certain records. This is an example of the recordset Brazil Brazil.com Argentina.net Bolivia The requirement is to strip out any part of the string from the dot and after. For example, “brazil.com” will become “brazil”. The problem I have currently is by running the SUBSTRING function across all the rows, if there is no dot in the row , the rown will return as blank. The only rows returning are the ones with a dot!... Read more →

SQL Server Stuff and comma delimited column

Question: I have two tables and would like to return a recordset which is made up of table1 plus an extra column which is made up of a comma-delimited string from table2 based on an INNER JOIN. I’ve read the comma delimited article , but this returns the full recordset as a CSV.SQL Server – Create a comma delimited sql result set (SQL Server ... Answer: One option to use is the SQL Server STUFF function.The SQL Server STUFF function is used to insert one string into another string. This makes it useful for what you’re attempting to achieve. You... Read more →

SQL Server uptime query

Question: Is there a reliable method to extract the sql server uptime? I’ve tried different methods , such as xp_readerrorlog , but I discovered a limitation when the error log is recycled , updated date & time information is added to the error log , which is the time \ date of the error log recycle , but not the sql server instance restart. Answer: There are two methods I use to for a sql server uptime query. Method 1 extracts the sql server start time from the sys.dm_os_sys_info DMV. Method 2 utilises the tempdb create date , as the... Read more →

Helpful sql commands to manage SQL Agent Jobs

Quite often you may not be available 24 x 7 , and need to delegate some actions for SQL Agent jobs to developers or Operations staff. The typical range of tasks in managing SQL Agent tasks can vary , but this list normally covers the range for the accidental | reluctant individual | DBA who needs to manage SQL Agent Jobs using t-sql --start the job manually --stop the job manually --check if the job is running i.e if it's not in the list it's not running --disable job --enable job --check the job last run time & finish time... Read more →