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

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 C:/...


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 ...


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

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... ;-)


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 ...


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 ...


10

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

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 ...


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 ...


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

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

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 ...


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( FORMAT(B.ISize/POWER(1024,pw),3),',','...


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

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 ...


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 ...


7

Given this is a Windows installation, @DTest still provided the initial proper direction. Apply the following formula: Most people use this: Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections I prefer this: Maximum MySQL Memory Usage = innodb_buffer_pool_size + ...


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

It sounds from your description like this Excel sheet will be used as a sort of interactive report. If that's the case, I encourage you to try out Reporting Services. It's included with every edition of SQL Server, ever since 2005. This way, accessing the report only requires connecting to the report server with a web browser, rather than going directly to ...


7

Your application is probably not using bind variables, which has caused the SGA to become fragmented (it fills up with multiple copies of SQL that use literals instead of binds). You can temporarily fix the problem by flushing the shared pool with: alter system flush shared_pool; There's a workaround you can try to stop it happening in the future. Turn ...


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

There is an existing tool that'll help you a great deal, WAL-E. It provides an archive_command and restore_command for PITR to S3. There are no commands to do incremental or differential logical backups. pg_dump can't take an incremental or differential. The only way to do that is via log archiving. In theory you could take a new full backup, do a binary ...



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