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

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


13

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


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

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


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

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

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

Personally, I tend to use the 2nd and subsequent executions as my benchmark when developing hence this answer... Arguably the real speed is with caching of data and execution plan. If it's run often enough to matter, then it will be cached in real life. You are measuring performance the 2nd time. For the first run, you're measuring load/compile times. On ...


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

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

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


5

PLEASE SEE MY WINDOWS ANSWER DOWN BELOW BY THE UPDATE 2012-05-22 12:42 EDT You need to make backups of each database separately First, go get all your databases MYSQL_CONN="-uusername -ppassword" SQLSTMT="SELECT DISTINCT table_schema FROM information_schema.tables" SQLSTMT="${SQLSTMT} WHERE table_schema NOT IN" SQLSTMT="${SQLSTMT} ...


5

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


5

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


5

I think the problem with your attempt is that you put quotes around the plugin name, along with the fact that you used a .so suffix to the library name. Step-by-step: Check that your MySQL install is dynamic_loading capable: mysql> show variables like '%dynamic%'; +----------------------+-------+ | Variable_name | Value | ...


5

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


5

SQL Server does not use the default access path when opening the database files. Instead it uses its own low-level driver to get the highest performance possible. As a result of that you cannot rely on any information that windows is displaying about the files. instead consult the appropriate system views and DMVs. I am however not aware of any way to ...



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