Hot answers tagged

28

Assuming Windows and SQL Server... There are two schools of thought. Leave 2-4 Gigs for Windows (depending on what is installed besides SQL Server). Leave 10% of your available memory free. As you get over 64 Gigs this gets to be a crazy large amount of memory to leave for the OS, which it probably won't need. Personally I'm in the first group. ...


17

SQL Server instances have separate sets of binaries for each installation. For the base engine, you will have separate SQL Server and SQL agent services that can run under separate accounts. They each have their own configurations and users. This is also true for other components like Analysis Services and Reporting Services if you install them. There ...


15

Think of the data file as a container, like your fridge. The fridge doesn't tell you when the milk is bad (ok, maybe the Jetsons' fridge does) - you need to check inside. For SQL Server, the timestamp in Windows has nothing to do with the data you are changing inside the file; it has to do with the last time the file itself has changed, such as a service ...


15

Assuming Linux, if you turn off swap and the kernel keeps killing your DB process because it's out of memory, that's a good indicator you're starving the OS for memory. Back off until that stops happening. A couple hundred megs is usually plenty.


15

From your paths, I can safely assume the following: You are running MySQL in Windows You used the MySQL MSI to install What you need to do is establish the file my.ini When installing MySQL for Windows using the MSI, the location of my.ini is expected to be C:/ProgramData/MySQL/MySQL Server 5.1. Please run the following in a DOS Window: cd ...


14

Have you considered SQLCMD? Here's an article on Using the SQLCMD command line utility. You can execute adhoc SQL statements, or run .sql scripts. The following example runs the C:\MyScript.sql script from the command line and outputs the queries and results to a text file located at C:\MyResults.txt. C:\Windows>sqlcmd -S NOTTINTRA3\SQL2005 -i ...


13

The words "securely", "exposing", "outside" and "database" do not belong together. Under no circumstance should you ever expose a SQL server to the internet. It's a very bad idea. So your question really should be: How do we give access to our SQL server to remote locations? The answer: VPN. The remote users should establish a VPN connection to your ...


13

PROBLEM In MySQL 5.6, ibdata1 includes 5 InnoDB in the mysql schema. mysql> select table_name from information_schema.tables -> where table_schema='mysql' and engine='InnoDB'; +----------------------+ | table_name | +----------------------+ | innodb_index_stats | | innodb_table_stats | | slave_master_info | | slave_relay_log_info ...


12

You definitely need to know your way around... Performance Monitor Disk Management (mount points, 64k clusters, RAID levels etc) Group Policy + Active Directory + Security Shares + NTFS permissions Cluster Service + resource groups Powershell Physical/Logical sockets/cores + affinity NUMA architecture Without these (and others) you're ineffective ...


12

There are no special settings for SQL Server which only uses physical memory normally Just do what MS say for Windows and that's it Oh, and buy more RAM anyway while we're one the subject... ;-)


11

I knew I had faced this before and managed to figure it out, so I searched my drives and found a batch file I had written last time. For anyone else who experiences this issue, what you need to do is to run the server daemon with the --bootstrap parameters. Here is the script I used to dump all the databases out to an SQL file and re-import them (that is, ...


11

I had the same issue installing 9.1.4 on Windows 7. I managed to find a solution online that worked. The steps I followed are: Uninstall PostgreSQL Delete the postgres user if it still exists. net user postgres /delete Create the postgres user with a password you can remember Add the postgres user to the Administrators group Add the postgres user to ...


11

I lost the database password. What can I do to recover it?: I lost the database password. What can I do to recover it? You can't. However, you can reset it to something else. To do this, you Edit pg_hba.conf to allow trust authorization temporarily Reload the config file (pg_ctl reload) Connect and issue ALTER ROLE / PASSWORD to set the new ...


9

Bit of a carbon copy of a previous answer... Personally, I disable it at both ends of the pipe but the problem usually manifests at the database end (due to the comparatively high traffic). As described in this MSDN blog post, I would start with disabling in the OS with: netsh int ip set chimney DISABLED It may be necessary to disable the features at the ...


9

I answer my own question here for completeness I will select @RolandoMySQLDBA as the preferred answer because it gave me the most hints even though it didn't actually solve my problem. Below are the results of my investigation Conclusion MySQL on Windows just creates lots of temporary tables and tuning MySQL by modifying the content of the configuration ...


9

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by: it did not make the actual MySQL setting changed I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine): The global variable change ...


