a true friend on database matters

Concurrent Administration Tasks?

noreply@blogger.com (Dinesh Asanka) — Fri, 27 Jan 2012 03:52:00 +0000

Can you run a full database backup and a file shrink at the same time?

Can you run a full database backup and a filegroup backup at the same time?

Answer to both of the questions are NO.

Can you run run full back and log backup at the same time?

Answer is Yes

How to you know these? What can be done with what? Here is the chart which tells you about it?

This image will tell you the story.

Source: MSDN

Not all administrative tasks are allowed to run concurrently. In the table above, a black circle indicates two operations that cannot run in a database at the same time.

Microsoft's SQL Server 2012 to launch on March 7

noreply@blogger.com (Dinesh Asanka) — Tue, 24 Jan 2012 04:22:00 +0000

The launch date for SQL Server 2012 is out of the bag: It’s March 7.

Why not to use NOT IN?

noreply@blogger.com (Dinesh Asanka) — Mon, 23 Jan 2012 18:27:00 +0000

NOT IN is handy syntax use. However, is it a syntax you can always use? Just see the following examples.

Let use say we have two tables named First and Second and data contains as follows.

Let’s say you execute following statement.

What do you think about about. You might think that you should get 2 – Oracle as the result. If so, you are WRONG!!!

Above query will result NOTHING!!! Yes nothing.

Let us try to replace NOT IN with NOT EXISTS and compare the results.

Well, NOT EXISTS returned the desired results.

Let us interchange the tables.

Again NOT IN not giving us the correct results. Point to note here is, NULL values not working very well with NOT IN. So make sure you are not dealing with NULL values when using NOT IN.

New SSIS Functions in Denali

noreply@blogger.com (Dinesh Asanka) — Sun, 22 Jan 2012 01:50:00 +0000

Four New functions are included in Denali for SSIS.

Function	Example	Output
LEFT	LEFT(“Denali,3)	Den
TOKEN	TOKEN(“SQL Server 2012”,” “ ,2)	Server
TOKENCOUNT	TOKENCOUNT(“SQL Server 2012”,” “ )	3
REPLACENULL	REPLACENULL(NULL,2)	2

[SS SLUG] January 2012 Meetup

noreply@blogger.com (Dinesh Asanka) — Tue, 17 Jan 2012 02:52:00 +0000

Announcing the January 2012 meet-up of the SQL Server Sri Lanka User Group (SS SLUG).

Session #1 Using Resource Governor to Manage workload and resources by Dinesh Asanka (MVP) Resource Governor is a new technology in SQL Server 2008 that enables you to manage SQL Server workload and resources by specifying limits on resource consumption by incoming requests. Join this session to see how you can take advantage of this cool piece of technology. Dinesh blogs at: http://dbfriend.blogspot.com/	Session #2 Eliminating roundtrips: MERGE and the TABLE data type by Dinesh Priyankara (MVP) Is your client application still making multiple calls to SQL Server for updating a single dataset? If so, each time the client application makes a call, it affects the network layer unnecessarily. In this session we will see how we can minimize this, passing the dataset with a single call and updating the table with a single statement. Dinesh blogs at: http://dinesql.blogspot.com/

Where: Microsoft Sri Lanka 11th Floor DHPL Building No. 42 Nawam Mawatha Colombo 2 SRI LANKA When: January 18th (WEDNESDAY), 2012 6:00 PM Onwards Who: Everyone are welcome. Entrance: FREE Map: CLICK HERE FOR MAP More Information: SS SLUG Home Page

Finding Running SQL Server Agent Jobs

noreply@blogger.com (Dinesh Asanka) — Mon, 16 Jan 2012 11:25:00 +0000

Monitoring SQL Server agent jobs is a critical task for DBAs. There are several ways of achieving this. Read this FAQ to find them.

Favorites in SSIS

noreply@blogger.com (Dinesh Asanka) — Sun, 15 Jan 2012 08:01:00 +0000

In SSIS sometimes it will be difficult to select correct task since there are lots of tasks. In SQL Server 2012 a.k.a. Denali there is a favorites so that it is easy to pick frequently used tasks.

Control Flow

Data Flow

You can move the task to favorites by right clicking the task and automatically most used task will be moved.

SQL 2012 Wallpapers

noreply@blogger.com (Dinesh Asanka) — Fri, 13 Jan 2012 05:44:00 +0000

@SQLSserver account posted 4 new wallpapers for SQL Server 2012

1024x768

http://twitpic.com/7d7y08/full

1280x800

http://twitpic.com/7d7x8d/full

1400x900

http://twitpic.com/7d7wiy/full

1680x1024

http://twitpic.com/7d7vwh/full

Ideal for the background of your presentation VMs,

How to Differentiate System and User Databases In SQL Server?

noreply@blogger.com (Dinesh Asanka) — Mon, 09 Jan 2012 02:52:00 +0000

This question is frequently asked in many forums and the most popular answer is to use the DB_ID() function which is not exactly correct. This FAQ shows how you can do that perfectly.

New T-SQL Features in SQL Server 2012

noreply@blogger.com (Dinesh Asanka) — Sun, 25 Dec 2011 16:45:00 +0000

