sp_attach_db / sp_detach_db are deprecated

These two system stored procedures are used frequently by DBAs in general administrative work. However these are marked as deprecated so they will be removed from future versions of SQL Server. I checked in MSDN and from SQL Server 2005 these are marked as deprecated. Interestingly they still available in SQL Server 2012 release as well. However it recommends not use them for any development work and use the new method using CREATE DATABASE statement. 

sp_attach_db, sp_detach_db


Attaching a database using CREATE DATABASE;


CREATE DATABASE Archive
      ON (FILENAME = 'D:\SalesData\archdat1.mdf')
      FOR ATTACH ;
GO

How to use SQL Server Management Studio (SSMS) effectively

SSMS is the primary development tool use by SQL Server professionals to work with SQL Servers. This has introduced in SQL Server 2005 and it has evolved with rich features till SQL Server 2012. In this article I discuss some of the valuable features of SSMS which increases the productivity and efficiency of users. The target audience would be any user (beginner to intermediate) who deals with SSMS to do various tasks with SQL Servers. 
sql-server-performance.com

Log Shipping Error: The server 'LOGSHIPLINK_%' already exists

I received the below error while setting up log shipping with monitoring server.


Save Log Shipping Configuration
- Saving secondary destination configuration [SERVER].[Database] (Error)
                Messages
                * SQL Server Management Studio could not save the configuration of 'SERVER' as a Secondary. (Microsoft SQL Server Management Studio)                
                ------------------------------
                ADDITIONAL INFORMATION:
                
                An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
                
                ------------------------------
                
                The server 'LOGSHIPLINK_% ' already exists. (Microsoft SQL Server, Error: 15028)
                
                For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.4316&EvtSrc=MSSQLServer&EvtID=15028&LinkId=20476
                
- Saving primary backup setup (Stopped)
- Saving Monitor configuration (Stopped)
- Rolled Back (Success)

Without the monitoring server it was successful. Then I dig into the issue and found the issue is related to the linked server mentioned in the error. (The server 'LOGSHIPLINK_% ' already exists.) The secondary server in the environment was recently built. So all the linked servers in the primary server are created manually in the secondary server as well. However the linked server mentioned in the error is related to log shipping and it should create through the log shipping process. So I dropped that linked server from secondary server and then setup the log shipping again and this time it was successful. Then I checked the existence of the same linked server in secondary server, and it was there again. 

Just thought of sharing the information. Cheers for reading the blog post.  

SQL Server 2012 Tutorials and Sample Databases

Microsoft SQL Server 2012 has released RTM recently. No doubt that you're interested to learn new features of the new version. If so then it is useful to have the tutorials and sample databases installed on your server. You can get them using below links;

Tutorials here

Sample Databases here

Note that sample databases are just data files (No log file). You need to attach it to your server to create the databases. Use the following T-SQL script.

CREATE DATABASE AdventureWorks2012

ON (FILENAME = 'D:\SQL Server\DATA\AdventureWorks2012_Data.mdf')

FOR ATTACH_REBUILD_LOG

Enjoy with the new version of SQL Server.

Reasons that can not reclaim transaction log space

The best practice is to setup log backups periodically (if the database is not in SIMPLE recovery) in order to keep the database log size minimum. However there are some occasions even the log backup is setup, the log size keeps growing. To find out the reason you can refer, log_reuse_wait in sys.databases.

As per MSDN, log_reuse_wait is as follows; Also look at the log_reuse_wait_desc column for more detail.

Reuse of transaction log space is currently waiting on one of the following:
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = Other (transient)

Cheers for reading this blog post.

How to get the execution plan of a stored procedure?

Think about a situation where you have identified a badly performing stored procedure in Production environment and now you want to see the execution plan of it for further analysis. Please note, executing the procedure with sample parameter values in Production environment should be done with extra care because if the procedure is only SELECT then no issue but if it has DML operations, then issue.

You can use following DMVs to get the execution plan either XML format or graphical one.

USE AdventureWorks2008R2

EXEC [dbo].[uspGetEmployeeManagers] @BusinessEntityID=60

Note: I executed the SP first so that plan will be created. In actual environment this is not necessary.

select Plan_handle from sys.dm_exec_procedure_stats ps

where ps.database_id=DB_ID('AdventureWorks2008R2')

              and object_id=object_id('dbo.uspGetEmployeeManagers')  

--Copy and paste the plan handle to the below query

select * from sys.dm_exec_query_plan(0x05000500EAE8807F40E13287000000000000000000000000)

Cheers for reading this blog post.

Multi-Subnet Clustering

This is a new feature in SQL Server 2012. However Windows 2008 has this feature. With the introduction of the new feature, SQL Server can have a cluster node in a different subnet. Subnet is a virtual grouping concept available in network. Subnet will enable network administrators to manage networks effectively over geographical areas. 


Since the subnet is a geographical network concept, now SQL Server clustering can be used as DR (Disaster Recovery) solution than ever before. 


More information can be found here.