Hi, my name is Satalaj. Here, I'm going to explain what is connection pool with solid proof.
Connection pool as name suggest its a pool of Established connections.
When we are closing the connection in finally or in try catch block that doesn't mean we are closing Established connection with your db server.
Closing connection means notifying the application about that connection is free for future request.

Here, I will tell my connection string to use min pool size = 1 and max pool size = 2.

As soon as pool gets initialized it will establish only one connection with SQL server db.  

You can use below query to test how many connections are established with your MS SQL db server. I established the connection with asptest DB.

select db_name(dbid) as DataBaseName , count(dbid) as

NoOFConnections , loginame as LoginName

from sys.sysprocesses where dbid > 0 and db_name(dbid) = 'apitest'

group by dbid,loginame

 

Here is screen shot of Established connection.

 

 I fired a command netstat -b from my command prompt and found that WindowsApplication1.VShost.exe with process ID 6140
Established one connection with MS SQL server IP 192.168.1.1.28 on port 1433.

My application is running and it has processed his task and I have closed the connection. However, you can see the connection is Established
and it will stay established, still the life of application. If I colse the application, I will not see any connection with my DB APITEST.

   TCP    smartmirror:2096                      192.168.1.28:ms-sql-s  ESTABLISHED     6140
  [WindowsApplication1.vshost.exe]

In my application I have one established connection. What will happen If I get 2 requests simultaniouesly?

Ans. The application will establish another connection to DB server as my Max pool size is 2 and that new connection will serve the request.

What if I get 3 or 4 simultaneous requests?

If there is no free  connection available in connection pool the application will throw an error

Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
 

What happens when I set Min pool size = 10 in my connection string ?
The application will establish 10 connection with DB.

You can see it by using query above and at client / server side, you can execute netstat -a command to know
which are the ports participates in this communication. You can see how my application is talking to sql server using ports ranging from 3979 to 3988.

 TCP    smartmirror:3979       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3980       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3981       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3982       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3983       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3984       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3985       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3986       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3987       192.168.1.28:ms-sql-s  ESTABLISHED
 TCP    smartmirror:3988       192.168.1.28:ms-sql-s  ESTABLISHED

It proves that I have 10 Established connections with SQL server and my application.
when I tell the connection object to close the connection, It only notifies the application about that "free connection", which can be used to serve next request.
When I tell connection object to open a connection I get a free connection form connection pool.

To know more about, how clients talks to the server refer http://revenmerchantservices.com/post/2010/01/25/client-server-communication.aspx

You can have multiple pools in applications. Of Course, there is a limit as there is limits on available ports ranging from 0 to 65535.

Its best practice not to have connection string opened in infinite time to serve the request. If you let the connection opened for infinite loop
your application will not be able to serve other requests. you can try it yourself by setting min and max pool size of your connection string.

By default min pool size is 0 and max pool size is 100.

Conclusion: I told connection object to open a connection with SQL DB server means I'm telling connection object to setup actual connection with DB server? 
                No, I'm telling the connection object to get the already established free connection from connection pool.

I hope you understood the location of Connection Pool and its importance.

Satalaj.

 I deleted 8 million records from one table using delete statement, it tooks 30min to finish the operation.
I truncated same records using Truncate table statement in fraction of second.
Truncate is faster as it has not to check the constrains unlike delete. Truncate will give you fresh copy of table with all seed maaped to its default index.

Satalaj

 If you found that your sql server is slow and almost not responding to the request made by web application and you also observed that
CPU and Memory utilization at normal level. Then.

Here is how you can investigate

Open SQL query analyzer and fire below query to know how many number of connections opened to which database?

SQL query

select db_name(dbid) as DataBaseName , count(dbid) as No_Of_Connections , loginame as LoginName  from sys.sysprocesses 
                                                                                                                                        where dbid > 0
                                                                                                                                     group by dbid,loginame

By frequently running this query. You observed that the number of connections opened is high then there exist bug in your application
which didn't closed the opened connections. Stop the web application who uses that database and start over, system would run first
and later it suffers slow response then stops responding. In this situation you need to fix your application and not to blame SQL server.

Satalaj

 SQL server default instance listen on port number 1433. You can tell SQL server not to listen on this port and listen to another port.

Open SQL server configuration manager, find your instance and follow below steps as shown in figure.

 
   After changing the default port settings you need to restart the instance.

To let your application talk to sql server on updated port, you need to do little modification at connection string (server=IPaddress,port number)

e.g.
 
 connectionString = "server=192.168.1.1,1477;user=sa;password=pass;database=aspnetdb"

If you want not alter your application configuration settings stored inside web.config file or in application itself,
you need to reroute the trafic from 1433 to update port 1477 using Sql server client network utility (Cliconfg)
you can refer http://www.revenmerchantservices.com/post/2010/01/05/cliconfg.aspx

Execute Netstat -an command from command prompt to know how communication is set betwwen your server and client

Satalaj

 

 

You need to install SP2 for MS SQL server 2005

Link to download SP2 http://www.microsoft.com/downloads/details.aspx?FamilyId=D07219B2-1E23-49C8-8F0C-63FA18F26D3A

Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments.
To enable database mirroring for evaluation purposes, use trace flag 1400 during startup.

From command prompt you can start SQL server like this to use startup option 

NET START MSSQLSERVER /T1400

Satalaj

OLE DB provider "SQLNCLI" for linked server  returned message "The partner transaction manager has disabled its support for remote/network transactions

QUICK FIX:  Disable Fire wall, Anti virus which blocks PORTS used by Distributed transaction cordinator.

Satalaj.

If you get below error

There is already a master key in the database. Please drop it before performing this statement.

you can remove existing master key

If you ge below error

Cannot drop master key because certificate 'HOST_B_cert' is encrypted by it.

You need to delete that end points.

You can get list of All end Points by executing below query

Use Master

select * from sys.EndPoints

Drop endpoint endpoint_mirroring

If you create new Master Key using encryption by passowd, you may receive below error.

"An error occurred during decryption"

You can fix this by force fully auto generating the key using Force option and later generate your own key

 Below query will tell you about version of MS SQL server installed and service pack version.

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel') as [service pack] , SERVERPROPERTY ('edition')


Satalaj

 You should backup the MSDB database because all of the SQL Server Agent job information is stored in that database.
It will help you to recover jobs information after SQL server disaster or migrating or upgrading your SQL server.
Execute below query to monitor jobs status like this

select * from msdb.dbo.sysjobhistory

select * from msdb.dbo.sysjobs

If you are having SSIS runing using SQL server agent, you can monitor the status of the job using above queries.

To get Instance name of youyr server you can execute below query

select @@SERVERNAME

Satalaj