How to calculate IOPS per disk

There are some numbers already provided by vendors

SAS (15K RPM) à 175 IOPS

SATA (7.2K RPM) à 75 IOPS

Now how do they come up with these numbers?

The formula is

Estimated IOPS = 1 / ((Seek time/1000) + (Latency / 1000))

Now let’s put this formula into perspective

Seagate Cheetah 15k hard drive

http://www.seagate.com/www/en-us/products/enterprise-hard-drives/cheetah-15k#tTabContentSpecifications

Estimated IOPS = 1 / ((average read seek time + averagewrite seek time)/2)/1000 + (Average latency / 1000)

Estimated IOPS = 1 / ((3.65 / 1000) + (2.0 / 1000) = 1 /(0.00365) + (0.002) = 176.9911

Answer is ~ 175 IOPS

Create a SQL Server Database on network drive

Sometime back I came across a situation when a server ranout of local storage and among other things we did not have HBA card readilyavailable so SAN storage could not be attached.

Only solution was to map a network drive and move somedatabase files to this network drive. So here is a small article on how to create a database on network drive

For SQL Server 2008and SQL Server 2005

By default you can not have database files (MDF, NDF, LDF)on a network drive. This is because any network glitch can cause databasecorruption along with I/O issues inherently caused by network drives.

But it is “allowed” by tweaking a setting in SQL Servertrace flags.

1. Enable trace flag 1807 (Description from BOL: Allows you to configure SQL Server with network-based database files.)

DBCC TRACEON(1807, -1)

2. Now make sure account under which SQL Server service is running has full access to the network location
   
   
3. Create database
   
   CREATE DATABASE [NetDriveDB] ON PRIMARY

( NAME = N'NetDriveDB',

FILENAME = N'\\computername\DBDrive\NetDriveDB_Data.mdf' ,

SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH= 1024KB )

LOG ON

( NAME = N'NetDriveDB_log', FILENAME = N'\\computername\DBDrive\NetDriveDB_Log.ldf' ,

SIZE = 2048KB , MAXSIZE = 4096GB , FILEGROWTH = 10%)

GO

For SQL Server 2008R2

In SQL Server 2008 R2 you do not need to turn on 1807 flag. However we need to make sure it is UNC path (\\ComputerName\SharedFolder\Resource\)

1. Now make sure account under which SQL Server service is running has full access to the network location
   
   
2. Create database
   
   CREATE DATABASE [NetDriveDB] ON PRIMARY

( NAME = N'NetDriveDB',

FILENAME = N'\\computername\DBDrive\NetDriveDB_Data.mdf' ,

SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH= 1024KB )

LOG ON

( NAME = N'NetDriveDB_log', FILENAME = N'\\computername\DBDrive\NetDriveDB_Log.ldf' ,

SIZE = 2048KB , MAXSIZE = 4096GB , FILEGROWTH = 10%)

GO

SQL Server 2000 & 7.0 Trace Flags

Here are some of the trace flagswhich are available for SQL server 2000 & 7.0

To turn on the trace

DBCC TRACEON(<Flag>, -1)

To check the trace status

DBCCTRACESTATUS(-1)

To turn off the trace

DBCC TRACEOFF(<Flag>, -1)

-1 in above is for applying tracesetting globally on the sever

Some of them are documented andsome of them are not

1	Sets trace flags for all client connections, rather than for a single client connection. Because trace flags set using the -T command-line option automatically apply to all connections, this trace flag is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF.
106	Disables line number information for syntax errors.
107	Interprets numbers with a decimal point as float instead of decimal.
205	Report when a statistics-dependent stored procedure is being recompiled as a result of AutoStat.
206	Provides backward compatibility for the setuser statement.
208	SET QUOTED IDENTIFIER ON.
242	Provides backward compatibility for correlated subqueries where non-ANSI-standard results are desired.
243	The behavior of SQL Server is now more consistent because nullability checks are made at run time and a nullability violation results in the command terminating and the batch or transaction process continuing.
244	Disables checking for allowed interim constraint violations. By default, SQL Server checks for and allows interim constraint violations. An interim constraint violation is caused by a change that removes the violation such that the constraint is met, all within a single statement and transaction. SQL Server checks for interim constraint violations for self-referencing DELETE statements, INSERT, and multirow UPDATE statements. This checking requires more work tables. With this trace flag you can disallow interim constraint violations, thus requiring fewer work tables.
257	Will invoke a print algorithm on the XML output before returning it to make the XML result more readable.
260	Prints the versioning information about extended stored procedure dlls.
302	Prints information about whether the statistics page is used, the actual selectivity (if available), and what SQL Server estimated the physical and logical I/O would be for the indexes. Trace flag 302 should be used with trace flag 310 to show the actual join ordering.
310	Prints information about join order. Index selection information is also available in a more readable format using SET SHOWPLAN_ALL, as described in the SET statement.
325	Prints information about the cost of using a nonclustered index or a sort to process an ORDER BY clause.
326	Prints information about the estimated and actual cost of sorts.
330	Enables full output when using the SET SHOWPLAN_ALL option, which gives detailed information about joins.
506	Enforces SQL-92 standards regarding null values for comparisons between variables and parameters. Any comparison of variables and parameters that contain a NULL always results in a NULL.
652	Disables read ahead for the server.
653	Disables read ahead for the current connection.
809	Limits the amount of Lazy Write activity in SQL Server 2000.
1180	Forces allocation to use free pages for text or image data and maintain efficiency of storage.
1200	Prints lock information (the process ID and type of lock requested).
1204	Returns the type of lock participating in the deadlock and the current command affect by the deadlock.
1205	Returns more detailed information about the command being executed at the time of a deadlock.
1206	Used to complement flag 1204 by displaying other locks held by deadlock parties
1609	Turns on the unpacking and checking of remote procedure call (RPC) information in Open Data Services. Used only when applications depend on the old behavior.
1704	Prints information when a temporary table is created or dropped.
1807	Allows you to configure SQL Server with network-based database files.
2505	Prevents DBCC TRACEON 208, SPID 10 errors from appearing in the error log.
2508	Disables parallel non-clustered index checking for DBCC CHECKTABLE.
2509	Used with DBCC CHECKTABLE.html to see the total count of ghost records in a table
2528	Disables parallel checking of objects by DBCC commands.
2701	Sets the @@ERROR system function to 50000 for RAISERROR messages with severity levels of 10 or less. When disabled, sets the @@ERROR system function to 0 for RAISERROR messages with severity levels of 10 or less.
3104	Causes SQL Server to bypass checking for free space.
3111	Cause LogMgr::ValidateBackedupBlock to be skipped during backup and restore operations.
3205	Disables hardware compression for tape drivers.
3222	Disables the read ahead that is used by the recovery operation during roll forward operations.
3502	Prints a message to the log at the start and end of each checkpoint.
3503	Indicates whether the checkpoint at the end of automatic recovery was skipped for a database (this applies only to read-only databases).
3602	Records all error and warning messages sent to the client.
3604	Sends trace output to the client. Used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF.
3605	Sends trace output to the error log. (If you start SQL Server from the command prompt, the output also appears on the screen.)
3607	Skips automatic recovery (at startup) for all databases.
3608	Skips automatic recovery (at startup) for all databases except the masterdatabase.
3609	Skips the creation of the tempdb database at startup. Use this trace flag if the device or devices on which tempdb resides are problematic or problems exist in the model database.
3626	Turns on tracking of the CPU data for the sysprocesses table.
3640	Eliminates the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. This is similar to the session setting of SET NOCOUNT ON, but when set as a trace flag, every client session is handled this way.
4022	Bypasses automatically started procedures.
4030	Prints both a byte and ASCII representation of the receive buffer. Used when you want to see what queries a client is sending to SQL Server. You can use this trace flag if you experience a protection violation and want to determine which statement caused it. Typically, you can set this flag globally or use SQL Server Enterprise Manager. You can also use DBCC INPUTBUFFER.
4031	Prints both a byte and ASCII representation of the send buffers (what SQL Server sends back to the client). You can also use DBCC OUTPUTBUFFER.
4032	Traces the SQL commands coming in from the client. The output destination of the trace flag is controlled with the 3605/3604 trace flags.
7300	Retrieves extended information about any error you encounter when you execute a distributed query.
7501	Dynamic cursors are used by default on forward-only cursors. Dynamic cursors are faster than in earlier versions and no longer require unique indexes. This flag disables the dynamic cursor enhancements and reverts to version 6.0 behavior.
7502	Disables the caching of cursor plans for extended stored procedures.
7505	Enables version 6.x handling of return codes when calling dbcursorfetchex and the resulting cursor position follows the end of the cursor result set.
7525	Reverts to the SQL Server 7.0 behavior of closing nonstatic cursors regardless of the SET CURSOR_CLOSE_ON_COMMIT state in SQL Server 2000.
8202	Replicates all UPDATE commands as DELETE/INSERT pairs at the publisher.
8206	Supports stored procedure execution with a user specified owner name for SQL Server subscribers or without owner qualification for heterogeneous subscribers in SQL Server 2000.
8207	Enables singleton updates for Transactional Replication, released with SQL Server 2000 Service Pack 1.
8599	Allows you to use a savepoint within a distributed transaction.
8679	Prevents the SQL Server optimizer from using a Hash Match Team operator.
8687	Used to disable query parallelism.
8721	Dumps information into the error log when AutoStat has been run.
8783	Allows DELETE, INSERT, and UPDATE statements to honor the SET ROWCOUNT ON setting when enabled.
8816	Logs every two-digit year conversion to a four-digit year.

Ref: http://www.sqlservercentral.com/articles/Monitoring/traceflags/737/

Find if SQL Server is clustered and which is the active node

SELECT
      SERVERPROPERTY('IsClustered') as _1_Means_Clustered ,
      SERVERPROPERTY('Edition') as Edition ,
      SERVERPROPERTY('ProductVersion') as Version  ,    
      SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as ActiveNode

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

Unable to connect SQL Server 2008 after SP3 is installed (Microsoft SQL Server, Error: 18401)

I recently installed SP3 for SQL Server 2008. Before installing the SP as usual I stopped all SQL services. My understanding is if SQL services are running then it will show up in blocked files list while installing service pack.

The install ran fine and after installation it asked to reboot the server which I did.

However after server reboot I was unable to connect to SQL server with following error.

TITLE: Connect to Server

Cannot connect to MYSERVER.

ADDITIONAL INFORMATION:

Login failed for user 'domain\login'. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18401&LinkId=20476

BUTTONS:

OK

I checked the SQL services and they were running fine. So why this error?

After installing Service pack SQL setup run some upgrade scripts namely “sqlagent100_msdb_upgrade.sql”

Following message was logged in SQL error log

2012-04-09 11:31:26.820 spid13s      ----------------------------------------------------------------

2012-04-09 11:31:26.820 spid13s      msdb_upgrade_discovery starting

2012-04-09 11:31:26.960 spid13s      MSDB format is: SQL Server 2008

2012-04-09 11:31:27.100 spid13s      User 'sa' is changing database script level entry 4 to a value of 2.

2012-04-09 11:31:27.120 spid13s      User 'sa' is changing database script level entry 5 to a value of 2.

2012-04-09 11:31:27.130 spid13s      User 'sa' is changing database script level entry 6 to a value of 2.

2012-04-09 11:31:27.130 spid13s      User 'sa' is changing database script level entry 6 to a value of 0.

2012-04-09 11:31:27.130 spid13s      Running SQL Server 2005 SP2 to SQL Server 2008 upgrade script

2012-04-09 11:31:27.130 spid13s      ----------------------------------------------------------------

What is the resolution?

Resolution is to just wait for few minutes till this upgrade script completes. I have seen some other blogs mentioning to turn of implicit transactions etc but in my case resolution was to WAIT and try connecting after few minutes