Hot answers tagged

19

If I have 8GB of RAM in a server and I run 4 instances of SQL Express, will the total memory limit used by SQL Server be 1GB or 4GB? Each instance can use up to 1GB of memory for the buffer pool. Each instance can use a bit more than 1GB in total because not all memory allocations go via the buffer pool. In your case, the maximum memory used by the ...


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


17

To do it through code... 1) If you can assume that this table is in the dbo schema. SELECT name FROM sys.databases WHERE CASE WHEN state_desc = 'ONLINE' THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[costomersHR]', 'U') END IS NOT NULL 2) Otherwise you could loop through all databases (and query sys.tables in each) with ...


13

It's not possible. You need to reinstall it. There is no instance rename facility for the Database Engine. http://social.msdn.microsoft.com/Forums/en/sqlsetupandupgrade/thread/d5d297b0-2995-46ba-9d3b-d1ec8825f479


12

We are mounting a database to an instance. See the concepts guide: The instance mounts a database to associate the database with that instance. One way I've seen it explained (probably here or on SO) is that the instance is the memory structures and processes, and the database is the data on disk. The instance has to have a database associated with ...


11

No, no and again no. One instance, one server, Always. The reasons have to do with the SQL Server memory manager and CPU scheduler architecture, it really works best if it has the whole box to itself and nothing else runs on the box. 'Partitioning' of resources (max server memory, affinity mask) solves some problems and introduces more. If you want to ...


9

Integration Services is not "Instance aware". The rest (Analysis, Reporting, DB) are. Note that SSIS doesn't need to be instance aware either. The service itself is pretty pointless and doesn't need to running. You simply need the SQL Server common binaries installed to run packages. The service is for SQL Server Agent and SSMS to browse packages etc Links:...


9

You can delete databases with DBCA which takes care of most of it. Or you can do as below, but this will do the same as removing the datafiles, redo logs, controlfiles manually. sqlplus / as sysdba startup mount exclusive restrict exit rman target / drop database including backups noprompt; exit After this, you still have to remove the entry that belongs ...


8

Are you running from the 10gen repository or from the default Debian/Ubuntu repo? I recommend using the official 10gen repository. Check this link out - [10gen MongoDB How-To Install on Ubuntu:] http://docs.mongodb.org/manual/tutorial/install-mongodb-on-ubuntu/. It is best to uninstall the previous mongodb installation prior to this change, which will also ...


7

Every Instance counts separate as it has a separate process.


7

Quite simply, client tools need to know explicitly which machine you're trying to connect to. This is like trying to access http://localhost/test_site by typing test_site into your browser. For SQL Server you can indicate the local machine in a variety of ways: machine_name\instance (local)\instance 127.0.0.1\instance .\instance


6

This is possibly because you have named instance installed instead or apart from Default instance. So the default instance looks like SERVERNAME While the named instance in you're case is SERVERNAME\SQL2012 From MS-LINK Client applications connect to an instance of Microsoft SQL Server 2005 to work with a SQL Server database. Each SQL Server ...


5

I can see why this might be confusing given the mental model we apply to the word mount. It may help to think of mount as an abstract Oracle term and only apply to it what the software actually does. Here are the stages of instance and database startup: Shutdown NoMount Reads the parameters in the initialization file. Allocates memory. Starts the ...


5

The SQL Server Customer Advisory Team wrote a blog post that has some information on this setting here. The –E startup flag The SQL Server startup flag –E forces SQL Server to allocate 4 extents at a time to each file, essentially quadrupling the stripe size. In heavy insert scenarios, this drives larger block sizes to the disk. Also, your pages ...


5

Think of it this way: the database is the set of physical files, on disk. It's a completely passive "thing". It doesn't "do" anything on its own - it's just data. the instance is the software/processes (with its memory) that manages the database. It's the active part. It's what clients connect to, it's what processes SQL, reads data, updates it, maintains ...


5

Instance Recovery is necessary to get the database back to a consistent state after shutdown abort or some other abnormal shutdown event. Oracle is never going to let you open the database in an inconsistent state, so no, there is no possibility that you can disable it. If Oracle is crashing during Instance Recovery you need to get in touch with Oracle ...


5

Not sure I fully understand all of the variables but I suspect you would be able to use synonyms that are different between the two environments which would allow the code to be the same. It is a layer of abstraction that requires documentation and/or an extra layer of the onion to peel, but can definitely simplify the code you write. Here is the CREATE ...


5

You cannot change the Instance Name after it has been installed. But you can uninstall that SQL Server instance and reinstall as a DEFAULT instance. If you have databases detach them and attach again after the reinstall. When installing a default instance of SQL Server Express at the Instance Configuration page, you must choose the Named Instance radio ...


5

You can use this tool RedGate SQL Search. It works really great.


5

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among ...


5

A single SQL Server instance knows how to manage its memory, CPU and IO in the most optimal way. Two (or more) SQL Server instances cannot cooperate to coordinate usage of shared resources. On this grounds, a single instance is more performant than more instances. If you split into 2 (or more) instances, then it would be required to partition the resources ...


4

You cannot change the instance name, but if I may suggest, you may be able to use server aliases instead. Eg: if your DB server was stackexchange and the instance name was sql2005, you would connect to the sql server by going to stackexchange\sql2005. An alias can be setup in the SQL configuration manger with a name of lets say dbaxchange and then pointed to ...


4

You've hit the nail on the head: copy the files. I've used this to good effect. I'd say you have to define "cheap" to include investment of time to prepare a solution that doesn't involve xcopy


4

I found the answer yesterday with the help of a friend of mine. I had to log in via SSMS with a user other than the Windows login I was attempting to use, delete the old login, and add my Windows login again. After that, I was able to transfer ownership of the job properly, SQL was able to get the user data from Windows, and all was right with the world.


4

When you added the new server name using sp_addserver, did you remember to include the "local" designation. It is that tag that updates the metadata for @@SERVERNAME. More information. sp_addserver 'servername', local


4

Almost nothing is going to be shared. From the perspective of the actual SQL Server services nothing is shared. Each instance get's it's own services and each has their own copies of the actual EXE files. Only one copy of the management tools is installed, and things like drivers are shared.


4

If the DB is running, you can look for the pmon process: ps auxww | grep pmon | grep -v grep You'll see one pmon process for each database that is up. For example ora_pmon_YOURDB, where YOURDB is the instance name. You can also ask the listener which services it is serving connections for, with: lsnrctl status


4

From View or Change the Default Locations for Data and Log Files: In Object Explorer, right-click a server and click Properties. In the left panel, click the Database settings page. In Database default locations, view the current default locations for new data files and new log files. To change a default location, enter a new default pathname ...


4

It's signalling a 'ORA-214' during instance startup, which is really bad. $ oerr ORA 214 00214, 00000, "control file '%s' version %s inconsistent with file '%s' version %s" // *Cause: An inconsistent set of control files, datafiles/logfiles, and redo // files was used. // *Action: Use a consistant set of control files, datafiles/logfiles, and redo //...


4

You need to check both sys.remote_logins and sys.linked_logins. You should be able to build a script dynamically to drop the set you find: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += N'EXEC sys.sp_droplinkedsrvlogin @s, N''' + remote_name + N'''' FROM sys.linked_logins WHERE server_id = 0 AND remote_name IS NOT NULL; SELECT @sql += N'EXEC sys....



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