SQL Server No comments yet

SQL Server: Error 15404 State 19 and Error 28005 State 2

By on November 12th, 2012

Today, I noticed a server which was experiencing a large number of context switches (40,000) as well as a large amount of waits on various Preemptive_OS metrics.  There were no active SQL statements running on the server, but the following message was repeated numerous times per second in the SQL Server Error Log:

An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user ‘<Domain>\<user>’, error code 0×5.

Also associated with the above error:

Error: 28005, Severity: 16, State: 2

Upon reviewing this with the application owner, it was learned the user above was locked.  After unlocking the user and waiting approximately 5 – 10 minutes, the context switches dropped and SQL statements began running.

SQL Family, SQL Server No comments yet

SQL Family – A local “thank you” goes to . . .

By on October 24th, 2012

It’s been a week since my first thank you to a great SQL mentor.  This week the series continues and another great SQL Server guru gets a well-deserved “thank you.”

Maybe it’s just me but when I first started using Twitter, I always tried to find someone local who shared the same interest.  This did not change with my transfer from network administration to database administration.

It didn’t take long to find someone local who not only shared the same interests, but excelled at it too.  It’s one thing to have awesome SQL Server skills.  It’s entirely another to transfer that knowledge to others with care and patience.  To be totally honest, I even stuck my foot in my mouth once and she kindly let me know.  Ooops!

Erin Stellato

The Awesome Erin Stellato

Wait, did I say “she”?  Why, I haven’t told you who I’m talking about!  I’m talking about Erin Stellato!

Soon after following Erin (blog | twitter) on Twitter, PASS Summit 2011 began and I watched her tweet about friendships, fun, and technology.  She shows a passion for all three and the SQL Server community should be very thankful to have someone like her involved.  In fact, she is one of the leading reasons we have a SQL Family.  Without leaders like Erin, we would simply be a group of geeks with no vision.

Erin’s passion not only revolves around technology.  She is also a great exercise motivator and volunteers her time at the Ohio North SQL Server User Group.  Little does she know it, but Erin is my role model in how I can teach others about SQL Server and how I would like to contribute to the community.

Erin,  I would like to thank you for the hard work and passion you show to others.  It hasn’t gone unnoticed and I hope a little recognition will brighten your day.  You have some awesome skills and I’m so thankful you are willing to share your knowledge and patience to those of us with rougher edges!  Without a doubt, you are a key figure in our SQL Family.

 

SQL Family, SQL Server 4 comments

SQL Family – My first “Thank You” goes to . . .

By on October 17th, 2012

Today, I’d like to begin my first series of blog posts, simply titled “Thank You!”  The reasoning for this series came to me in the form of a flash (i.e. an A-Ha! moment).

When I first became a SQL Server DBA, I wrongly assumed this would be your average administration style job with some off-hours support.  I had a Twitter account, but never used it.  Frankly, I did not understand what the fuss was about and let my Twitter account sit idle.

The Great Brent Ozar

After landing the job, I really wanted to shine. I found out very quickly that I didn’t know as much as I had originally thought. After hitting the search engines, I learned of the Twitter hashtag #sqlhelp. I asked questions and one name kept coming back with replies. Fast forward a few years and I inquired if anyone had tested various FusionIO cards and guess who came back with some quick replies?  At that moment, I realized it wasn’t enough to just send a “thank you” tweet in reply.  Instead, I decided to start a new series to let people know how much I appreciate what they do for others in the SQL Family.

Today, I would like to thank Brent Ozar!

After following Brent (blog|twitter) on Twitter, I realized he not only tweets about SQL Server, but has a really great time along the way.  I laughed at many of his tweets, which provides stress relief as well as keeps me well prepared to tackle issues.  Additionally, I would respond jokingly to some of his tweets, to which I’m sure he wondered who I was and why I felt the need to respond.

I have watched several of his webcasts and have learned significantly from them.  Also, his blog has some really great posts, not only from a technical perspective, but from a professional development perspective as well.

