Author: Latheesh NK

xp_servicecontrol in SQL Server

Problem Statement:

While I was automating a new server preparation step, one of my requirement is to make sure “MSDTC” service is up and running on the server. To enable this check, we can use an extended stored procedure – xp_servicecontrol. There are two parameters to this procedure as below.

It is important to understand that this extended procedure is an undocumented one which means, it cannot be used for a development purpose, this can be anytime removed from the product by Microsoft. However, this can be used as it warranted.

xp_servicecontrol @Action = N’Action’ @ServiceName = N’Service Name’

Action Description
start To start the service if it is not running. If the service is already in running state, an error is raised.
stop To stop the service if it is running. If the service is not running, an error is raised.
pause To pause a running service. An error is raised if the service is not running. Remember not all services can be paused.
continue To continue running a paused service. An error is raised if the serviced is not in paused state.
querystate To return the current state of the service.

Code:

declare @ServicesStatus table
 ( 
 Status varchar(50)
 )
    
 INSERT @ServicesStatus 
 EXEC xp_servicecontrol N'QUERYSTATE',N'msdtc'
    
 Declare @v varchar(max)
 select @v=status from @ServicesStatus
 select @v

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

How to Repair Database in Suspect Mode in SQL Server

Today, one of my colleague reached out to me that one of her database has gone into SUSPECT mode. She explained that while she was deploying the db changes, there was an unexpected shutdown happened on the server. While rebooting the server, it is observed the database has gone into SUSPECT mode.

Primary reasons when an SQL Server Database is marked in Suspect Mode

There may be many reasons for a db is getting into a SUSPECT mode. This is the worst case for any database can get as this would lead the database unusable.Here are few reasons I could think immediately, but there could be many other reason, feel free to add in the comment section if I miss anything.

    SQL Server Crash

    Database Files are inaccessible

    Improper shut down of SQL Server System

    Sudden Power Outage

    Hardware Failure etc.

Steps to Fix the SQL Server Database Suspect Mode Error

There are many articles on how to repair a SUSPECT db out there on a google search.

    Bring Database in EMERGENCY MODE

    Perform Consistency Check – DBCC CHECKDB

    Bring the Database in SINGLE_USER Mode with rollback immediate

    Take a Full Backup of the User Database

    Execute DBCC CHECKDB WITH REPAIR ALLOW DATA LOSS (Note: It might result in Data Loss)

    Bring the Database in MULTI_USER Mode for normal operations

Now, Let us look at our specific issue. Firstly, we tried to get this database in EMERGENCY mode with the below T-SQL.Unfortunately, we did end up with an exception given below which is clearly saying that this db has got a severe consistency issue which cannot be used further.
ALTER DATABASE dbname SET EMERGENCY
User does not have permission to alter database ‘dbname’, the database does not exist, or the database is not in a state that allows access checks.
How to drop SUSPECT database?

As we identified that this database has severely corrupted and it cannot be repair nor recovered, there is no way other than dropping the database. If you have backup of this database, you can always do restore from the latest valid backup (even up to the point in time). But unfortunately, in our case, it will not even allow you to drop and it would end up with the above same error message for DROP as well.

So, how do we drop this db? Its bit tricky and needs some outage of SQL Server (since one of the step is stopping the service).
    Stop the SQL Server service.

    Rename the file (MDF or LDF or both) of this database.

    Start the SQL Server service.

    Drop the database.

Caveat: The above method is recommended on non- production environments generally.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

ISUNIQUEIDENTIFIER function in SQL Server

Today, I had a requirement to check a value can be converted as uniqueidentifier datatype or not, so quickly come up with the below function. Hope this would help you if you come across similar requirement in future.

Code:

CREATE FUNCTION DBO.ISUNIQUEIDENTIFIER
(
@InputVal nvarchar(50)
)  
RETURNS SmallInt AS  
BEGIN

RETURN 
	CASE WHEN SERVERPROPERTY('PRODUCTMAJORVERSION') >=11 THEN
		CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER,@InputVal) IS NOT NULL THEN 1 ELSE 0 END
	ELSE (
		CASE WHEN LEN(@InputVal) = 36 AND @InputVal LIKE
		   '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+ '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]'
		+  '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' THEN 1 ELSE 0 END ) 
	END
END

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Some thoughts on working from home and remote work

“Work from Home” – A new IT norm in India. Before I jot down my views here, let me clarify, its my personal views, you may or may not agree to all, but good if you can share your thoughts in the comments section.

So, a new norm, Initially, I was not thinking Indian IT would adopt this so fast we are now for various reasons like intermittent network issues, unavailability of network at remote places and other logistics. But, CORONA the real game changer made it all happen so quickly. I would personally think we travelled at least 5 years ahead than we could have been if the pandemic had not been come into our life. Anyway, few things are positives, but lots of negatives like our normal life has impacted a lot like street shop businesses (one of the major business in India), hospitality, travel etc. A mood of despondency had set in many people. However, am taken aback to see our ability and tenacity to fight back with CORONA as well as the challenges due to that. As we started working from home, we also had lots of changes in our life styles. Now, there are few things, which I wanted to share with you all, very important for all of us, thought its not late yet. Here we go…