In every version of SQL Server new T-SQL features are introduced. These are new T-SQL features of SQL Server 2012.

Sequences
Function
- String function CONCAT,FORMAT
- Logical function CHOOSE,IIF
- Date & time function
  - DATEFROMPARTS
  - DATETIMEFROMPARTS
  - DATETIME2FROMPARTS
  - DATETIMEOFFSETFROMPARTS
  - SMALLDATETIMEFROMPARTS
  - TIMEFROMPARTS
  - EOMONTH
- Conversion function
  - PARSE
  - TRY_CONVERT
  - TRY_PARSE

Paging Data

Lag & Lead

Exception handling

WithResultSets

Sample Databases for SQL Server 2012 RC0

noreply@blogger.com (Dinesh Asanka) — Fri, 23 Dec 2011 18:20:00 +0000

Sample databases dedicated to SQL Server 2012 RC0 are released. AdventureWorks2008R2_Data.mdf and AdventureWorksDWDenali_Data.mdf are those files. You need to attach these mdf files to your SQL Server instance.

Download these sample data files from here.

New Express Edition : LocalDB

noreply@blogger.com (Dinesh Asanka) — Tue, 20 Dec 2011 17:35:00 +0000

New to the Express family, LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. Use this if you need a simple way to create and work with databases from code. It can be bundled with Visual Studio, other Database Development tools, or embedded with an application that needs local databases.

Try it here today!

Drop a Database by Closing Existing Connections using SSMS or T-SQL

noreply@blogger.com (Dinesh Asanka) — Mon, 19 Dec 2011 11:43:00 +0000

Dropping database from SQL Server is not a major task. However, it can be painful if that database is being used at time of dropping it. Read this FAQ from www.sql-server-performance.com

Two SPIDs in sp_who2

noreply@blogger.com (Dinesh Asanka) — Wed, 14 Dec 2011 14:13:00 +0000

sp_who2 is function you can use to identify who (Login) is running at what (DBName, command) from where (SPID & Host) as you can see in the following image.

If you look closely, you will see that there are two SPID columns with same values. By looking at them, they are same So let us see the coding of this.

So you would see that SPID column is convert(char(5),spid) so why on earth same column twice.

Just see the comment after second SPID.

Handy extra for right-scrolling users.

Do you agree. I am not .

SQL Server 2012 Training Videos on Channel9.MSDN.COM

noreply@blogger.com (Dinesh Asanka) — Mon, 12 Dec 2011 03:35:00 +0000

PowerPivot and Power View Lifecycle and BI Semantic Model (BISM) Overview

http://channel9.msdn.com/posts/SQL-Shorts-PowerPivot-and-Power-View-Lifecycle-and-BI-Semantic-Model-BISM-Overview

AlwaysOn SQL 2012

http://channel9.msdn.com/Blogs/mwilmot/SQLShorts-AlwaysOn-SQL-2012

AlwaysOn Availability Groups for SQL 2012

http://channel9.msdn.com/posts/SQLShorts--AlwaysOn-Availability-Groups-for-SQL-2012

SQL 2012 Apollo Column Store

http://channel9.msdn.com/posts/SQLShorts-SQL-2012-Apollo-Column-Store

TechInsights 2011

noreply@blogger.com (Dinesh Asanka) — Tue, 29 Nov 2011 18:46:00 +0000

I was a speaker at TechInsights 2011 which was held in Malaysia at Monash University Kuala Lampur.

Well, this is my first appearance out side Sri Lanka and was a great experience.

I did two sessions 1. CDC for SCD and 2. Resource Governor. To be honest, first session was not much participated second one was far better.

Here are few photos of the sessions.

Here are Presentations and Samples

SQL Server 2012 RC0

noreply@blogger.com (Dinesh Asanka) — Fri, 18 Nov 2011 19:16:00 +0000

SQL Server 2012 Release Candidate 0 is now available for download. SQL Server 2012 RC0 (previously code-named Denali) includes several new capabilities (most of which are related to the release’s new features) that weren’t available in SQL Server Denali CTP3, including the following:

AlwaysOn—SQL Server 2012 RC0 offers multiple readable secondaries in AlwaysOn, which lets you better distribute backup and reporting workloads, and it now supports the FileTable and FILESTREAM data types.
Resource Governor—According the SQL Server Team Blog, SQL Server 2012’s Resource Governor includes several new capabilities, including support for 64 resource pools, enhanced CPU usage controls, and enhanced resource allocation.
Cloud Computing—SQL Server Management Studio (SSMS) now supports snapshot backups to Windows Azure.
Data Quality Services and Master Data Services—This release provides Change Data Capture (CDC) support for Oracle.
Power View—The data visualization tool includes new features with SQL Server 2012 RC0.

For more information about the upcoming release of SQL Server, see Microsoft’s SQL Server 2012 page. You can download SQL Server 2012 RC0 here.

Combining Columns Ignoring Null Columns

noreply@blogger.com (Dinesh Asanka) — Sun, 13 Nov 2011 15:49:00 +0000

Let’s start with an example.

You will see that MiddleName column has NULL values. If you want to combine all four columns and display them in one column you will write following query.

