SQL RNNR

1 DBA's Professional Blog

Summit 2012

Categories: News, Professional, SSC, SSSOLV

Comments: No Comments

Published on: November 1, 2012

In case you have been living under a rock (I know it’s not under my rock), here is your notice that Summit 2012 is just a few days away at this point.

I will soon be boarding a jet bound for Seattle for a week full of all things SQL. When I say all things SQL – there isn’t much exaggeration to it.

During my week at summit, I will be busy from pre-dawn to post-dusk every day. That is not an atypical experience for Summit – but the reality hit me firmly as I finalized my daily schedule for next week.

Since I put it together, I decided to share it so others may see all that may be available to them.

I arrive Sunday. That evening after getting settled in, I plan to join the MCM study group (and maybe have the Sunday night NFL game on in the background).

Monday is a free day of learning hosted by Red Gate and their SQL in the City series. You can see the agenda here. After the event there is the after event social party. That party overlaps with the Networking Dinner hosted by Andy Warren (twitter) and Steve Jones (twitter) at Gordon Biersch. This dinner is a pre-registration event (as is SQL in the City). You can register here (if they have slots available).

Tuesday is a big day. Tuesday I will be taking the MCM Lab exam in the morning. Upon completion I hope to decompress for a few hours prior to the evening events. In the evening we have the Welcome reception/Quiz Bowl sponsored by SQL Sentry. Immediately following that, there are two events at the same time. The first is the Speaker/Volunteer party. The other is another Red Gate event – The Exceptional DBA Awards and SQLServerCentral party. For those from the US, this is also Election Tuesday. I hope for those from out of town you voted early or are sending in an absentee ballot.

Wednesday we jump into the sessions that I planned on attending (XE, SQLOS and Perf Tuning on my docket). But in addition to those sessions, I will be at the Apress booth (booth 342) from 12:30 – 1:00 and Wayne Sheffield (twitter) representing our book. And before anything even gets going Wednesday, I will also be at the Summit Community Zone from 7AM to 8AM. In the evening there is the Exhibitor Reception. And for anybody into Karaoke – you can do either of the two Karaoke events at about 9PM that evening. Don’t forget to wear your SQL Saturday shirt(s) on Wednesday.

Thursday I will be at the Community Zone again from 7AM to 8AM. If you have a kilt – wear it Thursday. I plan on attending the Query Tuning Mastery, Transaction Isolation Levels, and Recovering Lost Data sessions on Thursday. That brings us to the evening when we have the Community Appreciation Party that runs from 7PM to 10PM.

Friday we are on our last leg wrapping things up and hoping to not be overloaded by this point. The session I am most looking forward to is the last one of the day by Paul White (twitter). I mean who wouldn’t want to end summit by attending a deep dive session into the Query Optimizer. In fact I know Wayne Sheffield is presenting during the same time and he was hoping nobody would come to his session so he could attend Paul’s session instead. Of course he said that in jest, because he wants to do his presentation. But at the same time he really wants to get to Paul’s session. At the conclusion of this session, my agenda is concluded leaving Friday night my only open night.

I am looking forward to meeting you and chatting. If you see me, say hi. But please don’t try to interrupt my exam.

Do you Dream?

by Jason Brimhall

Categories: Certification, News, Professional, SSC, SSSOLV

Tags: Certification, Community, MCM

Comments: 4 Comments

Published on: October 11, 2012

You ever have those nights consumed by all things SQL?

I do. It happens more than I might like sometimes. Usually it happens when there is a work problem that I am trying to solve.

I don’t mind it sometimes because it does provide some results at times. Other times it is of no use.

Sometimes when multi-tasking like this, I wish I could recall some of the code. Others, I don’t care to recall the code. And yet other times, the code sits fresh in my head until I sit at a keyboard. Sometimes, that last one is the only way I am going to get some sleep – find the laptop and then bang out some code.

Of late though, my SQL dreams have been very relaxing. I have been pouring through books and thinking about topics I might want to study more in depth to become that more well rounded DBA. Of course, these studies also have another goal in mind. I am working toward the MCM.

So as I study and think of those extra tidbits I want to learn, I also dream of exotic solutions and implementations. I have been sleeping better and dreaming more about SQL. Which is kind of weird come to think about it. But hey, I will enjoy these SQL dreams and hopefully it will be more than just dreams. I will soon know if I get to move on to the next exam as I will be taking my Knowledge exam tomorrow.

My question for you is: Do you Dream of SQL? If you do, what kinds of SQL Dreams do you have?

Oh and if you practice this at your desk, do you just tell your boss that you are multi-tasking?

Database In Recovery

by Jason Brimhall

Categories: Corner, News, Professional, SQLServerPedia Syndication, SSC, SSSOLV

