Copy And Paste From SQL Server Management Studio 2012 New Line Issue Into Excel

select REPLACE(REPLACE(col, CHAR(13), ''), CHAR(10), ' ')




for eg : 

OrderStatus=REPLACE(REPLACE(cast(OrderTxtStatus as varchar(500)), CHAR(13), ''), CHAR(10), ' ')

sql server Replication and Move Database Files mdf and ldf to Another Location

Step 1 :  Stop log agent in primary DB replication located

Step 2 : Open Read or secondary DB , new query window --->USE MASTER;

Step 3 :  Run 

-- Take database in single user mode -- if you are facing errors
-- This may terminate your active transactions for database
ALTER DATABASE db_read
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Step 4 : -- Detach DB
EXEC MASTER.dbo.sp_detach_db @dbname = N'db_read'
GO

Step 5 : Manually move the mdf and ldf Files from Loc1 to Loc 2

Step 6 : -- Re-Attached DB
CREATE DATABASE [db_read] ON
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read.mdf' ),
( FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL\DATA\db_read_log.ldf' )
FOR ATTACH
GO

Step 7 : start the log agent in primary DB

Configure Database Mail – Send Email From SQL Database

Step 1) Create Profile and Account:

You need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail context menu of the Database Mail node in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

sp_CONFIGURE 'show advanced', 1
GORECONFIGUREGOsp_CONFIGURE 'Database Mail XPs', 1
GORECONFIGUREGO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USE msdb
GOEXEC sp_send_dbmail @profile_name='PinalProfile',@recipients='test@Example.com',@subject='Test message',@body='This is the body of the test message.
Congrates Database Mail Received By you Successfully.'

After all validations of the parameters entered are done, certain stored procedures are executed and the mail is queued by Service Broker, read more at SQL SERVER – Introduction to Service Broker.

Database Mail keeps copies of outgoing e-mail messages and displays them in thesysmail_allitems, sysmail_sentitems, sysmail_unsentitems, sysmail_faileditems . The status of the mail sent can be seen in sysmail_mailitems table, when the mail is sent successfully the sent_status field of the sysmail_mailitems table is set to 1 which can again be seen in sysmail_sentitems table. The mails that are failed will have the sent_status field  value to 2 and those are unsent will have value 3.
The log can be checked in sysmail_log table as shown below:

SELECT *FROM sysmail_mailitems
GOSELECT *FROM sysmail_log
GO

Status can be verified using sysmail_sentitems table.

After sending mail you can check the mail received in your inbox, just as I received as shown below.

Refer : http://blog.sqlauthority.com/2008/08/23/sql-server-2008-configure-database-mail-send-email-from-sql-database/

sql server concatenation

TSQL provides 2 ways to concatenate data, the + sign and the new CONCAT() function. This tip will cover the differences in the two, so you can achieve the expected behavior in your code.

The way most us are used to concatenating data together is using the + sign. The problem we run into using this method is it expects the different items being concatenated together to be of the same data type. You have to perform an explicit conversion if they are not the same data type. The following example is trying to concatenate a INT and a String together, you can see SQL cannot handle the different data types.

Refer:

http://www.mssqltips.com/sqlservertip/3014/concatenation-of-different-sql-server-data-types/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20140224

sql server performance optimization steps

 sql server performance optimization steps

 Investigate first and take action.

Step : 1  Check block query


              select *from sys.sysprocesses WHERE BLOCKED >=1

             check SP ID which query/SP is blocked

              dbcc inputbuffer(<<SPID>>)

              Kill SP ID

              Kill <<SPID>>


Step : 2   DBCC checkdb

Step : 3  Sp_updatestats

Step : 4  sp_msforeachdb 'checkpoint'

Step : 5  Some use begin_tran forgot to commit  statement. Because of blocking happen.
              Action :  Restart sql service.

Step : 6  CPU usage goes high, No doubt missed index in your database.  using below link to find missed index and create indexes.

http://selvasqlserversolutions.blogspot.in/2014/02/missing-index-script-sql-server.html

Missing Index Script - sql server

Performance Tuning is quite interesting and Index plays a vital role in it.

A proper index can improve the performance and a bad index can hamper the performance.

Here is the script from my script bank which I use to identify missing indexes on any database.

Please note, if you should not create all the missing indexes this script suggest. This is just for guidance. You should not create more than 5-10 indexes per table. Additionally, this script sometime does not give accurate information so use your common sense.

Query :

SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

SQL SERVER – List all the database and Find the Size of Database File – Find the Size of Log File

SQL SERVER - List all the database


EXEC sp_databases
EXEC sp_helpdb

old version

SELECT name
FROM sys.databases
SELECT name
FROM sys.sysdatabases

===================================================

 Find the Size of Database File – Find the Size of Log File



SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'employee'
GO