The service was started and then stopped

Sql server agent service issue: My junior DBA met me on an interesting issue. Sql service account is not able to start the Sql server agent whereas Sql server service is running. When he tries to start sql agent service then he gets following issue:
“The request failed or the service didn’t respond in a timely fashion. Consult the event log or other application error logs for details.”
“The service was started and then stopped”
Let’s look at the errorlogs from I:\MSSQL10.MSSQLSERVER\MSSQL\LOG. Following error is observed:
Here is the actual problem: “The service account phx\_ctpfort doesn't have perms to start Eventlog service.”
Lets look at the permsisions:

1. 1. Run -> cmd -> rsop.msc and press enter.
2. 2. Click on Windows settings.
3. 3. Click on System services.
4. 4. Click the properties on Windows event log.
5. 5. Click on View security to look for the service account under which the sql service is running.

We need to have it added there. Once added, the problem was resolved.
Please share your feedback and let me know if the blog was useful.

SCOM Powershell Queries

Recently, I was thrown in a drill where SCOM health needs to be verified.

"What is SCOM": SCOM is a server/client model that monitors the server health. Server heath can be categorized on multiple criteria. These criteria could be written in MP
(management pack) and then the servers are monitored on the basis of these parameters.
It would have been impossible for me to monitor 800+ servers without SCOM powershell commands.

Following are some commands where servers could be categorized in three
different states. The powershell command is also written against each and every category.

1. # of Servers where SCOM HeartBeat is Healthy get-scomagent | Where-Object {$_.healthstate -eq "success"} | Measure
2. # of Servers where SCOM Heartbeat has Issues get-scomagent | Where-Object {$_.healthstate -eq "uninitialized"} | Measure
3. # of Servers which are not healthy in SCOM get-scomagent | Where-Object {$_.healthstate -eq "Error"} | Measure

Please share your feedback which help me improve my articles.

Script of Getting OS Version using Powershell

Powershell makes it easy to get information from remote servers.

Below is the query to find OS Version of all the remote servers you are connected with-

Step1- Create the list of all the servers in a notepad with name "serverlist.txt". I have saved this file on location "D:\Powershell" you can change it with your own location.

Step2- Execute below script-

PS C:\Windows\system32>$serverlist = Get-Content "D:\Powershell\serverlist.txt"

PS C:\Windows\system32>foreach($server in $serverlist)

{

$serverversion = Get-WmiObject -computer $server -cl Win32_OperatingSystem

write-output $server, $serverversion.version | out-file "D:\Powershell\OSVersion.txt" -append

}

You can also save the script in a notepad and save it with extension ".ps1". When you will execute the file OS Version for all the servers in the server list will store in the OSVersion file.

Regards,
http://tuitionaffordable.webstarts.com

Error 1067: The cluster service is stopped on passive node of my two node sql cluster. It throws following error when I try to start this:

This is found that the cluster service in one of the passive stops. This throws following error when I try to start this.

Error 1067: The process terminated unexpectedly

I verified the password under which this cluster service is running. This account is perfect. So there should be some other problem.

Let's go ahead and see the problem in the cluster.log file.

Info from %WINDIR%\Cluster\cluster.log is as follows:

WARN [EVT] EvtBroadcaster: EvPropEvents for node 2 failed. status 1727
WARN [NM] RpcExtErrorInfo: Error info not found.

Error from clusterlog

Error: Failed to read (sector 12), error 170. and Failed to write (sector 12), error 170.


1. started from cmd. net start clussvc
2. The Windows Firewall/Internet Connection Sharing service should neither be enabled nor set to start automatically.

FmGetQuorumResource failed, error 170. Failed to read. Failed to form cluster, status 5086.

Follwoing was the problem:

The Admin NIC threw an error. This caused the netowrk connectionos in the cluster disabled. I disabled and enabled the NIC and this started working.

Regards,
tuitionaffordable

Sql Server Error 8951 Data row does not have a matching index row in the index

The error 8951 emerges most often when there is a duplicate row exists on PK. You would not be able to find the duplicate row

till you drop the PK and try as follows:

Let's Understand: There is a simple table EmployeeInfo where the PK is created on Empid, Rid, KeyInfo, DateTimeSlot columns. There are duplicate rows in the table for the columns on which PK is established but following query throws only one value for the following query even though two rows exist:

select Empid, Rid, KeyInfo, DateTimeSlot, count(1)
from Tuitionaffordable..EmployeeInfo
GROUP BY Empid, Rid, KeyInfo, DateTimeSlot
HAVING COUNT(1) > 1

Let's drop the PK and run the above query again. This shows me duplicate values for the columns on which PK is created. You need to filter the bad data out or modify these rows to avoid this info.

Let's again restore the same DB on a Test server and use following command for any specific value where this had duplicate rows:

select Empid, Rid, KeyInfo, DateTimeSlot, count(1)
from Tuitionaffordable..EmployeeInfo
where LTRIM(RTRIM(Empid)) = 97
and LTRIM(RTRIM(Rid))=2
and KeyInfo = 'ACAF387865567973E43C3ADB96C'
and DateTimeSlot= '2011-01-29 00:00:00.000'

This doesn't show the duplicate rows but following query will show you the data

select Empid, Rid, KeyInfo, DateTimeSlot,count(bit_delete)
from Tuitionaffordable..EmployeeInfo
where LTRIM(RTRIM(Empid)) = 97
and LTRIM(RTRIM(Rid))=2
and LTRIM(RTRIM(KeyInfo)) = 'ACAF31DBC69556EA9A973E43C3ADB96C'
and DateTimeSlot= '2011-01-29 00:00:00.000'
GROUP BY Empid, Rid, KeyInfo, DateTimeSlot

