Activity Monitor in SQL Server 2008 Administration

The Activity Monitor gives us a  view of current connections on an instance. The monitor can be used to determine whether we have any processes blocking other processes. To open the Activity Monitor in Management Studio, right click on the Server in the Object Explorer, then select Activity Monitor.






1. Session ID: The unique number assigned to a process connected to SQL Server. This is also called a SPID. An icon next to the number represents what is happening in the connection. If you see an hourglass, you can quickly tell that the process is waiting on or is being blocked by another connection.



2. User Process Flag: Indicates whether processes that are internal SQL Server processes are connected. These processes are filtered out by default. You can change the value to see the SQL Server internal processes by clicking the drop down and selecting the appropriate value.



3. Login: The login to which the process is tied.



4. Database: The current database context for the connection.



5. Task State: Indicates whether the user is active or sleeping. (No, this doesn’t mean the user is active or sleeping at his or her keyboard; it means that SQL Server keeps the connection active even though no activity is coming from the login until the user disconnects.)



6. Done: Completed.



7. Pending: The process is waiting for a worker thread.



8. Runnable: The process has previously been active, has a connection, but has no work to do.



9. Running: The process is currently performing work.



10. Suspended: The process has work to do, but it has been stopped. Additional information about why the process is suspended may be found in the Wait Type column.



11. Command: Shows the type of command currently being executed. For example, you may see SELECT, DBCC, INSERT, or AWAITING COMMAND here, to name a few. This won’t show you the actual query that the user is executing, but it does highlight what type of activity is being run on your server. To see the actual command, select a row in this table, right click and choose Details.



12. Application: The application that is connecting to your instance. This can be set by the developer in the connection string.



13. Wait Time (ms): If the process is being blocked or waiting for another process to complete, this indicates how long the process has been waiting, in milliseconds; it will have a value of 0 if the process is not waiting.



14. Wait Type: Indicates the event you are waiting on.



15. Wait Resource: The text representation of the resource you are waiting on.



16. Blocked By: The Session ID (SPID) that is blocking this connection.



17. Head Blocker: A value of 1 means the Blocked By Session ID is the head of the blocking chain,otherwise 0.



18. Memory Use (KB): The current amount of memory used by this connection. Number of pages in the Procedure cache attributed to this connection. Note this was reported in pages in prior releases.



19. Host: The login’s workstation or server name. This is a really useful item, but in some cases you may have a Web server connecting to your SQL Server, which may make this less important.



20. Workload Group: The name of the Resource Governor workload group for this query.

Shrinking a log file to a specified target size in sql

USE STAG_2011Q1_EUROPE;


GO

-- Truncate the log by changing the database recovery model to SIMPLE.

ALTER DATABASE STAG_2011Q1_EUROPE

SET RECOVERY SIMPLE;

GO

-- Shrink the truncated log file to 1 MB.

DBCC SHRINKFILE (STAG_2011Q1_EUROPE_log, 10000);

GO

-- Reset the database recovery model.

ALTER DATABASE STAG_2011Q1_EUROPE

SET RECOVERY FULL;

GO

The located assembly's manifest definition does not match the assembly reference

In my case this was the issue with Referencing of Log4net Dll.

My web was using absolute reference of Log4net Dll from shared assebmlies folder and was also consuming another Library(Email Template for sending Emails which was also using log4net Dll)

so that was the reason for conflict between them, after investigating a lot i found that EmailProcessor was referencing log4net , not from shared folder but from its own bin folder .

so issue was fixed by deleting bin folder of EmailProcessor and adding the correct reference of Log4net Dll to project.

Ms project Server Publish job 0% complete. Expected Wait Time 0s

I was having this issue while publishing my project to enterprise server ,
i tried with differnt names because while trying the same name it was saying that name already exists but was not uplaoding to project server .

the problem was with project srever , the server was crashed and its services were stopped last night ,

so by restarting the Project Server Eventing and Queuing Services Fixed for me.

An ASP.NET setting has been detected that does not apply in the Integrated managed pipeline mode

Running the following command.

%SystemRoot%\system32\inetsrv\appcmd migrate config "Default Web Site/".

and then  refreshing the webpage and everything will work perfectly!

