SQL Server with Mr. Denny


January 23, 2014  9:56 PM

SQL PASS BA Conference 2014 Discount Code

Denny Cherry Denny Cherry Profile: Denny Cherry

I’m pleased to say that I’ve been able to get a discount code setup for all my clients and blog post readers to get you $150 off the cost of entry to the SQL PASS BA Conference. To use the code simply register for a new registration and enter the discount code “BABN9H” in the discount code field. And that’s it. You save $150 off the registration fee for the PASS BA Conference.

If you don’t know what the BA Conference is, it is the premiere Business Analytics / Business Intelligence conference and unlike most conferences it is put on by data professionals not venders who have an agenda to push on you.

Denny

January 22, 2014  7:00 PM

Changing your Password on Windows 2012 via RDP

Denny Cherry Denny Cherry Profile: Denny Cherry

I ran into a problem at a client recently that I’m shocked that I haven’t run across before. I couldn’t figure out how to change my domain password when connected to their servers via remote desktop. You see the problem is that 99% of the time when I’m working with a client, either at their site or from my home I’m using my own machines to work, and simply connecting to their servers via remote desktop. This means that when I press control+alt+delete to get the change password option that option is for my local machine not the remote machine. As there’s no start menu on Windows 2012 the good old “Windows Security” option isn’t there when connected remotely. All the official help docs tell you to simply press control+atl+delete to get the change password option. Well that doesn’t work very well via RDP.

Thankfully you can simply press control+alt+end instead and that will bring up the same menu as control+alt+delete but on the remote machine so that you can actually change your password and stop using the crappy default password that the help desk setup for you.

If you run across this, hopefully you’ll remember this little trick.

Denny


January 17, 2014  2:00 PM

Recommended reading from mrdenny for January 17, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


January 15, 2014  2:00 PM

Fixing TempDB database problems when starting SQL using a minimum config

Denny Cherry Denny Cherry Profile: Denny Cherry

So the other day I typo’ed something. Usually this isn’t that big a deal, but in this case when fixing tempdb for a customer I told SQL to put two logical files into the same physical file. I was able to get it to let me do this because I first added the files, then realized that I had put them on the wrong drive so I just changed them using an ALTER DATABASE tempdb MODIFY FILE command to move them to the correct drive. Then I had the client restart the SQL Server later when it wasn’t being used.

When SQL tried to come online however there were some lovely errors in the log and SQL wouldn’t come up.

2013-12-08 13:59:38.23 spid9s Error: 5161, Severity: 16, State: 1.
2013-12-08 13:59:38.23 spid9s An unexpected file id was encountered. File id 3 was expected but 9 was read from “E:\TempDBs\tempdev2.ndf”. Verify that files are mapped correctly in sys.master_files. ALTER DATABASE can be used to correct the mappings.
2013-12-08 13:59:38.24 spid9s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.

This was a problem.

So I fired up the SQL Server using the “-c -f -m” parameters. This bring the SQL Server up as a command line application (-c), using a minimal config (-f) and in single user mode (-m). However when I tried to run the ALTER DATABASE tempdb MODIFY FILE commands all I was getting was an error about the tempdb not having the files that I was looking for. Apparently when you start SQL Server using a minimal config it only uses the first two tempdb database files, so I wasn’t able to make changes to the files.

Thankfully there’s another way to fix this sort of problem, but it isn’t for everyone. I had to go and edit the master.sys.master_files catalog view manually. Now thankfully it’s a lot easier then it seems, it’s just update statements. But you are using just T-SQL, probably in sqlcmd (at least that is what I was using) to make the changes. Now normally you can’t change catalog views manually using T-SQL. To make these changes you have to connect to the database engine using the DAC or the Dedicated Administrative Connection. Now if you are in a situation where you have to edit this stuff be VERY careful. If you run an update statement without a WHERE clause in here you’ll destroy every database on the system. But for those brave enough to try it, it’ll get the job done.

After running a bunch of update statements for files 3-9 on database_id 2 (which is the tempdb database on every SQL Server out there) a quick restart of the SQL Server service and the customer was back up and running again.

Denny


January 10, 2014  8:28 PM

Recommended reading from mrdenny for January 10, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry

Ad 

This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: markvsql also known as Mark Vaillancourt

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


January 7, 2014  8:46 AM

SQL 2012 Cluster, Windows 2012 R2 OS, Windows 2008 R2 Domains

Denny Cherry Denny Cherry Profile: Denny Cherry

Recently I was installing SQL Server 2012 on a new Windows 2012 R2 cluster for a client and ran across a bit of a problem. When the SQL Installer was attempting to start SQL Server for the first time SQL Server was throwing Windows errors 1069 and 1194 which basically say…

Cluster network name resource ‘%1′ failed to create its associated computer object in domain ‘%2′ for the following reason: %3.

