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 ...
user avatar
  • 34.2k
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 ...
user avatar
  • 7,783
13 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 ...
user avatar
  • 7,783
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 ...
user avatar
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. ...
user avatar
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:\...
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 ...
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 ...
user avatar
  • 41.4k
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, ...
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\...
user avatar
  • 397
8 votes

How can I wipe all databases and users from a SQL Server database?

Assuming you can get access to the box (if you need to recover access, see the link Kin already pointed out), you need to: Drop all user databases. Please run the PRINT first and make sure you're not ...
user avatar
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 ...
user avatar
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, ...
user avatar
  • 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 ...
user avatar
  • 18.4k
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 ...
user avatar
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 ...
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 ...
user avatar
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 ...
user avatar
  • 14.6k
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 ...
user avatar
  • 27.4k
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 ...
user avatar
  • 9,342
6 votes
Accepted

Difference in space used

No different. Space Reserved for your MDF file: 100.21 GB, which is equal to 105,082,944 KB (105,082,944 / 1024 / 1024 = 100.21) Space Reserved for *_1.ndf: 148.62 GB, which is equal to 155,843,968 ...
user avatar
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 ...
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 ...
user avatar
  • 18.4k
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 ...
user avatar
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). ...
user avatar
  • 34.2k
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 ...
user avatar
  • 34.2k
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 ...
user avatar
  • 15.4k
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 ...
user avatar
  • 67.5k
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 "...
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 -> ...
user avatar
  • 15.1k

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