Result is,

You will realize the obvious problems from the above result set. To avoid this what you should is, use the ISNULL function.

Though the above will solve your problem, as a developer you need to keep an eye on nullability of the column.

With Denali, you have a new function called CONCAT.

And the result is,

which is what we were looking for.

Next question is, how about the performance.

To answer that I will display query execution plans for two queries, one with CONCAT and the other with ISNULL function.

So there is no additional cost with the new function.

SQL Server 2012 Editions

noreply@blogger.com (Dinesh Asanka) — Sat, 05 Nov 2011 18:10:00 +0000

SQL Server 2012 users will experience new SQL Server editions called Business Intelligence to the addition of existing editions, Enterprise, Standard, Web , Developer, Express and Evolution.

If you wish for more news about editions and licensing click it here.

TRY_CONVERT

noreply@blogger.com (Dinesh Asanka) — Mon, 31 Oct 2011 10:51:00 +0000

I am sure you would have used CONVERT function many times.

If you see the above data set, last date value is an invalid date. If you are integrating with legacy systems, you will these types of records.

They you might need to convert these into a date time value. Naturally, you will use, CONVERT function.

But it will fail.

In Denali or SQL Server 2012 (as per CTP3) you have new function called TRY_CONVERT.

With this new function, you won’t get the error as well as you will be able to find out the errors as well.

Following query will return all the rows where you have an invalid date value.

You can use this for other data types as well.

Download the code from here

SQL Server 2008 SP3 is now available!

noreply@blogger.com (Dinesh Asanka) — Sun, 16 Oct 2011 10:49:00 +0000

Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3 (SP3). Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. As part of our continued commitment to software excellence for our customers, this upgrade is free and doesn’t require an additional service contract.

Download it from here

Failing of alter Table statement

noreply@blogger.com (Dinesh Asanka) — Fri, 30 Sep 2011 04:19:00 +0000

Replication Agent will fail when attempting to alter a column in the publisher which is part of an index.

Here is the faq of mine.

දත්ත මූලිකයන්හි සිංහලෙන් දත්ත ගබඩා කරමු

noreply@blogger.com (Dinesh Asanka) — Wed, 31 Aug 2011 16:50:00 +0000

මෙය මවිසින් ලියන ලද දෙවැනි ලිපියයි.

මෑතකදී, විශ්ව විද්‍යාලයක සිසුන් පිරිසක් තම පාඨමාලවේ පර්යේෂන වියාපෘතියකට ශ්‍රි ලංකා පොලීසිය සදහා ලැබෙන සුළු අපරාධ විශ්ලේශණය කිරීම තෝරා ගෙන තිබුනි. ඒ සදහා ඔවුන් සිංහලෙන් දත්ත ගබඩා කිරිමට තෝරා ගෙන තිබිනි. එයට මූලික හේතුව වී තිබුනේ, ලැබෙන දත්ත භාවිතා කරන අයට ඉංග්‍රීසි භාවිතයට වඩා සිංහල පහසු නිසාය. බොහෝ ශ්‍රි ලංකාවේ රාජ්‍ය ආයතයන් හි මෙම තත්වය ඇති බැවින්, ඒ පිළිබදව කිසියම් විස්තරයක් කිරීම මෙම ලිපියෙහි අරමුණයි.

දත්ත මූලිකයන්හි සිංහලෙන් දත්ත ගබඩා කරමු

Change the case of the Logical Name of a SQL Server Database

noreply@blogger.com (Dinesh Asanka) — Tue, 30 Aug 2011 17:20:00 +0000

When you create a database, by default data file and log file will be added to the database. For logical name it will be, Databasename_log. Let us say you want to change it to Database_Log (where L is in upper case).

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_Log')
GO

Then you will get an following error.

Msg 1828, Level 16, State 3, Line 1
The logical file name "TailLog_Log" is already in use. Choose a different name.

Let us try to do this from the SSMS.

So basically you are not allow to do this.

What you have to do is, first set it to another name.

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_log', NEWNAME=N'TailLog_LogTemp')
GO

The file name 'TailLog_LogTemp' has been set.

Then change it back to TailLog_Log so that you can achieve your target.

ALTER DATABASE [TailLog]
MODIFY FILE (NAME=N'TailLog_LogTemp', NEWNAME=N'TailLog_Log')
GO

Disk Usage Report in SSMS

noreply@blogger.com (Dinesh Asanka) — Sat, 27 Aug 2011 12:40:00 +0000

Reports in SSMS are very helpful when monitoring SQL Server. But today I encounter an issue with one of the reports, which is Disk Usage Report.

Above is the Transaction Log space usage report taken for same database but for different times. Actually, second one was after taking a log backup. After seeing this report my immediate reaction was log backup has not cleared the log. So I ran another log backup and results are similar. But when you analyze these graphs carefully you would see that in the first graph, Green is Used and in the second Green is Unused and Blue is vice-versa. (Oops).

I did few more research into this to find out the tipping point and this is happening after 50% percent. See the image below.

Now it is 50-50 and next one is,

You can clearly see the change. I verified this is the same behavior in SQL Server 2008 R2 as well.