Fixing Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.

We are getting following error in our application.

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.

If you want to find out what is eating up your connections, try this:

  1. Implement correctly all connections inside using blocks to close/dispose connections (as you said, this is already done)
  2. Check which user/machines are keeping opened connections. Run this query to identify the database id:

select distinct dbid, DB_NAME(dbid) FROM sys.sysprocesses where dbid > 0

Then, use this query to inspect all opened connections, replacing the dbid:

SELECT dbid, DB_NAME(dbid) as DatabaseName, COUNT(dbid) as ConnectionCount, loginame as LoginName
  FROM sys.sysprocesses
 WHERE  dbid = 1
 GROUP BY dbid, loginame
 ORDER BY count(dbid) desc

This can give you some hint about who is keeping too much connections opened.

  1. Implement pooling in connection string to limit connections. Use this in your application connection string:

Pooling=true; Min Pool Size=1; Max Pool Size=5

 

Advertisements

SQL Injection for beginners

When we talk about security vulnerabilities in software it’s worth thinking about computer programmes on a fundamental level. On the simplistic level a computer programme is something which takes in an input, usually from the user in the form of text, processes that input, which changes the state of the machine, and then gives as output or result to the user. A bug is when certain inputs aren’t processed correctly and the wrong output is given. For example, if 1 plus 1 results in 3. A security bug however, can be when a certain input is processed in such a way that compromises the security of information managed by a programme and may even output it. We often see this in practice in web applications. Continue reading “SQL Injection for beginners”

Identity column jumping from 6 to 1000+ in SQL Server 2012

I was surprised to find out that overnight, a unique identifier had jumped from 6 to 1012 and upon investigation, it looks like SQL Server, when restarted, messes up the seed of the unique identifier to a new value (this is why you should never use ints for primary keys!)
Don’t expect identity values to be dense. For example, a rollback when inserting a row will cause an identity value to be “consumed” leaving a gap in the numbers.

I wrote a small SP which can be used to re-seeed the identity of a table in a all DBs on a server:

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]
AS
BEGIN
begin TRAN
SET NOCOUNT ON;

DECLARE @db_name varchar(280), @SQL nvarchar(500);

PRINT '-------- DB Reseet Report --------';

DECLARE db_name_cursor CURSOR FOR
select DatabaseName + '.dbo.table' from tblClients WHERE ACTIVE = 1

OPEN db_name_cursor

FETCH NEXT FROM db_name_cursor
INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
print '----- Resetting count: ' + @db_name

-- Declare an inner cursor based
-- on vendor_id from the outer cursor.

SET @SQL = '
declare @id int = 0
SELECT @id = MAX(ID) FROM ' + @db_name + '

DBCC CHECKIDENT (''' + @db_name + ''', reseed, @id)
'

exec sp_executeSQL @SQL

FETCH NEXT FROM db_name_cursor
INTO @db_name
END
CLOSE db_name_cursor;
DEALLOCATE db_name_cursor;

Commit

END

Then add it in to Start up by using following syntax.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

Restore all the backup files from inside a folder on SQL Server – TSQL Script

First, you will need to enable xp_cmdshell

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO

Next, you will need to change the path to the directories:

USE Master; 
GO  
SET NOCOUNT ON 

-- 1 - Variable declaration 
DECLARE @backupPath NVARCHAR(500) 
DECLARE @cmd NVARCHAR(500) 
DECLARE @fileList TABLE (backupFile NVARCHAR(255)) 

-- 2 - Initialize variables 
SET @backupPath = 'F:\baks\' 

-- 3 - get list of files 
SET @cmd = 'DIR /b "' + @backupPath + '"'

INSERT INTO @fileList(backupFile) 
EXEC master.sys.xp_cmdshell @cmd 

SELECT 'RESTORE DATABASE [' + LEFT(backupFile, CHARINDEX('_', backupFile, 12)-1) + 
'] FROM DISK = '''  
       + @backupPath + backupFile + ''' WITH RECOVERY, REPLACE
	 GO'  from @fileList

Copy the generated script into a new window and voila! Your script is done. Just press F5 to run it.
Don’t forget to turn off xp_cmdshell when done.

How to order a result set in SQL based on the numeric part of the string value

There are occasions where you would like to search in a varchar column and order the results based on the numeric part of the field.

Imagine the following table:

ABC
ABC1
ABC2
ABC3 and so on...

ABC11
ABC12
ABC13 and so on..

ABC20
ABC21
ABC22 and so on..

So basically what I have is any string value (not always ABC, any string value) that can either be followed by the number or it may just be a string without the number.

When you run

select * from table order by my column asc

you get following results:

ABC
ABC1
ABC11
ABC12
ABC13
ABC2
ABC20
ABC21
ABC22
ABC3
ABC31
ABC32

In this scenario, you would need the results sorted by the numeric value and not alphabetically.
Eg:

ABC
ABC1
ABC2
ABC3
ABC11
ABC12
ABC13
ABC20
ABC21
ABC22
ABC31
ABC32

How can this be accomplished?

Continue reading “How to order a result set in SQL based on the numeric part of the string value”