Brent, I would like to send some long overdue, sincere appreciation your way. You have no idea of the impact you made on my career and my passion for SQL Server.  I have watched you assist people on Twitter, through your blog, and on your webcasts with utmost respect and confidence.

Brent, thank you for all you do.  Many times, a huge amount of work and effort goes unnoticed and today I’d like to turn the spotlight your way in recognition of the many things you do to make the SQL Family a welcome place to be!

 

SQL Server No comments yet

Reading PBM Execution History

By on August 14th, 2012

I’ve had a recent need to read the execution logs generated by SQL Server 2012’s Policy-Based Management.  Fumbling through xp_readerrorlog, I learned these logs are not accessed by normal methods.

Instead, the execution histories are stored in the msdb database, as I learned by using SQL Profiler.  I modified the script gleaned from SQL Profiler into the following script, which gathers the last 30 days for all policies:

SELECT spp.NAME AS [Policy Name]
,h.start_date AS [Start Date]
,h.end_date AS [End Date]
,h.result AS [Result]
,ISNULL(h.exception, N”) AS [Exception]
FROM msdb.dbo.syspolicy_policies AS spp
INNER JOIN msdb.dbo.syspolicy_policy_execution_history AS h ON h.policy_id = spp.policy_id
WHERE h.start_date > DATEADD(day, – 30, GETDATE())
ORDER BY spp.NAME ASC
,h.start_date DESC
,h.end_date DESC

Note that [Result] = 1 when successful, so you can find unsuccessful executions only by adding to the WHERE clause:

h.result <> 1

Additionally, for any [Result] <> 1, the [Exception] column should be populated.

PowerShell, SQL Server 1 comment

Using TSQL to script file renaming

By on July 25th, 2012

Today on the Twitter hashtag #sqlhelp, someone asked for a script that would rename data and log files so they matched their logical file name.

I love a challenge and have come up with the following script.  Note you will need to run the output of the script below in a few steps:

  1. Set the database offline
  2. Run the powershell output to rename the files
  3. Run the remaining TSQL to lineup the master database with the new filenames
  4. Set the database online

Someone also asked for just a snippet to generate the PowerShell, so that is included as well.

Script_PS_TSQL_to_rename_files

Script_PS_to_rename_files

Ham Radio, SQL Server No comments yet

My Need for Immersion

By on June 25th, 2012

I’m a DBA by choice, but I’ve always been about knee deep in SQL Server, mainly due to my employer being heavily reliant on Oracle.  That said, SQL Server databases outnumber our Oracle database by a wide margin (3:1) , yet it’s still considered the minority system by most, but obviously not by me.

Considering the above scenario, I decided to enter a contest for admission to a SQLSkills Immersion Training event, sponsored by SQL Sentry.  I’ve been wading in the waters long enough and would love to go headlong into this pool and expand my knowledge and skills.  I cannot think of a better way to do this.

It makes perfect sense considering my hobbies outside of SQL Server:

  1. Ham Radio – You must know how things work to be licensed.  I like to take it a step further and understand how my radio and antenna systems work in my environment to make them work efficiently.  It saves power and interference to TVs and radios.
  2. Motorcycling – I’ve been to several classes in order to be a better motorcyclist.  Learning to navigate tight turns and going over rough terrain.  I also maintain the bike myself by experience and learning how to do new things, like replacing brakes.

In both of these scenarios, it’s easy to see that I like to learn how things work under the covers, even if I won’t use it every day.  It’s what makes us better overall in our chosen profession and hobbies.

There are people who choose a profession and do just enough to get through.  On the other hand, there are those that want to know as much as possible and do whatever it takes to make it happen.  I fall in the latter category.

Here’s hoping I win!

SQL Server No comments yet

Firewalling SQL Server on Windows 2008

By on March 26th, 2012

One item that we had never enabled on our SQL Server machines was the remote dedicated administrator connection.  If you don’t know what it is or why you need it, here are two links to get you started:

