DECLARE @Database VARCHAR(255) DECLARE @Table VARCHAR(255) DECLARE @cmd NVARCHAR(500) DECLARE @fillfactor INT SET @fillfactor = 90 DECLARE DatabaseCursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','msdb','tempdb','model','distribution') ORDER BY 1 OPEN DatabaseCursor FETCH NEXT FROM DatabaseCursor INTO @Database WHILE @@FETCH_STATUS = 0 BEGIN SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [' + @Database + '].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' -- create table cursor EXEC (@cmd) OPEN TableCursor FETCH NEXT FROM TableCursor INTO @Table WHILE @@FETCH_STATUS = 0 BEGIN IF (@@MICROSOFTVERSION / POWER(2, 24) >= 9) BEGIN -- SQL 2005 or higher command SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')' EXEC (@cmd) END ELSE BEGIN -- SQL 2000 command DBCC DBREINDEX(@Table,' ',@fillfactor) END FETCH NEXT FROM TableCursor INTO @Table END CLOSE TableCursor DEALLOCATE TableCursor FETCH NEXT FROM DatabaseCursor INTO @Database END CLOSE DatabaseCursor DEALLOCATE DatabaseCursor
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
usingblocks 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 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.
- 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] FROM sys.procedures 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:
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';
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:
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.