Script to find seed, Increment and Current Identity value of all tables

SELECT 
IDENT_SEED(TABLE_NAME) AS Seed,
IDENT_INCR(TABLE_NAME) AS Increment,
IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
TABLE_NAME
FROM 
INFORMATION_SCHEMA.TABLES
WHERE 
OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'

Attach database without LDF file

I came across a situation when a database was detached and accidentally the log (.LDF) file was deleted. I wanted to recover the database without having log file.

Here is how to do this.

First I am detaching the database for which files are located here

D:\MSSQL\Data\TestDB.mdf

E:\TransactionLogs\TestDB_log.ldf

USE [master]

GO

ALTER DATABASE [TestDB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

USE [master]

GO

EXEC master.dbo.sp_detach_db @dbname = N'TestDB'

GO

After detaching the database I manually deleted the LDF file

Now first attach the database without files.

USE master;

GO

EXEC sp_detach_db @dbname = 'TestDB';

GO

At this time you will not see the database in SSMS under databases

Once database is attached use sp_attach_single_file_db SP to attach the DB with just MDF file. LDF file will be automatically created at default location specified in server properties.

EXEC sp_attach_single_file_db @dbname = 'TestDB',

    @physname =

N'D:\MSSQL\Data\TestDB.mdf';

Now run following query to confirm both data and log files

USE TestDB

select * from sys.database_files

--D:\MSSQL\Data\TestDB.mdf

--E:\TransactionLogs\TestDB_log.ldf

Find more details about sp_attach_single_file_db here

http://msdn.microsoft.com/en-us/library/ms174385.aspx

There is another way to recover database without LDF files.

USE master;

GO

sp_detach_db TestDB;

GO

CREATE DATABASE TestDB

      ON (FILENAME = 'D:\MSSQL\Data\TestDB.mdf') FOR ATTACH ;

GO

Find more details about CREATE DATABASE FOR ATTACH here

http://msdn.microsoft.com/en-us/library/ms176061.aspx

SQL Server Maximum Concurrent connections and worker threads

SQL Server Maximum Concurrent connections and worker threads

Maximum number of concurrent user connections allowed by SQL Server 2008 and above is 32767

Source: http://msdn.microsoft.com/en-us/library/ms143432.aspx

Number of worker threads is

Number of CPUs	32-bit computer	64-bit computer
<= 4 processors	256	512
8 processors	288	576
16 processors	352	704
32 processors	480	960

Source: http://technet.microsoft.com/en-us/library/ms187024(v=sql.90).aspx

You can also find current thread count by using either of the queries

select max_workers_count from sys.dm_os_sys_info

select count(*) from sys.dm_os_threads