58
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 ...
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,984
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,984
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.
...
- 47.7k
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
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\...
- 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 ...
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 ...
- 16.3k
8
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 ...
- 10.4k
7
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 ...
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 ...
- 29.8k
7
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 ...
- 70.5k
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).
...
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.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 ...
- 18.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,964
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 -> ...
- 18.3k
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 ...
- 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 ...
- 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 ...
- 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 ...
- 23.2k
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 ...
- 5,964
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 ...
- 2,437
5
votes
Increased memory to server, but do I need to change page-file size?
This is spelled out pretty explicitly in How to determine the appropriate page file size for 64-bit versions of Windows
Crash dump setting
If you want a crash dump file to be created during
...
- 47.7k
5
votes
Accepted
Does restarting Windows restart the SQL Server?
Assuming you have a single MS SQL Server instance on a single Windows machine the answer is a very definite resounding yes, restarting Windows will mean restarting all the services running on it ...
- 32.4k
5
votes
SQL Server Access Database in Filesystem
If you can find .mdf and .ldf file you can try to attach it to the new SQL instance using below:
CREATE DATABASE <db_name>
ON (FILENAME = '<mdf file path>'),
(FILENAME = '<ldf ...
- 164
5
votes
Upgrading out of support Microsoft SQL instance multiple questions?
The bitness of the backup does not affect anything. What does affect things is the version of the backup. Backups before 2005 cannot be restored without an intermediate step, but 2005 is fine to ...
- 14.6k
5
votes
SQL Server database size is 10GB but tables are only around 450MB, what is occupying the rest and how can I reduce it?
Did you run the "Disk Usage by Table" report in SSMS? It runs this query:
SELECT
(row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [...
- 47.7k
4
votes
Accepted
MySql service on Windows won't start after changing datadir
If You open Windows Services and check the service properties, You will find something like this:
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe" --defaults-file="C:\ProgramData\MySQL\MySQL ...
- 3,665
Only top scored, non community-wiki answers of a minimum length are eligible