Tags: MCM, SQL Internals, SQL Script, TSQL

Comments: 4 Comments

Published on: June 4, 2012

What do we do?

Have you ever run into a database that is in the “In Recovery” state?

If that has happened, have the bosses and/or endusers come to you asking “What do we do?” or “When will it be done?”. They probably have – it is inevitable.

The question is, what do you do when you run into a database that is in this state?

We all know that it doesn’t help much if we are panicked about the issue – that just feeds the already growing anxiety. If you feel anxiety – that’s OK, just don’t show that to the endusers or to the boss. You need to portray to them that you are on top of the issue.

While trying to keep everybody calm and apprised of the situation, you would probably like some assurances for yourself that the database is progressing to a usable state. That is what I want to share today – a little query that I wrote for this very instance.

Anxiety Tranquilizer

Code block

DECLARE @ErrorLog AS TABLE([LogDate] DATETIME, [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))
 
INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database'
 
SELECT  DB_NAME(dt.database_id) AS DBName,GETDATE() AS currenttime, AT.transaction_begin_time
      ,dt.transaction_id,AT.name AS TranName
	  ,cx.PercentComplete,cx.MinutesRemaining
      ,d.log_reuse_wait_desc
      ,database_transaction_log_record_count, database_transaction_log_bytes_used
	, database_transaction_next_undo_lsn
	,CASE AT.transaction_state
		WHEN 0 THEN 'Not Completely Initialized'
		WHEN 1 THEN 'Initialized but Not Started'
		WHEN 2 THEN 'Transaction is Active'
		WHEN 3 THEN 'Read-Only tran has Ended'
		WHEN 4 THEN 'Distributed Tran commit process has been initiated'
		WHEN 5 THEN 'In prepared state and waiting resolution'
		WHEN 6 THEN 'Transaction has been committed'
		WHEN 7 THEN 'Transaction is being rolled back'
		WHEN 8 THEN 'Transaction has been rolled back'
		END AS TranState
      FROM sys.dm_tran_database_transactions dt
		LEFT OUTER JOIN sys.dm_tran_active_transactions AT
			ON dt.transaction_id = AT.transaction_id
		INNER JOIN master.sys.databases d
			ON d.database_id = dt.database_id
		Cross Apply (SELECT TOP 1
			 [LogDate]
			,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
			,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
			,DB_NAME(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1,CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) ) AS DBName
			,CAST(SUBSTRING([TEXT], CHARINDEX('(', [TEXT]) + 1,CHARINDEX(')', [TEXT]) - CHARINDEX('(', [TEXT]) - 1) AS INT) AS DBID
			FROM @ErrorLog ORDER BY [LogDate] DESC) cx
		WHERE d.state_desc <> 'online'
			And cx.dbid = dt.database_id

Unfortunately, this query does not demonstrate the time remaining for the rollback nor the percent complete without needing to query the error log. Those would be awesome additions if you know how to do it (and let me know), other than via the error log. Thanks to a blog post by Tim Loqua for the base info on querying the error log for the percent complete.

I think the key component on this query is the LEFT OUTER JOIN to sys.dm_tran_active_transactions. This is essential since the recovery is shown in two transactions. One transaction is numbered and is the placeholder for the un-numbered transaction where the work is actually being done. In the numbered transaction, you should see a transaction name of “Recovery Allocation Locks” and nothing for the unnumbered transaction.

Now, unnumbered is not entirely accurate because that transaction has an id of 0, but you will not find a correlating transaction for that in the sys.dm_tran_active_transactions DMV.

The transactions displayed here will be displayed until recovery is complete. That also means that if you really wanted to, you could create a table to log the recovery process by inserting the results from this query into it. Then you could revisit the table and examine at closer detail what happened during recovery.

The anxiety killer from this query is to watch two columns in the unnumbered transaction. These columns are database_transaction_log_record_count and database_transaction_next_undo_lsn. I rerun the query multiple times throughout the process of recovery. I check those columns to ensure the data in them is changing. Changing results in those fields means that you are seeing progress and can provide some comfort by seeing actual progress (even though we know in the back of our head that it is progressing).

System Base Tables

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: MCM, SQL Internals

Comments: No Comments

Published on: January 30, 2012

On January 19th, I published a post about the Dedicated Administrator Connection. I spoke very briefly of the system base tables in that article. Today, I want to dive into these tables a little bit more.

First, let’s get the Microsoft definition for these tables. ”System base tables are the underlying tables that actually store the metadata for a specific database.”

Have you ever queried sys.objects from the master database and wondered about some of the results? You can see all of the System base tables when querying the sys.objects view. These tables are denoted in sys.objects by type of ‘S’ and a type_desc of ‘SYSTEM_TABLE’.

