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 Pack, which came out just about 3 years ago to the day. That way you'll be covered if you're hitting a weird bug or something. Head on over here to figure out what ...
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 that had a massive amount of memory.
There are two primary purposes of the pagefile:
To use as a swap file for memory. If SQL Server and other applications are ...
What do you need to take into account when migrating SQL Server to another domain?
The steps below presume:
IP address will also change
SQL Server is NOT clustered
BEFORE: backup the databases off-machine
BEFORE: depending on the nature of the change/move, you may want to set service start to Manual for all SQL Service
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 of the networking has been fully initialized. This, then, results in a failure of the service account authentication, and the service start fails accordingly.
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 I don't recall all of the variations).
SQL Server service packs are recommended primarily because the RTM branch (and earlier service pack branches) are ...
I found several bug reports indicating that pg_dump has problems writing to a pipe on Windows:
BUG #7794: pg_dump: errors when using pipes/streams
BUG #6726: pg_dump - ftell mismatch when using -Fc and split
Instead of writing to stdout and redirecting to a file, try using -f specify the output file:
pg_dump -Fc -U "username" -w "dbname" -f "filename".dmp
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.
However, if each Volume maps to a different SAN LUN, it's possible for the SAN to allocate storage resources differently to the volumes. For instance, they can be ...
Ignoring the error
First of all, you should be able to ignore string truncation by going to your Flat File Source, Error Output and then changing "Fail Component" under "Truncation" to "Ignore Error".
The real issue could be that the string length inside the SSIS pipeline is still wrong because it got initialized at some earlier point.
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:\ProgramData\MySQL\MySQL Server 5.6\my.ini
Add this to the my.ini
datadir = D:/ProgramData/Data
net start mysql
If you can not execute ...
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
Understanding VMware Ballooning
Working with my sysadmin was key, but was not easy...
Great question, and I did a session about this at TechEd a few years ago called Building the Fastest SQL Servers:
In it, I explain that for data warehouses, you need storage that can provide data fast enough for SQL Server to consume it. Microsoft built a great series of white papers called ...
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, Microsoft GTSC.
In this blog we would discuss about behavior of AlwaysOn availability group where the secondary database doesn't come automatically when the primary ...
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:
ProtectionPolicy = 1 (DWORD)
Now I can connect.
I found some other postings about ...
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 your script
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
#Need SmoExtended for backup
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 dropping anything you may regret. You may want to take backups of all databases first just in case.
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql += N'
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 check SQL Server memory utilization. It would never show you correct result. You should use a DMV sys.dm_os_process_memory, if you are using SQL Server 2008 ...
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 for only through the web interfaces of your applications, correct?
If so, then is not adding an explicit rule in the Windows Firewall on this server to allow ...
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 2008 R2 are Reporting Services (SharePoint) and Reporting Services Add-in for SharePoint products as mentioned on the "Hardware and Software Requirements for ...
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 the machine on which SQL is installed. (In your example, 64%, which is not that far from Resource monitor.)
I use a query to get the CPU utilization on my server....
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 folder (this is also Microsoft's recommendation). Setting & verifying permissions at the root of the drive does not always work the same as setting ...
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 search existing firewall rules by name by using the Get-NetFirewallRule cmdlet, for example:
Get-NetFirewallRule -Name "*WMI*"
This uses wildcards to final all the ...
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 which provider to use, which isn't required in normal PowerShell but is required in the SQL Server implementation.
Alternatively, you can change directory ...
The actual memory in use won't increase until you actually load data into memory. It's not going to jump to 10GB combined allocated just because that's what you've set max memory to and because your databases are at least that large. It's a max, not a min (and never mind that min doesn't work that way either), and it isn't going to try to guess - before you'...
Windows Server 2008 (no R2) did ship a 32-bit variant of the OS (last one MS shipped that way). So you can cluster it. You can install a 32-bit FCI on a 32-bit node. You cannot install a 32-bit FCI on a 64-bit node. So no, what you want is not possible. If you need a 32-bit FCI, install it on a 32-bit OS.
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, selecting Advanced Editor. Go to Input and Output Properties and check the data type for that column for all items - both External, Output, and Input. I have ...
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 KB (155,843,968 / 1024 / 1024 = 148.62)
The numbers match up just fine. The Operation System is going to see Space Reserved, and that's how you would need to ...
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 connections allowed also depends on the
version of SQL Server that you are using, and also the limits of your
application or applications and hardware....
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 Express instance and select Properties. On the Startup Parameters tab add -t7806, hit Add, then OK, and restart the service.
You can check the error log or ...
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).
Examine the plan cache using a free script -- I co-author one called sp_BlitzCache. By default, it will return the top 10 CPU consuming queries in your server's ...