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 http://msdn.microsoft.com/en-us/library/ms143799.aspx 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. (http://www.sqlservercentral.com/Forums/Topic1299749-391-1.aspx, http://stackoverflow.com/questions/907851/change-sql-server-instance-name).
 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>;
GO
sp_addserver <new_name>, local;
GO

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

-- 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: http://msdn.microsoft.com/en-us/library/ms190956.aspx)
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
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=10000, 
		@jobhistory_max_rows_per_job=200
GO

• 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: http://msdn.microsoft.com/en-us/library/ms182512.aspx)

• 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!

TIMEOUT /T 5 /NOBREAK

• 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) 
Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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