Another thing we can try is that , changing the web's application pool from "Default applicaion pool" to FrameWork 4.0 classic also do the trick some times.

Team Foundation server Reporting Services Error

Error 28805 (Setup cannot finish the request to the SQL Server 2005 Reporting Service report server. Verify that the report server is installed and running and that you have sufficient privileges to access it) - Check the following to resolve this problem:




1. In the SQL Reporting Services Configuration Tool, click on the Database Setup tab and make sure

you are using Windows Credentials.

2. Delete encryption keys in reporting service configuration if they exist.

potentially dangerous request.form asmx web service

I was having this issue while using http post protocol on a web service (asmx) .

i was pasing xml string to the browser and on local network it worked fine on windows 2003 server (Developed on visual studio 2008)

application was moved to windows server 2008 and on .net framework 3.5 on deployment server .

i came to know the following solution but was not allowing me to add this in web.config because of the reason , i was using visual studio 2008 for updating web.config

The solution was to update the project to visual studio 2010 and to add the following line in web.config file
<
httpRuntime requestValidationMode="2.0"/>

To Know , which Service Pack and version is installed of SQL Server 2005

SELECT SERVERPROPERTY('productversion') as productversion




,SERVERPROPERTY('productlevel') as productlevel



,SERVERPROPERTY('edition') as edition

Using Temp Table in Functions

CREATE TABLE #t (id INT)

GO

INSERT INTO #t VALUES (1),(2),(3)

GO

CREATE SYNONYM tSynonym FOR #t

GO

CREATE FUNCTION fnSTempTbl ()

RETURNS TABLE

AS

RETURN

(

SELECT * FROM tSynonym

)

GO

SELECT * FROM  fnSTempTbl ()

GO

Distributed Transaction Coordinator (DTC) is not enabled

The Error Was
"Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool"

after investing a huge amount of time and searching a lot on the web , what i found was the issue of time synchronization with the other machine,
though i was under a domain controller but one of my services coordinating with domain controller failed,thats why  my machine's time was not synchronized with the server ,
it all went Good after just updating the Clock Time.

Silver Light Debugger stops hitting Break points

It Often happens when silver light debugger stops hitting break points .
we can activate our debugger again by trying these simple steps

1. Check the Silverlight is enabled for debugging.


2. Clean the solution (delete files manually, Silverlight does not clean things properly , automatically.) and rebuild. Esp. Delete *.suo file.

3. Clear the browser history and ensure your recent xap is getting downloaded.

4. If you get some message when you start running your project. you are in trouble of different SL developer runtime and sl runtime. Align them.

could not download the silver light application

For me the issue was first due to the reason that mime type for .xap was not registered and secondly
it was due to wrong .xap file path in the .aspx page
Visual studio was creating it under ClientBin\Release\**.xap folder while it was mentioned as ClientBin\**.xap in aspx file .

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode.

found this error while deploying an application to 2k8 web server and resolved it by adding the following section in my application's web.config file .

It is an error to use a section registered as allowDefinition='MachineToApplication' beyond application level. This error can be caused by a virtual directory not being configured as an application in IIS

Some times we get this error when we have two more than one configuration files either at the root level or  in any sub directory of the application.

Skype Crashes

If your skype is crashed , you can simple put it to work by

Deleting Shared.xml file from your documents and settings folder

C:\Documents and Settings\%username%\Application Data\Skype\shared.xml

potentially dangerous request.form value was detected from the client

Some times when we try to post a data  to asp.net forms in the form of some script or xml Asp.net blocks that request do not forward it
if we are developing our own project or its a .aspx page then we can simply avoid it by using ValidateRequest=false in page directive
but if had not developed anything and are just deploying that on our server
so for that we can use ValidateRequest =false in our web.config file also
like
'<'pages validateRequest="false" controlRenderingCompatibilityVersion="3.5" clientIDMode="AutoID"/'>'
but some times this also do not work for ur
because validationmode property is not  set to correct values especially in the case when we have multiple dot net frameworks running appliations on IIS
so we can use  validation mode to avoid this error
like


'<' httpRuntime requestValidationMode="2.0"/  '>'
under the System.web node.

