List all tables in the database along with their row counts

There is a catalog view that will give us this information. The benefit of this approach is that it uses figures that have already been calculated by SQL Server so it should run very quickly. Here is the script :

SELECT T.name TableName,i.Rows NumberOfRows
FROM sys.tables T
JOIN sys.sysindexes I ON T.OBJECT_ID = I.ID
WHERE indid IN (0,1)
ORDER BY i.Rows DESC,T.name

The script uses the clustered index to source the information (where indid=1). Tables that don’t have a clustered index are stored as a heap but still have a row in sysindexes (where indid=0) containing the rowcount. There is one important caveat, though, the figures may not be entirely accurate ! This is because SQL Server doesn’t always keep this bang up to date. Fortunately there is a DBCC command which updates these figures. Just run this command before the script above to make sure the figures are accurate :
DBCC UPDATEUSAGE(0)

How to determine Index fragmentation and then defragment a database

Index fragmentation is always an issue with big data. I have written some articles before:
How to rebuild all indexes
How to script all indexes
How to examine index fragmentation in SQL Server 2012

The easy way:

SELECT
DB_NAME(DPS.DATABASE_ID) AS [DatabaseName]
, OBJECT_NAME(DPS.OBJECT_ID) AS TableName
, SI.NAME AS IndexName
, DPS.INDEX_TYPE_DESC AS IndexType
, DPS.AVG_FRAGMENTATION_IN_PERCENT AS AvgPageFragmentation
, DPS.PAGE_COUNT AS PageCounts
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, NULL) DPS --N'LIMITED') DPS
INNER JOIN sysindexes SI
ON DPS.OBJECT_ID = SI.ID
AND DPS.INDEX_ID = SI.INDID
ORDER BY AVG_FRAGMENTATION_IN_PERCENT DESC

As a rule, all indexes with a fragmentation state over 30% should be defragmented:

— Index neu erstellen
EXEC sp_MSforeachtable @command1=”print ‘Erstelle Index für ?’ ALTER INDEX ALL ON ? REBUILD WITH (FILLFACTOR = 90)”
GO

— Index reorganisieren
EXEC sp_MSforeachtable @command1=”print ‘Reorgansiere Index für ?’ ALTER INDEX ALL ON ? REORGANIZE”
GO

How to rebuild all indexes in all databases

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

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

 

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”

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.