Meaning of auto generated STATISTIC name

Use sys.stats catalog to see all the STATISTICS available for the database. To see the statistics in AdventureWorks2012 database, use the following T-SQL statement.

USE AdventureWorks2012
GO
SELECT * FROM sys.stats

You would see the output something like below; (only the values of name column appeared)

_WA_Sys_00000009_00000005
_WA_Sys_00000005_00000005
_WA_Sys_00000003_00000005
_WA_Sys_00000004_00000005

How do you understand above names.

_WA - Washington, the state of the US where SQL Server development team is located.
All automatically generated statistics have the name starting with _WA_Sys. The first number is the column id of the column which these statistics are based on. The next number is the hexadecimal number of the object id of the table.
Source: Inside the SQL Server Query Optimaztion by Benjamin Nevarez

Replication framework between relational database and document oriented (NOSQL) database

I’m working on a research project of building a replication framework from relational database to document oriented (non-relational/nosql) database system. This is to fulfill the research requirement of the MSc degree. The POC would be developed to demonstrate the solution by using MS SQL Server (relational) and mongoDB (document oriented).  I would like to hear any comments/thoughts about this from the community.


I started a discussion on this in LinkedIn mongoDB user group and received couple of valuable comments. Many thanks for those who given the comments. 


You can see them here.

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.