On Which Port Sql Server Is listening on

though SQL server's default instance listen on port 1433
but some how if we want to check or know on which port an instance is running we can use

select local_tcp_port from sys.dm_exec_connections where local_tcp_port is not null
command to know the port number

we can also check it by using the Error log information
Managment Studio -> Managment ->SQL Server Logs->Current
and then looking for the Entry
Sql server is listening on 127.0.0.1 ipv4 1434

or we can use the following procedure (even the registery enteries)
DECLARE @tcp_port nvarchar(5)EXEC xp_regread


@rootkey = 'HKEY_LOCAL_MACHINE',

@key = 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',

@value_name = 'TcpPort',

@value = @tcp_port OUTPUT

SELECT @tcp_port

if any of the above does not work then make sure
TCP\IP protocol is enabled , if it is disabled the enable it first  

Transpose Columns to Rows in Sql Server 2005 using UNPIVOT

In Sql Server 2000, it was very hard converting column based values to transpose into row based values and vice versa.


PIVOT and UNPIVOT did the trick in Sql server 2005.



Below is the sample that transpose values from column to rows in sql server using UNPIVOT relational operator.



Sample input table





view plaincopy to clipboardprint?

01.create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7)

02.,R tinyint,G tinyint,B tinyint)

03.GO

04.insert into #temptable values(1,'Violet','#8B00FF',139,0,255);

05.insert into #temptable values(2,'Indigo','#4B0082',75,0,130);

06.insert into #temptable values(3,'Blue','#0000FF',0,0,255);

07.insert into #temptable values(4,'Green','#00FF00',0,255,0);

08.insert into #temptable values(5,'Yellow','#FFFF00',255,255,0);

09.insert into #temptable values(6,'Orange','#FFA500',255,165,0);

10.insert into #temptable values(7,'Red','#FF0000',255,0,0);

11.GO

12.select * from #temptable

13.GO

create table #temptable(rowid int,colorname varchar(25),Hexa varchar(7)

,R tinyint,G tinyint,B tinyint)

GO

insert into #temptable values(1,'Violet','#8B00FF',139,0,255);

insert into #temptable values(2,'Indigo','#4B0082',75,0,130);

insert into #temptable values(3,'Blue','#0000FF',0,0,255);

insert into #temptable values(4,'Green','#00FF00',0,255,0);

insert into #temptable values(5,'Yellow','#FFFF00',255,255,0);

insert into #temptable values(6,'Orange','#FFA500',255,165,0);

insert into #temptable values(7,'Red','#FF0000',255,0,0);

GO

select * from #temptable

GO



The sample table contains colorname with "R","G" & "B" column with specific values.



Our goal is to convert "R","G" & "B" as rows with specific values in single column.



Main requirement of transposing columns to rows is to have identical datatypes of converting columns.

In the sample the datatype of "R", "G" & "B" being tinyint.





The T-SQL query to convert columns to rows is




01.SELECT rowid,colorname,hexa,rgb,rgbvalue

02. FROM