Here is a simple query to take a quick peek at these tables.

Code block

SELECT * FROM sys.objects
	WHERE Type = 'S'
	ORDER BY NAME

There is no need for a Dedicated Administrator connection in order for this query to work. You can view these results with a non-DAC connection to the instance so long as you have adequate permissions to query sys.objects. That said, not all objects returned by that query are System Base Tables. Furthermore, it appears that the list from MSDN is not comprehensive. One such example is the reference to sys.sysserrefs that does not appear to exist in SQL 2008 R2 and the missing System Base table called sys.sysbrickfiles (which is used by sysaltfiles as shown in this execution plan).

If I try to query the sysbrickfiles table (as an example) without connecting via DAC, I will get an error message like this:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysbrickfiles'.

This is normal behavior. You cannot query the system base tables without first connecting via DAC. Having said that, the obligatory warning is required. As explained on MSDN, these tables are intended for use by Microsoft. Proceed at your own risk and please make sure you have backups.

In addition to these System Base tables, you will find tables not mentioned in the article nor in the master database. These System Base tables are found within the Resource database. The resource database does contain most of the tables mentioned in that article, but there are some differences. I will leave that discovery exercise to the reader.

There is plenty about SQL Server that many of us take for granted. Under the hood, there is much more to learn. Taking a peek at the System Base tables is one of those areas that will help you to learn more about SQL Server. My question is this: How far are you willing to explore to learn more about SQL Server?

Dedicated Administrator Connection

by Jason Brimhall

Categories: News, Professional, SSC

Tags: DAC, MCM, SQL, SQL Server

Comments: 2 Comments

Published on: January 19, 2012

Recently you may have read my article about some hidden functions in SQL Server. In that article you learned that those functions were in some DMOs and that you could get at them through the resource database.

Today I found myself learning more about the resource database. Due to what I had learned in my prior foray into the resource database, I was curious if certain other functions might call some hidden functions in that database.

Sadly – they did not. But in my travels I did happen across something else that is in that database. Those items are called system base tables. Unlike the trio of functions from the last article – you can get to these but it is STRONGLY advised to not do it.

Naturally, I want to check these tables out – especially since the MSDN article does say how to get to them. I will write about some adventures into looking at these tables in the future. I already found one interesting thing that seemed odd – but first I will need to login using the DAC and start testing to confirm a hypothesis.

For now, I want to cover how to create a Dedicated Administrator Connection. This should be something that DBAs know how to do. It isn’t difficult, and I will only cover one method and leave the other method to the Microsoft documentation.

You can create a DAC through either SSMS or through SQLCMD. You can create one remotely, but you will need to enable that option since it is disabled by default. You can find the method for creating this connection via SQLCMD here.

To create a connection through SSMS, it is rather easy as well. You simply add (case insensitive) “admin:” to the beginning of your server as shown in this image.

In order for this to work, you will need to have the browser service running. If it is not running, you will get an error message. This error message is informative if you read it. It will provide a clue to look at the browser service.

Once you have successfully created this connection, you can now use it when necessary to perform administrative tasks or for some learning opportunities. If you open a query using this connection you will see something like this next image in your query tab.

You can see in the tab of this query tab that there is the label “ADMIN:”. This is your DAC connection. You are limited to one of these at a time – period.

If you try to create a second connection, you will get a nasty message. The message is not entirely informative – just understand that you are getting it because you already have a DAC open.

It is a good idea to become familiar with how to connect via the DAC. I have a connection saved for quick access. Luckily I have a development server which I can test and use for learning opportunities. As the warning MSDN states: “Access to system base tables by using DAC is designed only for Microsoft personnel, and it is not a supported customer scenario.” If you venture into the system base tables via the DAC – Microsoft will not support it if you break it.

MCITP: 4 Down 0 To Go

by Jason Brimhall

Categories: News, Professional, SSC

Tags: Certification, MCM

Comments: 9 Comments

Published on: December 7, 2011

Yay. I finally took the plunge and decided to take Exam 70-451. This is the MCITP exam for the SQL 2008 Dev track.

Frankly, I had taken my time with this one because I was a bit concerned after taking the 70-450 exam. I saw that exam was more difficult than its MCTS counterpart and fully expected the 70-451 exam to be more difficult. And based on that assumption I wanted to devote some time to study.

So here is what I did to study:

…

Well, I did not find the time to study and decided to just get it done. I took the exam cold. And not only was it cold by means of not studying, the building did not feel like it was heated and I walked out, after finishing the test, a popsicle. Add to that the next level of cold – I was up until 2am working. Then we throw in a snafu created by Prometric (happens every time though) and a 20 minute wait to start the test. Things could have been better going into this exam.

