56 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 ...
  • 35.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 ...
  • 7,841
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 ...
  • 7,841
12 votes

SQL Server Cumulative Updates - not visible in Windows Update

Windows Update will only tell you about service packs for SQL Server, and only when you've enabled the "update other Microsoft products too" option (sorry, the wording has changed across versions, and ...
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. ...
10 votes

Changing Data Directory on MySQL DB Server

Open Windows CLI as Administrator and Shutdown MySQL net stop mysql Copy the Data to D:\ProgramData\Data xcopy /s C:\ProgramData\MySQL\MySQL Server 5.6\data D:\ProgramData\Data Create or edit C:\...
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 ...
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 ...
  • 42.2k
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, ...
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\...
  • 407
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 ...
7 votes
Accepted

The value could not be converted because of a potential loss of data

I have run into this a few times and the issue has been a mismatch of data types in the export/import parameters. First thing to do is check those parameters by right click on source or destination, ...
  • 86
7 votes
Accepted

Virtualized SQL Server Memory Oddity

What I am seeing is that in Task Manager, sqlserver.exe is using about 163 Mb of RAM, if I use procexp, the same service shows just under 500 MB of RAM used. You should never look at task manager to ...
  • 18.6k
7 votes

SQL Server 2012 - SA account probe

So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except ...
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 ...
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 ...
7 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 ...
  • 15.7k
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 ...
  • 28.3k
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 ...
  • 9,622
6 votes

Configuring SQL Server 2016 to allow remote connections

Figured it out! Since the server is running on a remote VMWare ESXi instance, the subnets are completely different. I changed: netsh advfirewall firewall add rule name = SQLPort dir = in protocol ...
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 ...
  • 18.6k
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 ...
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). ...
  • 35.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 ...
  • 35.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 ...
  • 15.5k
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 ...
  • 68.3k
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 "...
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 -> ...
  • 15.7k
5 votes
Accepted

SQL Server 2012 - SA account probe

SQL Server runs locally with IIS to serve its websites The main problem I see is having sql server and IIS running on the same server. IIS serves website and SQL Server should be on a different ...
  • 61.6k
5 votes
Accepted

How does SQL Server calculate the initial size of a (compressed) backup?

How does SQL Server calculate the initial size of a (compressed) backup? From BOL : For compressed backups, the size of the final backup file depends on how compressible the data is, and this is ...
  • 61.6k

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