The text for the associated error code is: %4

Please work with your domain administrator to ensure that:
– The cluster identity ‘%5′ can create computer objects. By default all computer objects are created in the ‘Computers’ container; consult the domain administrator if this location has been changed.
– The quota for computer objects has not been reached.
– If there is an existing computer object, verify the Cluster Identity ‘%5′ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.

What this basically means is that the account which is trying to create the computer account within the domain doesn’t have the correct permissions needed to create the account. Now in this case the domain has a domain group called “Add Computers To The Domain” which has the “Create Computer” right in all OUs within Active Directory. Adding the CLUSTER$ account for this cluster to this group did nothing. The reason for this is because this group has only a single right “Create Computer”. For Windows 2012 R2 clusters to be able to successfully create computer objects in Active Directory the CLUSTER$ account needs to have what is considered to be “Read” permissions. Specifically this is three different permissions on the OU which are:

  • List Contents
  • Read all properties
  • Read Permissions

In addition this account needs to “Create Computer” right as well.

My solution in this case was to create a new group in Active Directory and grant that group these four rights to the group. This way if the problem comes up again for new clusters (which I’m sure that it will) the fix will simply be to drop the computer account for the cluster into the group, wait for domain replication to finish, then try starting the client access point again.

My solution in this case was to grant the domain group rights to the OU which the SQL Server objects where going to be created in within Active Directory. For some reason when attempting to grant this permission through a group the permission wasn’t granted correctly.

Much thanks to Allan Hirt (@SQLHA) for validating that I’m not crazy and that these were the correct permissions at midnight my time which was 3am his time.

Denny


January 6, 2014  2:00 PM

SQL Saturday Nashville Pre-Con and Sponsorship

Denny Cherry Denny Cherry Profile: Denny Cherry

I’m pleased to be able to report that I’m giving a pre-con on SQL Server Performance at SQL Saturday Nashville. The sign up page is available here.

In addition to giving a pre-con I’m also signed up as a Silver sponsor, so I’ll have a booth at the pre-con setup where you can come talk to us about the services that we offer or get some technical advice to fix problems at work.

I hope to see you at the SQL Saturday in Nashville.

Denny


January 3, 2014  2:00 PM

Recommended Reading from mrdenny for January 3, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry


This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.

This weeks SQL Server person to follow on Twitter is: NikoNeugebauer also known as Niko Neugebauer

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


January 2, 2014  2:00 PM

Why would I want to use a non-durable in memory table?

Denny Cherry Denny Cherry Profile: Denny Cherry

With SQL Server 2014 we were introduced to in memory tables, and specifically the ability to use non-durable in memory tables. These non-durable tables, are special case tables. The schema will exist after the database engine is rebooted, however the table will be empty after the server is rebooted.

These tables are great for filling a specific need, and are useless for other needs. The need that these tables fill is processes such as staging tables in data warehouses, logging tables, and that’s just about it. These tables are fantastic when loading data where the data can be lost, or when the data can be recreated on the fly. This is why they are fantastic for loading tables for data warehouses because by their nature the data being written to loading or staging tables can be reloaded from the source system at a moments notice.

For production tables within an OLTP system these non-durable tables are a really bad idea. If you were to use non-durable tables in a production OLTP database, under most situations if the data was lost this would end up being a really bad idea because data would be lost when the SQL Server instance is rebooted.

So as you continue to look into SQL Server 2014 and as you begin to work with the in memory tables, be sure to understand the difference between the durable and non-durable tables and when to use each one.

Denny


January 2, 2014  2:00 PM

Blog Year 2013 In Review (Sort Of)

Denny Cherry Denny Cherry Profile: Denny Cherry

Welcome to 2014. For the last couple of years I’ve done a top posts of the last year post. This year will be no different.

What is different between this year and prior years is that this year I’ve got really bad data compared to prior years. I’ve got great data starting 10/3/2013 to current, but the data before that is … we’ll say lacking in detail.

Without waiting any longer the top posts in 2013 are…

10. What Exactly Is MSDTC And When Do I Need It
9. Will SQL Performance Be Better In A VM?
8. What Should Happen To Blackberry
7. If you could give an aspiring DBA just one piece of advice what would it be?
6. TempDB Latch Timeouts With Lots of RAM
5. SQL PASS 2013 Webcast Recording
4. How to Configure DTC on Windows 2008
3. Replication and VLFs
2. Seeks Aren’t Always Better Than Scans
1. Moving the SCCM Database

Comparing my top posts to last year we see a log of changes in the top 10 (unlike between 2011 and 2012).

The only posts which stayed in the top 10 from last year are:
1. How To Configure DTC on Windows 2008
4. What Exactly Is MSDTC And When Do I Need It

Based on this it sounds like (at least some people out there) are liking my new posts, which is good.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: