Number of words in a string

In SQL Server there is not direct function to calculate the number of words in a string. 
But we can calculate the number of words in following way:

Option 1 :
CREATE FUNCTION dbo.UDFWordCount(@Text VARCHAR(8000))
RETURNS int
as
/*
SELECT dbo.UDFWordCount ('hello   world')
*/
BEGIN
     
    DECLARE @iCtr int, @iCtx INT, @Words int
    SELECT @iCtr = 1, @Words = 0 

    WHILE @iCtr <= DATALENGTH(@Text)
    BEGIN
        SELECT @iCtx = CHARINDEX(' ', @Text, @iCtr)
        if @iCtx = 0
        BEGIN
            SELECT @iCtx = DATALENGTH(@Text) + 1
        END
        IF SUBSTRING(@Text, @iCtr, @iCtx - @iCtr) <> ' '
              SELECT @Words = @Words + 1 
        SELECT @iCtr = @iCtx + 1
    END
    RETURN(@Words)
END
GO

SELECT dbo.UDFWordCount ('Microsoft SQL Server2008')
SELECT dbo.UDFWordCount ('Microsoft  SQL  Server  2008')

Option 2 :
DECLARE @strTemp VARCHAR(4000)
SELECT @strTemp = 'SQL Server 2005'
SELECT LEN(@strTemp) - LEN(REPLACE(@strTemp, ' ', '')) + 1

But in this option if word are separated by a single space than it will give you the correct result, If there is more than single space between words than it will give you  wrong result.

Query to find the Index with size

Use below query to find the Index with size in a table:

SELECT
OBJECT_Name(I.OBJECT_ID) AS TableName,
I.index_ID AS IndexID,
I.Name AS IndexName,
8 * SUM(A.Used_Pages)/1024 AS 'IndexSize in KB'
FROM sys.Indexes AS I
JOIN sys.Partitions AS p ON p.OBJECT_ID = I.OBJECT_ID AND p.index_ID = I.index_ID
JOIN sys.Allocation_Units AS A ON A.Container_ID = P.Partition_ID
WHERE I.Object_ID = Object_ID ('<Table_Name>')
GROUP BY I.OBJECT_ID,I.index_ID,I.Name

Replace <Table_Name> with Table Name

SQL Server could not spawn FRunCM thread error

Today I had to change the domain account on some Server. After I had changed the account the SQL server was not starting and showing below error:

Server Error: 17190, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server Initializing the FallBack certificate failed with error code: 1, state: 1, error number: -2146893802.

2014-07-17 11:42:06.11 Server Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.

2014-07-17 11:42:06.11 Server Error: 17182, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server TDSSNIClient initialization failed with error 0×80092004, status code 0×80. Reason: Unable to initialize SSL support. Cannot find object or property.

2014-07-17 11:42:06.11 Server Error: 17182, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server TDSSNIClient initialization failed with error 0×80092004, status code 0×1. Reason: Initialization failed with an infrastructure error. Check for previous errors. Cannot find object or property.

2014-07-17 11:42:06.11 Server Error: 17826, Severity: 18, State: 3.

2014-07-17 11:42:06.11 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.

2014-07-17 11:42:06.11 Server Error: 17120, Severity: 16, State: 1.

2014-07-17 11:42:06.11 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

After trying all trial and run I notice that when logging on with existing profile on the box there was an error. Something about that the profile couldn't be created and a temporary profile would be used instead.

In the end I had to delete the user profile and than start SQL Server and now SQL server is started properly.

I had followed the below steps:

Right click on computer, select Properties
Click on Advanced System Settings
Select the Advanced Tab
On the user profiles section click on Settings

Select the profile and hit Delete

If you try to log in as that user, the profile will be recreated.

Now when I tried to start SQL Server again, the errors were not there and SQL Server started up without a problem.

Script to find the No of Reads / Writes on Database files

Use below script to find the No of Reads / Writes on Database files

SELECT DB_Name(MF.Database_ID) AS [DB Name],
       MF.Physical_Name AS [Physical File Name],
       Num_Of_Bytes_Read,
       Num_Of_Reads,
       IO_Stall_Read_MS,
       Num_Of_Bytes_Written,
       Num_Of_Writes,
       IO_Stall_Write_MS,
       IO_Stall,
       Size_On_Disk_Bytes
FROM sys.dm_IO_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS MF ON MF.Database_ID = divfs.Database_ID
AND MF.File_ID = divfs.File_ID
                                  

Script to track Database Growth

Use below script to track database growth over a period of time:

WITH C AS (
SELECT 
RN = ROW_NUMBER() OVER( ORDER BY Backup_Start_Date DESC),
BackupDate = CONVERT(VARCHAR(10), Backup_Start_Date, 111), 
SizeInMB = FLOOR(Backup_Size/1024000) 
FROM msdb.dbo.BackupSet 
WHERE Database_Name = DB_NAME() -- put your databaseName here
AND Type = 'd'
) SELECT C.* , Diff = SizeInMB - (SELECT SizeInMB FROM C C2 WHERE RN = C.RN + 1) FROM C ORDER BY 1 ASC