We are getting following error in our
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:
- Implement correctly all connections inside
using blocks to close/dispose connections (as you said, this is already done)
- 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
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.
- 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
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”
SELECT Object_name(object_id) as [Procedure Name],
Object_definition(object_id) as [Definition]
WHERE Object_definition(object_id) LIKE '%cursor%'
Order by [Procedure Name]
Using a SQL Database in the cloud can be useful. You get a completely managed database without any prior configuration. It scales well and can be used from within your cloud or onpremise applications. Continue reading “Handling exceptions by using retries (SQL Database)”
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:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[ResetOrderIdentityAfterRestart]
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
FETCH NEXT FROM db_name_cursor
WHILE @@FETCH_STATUS = 0
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
Then add it in to Start up by using following syntax.
EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';
First, you will need to enable xp_cmdshell
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
-- To update the currently configured value for this feature.
Next, you will need to change the path to the directories:
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.
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:
ABC3 and so on...
ABC13 and so on..
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:
In this scenario, you would need the results sorted by the numeric value and not alphabetically.
How can this be accomplished?
Continue reading “How to order a result set in SQL based on the numeric part of the string value”