SQL Server with Mr. Denny


January 29, 2014  10:42 PM

eForensics Magazine SQL Server Issue

Denny Cherry Denny Cherry Profile: Denny Cherry

The digital magazine “eForensics Magazine” has put together their 1st issue talking specifically about SQL Server security.  There are articles written by a bunch of SQL Server experts including the two articles which I have in the issue.  There’s a free teaser of the issue which you can download as well (it’s down at the bottom of the page) before you buy.

Denny

January 29, 2014  2:00 PM

Log Shipping Databases and Permissions Problems

Denny Cherry Denny Cherry Profile: Denny Cherry

Recently I was doing log shipping for a client in preparation to move their databases from one data center to another, when I was asked to change the drive that one of the target databases was being restored to.

No problem, I’ll just use ALTER DATABASE … MODIFY FILE (name=’xxx’, filename=’yyy’) to change the files, stop SQL, move the database files, and restart no problem.

Well apparently when doing this I managed to not reset the permissions correctly but only on the ndf file. Because SQL was able to access the MDF the database came up on NORECOVERY waiting for more logs to be restored. But when the next transaction log file was attempting to be restored, the error message that I got was not all that helpful to the problem at hand.

Msg 4319, Level 16, State 5, Line 1
A previous restore operation was interrupted and did not complete processing on file ‘xxxxxxx’. Either restore the backup set that was interrupted or restart the restore sequence.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

I tried restoring the older log backup and that didn’t help any. So I went and looked at the permissions again, and apparently SQL didn’t have write access to the NDF file so it wasn’t able to roll the logs forward. Thankfully I noticed the problem right away when I manually did the next log restore otherwise this could have been a nightmare to track down given that oh so helpful error message.

Denny


January 24, 2014  2:00 PM

Recommended Reading from mrdenny for January 24th, 2014

Denny Cherry Denny Cherry Profile: Denny Cherry
EMC Arrays, EMC VNX, SQL, SQL Server, VMFS, VMworld, VMworld 2013

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: DoktorKermit also known as Kenneth M. Nielsen

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 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


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: