Wednesday, August 31, 2011

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

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

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

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

Tuesday, August 30, 2011

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

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.

image

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

Saturday, August 27, 2011

Disk Usage Report in SSMS

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.

 

imageimage

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.

image

image

image

image

Now it is 50-50 and next one is,

image

image

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

Thursday, August 11, 2011

Using $IDENTITY function

You may have used many different Identity functions like SCOPE_IDENTITY(), @@IDENTITY etc. but have you used $IDENTITY function.

Let us say you have a table where identity property is set in one of the columns.

image

if you run the following query,

image

Following is the result,

image

This means, you don’t have to explicitly mention the identity column instead you can use $IDENTITY.  You can use this for all aggregations ( AVG ($IDENTITY), MAX($IDENTITY) etc) as well as in grouping columns.

Tuesday, August 9, 2011

පූර්ණ කාලීන දත්ත මූලික කළමනාකරුවන් (DBA) අවශ්‍ය ඇයි? (si-LK)

මෙය මා විසින් ලියන ලද පළමු සිංහල blog සටහනයි. බොහෝ කලක පටන් පැවති අදහසක් "දැවන්ත" කාර්යයකින් පසු මෙසේ සටහනක් බවට පත්විය. මේ වෙනුවෙන් මට සහාය වු, Fiqriට මගේ ස්තුතිය.

පූර්ණ කාලීන දත්ත මූලික කළමනාකරුවන් (DBA) අවශ්‍ය ඇයි? (si-LK)

Sunday, August 7, 2011

@Refresh Colombo

This was my first presentation in public outside Microsoft technologies. Presentation was on MongoDB which Susantha and myslef are doing quite a bit of R&D lately.

Our presentation went well and had few questions from the audience which was just over 1000.

Entire event was covered by adaderana

You can view all the pictures at facebook and samples are available in my skydrive.