Friday 17 August 2012

SQL Server–Start SQL Server instance in single-user mode

There are certain situations when you have to start SQL server in single-user mode from the start up. For example, you may want to restore Master database or recover other system database or to change server configuration options and that will require to start an Instance of SQL Server in single-user mode.

  • To start SQL Server in single-user mode is very simple. Please use MS-DOS change directory (cd) command to move to the correct (SQL Instance) directory before starting sqlservr.exe, as shown below

cd \Program Files\Microsoft SQL Server\MSSQL10.50.MSSQLSERVER\MSSQL\Binn

To start default instance enter the following command

sqlservr.exe –m

To start named instance of SQL Server enter the following command

sqlservr.exe - m -s <instancename>

Connect to SQL Server instance using SSMS or sqlcmd after running above command – Make sure to keep MS-Dos command window open. To stop single-user mode just press CTRL+C and it will prompt you to stop SQL Service.

  • Alternatively (easy) use SQL Server Configuration Manager and click on SQL Server Services. Right click on desired SQL Server Instance and go to properties. On the advance table enter param ‘-m;’ before existing params in the startup parameters. Press apply and then restart the SQL Server Instance service.

Make sure to remove added parameter and restart service to allow multiuser connection.

Note: Stop the SQL Server Agent service before connecting to an instance of SQL Server in single-user mode; otherwise, the SQL Server Agent service uses the connection, thereby blocking it.

Technorati Tags: ,,,,,,,,,,,,,,,,,

Wednesday 25 July 2012

How to find Missing Identity Values?

How to find Missing Identity Values?
One of my friends asked me long time ago to get missing identity values. Through power of Row_Number() Window function we can achieve it as below.
--Logic To Identify Missing Values.
Declare @MissingIdentityValues Table
      IDCol Int

Insert @MissingIdentityValues(IDCol) Values (1) , (2) , (5) , (7) , (8) , (10) , (11) , (15) , (16)

Select MIN(IDCol) StartRange, MAX(IDCol)  EndRange
            select IDCol , IDCol -  ROW_NUMBER() OVER(Order By IDCol) as GroupDiff
            from @MissingIdentityValues
      ) AS GroupDiff
Group By GroupDiff

Monday 14 May 2012

SQL Server – AdventureWorks for SQL Server 2012

I have just started learning about SQL Server 2012 RTM and realised that I haven’t installed AdventureWorks sample database. I have quickly searched online and reached to Microsoft Database Products Samples where it provides information about all sample databases and procedure to install databases for SQL Server 2012. See below to install (restore) database:
  1. Download AdventureWorks from here
  2. Run below script
CREATE DATABASE AdventureWorks2012
ON (FILENAME = 'D:\SQL\AdventureWorks2012_Data.mdf')

You will see below message and you are DONE after running above script!

File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Log.ldf" may be incorrect.

New log file 'D:\SQL\AdventureWorks2012_log.ldf' was created.

Converting database 'AdventureWorks2012' from version 705 to the current version 706.

Database 'AdventureWorks2012' running the upgrade step from version 705 to version 706.


Wednesday 18 April 2012

SCVMM 2012 RC to RTM upgrade fails with error “upgrading from currently version is not supported”


I  have just tried to upgrade SCVMM 2012 RC to RTM and it failed to upgrade with following error “Upgrading from the currently installed version of VMM System Center 2012 Virtual Machine Manager is not supported. You must be running VMM 2008 R2 with Service Pack 1 (SP1) to upgrade to System Center 2012 Virtual Machine Manager.”


·         Uninstall current (RC) version of SCVMM. Make sure you select “Retain Database” in Database Options.

·         Using SQL Server Management Studio modify values of “DatabaseVersion” and “QFEDatabaseVersion” in table  tbl_VMM_GlobalSetting from RC (3.0.5007.0) to RTM (3.0.6005.0 or alternatively match to the version of setup.exe).


  • Start SCVMM 2012 installation and select existing database.
  • Finally, Don’t forget to update the VMM host agents!!
Technorati Tags: ,

Wednesday 7 March 2012

How to Round to 2 decimal places without Round function

Today, Someone asked me how to round float value to 2 decimal places without using T-SQL Round function. Let me know your thoughts for below T-SQL block.

      @MyValue Float = 3.1471,
      @IntPart1 int = 0 ,
      @intPart2 int = 0
Select @IntPart1=@MyValue
select @intPart2 = (@MyValue - @IntPart1)*100
If (@MyValue  - ( Cast(@IntPart1 AS Float) + Cast(@intPart2 AsFloat)/100))*10000 > 50
      Select Cast(@IntPart1 AS Float) + Cast(@intPart2+As Float)/100 ASRound1
      Select Cast(@IntPart1 AS Float) + Cast(@intPart2 As Float)/100  AS Round1