admin

Here are a few useful SSMS shortcuts:

Usual Suspects

Ctrl-F, Ctrl-H : Find/Replace

F5 or Ctrl-E   : Execute query

I came across this cautionary tale from the SQL Server Development Customer Advisory Team, while looking for information on virtual log files (VLF):

The ISV created an empty database, setup a backup device, set the database recovery model to FULL, and then started loading data. They also intended to backup the transaction log occasionally to keep the log a manageable size. However, at the end of the load process although the database contained 92 GB of data, the log was only 1.2 GB. One would have expected that the transaction log in the case of FULL recovery would have been approximately the same size as the data.

After confirming the database was in FULL recovery mode, they tried BULK LOGGED and got the same result!

The reason? The database had never had an initial FULL backup!

From Books Online ‘Working with Transaction Log Backups’:

‘Before you can create the first log backup, you must create a full backup, such as a database backup or the first in a set of file backups. […]‘

Because there was no initial FULL database backup, the database ‘effectively’ remained in SIMPLE recovery mode; SQL Server was truncating the transaction log automatically.

So, for FULL and BULK-LOGGED recovery models, create a full database backup right after creating a database. (This initial Transaction Log backup will be ‘empty’ because the database contains no data)

Old news, but the SQL Server Migration Assistant now supports migrating from MySQL and Access to SQL Azure. The latest SQL Server Migration Assistant is available for free download here:

1. SSMA for MySQL v1.0
2. SSMA for Access v4.2
3. SSMA for Oracle v4.2
4. SSMA for Sybase v4.2

Quick comparison table taken from an excellent MSDN article: Comparing the Timer Classes in the .NET Framework Class Library 

When I first heard about the SQL Server Microsoft Certified Master (MCM) certification, it immediately struck me as something worth doing. Then I found out how much it cost! Microsoft have recently announced changes to the original program. In order to pursue the SQL Server Microsoft Certified Master certification you still need the pre-requisites MCITP: Database Developer 2008 (Exam 70-450) and MCITP: Database Administrator 2008 (Exam 70-451). If you work with SQL Server, these 2 exams are relatively easy (unlike the rest of the MCM). 

The Original Program:

• Cost: 18,500 is a considerable sum of money to find in one go (even with the strong Aussie dollar), and that’s in addition to taking 3 continuous weeks away from work. Plus the course only happened in Redmond, so add travelling costs as well. 

  The New Program:

  • Certification and training are separate
  • A written multiple-choice knowledge exam (88-970): cost US2,000, taken at selected secure Prometric testing centres

  Microsoft will no longer provide training for the SQL MCM. Instead, training will provided by approved third-party vendors. I’ve just visited the Prometric site but cannot find any test centres for the MCM exams in Australia!

  If the MCM is something you are interested in, Microsoft (in partnership with SQLskills) has made 40 hours of free introductory training available for the MCM program, which you can download here. These videos give an indication of the breadth of material you need to know for each area (rather than everything you actually need to know). There is also an excellent reading list put together by SQLskills at Microsoft Certified Master – SQL Server Online Training – Links and Study Aids

  (Ref.: Brent Ozar’s blog, Paul Randell’s blog)

I really enjoy our User Group’s end of year ‘DevJam’ events (despite the fact that it involves a bit of work to organise). It is great to see so many local developers willing to give up their time to present a talk. Around 50 people attended last Thursday’s event, so it was a relief when the pizza finally arrived. A crowd of developers deprived of pizza is a riot waiting to happen 😉 !

It was a great line-up of topics with a nice mixture of content, and every presenter did a great job of informing and entertaining. Every talk received a good number of audience votes, but we have to have some winners in order to give out the prizes! The favourite talks (in order) were:

1. Adrian McGrath  – WPF: Design Time Data
2. Alistair Waddell  – A simple approach to test your data driven app
3. Michael Minutillo – Dynamic UI in WinForms

Thank you to all the sponsors who help make these events possible: Microsoft (for the venue and VS2010 MSDN door prize), UGSS for the pizza and refreshments, JetBrains, Joe Albahari, RedGate, TekPub, Scooter Software, DevExpress, TechSmith. [The presenter VS2010 with MSDN prize was donated by myself from the MVP program.]

Thank you to everyone who attended DevJam and meetings throughout the year. Special thanks to Dave Gardner who fetched and delivered the beer.

[ If you are interested in running one of these events at your User Group, I put together some notes here:  Running a DevJam Community Event ]

Here’s the line-up of talks for our end of year DevJam community event: 

• Mike Bain – Using SpecFlow and WatiN
• Michael Minutillo – Dynamic UI in WinForms
• Piers Williams – Rx:Reactive Framework
• Alistair Waddell – A simple approach to test your data driven app
• Dave Gardner – NuGet: The caramel goodness of open source in your projects
• Darren Helsby – Asynchronous programming in .NET 5
• Sam Ritchie – Ditching BizTalk for NServiceBus
• Adrian McGrath – WPF: Design Time Data

Where and when is DevJam happening?:

• DATE:   Thursday, Dec 2nd, 5:30pm
• VENUE:  Enex100 Seminar Room, Level 3, 100 St Georges Terrace, Perth
• COST:   Free. All welcome

There will be pizza and refeshments provided. Not to mention some great prizes!…

I was updating several TSQL scripts today containing ARGB values and thinking (not for the first time) that it was a shame I couldn’t use the Hex values directly (eg 0xFFB40000) rather than converting to the harder to manage base10 integer equivalent (-4980736).

The SQL Server 2008 release updated the CONVERT() function to be able to do just that!

     CONVERT(varbinary(4), ‘0xFFB40000’, 1)

The last Style parameter governs the the conversion.

From Books Online:

Binary Styles

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

 

Value	Output
0 (default)	Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1. If the data_type is a binary type, the characters 0x are added to the left of the result.
1, 2	If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised. If the length of the converted expression is greater than the length of the data_type the result will be right truncated. Fixed length data_types that are larger then the converted result will have zeros added to the right of the result. If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated. If the data_type is a fix sized character type and the length of the converted result is less than its length of the data_type; spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits. The characters 0x will be added to the left of the converted result for style 1.

If you missed this in the last MSDN Flash, J. D. Meier (Developer Guidance, Patterns & Practices) has a roundup of various ASP.NET code sample on his blog: ASP.NET Code Samples Collection

The ASP.NET Code Samples Collection is a roundup and map of ASP.NET code samples from  various sources including the MSDN library, www.ASP.net, Code Gallery, CodePlex, and Microsoft Support.

You can use Perfmon to determine if your .NET application is leaking memory (managed or unmanaged).

Run Perfmon.exe and add these counters:

• Process / Private bytes
• .NET CLR Memory / # bytes in all heaps
• .NET CLR LocksAndThreads / # of current logical threads

Run your application for a representative length of time and exercise its functionality.

If Private bytes is increasing but # bytes in all heaps is not, then unmanaged memory is leaking. If both both are increasing, then managed memory is leaking.  If the # of current logical threads is increasing beyond what you expect, then thread stacks are leaking.

If Private Bytes is periodically jumping in 1MB increments with a corresponding increase in # of current logical Threads, a thread stack leak is the likely cause.

[Tracking it down is another matter! For that, one of the commercially available tools is invaluable: Memprofiler, ANTS Memory Profiler, dotTrace]