SQL SERVER: Database monitoring items checklists in SQL Server

Details

Written by Ranjeet

From Long time back, I was planning to summarize all the monitoring items/checklist at one place to help DBA’s who need to setup in their environment. Every database environment must have all kind of required monitoring items configured and also related TSG must be updated. In this article I will try to list out all those monitoring items/checklists. Follow my subsequent articles to have every item’s description and get full scripts. Let me categories the items/checklists. It’s totally database administration based not any application based.

Database Related

 1.       File group Monitoring

 2.       Tempdb Monitoring

 3.       Data file Monitoring

 4.       Log file Monitoring

 5.       Blocking Monitoring

Register to read more...

0 Comments

SSMS: Creating an instance of the COM component with the following error: c001f011

Details

Written by Ranjeet

Error: 

Creating an instance of the COM component with CLSID {AA40D1D6-CAEF-4A56-B9BB-D0D3DC976BA2} from the iClassFactory failed due to the following error: c001f011..

Today, when I created a job and then tried to add one more step in the job. I encountered the above error. I browse the internet I saw Microsoft suggesting for one cumulative update. Other suggesting for check for the dts.dll location and register it.

Anyway, I didn’t do any R&D at this moment. Just closed the SSMS and restarted it and problem went away. Let see if it come again then will surely do some research.

0 Comments

SQL SERVER: Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database

Details

Written by Ranjeet

Error in Event Log: 

Report Server Windows Service (MSSQLSERVER) cannot connect to the report server database.

Recently, I encountered with above error in event viewer. This error has been occurring from couple of months back. When first time I saw, I thought it must be due to some SQL server side problem or might be due to some heavy operation going on at that particular time. The best part is this error was happening at specific time only every day.

I started troubleshooting from SQL server level. I couldn’t see any heavy workload going on which can stop any new connection coming in. Then I browse online content and saw people are suggesting to change like

• Change AD service authentication to local
• Give specific permission
• Etc

Register to read more...

0 Comments

T-SQL: How to create and test deadlock in SQL server

Details

Written by Ranjeet

When we write code to get alert of deadlock occurs on the server. It is very important to test it. I have written small example as below which can create deadlock situation and then deadlock scenario can be tested/verified.

1. Create two tables

create table rr(roll int, name varchar(50))

create table tt(roll int, name varchar(50))

2. Populating both the tables

insert into rr(roll, name) values(1, 'SQL Server Base rr')

insert into tt(roll, name) values(2, 'SQL Server Base tt')

Register to read more...

0 Comments

T-SQL: How to create and test blocking in SQL server

Details

Written by Ranjeet

When we write code to get alert of blocking. It is very important to test it. I have written small example as below which can cause blocking and blocking alert can be tested.

1. Populate a test table first as below with scrip

CREATETABLE RR(ROLL INT, NAME VARCHAR(50))

-->> Populate table with few sample data
declare @i int

set @i=1
while @i< 10

begin

      insertinto rr(roll, name)values(@i,'SQL Server Base'  +CAST(@i asCHAR(100)))

      set @i=@i+1

end

Register to read more...

0 Comments

T-SQL: Convert milliseconds into Hour Minutes and Seconds in SQL Server

Details

Written by Ranjeet

While writing some complex query we often need to convert milliseconds column into hour minutes and seconds for better readability in report. Below is a sample example query to full fill same.

declare @Milliseconds int

set @Milliseconds=1051406

select convert(varchar,(@Milliseconds/(1000))/(60*60)) + 'H' + convert(varchar,(@Milliseconds/(1000))/60) + 'M ' + convert(varchar,(@Milliseconds/(1000))%60) + 'S' 

0 Comments

POWERSHELL: How to get the parameters details of any cmdlet in PowerShell

Details

Written by Ranjeet

We know how to run a cmdlet on powershell command prompt. To get the specific details from cmdlet, we need to know all the parameter options available for that particular cmdlet. This article is just to know what the help cmdlet available to get these details. We can use below format to get the details. Here in this example I am trying to list the available parameters in Get-Date cmdlet. Will also show some other options to get similar details. 

1. Use dot parameters option of Get-Help cmdlet

PS C:\Users\ranjeet> (Get-Help Get-Date).parameters

Register to read more...

1 Comment

POWERSHELL: More Vs –Paging in PowerShell

Details

Written by Ranjeet

More is an alias of cmdlet Out-Host –paging whereas –paging is a parameter of Out-Host cmdlet which restrict the output to display per page. You can press space key to go to next page and “Q” key to quit the page. Below is the syntax and output sample.

-Paging

PS C:\Users\ranjeet> Get-Command | Out-Host -Paging

More

PS C:\Users\ranjeet> Get-Command | more

0 Comments

POWERSHELL: Write-Output Vs Write-Host in PowerShell

Details

Written by Ranjeet

As we know, powershell cmdlet command has been managed in Verb-Noun format and it works on object concept rather on text based. Here is this article Write-Host and Write-Output both belongs to same verb family but noun is different. So, one thing is sure that both are serving same thing but for different purpose.

Write-Output: As noun “output” looks it works to display the result to console. It’s true but only if Write-Output cmdlet comes as last command. Like PS:\>Get-Command or PS:\>Get-Command | Write-Output is the same thing. So, this cmdlet is not used to display the output for sure. This cmdlet actually pass the object to next command in pipeline hierarchy. It is also known as “output stream” or “Success Pipeline”. Below is an example:

PS C:\Users\ranjeet> Get-Childitem | Write-Host -ForegroundColor yellow -BackgroundColor red | Out-Host -Paging

Register to read more...

0 Comments

Database: Can we update resource database in sql server

Details

Written by Ranjeet

Can we update resource database? Answer No. Resource database is system database that is not visible in SSMS. It is a read only system database which contains all the system related objects. All the system objects which stores here in resource database is logically visible in the entire database as sys. Schema objects. It is not editable and even it doesn’t store any user data or any metadata related to user objects or databases.

We can backup and restore the resource database. There is fixed drive location where its datafile is being store. Its default path is: C:\Program Files\MicrosoftSQLServer\MSSQL\<sqlversion>.<instancename>\MSSQL\Binn 

Get information about resource database:

1. Can query SERVERPROPERTY(‘ResourceVersion’)
2. Also query SERVERPROPERTY(‘ResourceLastUpdateDateTime’)

1 Comment