57 votes
Accepted

Slower SQL Server performance after allocating more CPU and RAM

There's a lot going on here, and most of it is pretty broad and vague. 2008R2 RTM came out on April 21, 2010. It's totally out of support. You'll want to prioritize getting on the latest Service ...
Erik Darling's user avatar
  • 40.5k
22 votes
Accepted

Increased memory to server, but do I need to change page-file size?

I believe the 2x size of RAM recommendation is outdated and unrealistic. If a server only has 32 GB of RAM it might make sense, but it's a waste of expensive disk space to make it 2x on a system ...
Tony Hinkle's user avatar
  • 7,939
14 votes

SQL Server Service does not start automatically after system restart

One issue that can cause this is that some systems, particularly VMs with the system volume on solid state storage, is that it boots up so fast and gets to the point of starting services before some ...
Tony Hinkle's user avatar
  • 7,939
11 votes
Accepted

SQL Server - Benefits of splitting databases across different logical drives

is there any performance benefits to keep the data files split across different logical drives? If all the volumes map to the same set of physical disks on the SAN there's normally no difference. ...
David Browne - Microsoft's user avatar
10 votes

Slower SQL Server performance after allocating more CPU and RAM

Yes! I've experienced this type of situation on SQL Server vms in our server farm. Look at the vm's host CPU ready time and the memory balloon driver counters. CPU READY TIME – BLOG PART I and ...
thundercougarfalcon's user avatar
9 votes
Accepted

Data warehouse server. How do you calculate RAM/CPU specifications?

Great question, and I did a session about this at TechEd a few years ago called Building the Fastest SQL Servers: https://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI328 In it, I explain ...
Brent Ozar's user avatar
  • 42.9k
9 votes
Accepted

Always On AG is Down when service is stopped

It could be the issue described in INF: AlwaysOn – The secondary database doesn’t come automatically when the primary instance of SQL Server goes down by Arvindh Kalidasan - Support Engineer, ...
Christopher Silva's user avatar
9 votes
Accepted

'The computer must be trusted for delegation and the current user account must...' error when trying to connect to sql server 2008

According to KB3000850, this affects domain-joined Windows 8.1 computers after installing security update 2992611 or update 3000850. I added the following value under: HKEY_LOCAL_MACHINE\SOFTWARE\...
boggy's user avatar
  • 427
8 votes
Accepted

Missing assembly when running PowerShell script using SMO

Using some information found in SQL Server PowerShell : How to Restore SQL Server Databases Using SMO and PowerShell, I was able to get past that error. Try adding the following to the very top of ...
Scott Hodgin - Retired's user avatar
8 votes
Accepted

Can't create database on volume mounted as folder (Error Msg 5123 & 1802)

Creating SQL Server Data files at the root of a volume is problematic, due to permissions issues. I always recommend creating a folder on the root of the volume, then placing data files inside that ...
AMtwo's user avatar
  • 16.1k
7 votes

Can't install Microsoft SQL Server 2016 Release Candidate 3 (RC3) on Windows Server 2008 R2

I found the answer here: Warning Setup Limited to Reporting Services for SharePoint by Alberto Morillo Which, in short, says: The only SQL Server 2016 features allowed to install on Windows Server ...
user2109254's user avatar
7 votes

Why is the processor % different in Activity Monitor vs Resource Monitor?

Resource monitor shows CPU from Processes and Services on two different graphs. (In your example, 32% + 37%, which means a total 69% CPU). As for SQL Activity Monitor, it shows CPU utilization for ...
Danielle Paquette-Harvey's user avatar
7 votes
Accepted

how to Enable these Windows Firewall rules using powershell?

You would use the Enable-NetFirewallRule cmdlet: Enable-NetFirewallRule -Name "WMI-WINMGMT-In-TCP", "WMI-RPCSS-In-TCP" In order to find the relevant rule names (in order to enable it), you can ...
Josh Darnell's user avatar
  • 29.2k
7 votes
Accepted

PowerShell command 'New-Item' fails when invoked through SQL Server Agent job

When accessing UNC paths, or any 'location' that isn't a local drive (i.e. registry), from a PowerShell job step, you need prefix the path with Microsoft.PowerShell.Core\FileSystem::. This tells SQLPS ...
HandyD's user avatar
  • 9,922
6 votes
Accepted

Is it possible to connect MS SQL Server Express 2012 through DAC Connection?

To use the DAC in Express, you need to enable startup trace flag 7806. This is documented here and in more detail here. Open Configuration Manager, in your SQL Server Services pane right-click the ...
Aaron Bertrand's user avatar
6 votes
Accepted