03. (SELECT rowid,colorname,hexa,r,g,b

04. FROM #temptable) p

05. UNPIVOT

06. (rgbvalue FOR rgb IN (r,g,b))

07. AS unpvt;

SELECT rowid,colorname,hexa,rgb,rgbvalue

FROM

(SELECT rowid,colorname,hexa,r,g,b

FROM #temptable) p

UNPIVOT

(rgbvalue FOR rgb IN (r,g,b))

AS unpvt;

The output would be







In the above sample "rowid,colorname,hexa" columns remains unchanged. "R", "G" & "B" columns are converted to rows with new column "rgb" and its specific values are added to new column "rgbvalue".



syntax for understanding:




01.select [<>],<>,<>

02.from

03.(

04. select [<>],<> from <>

05.) <>

06.UNPIVOT

07.(

08. <> FOR

09. <> IN (<>)

10.)

11.AS <>

Could Not Access IIS Metabase

Some times ASP.net applications hosted on iis could not load and start giving errors like
"Could not Access IIS Metabase" or "The process account used to run ASP.NET must have read access to the IIS metabase"
After spending a lot of time on it and trying a lot of things like
Installing aspnet user using aspnet_regiis -i
Reinstalling IIS
Reparing visual studio
Granting access to IUser and Aspnet user to root directory and the direcotry where these files were stored
etc ,
I finally got rid of this situation by
Adding Aspnet user to the Administrator pool of this machine and it worked for me
but it was not a good idea so i tried  with ASPNet User  like
aspnet_regiis -ga ASPNET 
and it worked for me .
hope this will work for you as well.

Optional Paramertes Queries in SQL

We often require to implemnet some search mechanism , in which we provide some input parameters and result set includes result based on those parameters
if we provide all parameters and use all of them in our select query then everything is good, but when we say use can provide some of them and others will not be a part of select statement , here we have some confusion
there are two approaches for this scenerio
No 1 is use IsNull Check
Say we have a parameter @Id and a column Id in table we can write query like
Select * from Employee
Where Id=IsNLL(@Id,Id)
So here if @id parameter is not provided and will contain null , no search criteria will be applied and all employees will be returned
this thing can be exteneded to multiple parameters as well like
Select * from Employee
Where Id=IsNLL(@Id,Id)
AND Name = ISNULL(@Name,Name)
Problem
this is good for all those table columns which does not contain NULl ,
but will fail on those column fields which can contain null values
so in order to avoid such mistakes we can use above queries by modifying a bit
No 2

Select * from Employee
Where ( @Id is null or Id=@Id) AND
(@Name is null or Name=@Name)

Visual Studio Settings Default Settings

Some times Visual studio Development environment is configured to other settings by mistake or by any xyz reason which does not suits you , you might be unable to create any sort of project ,
so in order to change that settings just go to menu
Tools->Import Export Settings and load settings of your choice again

Could not load file or assembly Access is denied silver light

This occurres to me on Silver light component which was hosted in aspx page but was not opening , giving error could not load file or assembly access is denied but was displaying in html hosted page.

The problem i found was an identity element in my web.config which when changed/removed do the work for me

build failed no errors

visual studio 2010 build failed no errors
some times this happens due to long bin direcotry paths on the hard drive
Resolution
Move your project to some short path on the disk and then load it into solution

Use Query String Parameters in Silver light

HtmlPage.Document.QueryString["AuthorizationCode"]
can be used to access Query string paramertes
which can be find in System.windows.browser namespace

The HTTP request to has exceeded the allotted timeout. The time allotted to this operation may have been a portion of a longer timeout.

The HTTP request to has exceeded the allotted timeout. The time allotted to this operation may have been a portion of a longer timeout.









Three places to set time values to fix this issue…







1) Web.Config

'<'httpruntime executiontimeout="600" '>'





2) On your Web.Config Binding Elements

'<'binding closetimeout="00:10:00" name="customBinding123" opentimeout="00:10:00" receivetimeout="00:10:00" sendtimeout="00:10:00" '>'

3) On your ServerReferences.ClientConfig binding elements within the system.serviceModel

'<'binding closetimeout="00:10:00" name="CustomBinding" opentimeout="00:10:00" receivetimeout="00:10:00" sendtimeout="00:10:00"'>'

Get Date Only from DateTime

Simple Conversion to nvarchar can be used to get only Date part from date and time string like

SELECT CONVERT(NVARCHAR(10),GETDATE(),120)

Iterating a RadGrid view in silver light

private void Button1_Click(object sender, RoutedEventArgs e)


{

foreach(Player player in this.playersGrid.Items)

{

var row = this.playersGrid.ItemContainerGenerator.ContainerFromItem(player) as GridViewRow;

if(row.IsSelected)

{

//you may implement your custom logic here.

}

}

}

or we can use it at GridDatabound event  like

protected void RadGrid1_DataBound(object sender, EventArgs e)


{

foreach (GridDataItem item in RadGrid1.MasterTableView.Items)

{

bool oneValue = Convert.ToBoolean(item.GetDataKeyValue("status").ToString());

CheckBox chk = (CheckBox)item.FindControl("MyCheckBox");

chk.Checked = oneValue;

}

}

Add mime type for silverlight in iis

If .xap mime type is not registered on iis it can be added by using the following path in mime type add dialogue
application/x-silverlight-app

and using .xap extenstion in