9

Here is a start, if I can guess that by row you actually mean column: SELECT [table] = QUOTENAME(OBJECT_SCHEMA_NAME([t.object_id])) +'.'+ QUOTENAME(t.name), [column] = c.name FROM sys.tables AS t INNER JOIN sys.columns AS c ON t.[object_id] = c.[object_id] ORDER BY t.name, c.column_id; This doesn't provide exactly the output you want, but surely ...


8

No. As you suggested, the PCI Data Security Standard actually prefers Windows Authentication over any other means of authenticating to SQL Server. The section of the standard that covers this is Requirement 8: Assign a unique ID to each person with computer access. SQL Server authentication (which Mixed Mode authentication allows) fails, or makes it ...


8

I have some queries you can run against the INFORMATION_SCHEMA Run this to get the Total MySQL Data and Index Usage By Storage Engine SELECT IFNULL(B.engine,'Total') "Storage Engine", CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ', SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE( ...


8

There are a number of options and please don't limit yourself to my answer here. In particular you may find array-native databases to be of help. My answer is going to be specifically about your questions on SQL-based databases. It sounds to me like this is a question of geospacial information. SQL-based databases are in fact used in such fields quite ...


8

Looking at the my.ini, I have two suggestions SUGGESTION #1 I would bump up the following settings in your my.ini sort_buffer_size=4M join_buffer_size=4M This will make some joins and sort stay in memory. Of course, once a JOIN or an ORDER BY needs more than 4M, it will page to disk as a MyISAM table. If you cannot login as root@localhost, then restart ...


7

this bug is reported in MySQL Forums (67179) and the solution is mentioned here. please note that you should do clean-up, before creating tables /* temporary fix for problem with windows installer for MySQL 5.6.10 on Windows 7 machines. I did the procedure on a clean installed MySql, and it worked for me, at least it stopped lines of innodb errors in ...


7

Usually PostgreSQL 9.3 is generally faster then 8.3 - but hard to say what is wrong. Possible sources: problems with IO, wrong PostgreSQL configuration - max_connections = 1000 is probably terribly wrong value, default work_mem is usually too small, hitting hw limits (9.1 and higher should to better use more CPU), wrong testing ... Other problems can be ...


7

This answer applies to modifying the PostgreSQL source code by applying a "diff" or "patch". It's not about installing minor version updates; to do that, just download and run the installer. To alter the PostgreSQL server its self or its procedural language runtimes, you will generally need to recompile PostgreSQL from scratch. On Windows that's a bit of ...


7

By setting innodb_flush_log_at_trx_commit, you risk confusion with mysqld/OS interoperability. I say this because the OS is being trusted to perform the flush. Note the caution in from the MySQL Documentation Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though ...


7

Long shot perhaps, but worth checking file \Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini or the equivalent on your setup. You may have to manually edit it with the instance name. Otherwise SSIS connections might be looking for an msdb of a default SQL instance that doesn't exist.


7

Why 52395 instead of 1433 Named instances listen on dynamic ports. Is the job of the Sql Server Browser Service to inform the clients of the actual port. The Sql Browser listens on UDP 1434 and answers questions of the form 'what is the listenning port of instance "foo"?'. Sql Server Browser service is required for both TCP and named pipes protocols. ...


7

Create matching usernames and passwords. It really is that simple. For your example, create an account for User1 on Server1 with the same password as on PC1. Just in case this is an issue you're having with connecting SSMS with a non-domain account, that requires jumping through an additional hoop. Some applications don't support stored usernames and ...


7

There is nothing wrong with opening Management Studio, clicking 'New Query', and just typing out T-SQL commands. You may be making it more complicated than necessary.


7

You can leverage Amazon's experience running thousands of customers' database servers here: On Amazon Relational Database Service, they set MySQL's innodb buffer pool to 3/4 of the system's memory, regardless of how much memory that is. Add in up to a couple megs per connection for various query buffers, and they're likely leaving 10-20% of the memory to the ...



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