Needless to say, experience pays off with this exam. The biggest piece of the exam is based on practice and not so much the semantics of the code. That said, I did have three questions that were impossible to answer. The question description and requirements immediately eliminated all of the answers. Needless to say, I did not like those questions and left ample comment about them.

Otherwise, the exam was successful. I passed missing only 5 questions (by my calc). Now I am off to start down the 2008 BI track followed by the MCM Knowledge Exam.

If you are interested here is an excellent resource to study. Yes, I actually looked at the topics to be tested prior to taking the exam. I felt comfortable in most areas and felt I could handle the exam too.

And here is my study dump:

…

Did you really think I was gonna give you a dump of the exam?

Powershell, Me and the MCM part II

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Certification, MCM, Powershell, Professional, SQL

Comments: 1 Comment

Published on: December 20, 2010

Last week I posted the first part of this series. I thought it would be a good idea to give a little follow-up on how my foray into Powershell as I attempted to download this videos.

Attempt 1: Failed miserably. I did not have enough disk space and thus only downloaded about half of the videos. I tried to remedy this by changing the path to a different volume but that threw out some error messages. The script works best when continuing to use the $env variable in the path for the location to dump the files.

Attempt 2: From a different machine this was working beautifully until I noticed that every once in a while a file would error out and the download would resume with the next video in the list. When I attempted to look up those particular videos, they were all present. So I restarted thinking it was a local environment problem.

Attempt 3: I figured out that attempt 2 was failing because my laptop was going to standby – duh!!! I disabled the standby and finally got the download to work from start to finish without fail.

Now, I can place these videos on my Android and watch/listen from anywhere I have my phone. Since I have an auxiliary port in my car, I can just plug the phone in, let it charge, and listen to the videos while I commute – through the car speakers. It is nice to have such a high-level of training material available for a quick refresher or for that first time through. I recommend people get on board and take advantage of what Microsoft, Paul Randal, and others have given to the community through this training. Since I pay out of pocket for my training – this is the right price for me.

Thanks for the Christmas Gift.

Powershell, Me and the MCM

by Jason Brimhall

Categories: News, Professional, SQLServerPedia Syndication, SSC

Tags: Certification, MCM, Powershell, Professional, SQL

Comments: 3 Comments

Published on: December 16, 2010

If you haven’t heard yet, Microsoft has made some significant changes to the MCM program. The changes make the certification more accessible to the masses.

You can read more about that from sources such as the following:

This post is not so much about the changes. More it is about the information dissemination related to the MCM. I saw on twitter that there were some videos available to watch concerning the MCM training. I was curious where these videos were so I decided to go looking for the videos. In my search for the videos, I came across the Blog post referenced above by Jason Strate. In that post, he has a link to the videos. I decided to check out the videos and I decided to start downloading them so I could use them as study aids as I work on the MCITP.

Downloading those videos one by one is somewhat tedious and time consuming. Thus while some were in the process of downloading, I started working on a few other things and saw another twitter post come across about a Powershell script to download those MCM training videos. This is great news – except I have never run a powershell script.

Well, no time like the present to learn. First thing to do is to check out the blog article about the script – it has a few downloads. The blog post is by Eric Humphrey, and can be found here. After having read what he had to say about the script and downloading the necessary files that he lists, it was time to find out how to run a powershell script. A little looking and I came across this article.

Excellent I am well under way now to run this script. After following some of the recommendations in that article (e.g. security settings), it was time to look at the script and see what I needed to know from there, such as paths required for add-in files or paths for destination files. Some quick adjustments to the userprofile path and to the download directory, as well as copying the HTMLAgilityPack into the userprofile path – and we are all set.

Now I have the script running and downloading the files – but it looks like it is going to be running for a long time. This is a very useful tool for me at this moment. This demonstrates another use for Powershell as well. I haven’t yet decided that I will pursue the MCM, however I will use these videos to improve my knowledge base on SQL server. I would recommend that people go check out these videos. It was somewhat surprising to me to see that many of the pages had not been viewed very frequently at all. This is great training material. I recommend getting it and adding it to the reference material you may already have.

If you are just looking to browse the videos individually, you can find the site here.

page 1 of 1

M	T	W	T	F	S	S
« Apr
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31

SQL RNNR

Summit 2012

Do you Dream?

Database In Recovery

What do we do?

Anxiety Tranquilizer

System Base Tables

Dedicated Administrator Connection

MCITP: 4 Down 0 To Go

Powershell, Me and the MCM part II

Powershell, Me and the MCM

Now Reading

Planned books:

Current books:

Recent books:

Popular Posts

Blogroll

Categories

SQLHelp