Tag Info

Hot answers tagged

25

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

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


11

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


10

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


9

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


9

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


9

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

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

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

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


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

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

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


7

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


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


6

No, but there are workarounds One workaround is to run a virtual machine that has Oracle and only Oracle installed. this has the significant benefit of insuring that the many kernel tweaks required of oracle are constrained within one instance. Consider trying deployment within: VMWare Thinapp, VMWare Player (maybe), and I believe VirtualBox offers a ...


6

You are receiving an Oracle ORA-03297 error because the HWM (High Water Mark) of a table is beyond the size you tried to shrink a datafile to. First try and "shrink space" for each affected table: alter table fragmentedtable enable row movement; alter table fragmentedtable shrink space; Next, check to see how much space can be freed from each of the ...


6

This is broken due to this patch: http://technet.microsoft.com/en-us/security/advisory/2661254 As per this link http://trinidba.wordpress.com/2012/10/11/internet-explorer-cannot-display-the-webpage-when-connecting-to-oem/ you can fix it with Oracle Doc ID Doc ID 1489557.1


6

Creating a new instance The following command line call will create a new database + instance dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid -sysPassword pass -systemPassword pass because I have a dedicated oracle user for my install I also had to use -serviceUserPassword. With regards to the passwords, despite being in ...


5

You need to have SQL*Net installed in order to connect PL/SQL Dev to Oracle. SQL*Net is installed by default with most Oracle DB products (Oracle client for example). Since PL/SQL dev is a Windows app, you can see what Oracle products you have installed by looking into the registry (HKEY_LOCAL_MACHINE\SOFTWARE\Oracle). Also in PL/SQL dev settings ...


5

This means you have logged in correctly to SQL Server, but the database in the connection string (or default database from CREATE LOGIN) is one of not there unavailable (eg autoclosed or recovering) no login-user mapping (eg CREATE USER as not been run)


5

Can reproduce on the 11gR2 database hosting apex.oracle.com The following query results in 3 rows: SELECT t1.*, t2.*, t3.dummy, case when 1=1 then 'a' else 'b' end "Case/Decode" FROM t1 LEFT JOIN (select 1 dummy from dual) t3 ON t3.dummy = t1.id1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE (t2.id2 IS NOT NULL or t3.dummy IS NOT NULL) Removing the CASE ...


5

Are you running MySQL Administrator? That includes a backup scheduling mechanism.


5

Look into lock pages in memory. This way, you can give preference for your SQL service account to use available RAM rather than paging to disk. To read more on lock pages in memory, check this link. A snippet follows: The Windows policy Lock Pages in Memory option is disabled by default. This privilege must be enabled to configure Address Windowing ...



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