How many users are supported on SQL Server 2012 Express?

The limitation is for database not for instance. Plus 10G limitation is ONLY for data file, the log file can be 10.5 G or 20G for example. As per this BOL document it says that The actual number of ...
Shanky's user avatar
  • 19k
6 votes
Accepted

SQL Server Windows NT 64 bit Sudden High CPU Spike

There are a few ways to approach this. Download sp_WhoIsActive and run it when you notice a CPU spike, or log it to a table and query the table looking at the CPU column (maybe ORDER BY CPU DESC). ...
Erik Darling's user avatar
  • 40.5k
6 votes
Accepted

SQL Server CPU usage is overloaded sometimes

Is anyone able to shed some light on what might be going on, or guide me through some things to check. Let's start with the obvious stuff. Your server, for a production SQL box, isn't that highly ...
Erik Darling's user avatar
  • 40.5k
6 votes

when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools?

None of the services in your screenshot have anything to do with VMWare. The one you highlighted is Wmiprvse.exe which is basically WMI WMI resides in a shared service host with several other ...
Tom V's user avatar
  • 15.7k
6 votes
Accepted

when applying a sql server cumulative update or service pack on a virtual machine - what could be a safe way of stopping VMWaretools?

The WmiPrvSE.exe belongs to the service Windows Management Instrumentation and is located in the %WINDIR%\SysWOW64\wbem directory. However, when looking at the Task Manager compared to the details of ...
John K. N.'s user avatar
  • 17.6k
6 votes

Run Powershell Script From Agent As Administrator

Create a PowerShell proxy in SQL Server agent that uses a privileged Windows account via a SQL Server credential. CREATE CREDENTIAL docs. How to create a proxy That will allow you to run PowerShell ...
Hannah Vernon's user avatar
  • 70k
6 votes
Accepted

How do I prevent my Windows Server from being automatically rebooted by SQL Server updates?

To prevent SQL Server updates to be installed automatically, you need to: open up PowerShell window (Command prompt will also work) type "sconfig.cmd" and run it press 5 to select "...
Aleksey Vitsko's user avatar
5 votes

How to allow connections to SQL Server with 2 differents ip

This is the type of setup we use at the place I work for. I'll assume for the benefit of giving a better example, that your Aliases (CNAMEs) are configured like this: SQLINSTANCE.my.network -> ...
John K. N.'s user avatar
  • 17.6k
5 votes
Accepted

Missing options in Feature Selection when installing SQL Server 2016 on existing server

I don't think Sql Server 2016 will run on a Windows 2008 R2 server. See the requirements for OS here https://msdn.microsoft.com/en-us/library/ms143506.aspx#TOP_Principal
Scott Hodgin - Retired's user avatar
5 votes
Accepted

Fastest way to move MongoDB to a new server

Final solution: We went with option #2 - turning off MongoDB and manually copying the database files over to the new server. It worked like a charm. For future database migrations and changes, I ...
schneiju's user avatar
  • 131
5 votes

Slower SQL Server performance after allocating more CPU and RAM

One thing I didn't see pointed out, is that adding vCPUs to a VM can very often slow it down due to scheduling. The basic idea is that if a VM has 4 vCPUs, then the hypervisor must wait for 4 ...
briantist's user avatar
  • 151
5 votes

Cluster network name resource 'Cluster Name' failed registration

I have come across this issue with my dev environment usually when during the setup of the cluster, i skip the warning for network binding. When you run a cluster validation, do you receive any ...
samosql's user avatar
  • 530
5 votes
Accepted

MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

A similar issue, Information_Schema and mysql databases not shown, was submitted as a bug report to the MySQL Bug System nine years ago. The report was resolved as Not a Bug as the issue turned out to ...
Andriy M's user avatar
  • 22.9k
5 votes

Windows Logs Application is full of "Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: ****]"

It looks like your SQL Server is exposed to Public Internet, and someone is trying to brute force (hack) password from your SA account... I had the same situation when one of my SQL Servers was in ...
Aleksey Vitsko's user avatar
5 votes

How can I create a Windows Firewall rule to allow only one IP address to connect MS SQL 2017 Express database in Windows 2016 server?

Seems follow-up question of this. However, following are the recommended actions in your case which could be quick release from the cause: Define static custom port (other than 1433) for the SQL ...
Shekar Kola's user avatar
  • 2,407

Only top scored, non community-wiki answers of a minimum length are eligible