Knowledge Share 2014-06

• Can I change SQL Server Name/ Instance Name:
o Yes, you can change SQL Server name. Especially when you rename your server name, it is good option to change your SQL Server name.
o is the article which can help you to rename your server name
o How about Changing instance name:
 Recommendation is not to consider changing instance name as this is not working as expected. (,
 To experiment, I changed my default instance to named instance @@SERVERNAME and sys.servers shows new name but I could not connect with new name.

-- Default Instance Server name change 
sp_dropserver <old_name>;
sp_addserver <new_name>, local;

-- Name instance Server name change 
sp_dropserver <old_name\instancename>;
sp_addserver <new_name\instancename>, local;

-- Verify ServerName 
select @@servername;
select * from sys.servers

• How to change the length of SQL job history
o SSMS -> SQL Server – SQL Server Agent Properties -> History -> Update (Maximum job history log size, Maximum job history rows per job). (Ref:
o I recommend to capture existing properties before changing properties using EXEC msdb.dbo.sp_get_sqlagent_properties.

EXEC msdb.dbo.sp_get_sqlagent_properties
USE [msdb]
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000, 

• How to move SQL Error Log to new file
o If your error log file has grown too big to manager (open), you can execute (EXEC master.sys.sp_cycle_errorlog) to create new file (Ref:

• SQL sleep command

 WAITFOR DELAY ’00:00:15′; 

* Powershell Sleep Command

start-sleep 5

• Sleep command in DOS (PAUSE Command): In below example, DOS pauses for 5 seconds!


• What is difference between count(*) and count(ColumnName)?
 •• count(*) includes the count of NULL values, count(ColumnName) excludes Null values in the count

• You can convert the string ‘TRUE’ or False into a bit (1 or 0) using CAST as shown below;

select	CAST('FALSE' as bit) 
select	CAST('TRUE' as bit) 
This entry was posted in knowledge, SQL and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s