1. Our posture is our future
It is important to keep a good posture at work. People tend to take their comfort with different method, sitting on bed, lying down or any other awkward ways. But, it is very important that we should follow the right postures for a long run. Desk ergonomic is important to be followed as this work from home extends to more months.
https://www.computer-posture.co.uk/good-computer-posture/

2. Exercise to live healthy
“Good things come to those who sweat.” – It has everything. You may not be able to go to your regular gym, but make sure you continue your walk.

Here are few suugestions:

Yoga
Squats
Jogging on the spot
Plank
Push ups
Skipping rope

https://thewaryworker.com/work-from-home-exercises/

3. Right food at right time
Being at home, the big negative is our food intake. We work along with family, they would supply lots of food to make us energetic, but be aware, those are not really good for our health. Have food for our thoughts not for body (too much). Try to shift from junk to healthy. Avoid dry snacks/chips etc. Staying hydrated is equally important having food at time.

https://medicalxpress.com/news/2020-04-lockdown-opportunity-food-habits-good.html

4. Shift ear-in headphones to ear-on/over headphones
Important guys, I am a victim of it. Switch immediately from ear-in to ear-on headphones. My doctor advised me to do this shift for everyone including kids. Now, kids have online classes and they spend more time with head phones. If you still use ear-in then change to ear-on ones. The later causes lots of fungal issues in your ears and it will lead to a very bad pain in your ears. So, take swift action!

https://www.hearing-speechcenter.com/ear-pain-earphones/

5. Take leaves and clean leaves around you
Now days, no celebrations/marriage functions etc. So you will have many leaves in credit. I would recommend to take the leaves to spend with your family. I used to go to my backyard with my family and clean the area on those days. Good for health and thoughts. Its always good to stick to the office time as you were in office. We may tend to extend our office hours naturally, but we need to be aware when to close the business hour and involve with rest of the world. 🙂

Hope this helps you also, share your thoughts. Stay home stay healthy!

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Calendar Table in SQL Server

What is Calendar Table in SQL Server ?

Calendar Table is a custom/user table in SQL Server created for ease of users coding. At times, it is observed many programmers are struggling to write date/calendar queries. Also, it is observed online processing of these functionalities has its own performance impact. As mentioned, Calendar table would come in time to save programmers as those are pre populated and quite easy to use as well.

Calendar Table Code

Drop Table if exists Calendar;

Create Table Calendar 
(
	Date Date Primary Key, DayNo TinyInt, MonthNo TinyInt, YearNo SmallInt
	, WeekNo TinyInt, ISOWeekNo TinyInt,WeekDayNo TinyInt
	, DayName Varchar(10), MonthName Varchar(10), QuaterNo TinyInt
	, DayOfYearNo SmallInt, IsWeekEnd TinyInt
	, IsHoliday TinyInt, HolidayDescription nVarchar(500)
)

;with cteSeed  as ( Select 0 Seed union all Select Seed +1 From cteSeed where Seed <=100000)
,     cteDateDimenion as ( Select dateadd(Day,Seed,cast('1990-01-01' as date) ) Date from cteSeed)
,     cteCalendar as (
		Select 
		         Date,Day(Date)DayNo,Month(Date) MonthNo, Year(Date) YearNo 
			,DAtePArt(Week,Date) WeeKNo, DATEPART(ISO_WEEK,Date) ISOWeekNo,DATEPART(WEEKDAY,Date) WeekDayNo
			,DAteName(WEEKDAY,Date) DayName, DAteName(Month,Date) MonthName
			,DATEPART(Quarter,date) QuaterNo, DATEPART(DAYOFYEAR, date) DayOfYearNo
			,Case when DAteName(WEEKDAY,Date) in ('Saturday','Sunday') Then 1 Else 0 End 'IsWeekEnd'
			,Case When Day(Date)=1 and Month(Date)=1 Then '|New Year Day|' 
					When Day(Date)=25 and Month(Date)=12 Then '|Christmas Day|' 
					Else '' End  
			+ Case when DAteName(WEEKDAY,Date) in ('Saturday','Sunday') Then '|' 
			+  DAteName(WEEKDAY,Date) + '|' Else '' End as HoliDayDescription
		From cteDateDimenion )
Insert into Calendar (Date,DayNo,MonthNo,YearNo,WeekNo,ISOWeekNo,WeekDAyNo
,DayName,MonthName,QuaterNo,DayOfYearNo,IsWeekEnd,IsHoliday,HolidayDescription)
Select Date,DayNo,MonthNo,YearNo,WeekNo,ISOWeekNo,WeekDAyNo
,DayName,MonthName,QuaterNo,DayOfYearNo,IsWeekEnd,NULL,HolidayDescription From cteCalendar
OPTION (MAXRECURSION 0);

Update Calendar Set IsHoliday = Case when Len(HoliDayDescription) > 0 Then 1 Else 0 End 

Select * From Calendar

Assumptions:

1. The week day starts from Sunday.

2. Holiday list is specific to country, so the code above is as an example, feel free to add your specific holidays.

Use Cases:

How do you calculate business/working days between two dates?

Select count(1) as 'No. of Business Days' From Calendar 
where Date between '2020-01-01' and '2020-12-31' and IsHoliday != 1

How do I get Day name of first day of given month and year?

Select Date,DayName FRom Calendar where YearNo=2020 and MonthNo =11 and DayNo=1

I am sure, there many other use cases it would be helpful in real time, keep explore and feel free to make modification as required.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!