DBA – Move master Database to another drive – in simple steps

SQL with Manoj

Well, there are times when you want to move your master database from the default location to some other drive. Now this activity cannot be done with the normal ALTER DATABASE statement with MODIFY FILE option. And you need a spacial handling for this case of master DB.

–> Let’s first check the location of master DB:

Move Master 01

–> Now leave SSMS, and open SSCM i.e. SQL Server Configuration Manager. Here select “SQL Server Service”, and Rigth Click on the instance of SQL Server, and choose Properties. Now select the Startup Parameters tab.

Move Master 02

Here you will see 3 line items:

1. -d is the path of the master data file.

2. -e is the path of the SQL error log file.

3. -l is the path of the master log file.

So, you need to update the 1st and 3rd ones. As I want to move my files to E:SystemDatabasesMaster

View original post 75 more words


SQL Server 2016 full and final version available – Download it now !!!

SQL with Manoj

Its 1st June 2016 and finally Microsoft has released SQL Server 2016 full and final version, and is available for download!

Exactly a year back the first CTP version of SQL Server 2016 was released and we got a chance to get a glimpse of the new features coming in. Then after every month or two Microsoft kept rolling out the CTP versions with more new features, enhancements, and bug fixes on the previous CTP builds.

–> Download SQL Server:

To download SQL Server 2016 you can Register and Download the SQL Server 2016 Full or free Evaluation version (180 days) here.

Or you can Direct download the DVD ISO file image (~2.1 GB) SQLServer2016-x64-ENU.iso

–> Free Developer Version:

Microsoft on March 2016 announced that going forward the Developer version of SQL Server any release will be free for Developers and Learning purpose. Register and Download the Developer version.


View original post 204 more words

Copy one column to another sqlserver

UPDATE table SET columnB = columnA --This will update every row. UPDATE table_name SET destination_column_name=orig_column_name WHERE condition_if_necessary --This will update rows which satisfy the WHERE clause UPDATE table1 SET column1 = ( SELECT column2 FROM table2 WHERE table2.id = table1.id ); Whereby: table1 = table that has the column that needs to be updated table2 … Continue reading Copy one column to another sqlserver

How to prevent users from accessing SQL Server from any application or any login expect your main application & its login

The SQL Dude!

When a user who isn’t in the logon trigger exception list above tries to connect, they will get this error.

Cannot connect to SERVERNAME.



Changed database context to ‘master’.

Changed language setting to us_english.

(Microsoft SQL Server, )



View original post

Did You Know?? R Services is in Standard Edition of SQL Server 2016 — The SQL Herald

While my last post extolled the virtues of SQL Server Standard Edition, this week while doing some client testing with Microsoft, I learned about another key standard edition feature. The new SQL Server R services is supported in standard edition of SQL Server 2016. While you won’t get access to some of the cool functions […] … Continue reading Did You Know?? R Services is in Standard Edition of SQL Server 2016 — The SQL Herald

Kill All Sessions Coming from a Host in SQL Server

Kill Sessions by HOST SQL Server --Check no. of sessions coming from all hosts connected to DB -- SELECT hostname, COUNT(hostname) FROM sys.sysprocesses P JOIN sys.sysdatabases D ON (D.dbid = P.dbid) JOIN sys.sysusers U ON (P.uid = U.uid) WHERE hostname != '' GROUP BY hostname ORDER BY COUNT(hostname) DESC; -- Create Procedure to kill sessions … Continue reading Kill All Sessions Coming from a Host in SQL Server