27

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


26

Found this on Microsoft Connect: According to this page on MSDN http://msdn.microsoft.com/en-us/library/ms143547(v=sql.120).aspx , sqlcmd.exe and bcp.exe are now in < Install Directory >\Client SDK\ODBC\110\Tools\Binn Indeed, sqlcmd.exe and bcp.exe are in that folder in my machine. Make sure you installed Client SDK Tools.


13

SQL Profiler does not come with SQL Server Express 2008 R2*. You can get it with the installation media for Datacenter, Enterprise, Standard, Web, Developer, or Workgroup licenses. If you have the installation media, you can get Profiler that way. Otherwise, Developer Edition is available for $50. There are some other tools available, and you could even ...


13

See my comment above. I used Process Monitor to trace the registry access and found it was getting an Access Denied while trying to write: 3:16:40.8405491 PM sqlservr.exe 8756 RegCreateKey HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11E.LOCALDB\MSSQLServer ACCESS DENIED Desired Access: Write I got around this by opening the Registry ...


12

SQL Express is limited to 1GB for the Buffer Pool, but there are many other memory pools in SQL Server. What I find surprising is the excessive use of non-buffer memory pools. To find out memory usage per memory clerk, run this: SELECT type, SUM(pages_kb)/1024 AS MemoryMB FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 2 DESC Hope this helps


12

This table is very small! It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints). CREATE TABLE Person.ContactType( ContactTypeID int IDENTITY(1,1) NOT NULL, Name dbo.Name NOT NULL, ModifiedDate datetime NOT NULL, CONSTRAINT ...


12

Since you are not familiar with the T-SQL BACKUP DATABASE command, I thought I'd add some details about that. You probably want to run something along the lines of the following statement through the Windows Task Scheduler service, since you don't have access to SQL Server Agent (I see from your other questions, you use SQL Server Express). BACKUP DATABASE ...


11

Yes, the Express editions do support CLR integration. The link is for the latest version, but if you switch to 2008 you will see it is supported there too.


11

There are multiple entries because you can be granted the same permission multiple times, from different "grantors". If that principal (i.e. the "grantee") were already granted that same permission from two grantors, then you would see three entries for that same permission on this screen. For the "duplicate" entries that you are seeing, the one with the ...


10

I will be totally honest with you. While MySQL works fine in Windows, I would not recommend it for any Enterprise use in Windows. You should go with SQL Server Express because you will have a database that works great in Windows for Free. Should your company fully commit to Enterprise Database usage, you could simply upgrade to SQL Server (different ...


9

Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases. (Usually, the symptom of this is that ...


8

Even though SQL Profiler does not come with SQL Server Express, you can still create server-side traces using T-SQL. Check out sp_trace_create and sp_trace_setevent. The page for sp_trace_setevent also has a list of traceable event numbers. Finally, if you need a place to get started, this link may serve as a good jumping-off point.


8

Backup are automated via SQL Agent jobs in SQL Server. Since no express edition includes the SQL Server Agent, then you would have to find a way to have your application initiate the backups. Otherwise, you need standard edition. What other features are you using? Some of them may require enterprise or standard. Personally when I was doing a lot of ...


8

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible? SQL Server Express cannot serve as a Publisher or Distributor. SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details. EDIT : To make my answer more ...


7

You could detach the database, that way the file isn't deleted and it won't display inside of SSMS.


7

Unfortunately, no. You are installing a program and making service and registry changes, etc. You need to run setup as an account that has local administrative rights. No workaround to this that wouldn't involve some back door into administrative rights. Best bet is to ask for someone to temporarily give you the rights or install it for you. Source: The ...


7

There is a free, open source tool called ExpressProfiler - https://expressprofiler.codeplex.com/ Very convenient tool. In addition to SQL Express databases, it even supports a localDB.


7

Every Instance counts separate as it has a separate process.


7

Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was). If you don't want to risk data loss, you need to correct that at the source (e.g. shrink ...


7

It sounds like the database is corrupt. Before you do anything further, make a copy of the files, and only work with copies. You might try the following, in case the problem is isolated to the log: CREATE DATABASE [GRMStemp] ON (FILENAME = 'c:\wherever\MDF_File.mdf') FOR ATTACH_REBUILD_LOG; If that fails, there is a forum post on MSDN by Paul Randal ...


7

Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe This is for installing the 32-bit version of SQL Server Express, just the engine, on a 64-bit OS in WoW64 mode. WoW64 mode is described in excruciating detail here. Express 32BIT\SQLEXPR_x86_ENU.exe This is for installing the 32-bit version of SQL Server Express, just the engine, on a 32-bit OS. Express ...


7

Backups in SQL Server don't work that way, they aren't just copies of the database files in another location but instead they are backups of the database content stored in a backup file with a different structure than the database files (you could make a backup of just a database file but even in that case the backup format would be different). To perform a ...


7

You've pretty much self answered here - you won't be able to rely on SQL Server to do the scheduling for you. The most common method is to use Windows Task Scheduler, but if you have any other job scheduling application already that might be a better choice. Either way, you'll need to get comfortable with the sqlcmd utility if you aren't yet. If I were ...


6

I finally found the cause of my problem! I tried for months to track down the issue, but finally gave up, disabled automatic change tracking, just manually initiated incremental population and moved on with my life. Meanwhile, there had been another nagging error that I was having trouble tracking down. Periodically the website would throw a DB ...


6

The "Database Size" on the properties page is showing the size of the data file. This will be the size of your data, with some empty space for new data. You can configure the rate at which this empty space is allocated in the database properties. It says space available 0.03Mb because you've shrunk the data file down - it will grow by itself to allow for ...


6

If the automated scheduling is something you were hoping to use, as noted elsewhere that is not a feature of SQL Server Express Edition. However, Windows Task Scheduler should be a more than serviceable option for you. The base API is not .NET friendly so unless you're familiar with working with COM interfaces, it seems the best approach is to either use ...


6

Here is a good script I shamelessly ripped from here: use [Insert DB Name] select a.FILEID, [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)), [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name, 'SpaceUsed')/128.000,2)), [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name, 'SpaceUsed'))/128.000,2)) , NAME ...


6

If you are not comfortable using Powershell, then you can go for native service broker functionality. This might sound complex, but below links has all the scripts : Scheduling Jobs in SQL Server Express - Part 1 and Scheduling Jobs in SQL Server Express - Part 2 Alternatively, you can use Standalone SQL Agent - still in BETA statge from codeplex. You can ...


6

You can't, as found under the 'Management Tools' section here: http://msdn.microsoft.com/en-us/library/cc645993%28v=sql.100%29.aspx If this is just for your development environment, you can pick up a copy of Developer Edition for about $50 US. When you release, you will need a copy of Standard Edition or higher to be able to use the job agent.


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