Missing mssqlsystemresource files – SQL Server 2014

I recently did 4 installs of SQL Server 2014 on two different servers.   When I checked the services I saw that on SQL Server did not start.  This is the error in the log:

2017-02-14 14:30:00.11 spid12s     FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL12.ABC\MSSQL\Binn\mssqlsystemresource.mdf for file number 1.  OS error: 2(The system cannot find the file specified.).

2017-02-14 14:30:00.11 spid12s     Error: 5120, Severity: 16, State: 101.

2017-02-14 14:30:00.11 spid12s     Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL12.ABC\MSSQL\Binn\mssqlsystemresource.mdf”. Operating system error 2: “2(The system cannot find the file specified.)”.

2017-02-14 14:30:00.11 spid12s     Error: 17207, Severity: 16, State: 1.

The fix was simple, I copied the files from the other instance on the system and everything started up.  I have run into this before with other versions of SQL Server.  I have access to a lot of other systems so I can find them on other computers and copy them over.

 

Advertisements

Problems with installing SQL Server 2016, 2nd instance

Today I had a request to install two instances of SQL Server 2016 on a VM.  I have done this many times with other versions of SQL Servers and it is a very routine thing.  The First instance installed without a hitch and was successful.  So I started to install the second instance and it came up and said that I had to reboot.  While it is did not say to reboot after the install, this is not unusual.  So I rebooted the server and started my install again.  I got this error:

image1

C:\program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016\resources\1033\setup.rll is either not designed to run on Windows or it contains an error.  Try installing the program again using the original installation media or contact your system administrator or the software vendor for support.  Error status 0xc000012f.

What the heck, I just installed SQL Server from this same media.  After doing some searches on the web I tried to replace the setup.rll but without success.  So I decided to remove the setup.rll file with the idea that the install should replace that file.  I got this error:

image2

The English-language version of SQL Server is not supported by this SQL Server media.  Use the matching language-specific SQL Server media; or install the language specific MUI, and change the format and system locales through the regional settings in the control panel. 

Once again, I just installed with this media.  I checked my region and language settings and they looked right so I decided to apply SQL Server 2016 SP1 to the instance that I had already installed.  That went without any issue.  Once that completed, I started the install for the 2nd instance and it worked flawlessly.  Just thought I would post what worked for me, YMMV.  Good luck, be safe out there.

 

 

How to Hack Your Way into SQL Server

I work on a lot of different customer’s computers.  And once in a while I find a SQL Server that nobody knows the SA password and it is not in our database of passwords.  And of course I don’t want to go back to one of our customers and tell them that we don’t have access.  Or once in my case the DBA that was suppose to tell me about all of the SQL Servers “forgot” to tell me about a few servers and I found them after she was fired.  Or once I found some old test servers that people forgot about and I needed to get access to them.  And of course you don’t want any downtime.

What you need to do is simple, you need a program from SysInternals called PsExec.  You can download it here – https://technet.microsoft.com/en-us/sysinternals/bb897553.aspx – make sure you get it from Microsoft, they purchased Sysinternals a long time ago.  Place a copy of PsExec.exe in a path directory, your System32 directory would work, or if you want to run this from the command line put a copy in the directory you are working from.  You need to know the path to SSMS.EXE and the fastest way for me to tell you to find it is to look at the properties of SSMS icon, just highlight and copy the target path.

image

Your path will very depending on version and where you did your install.  In my case I put a copy in c:\windows\System32 and I just did a start –  run

                           PsExec -s -i “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe”.

If this is the first time you have run the PsExec program it will ask you to accept the license.  Say Agree.

You will now be prompted to log into your SQL Server instance using windows authentication.  Do it and you will be logged in as the NT AUTHORITY\SYSTEM account.  I then add an account or reset the SA password and then log in under that account. Hopefully you are not using SA to run any applications so be careful if you reset that password.  Adding an account is the safest thing to do. 

You now can log into your SQL Server without have to restart the services or having to reinstall SQL Server. 

Remember you need to be a local admin in order to log in as the NT AUTHORITY\SYSTEM.  Download PsExec and keep it in your tool belt.  You never know when you will need it.

Saving CPU information with a SQL Server SQL Agent Job.

I had a requirement to save the CPU data on a server.  You know managers like to have fancy charts.  I know that SQL Server saves the last 256 minutes worth of CPU data and I obtained a script from Glenn Berry (https://www.sqlskills.com/about/glenn-berry/) that would give me that information.  If you are not familiar with Glenn’s DMV scripts you should check them out.

start of code

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(256) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Date and Time]
FROM (
SELECT record.value(‘(./Record/@id)[1]’, ‘int’) AS record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’)
AS [SystemIdle],
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,
‘int’)
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE N’%%’) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);

That will give you the last 256 minutes of CPU usage.

end of code

I created a database called CPU and a table called CPUData (I am not real original about names)

Here is the table, I assume you can create a database in SQL Server.

start code

USE [CPU]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CPUDATA](
[SQL Server Process CPU Utilization] [int] NULL,
[System Idle Process] [int] NULL,
[Other Process CPU Utilization] [int] NULL,
[Date and Time] [datetime] NULL
) ON [PRIMARY]

GO

end of code

I then modified Glenn’s script to insert the last record into my table in a new SQL Server job that I set to run once a minute.

Start of code

SET QUOTED_IDENTIFIER ON –added this so  could run this as a job
DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

insert into CPU.dbo.CPUDATA
SELECT TOP (1) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Date and Time]

FROM (
SELECT record.value(‘(./Record/@id)[1]’, ‘int’) AS record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’)
AS [SystemIdle],
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,
‘int’)
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE N’%%’) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);

–I also loaded the last 256 minutes of data into my table to get things started – run this in QA:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);
insert into CPU.dbo.CPUDATA
SELECT TOP (1) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
SystemIdle AS [System Idle Process],
100 – SystemIdle – SQLProcessUtilization AS [Other Process CPU Utilization],
DATEADD(ms, -1 * (@ts_now – [timestamp]), GETDATE()) AS [Date and Time]

FROM (
SELECT record.value(‘(./Record/@id)[1]’, ‘int’) AS record_id,
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]’, ‘int’)
AS [SystemIdle],
record.value(‘(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]’,
‘int’)
AS [SQLProcessUtilization], [timestamp]
FROM (
SELECT [timestamp], CONVERT(xml, record) AS [record]
FROM sys.dm_os_ring_buffers WITH (NOLOCK)
WHERE ring_buffer_type = N’RING_BUFFER_SCHEDULER_MONITOR’
AND record LIKE N’%%’) AS x
) AS y
ORDER BY record_id DESC OPTION (RECOMPILE);

end of code

And now I just let my job run every minute and when I want to collect the data I will just run this:

select * from CPU.dbo.CPUDATA order by [Date and Time] DESC

CPU query

I thought about running my job just once an hour or two and changing it to top 60 or 120 and I might but for now I am done.

if you wanted to run the script every two hours you would change this line in the SQL job.

SELECT TOP (120) SQLProcessUtilization AS [SQL Server Process CPU Utilization],

My First Post

I wanted to start this blog to share some of the things that I encounter day to day as a production DBA.  It will also help me remember some of the things that I have done so I don’t have to reinvent the wheel the next time I have to do this.

I hope this blog will be a help to you.  I figure that if it was something that I encountered, someone else might have as well.

David Hutton