At the end of the day, you’ll want this enabled for when things are going bad.  Do this on a good day and not when disaster strikes!

After enabling, I learned it picks a dynamic port to listen on.  That means I must look at the ERRORLOG file and change the Windows firewall accordingly.  If the port were to change on restart, I’d have to revisit the firewall configuration again.

That’s not what I call “ease of use” and the last thing I need to be doing when I need the DAC is to configure the firewall.  So I changed my firewall rules to point to the sqlservr.exe program instead.  Upon testing, it works just fine.

To do this, you can use the GUI or use the following template below in a command prompt window:

netsh advfirewall firewall add rule name=”SQL Server <instance name>” dir=in action=allow program=”<path>\sqlservr.exe”

Don’t forget you also need to allow SQL Server Browser:

netsh advfirewall firewall add rule name=”SQL Server Browser” dir=in action=allow program=”<path>\sqlbrowser.exe”

SQL Server, VMware 1 comment

Updated SSRS Reports for VMware

By on March 23rd, 2012

In a previous post, I blogged about SSRS reporting for VMware.  I have added a lot of new reports and updated others.  As such, you’ll find the updated files below.

Note the following about the reports / procedures:

  1. For the software licensing report, it relies on a description/annotation of the VM in the format of “#???#”.  For example, #SQL# may refer to your SQL boxes.
  2. For the datastore trending report, you must schedule the spLocal_DS_Trending_Insert stored procedure via SQL Server Agent.
  3. Until I can figure out an alternative, the snapshot report will only show snapshots less than the taskMaxAge parameter set in the vCenter client (assuming that you have tasks being aged).
Linux No comments yet

Determine memory size in Linux

By on February 25th, 2012

As many already know, you can use:

free -t

to determine how much memory you have and how much is used in Linux.

Today, I needed to find out the size of each memory stick in the machine.  Some googling about, and I stumbled upon an answer:  my old friend dmidecode.

I ran this on OEL 5, but should also work on modern distributions.

For all memory sticks, including slots with no memory installed:

dmidecode --type 17 | grep Size

For just the slots with memory installed (unordered and generally unreadable if you have multiple size sticks):

dmidecode --type 17 | grep Size | grep MB

For a grouped list of each specific size:

root@host1:/root> dmidecode --type 17 | grep Size | grep MB | sort | uniq -c
 3 Size: 2048 MB
 3 Size: 4096 MB

In the last case, it shows I have 3 x 2GB and 3 x 4GB DIMMs installed in my server.

Cisco No comments yet

Cisco ASA 8.4.3 on ASA5505

By on January 20th, 2012

Cisco recently released ASA software 8.4.3.  I have an ASA5505 I use at home and keep it up to date, so I immediately downloaded the software along with ASDM.

This upgrade went horribly wrong.  I first upgraded ASDM and relaunched it to get the new software on my local machine.  I uploaded the latest ASA software and suddenly my ASA would not boot.

Connecting the console cable, I saw the device boot to the point where it should identify the ports and continue booting.  It would hang, and sometimes output a cryptic character, similar to what you’d see if you peeked at the contents of a binary file through a text editor.

I rebooted the device several times to no avail, including unplugging it.  Since I kept the 8.4.2 image on the disk, I simply rebooted, broke the boot sequence, and issued:

boot disk0:/asa842-k8.bin

The device came up normally.

So I suspected the file on the disk may be corrupt, so I deleted it and re-uploaded again.  Once more, the device wouldn’t boot with the same issues.  However, now I couldn’t get it to boot 8.4.2 either.  I unplugged it and thought for a few and then plugged it back in and tried to boot 8.4.2.  Fortunately, it booted this time.

I changed the default boot image to 8.4.2 and rebooted several times with success.  Next, I went into ASDM and told it to check Cisco’s site for a new image.  Of course, it found it and downloaded it directly.  This time, the upgrade was successful.

Was my inital image corrupt?  It didn’t appear so by byte size as they were exactly the same, but I didn’t check MD5s.