Following is the data:



Empid Rid KeyInfo DateTimeSlot (No column name)
97 2 ACAF387865567973E43C3ADB96C 2011-01-29 00:00:00.000 1
97 2 ACAF387865567973E43C3ADB96C 2011-01-29 00:00:00.000 1

The problem is in your DateTimeSlot column. drop the PK and this will catch where your duplicate data is.

Regards,
http://tuitionaffordable.webstarts.com

Restoring the stripped backup Files

The blog can give you good understanding on "MSSQLServer stripped backup".


Why anyone wants to go to stripped backups?
The answer is that when you have your drives on SAN and you want to use the I\O

from each more than one SAN while taking the backup then this is a good option to go for the stripped backups. Another point is that in some scenario the free space in one drive is not enough to accomodate your complete backup file.

After you are clear on why you decide stripping the backup file, lets talk on how to stripe the backupfile.

Command to create the stripped backups

BACKUP  TO
DISK = 'E:\MSSQL\BAK\Tuitionaffordable_1of2.BAK'
,DISK = 'J:\MSSQL\BAK\Tuitionaffordable_2of2.BAK'
WITH  INIT

Let's go ahead and restore the command
RESTORE   DATABASE Tuitionaffordable
WITH   RECOVERY, REPLACE



FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK'
,DISK = 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK'
,DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK'

There is one more important thing. In case your destination server doesn't have enouth free space in the corresponding drives then above command will fail. Backup file has the information where it needs to create the mdf, ndf and ldf files. Let's take a scenario when you are going to MOVE all the files to only one drive eg K because you have enough free space only in K drive of your destination server. The command will be as follows:

RESTORE DATABASE Tuitionaffordable
FROM DISK= 'K:\MSSQL\Test\Tuitionaffordable_1of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_2of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_3of4.BAK',
DISK= 'K:\MSSQL\Test\Tuitionaffordable_4of4.BAK',
WITH RECOVERY, REPLACE  

MOVE 'Tuitionaffordable' TO 'K:\MSSQL\Test\Tuitionaffordable.MDF',
MOVE 'Tuitionaffordable_Data2' TO 'K:\MSSQL\Test\Tuitionaffordable_Data2.NDF',
MOVE 'Tuitionaffordable_Data3' TO 'K:\MSSQL\Test\Tuitionaffordable_Data3.NDF',
MOVE 'Tuitionaffordable_Data4' TO 'K:\MSSQL\Test\Tuitionaffordable_Data4.NDF',
MOVE 'Tuitionaffordable_Data5' TO 'K:\MSSQL\Test\Tuitionaffordable_Data5.NDF',
MOVE 'Tuitionaffordable_Data6' TO 'K:\MSSQL\Test\Tuitionaffordable_Data6.NDF',
MOVE 'Tuitionaffordable_Data7' TO 'K:\MSSQL\Test\Tuitionaffordable_Data7.NDF',
MOVE 'Tuitionaffordable_Data8' TO 'K:\MSSQL\Test\Tuitionaffordable_Data8.NDF',
MOVE 'Tuitionaffordable_Data9' TO 'K:\MSSQL\Test\Tuitionaffordable_Data9.NDF',
MOVE 'Tuitionaffordable_Index' TO 'K:\MSSQL\Test\Tuitionaffordable_Index.NDF',
MOVE 'Tuitionaffordable_Index2' TO 'K:\MSSQL\Test\Tuitionaffordable_Index2.NDF',
MOVE 'Tuitionaffordable_Log' TO 'K:\MSSQL\Test\Tuitionaffordable_Log.lDF'

Regards,
http://tuitionaffordable.webstarts.com

Logshipping Error: "Skipping log backup file" and "Could not apply log backup file"

This was a bad day for log shipping. I was on a leave for last two days and got my junior DBA screeming over logshipping latency as soon I stepped in.

DBA got many skip messages in logshipping as follows:

2011-01-24 21:50:11.17 Skipping log backup file 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn' for secondary database 'Tuitionaffordable' because the file could not be verified.

Another interesting error was as follows:

2011-01-24 21:50:10.92 *** Error: Could not apply log backup file 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn' to secondary database 'Tuitionaffordable'.(Microsoft.SqlServer.Management.LogShipping) ***
2011-01-24 21:50:10.92 *** Error: Cannot open backup device 'j:\logshipping\Tuitionaffordable\Tuitionaffordable_20110123100410.trn'. Operating system error 32(The process cannot access the file because it is being used by another process.).
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Let me discuss about these errors one by one.

Error 1 indicates that some transactional log file is missed in between. It just means that log chain is broken and you need to copy those logs manually to the secondary server.


Error 2 indicates that the log file is corrupt. Let's run following command to verify the log file.

RESTORE VERIFYONLY
FROM DISK = 'J:\logshipping\Tuitionaffordable\bak\Tuitionaffordable_20110123100410.trn'

Msg 3203, Level 16, State 1, Line 1
Read on "J:\logshipping\Tuitionaffordable\bak\Tuitionaffordable_20110123100410.trn" failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

And this told that your log file is invalid. This happens when you use robocopy or xcopy to transfer file manually and your session is logged off. So get those files again from primary DB manually. I prefer to create a job and run it so that session logoff doesn't cause any problem.

Most of the readers of this blog also read following interview questions so linking them here:
Powershell Interview Questions and Answers
SQL Server DBA “Interview Questions And Answers”
SQL Azure Interview Questions and Answers Part - 1



Regards,
http://tuitionaffordable.webstarts.com