Simons SQL Blog

Pop quiz.

Your database is in bulk logged mode. You do a transaction log backup.

Can you restore the log to a point in time between the main backup and the log backup?

Scroll down for the answer

Answer:  It depends

If you don't have any bulk logged operations then you can however the second you have a bulk logged operation you are stuffed, and you have to restore the whole log. So if someone has put a little SELECT INTO statement of a SSIS package that does BULK insert you won't be able to do point in time recovery.

So the lesson learnt is, only switch to BULK logged mode when you don't want to do point in time recovery.

When I was a lad and starting out in work an learning about servers and disks I was told of large corporations that regularly replaced storage drives to mitigate against failures.

They would start after x years of buying new storage and start replacing drives. They would, by the time they reached 3 or 4 years have replaced all the drives and be looking to cycle through again.

Things have moved on greatly since I heard this story and wondered if anyone does this, given the proliferation of SANs and often the complete replacement culture I've seen in many places.

The other thing I often find in small places is the lack of spare drives being used in RAID arrays. Whilst I can understand that if, you have spare drives somewhere close, you actively monitor the drives. However in the event of a failure over the weekend a spare can save you in the even you are running a less resilient RAID array or are unlucky.

Do people think that with RAID 10 that the likelihood of getting 2 drive failures in the same mirrored pair is really rare and so don't need spares.

I've created a quick survey if you would like to participate. Its only got two questions so should take you less time that it has for you to read this post.

If the survey doesn't appear above Click Here to take survey Cheers

I've submitted a couple of sessions for SQLBits IV 

You can see the sessions that have been submitted so far by going here -Submitted Sessions

LINQ to SQL allows you write your data access statements in your .Net code in LINQ which is then translated into SQL. LINQ supports many of the same constructs, predicates of SQL however some aren't supported.

One set of predicates that aren't supported are those for full text, i.e. CONTAINS, CONTAINSTABLE, FREETEXT, FREETEXTTABLE.

That means you can't write something like,

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.Contains("Description,Title","simon")

                 select s;

You can however do

var mySearchList = from s in new SQLBitsDataContext().sessions

                  where s.description.Contains("simon")

                 select s;

So you might think that full text is supported. Unfortunately Contains is translated into a LIKE predicate with two wildcards, i.e.

select *

  from sessions

 where description like '%simon%'

So how do you get it working. Well the magic is in Inline Table Valued Functions. I've talked about them before when discussing performance of scalar functions. An inline table valued function is essentially a prameterised SQL statement, and importantly only one statement. This means the optimiser can merge the SQL Statement into the query that uses it.

To get it working you need to create a table valued function that does nothing more than a CONTAINSTABLE query based on the keywords you pass in,

create function udf_sessionSearch

      (@keywords nvarchar(4000))

returns table

as

  return (select [SessionId],[rank]

            from containstable(Session,(description,title),@keywords))

You then add this function to your LINQ 2 SQL model and he presto you can now write queries like.

var sessList = from s   in DB.Sessions

               join fts in DB.udf_sessionSearch(SearchText) on s.sessionId equals fts.SessionId

             select s;

If you want you can extend this to limit the number of results from containstable. If you want to search different columns then you will need different functions as the colum list used by containstable cannot take a parameter.

Just a quick note if you run profiler on a SQL 2008 machine and fine there are no templates then thats possibly because you have run the SQL 2005 version of profiler, this version of profiler doesn't read the SQL 2008 templates. There are a different set of templates for each version of database you can connect to. They are stored in C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates on an x64 box and in C:\Program Files\Microsoft SQL Server\100\Tools\Profiler\Templates on an x86 box.

You will see folders for 80, 90 and 100 (SQL 2000, 2005 and 2008 respectively).

So in summary make sure you run Profiler.exe and not profiler90.exe. The latter is the SQL2005 version of profiler.

Just read a nice summary of the status of the ORM contenders from Microsoft.

http://visualstudiomagazine.com/features/article.aspx?editorialsid=2583

This article references some great posts from Ian Cooper and Mike Taulty.

I checked again today and the current version of Skype 3.8.0.188 supports windows server 2008. (well I haven't had any problems yet)

Do you use TSQL and find it clunky. If you want to have your say on what Microsoft are thnking about the planned changes to TSQL then you need to come along to the next SQL Usergroup meeting in London on the 19th December http://sqlserverfaq.com/events/137/Business-Intelligence-Text-Mining-SQL-PASS.aspx

I will also be covering the proposed changes to managing databases that will be coming in the next versions of SQL.

As per usual we will be having beers and pizza, but as it Christmas we plan on having some fun, we've got a quiz planned with questions from some of my fellow SQL MVPs.

We've got lots of goodies to giveway, including free versions of SQL Server.

Thanks to Conchango for supporting this event