Tag Info

Hot answers tagged

10

SQL Server Express is limited to 1 CPU only (to add more details it's limited to one socket or four cores, whichever is less) see http://msdn.microsoft.com/en-ie/library/cc645993.aspx


10

SQL Express creates database with AUTO_CLOSE ON by default. Auto close databases automatically close themself (and thus release the file handles) whenever there are no sessions using the database. This works 'as advertised', you just have to read the small print: True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, ...


8

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


6

You are getting a permissions error. The account which is running the SQL Server doesn't have the needed rights on the folder which will contain the database files. You need to give the account which is running SQL Server (not your account) full control of C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA.


5

A simple and complete solution to error ' Could not find database engine startup handle' in below link Reason for the error can be 1.The account which user selected on Server Configuration page window ( during installation) is somehow not able to bring SQL Server database engine services online. Either is lacks privilege or it is corrupted. During ...


5

If you have Management Studio Express installed (any version prior to SQL Server 2012 SP1), it simply doesn't contain any facility for SQL Server Agent (no matter which edition you're trying to manage). You can fix this by getting the very latest version from here: http://www.microsoft.com/en-us/download/details.aspx?id=43351 You want the file ...


5

Try ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF) This recreates the clustered index, so you will need extra room in your database for the operation to complete. If you don't have any extra room because your disk is full, you could possibly add a new data file to the database (on a different disk) and move the table to it. It is also ...


5

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(single_pages_kb)/1024 AS MemoryMB FROM sys.dm_os_memory_clerks GROUP BY type ORDER BY 2 DESC Hope this helps


4

Based on our comment thread it sounds like you may have gotten yourself a bit sideways during install. The installer allows you to choose your default data directory and (I would assume) sets the appropriate permissions on that directory for the service account that you specified. In your CREATE DATABASE statement you're specifying a location, but was that ...


4

As Max Vernon states, the database is trying to restore to a location that doesn't have space to contain it. If the backup file is compressed or mostly empty, you will still need the full uncompressed space to restore to. Additionally, by default SQL Server will try to restore those files to their original file location, so if that original location does ...


4

You can set the database's max file size to 9GB, but you will need to do the purging yourself. This is not something that SQL Server supports. You could get notified of the limit being reached using extended events, but there is no way for SQL Server to know what records to delete without you telling it. SQL Server does not log or otherwise keep track of ...


4

After working with Microsoft Technical Support on this issue, we determined that the act of uninstalling SQL Server Standard and re-installing SQL Server Express leaves the service account (in this case, NT Service\MSSQL$SQLEXPRESS) in a state in which it doesn't have the permissions needed to run the service. To fix the problem, we manually went through ...


4

Following up on my comment, you have to select ENU\x64\SQLEXPRADV_x64_ENU.exe for installation. Download link. Then run the installation package and follow instruction until you get feature selection page. Follow rest of the steps and complete the installation. Now Verify the RS installation : Note: You must be logged in as a member of the local ...


3

You say in a comment the only thing we have done is replaced ScanImage on every row with a much smaller image (this is how so much unused space is there) From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in). Example code that achieved ...


3

Assuming the following table definition CREATE TABLE T ( Name VARCHAR(50) NOT NULL, IsDeleted BIT NOT NULL ) Then you can achieve this with a unique index filtered to only include those Names that you wish to apply the constraint on. CREATE UNIQUE INDEX ix ON T(Name) WHERE IsDeleted = 'false'


3

Is Sql server thread-safe? This issue is not with that SQL Server being thread safe or not.The issue is there with SQL Server Connection Pooling In Short:- Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new ...


2

It's pretty likely you were using RC0, which expired a few days ago. I would imagine that if you start and stop the 2012 engine service in the services control panel, then it will refuse to start again. When you have reinstalled Express it will be fine.


2

Another option, one which I've just learned, comes from the sqlcmd documentation. You need to set the codepage for sqlcmd to match that of the file encoding. In the case of UTF-8, the codepage is 65001 so you'd want: SQLCMD -S .\MSSQLSERVER08 -V 17 -E -i %~dp0\aqualogyDB.sql -o %~dp0\databaseCreationLog.log -f 65001


2

As from the comments, the problem is not exactly with the table or the way SQLCMD imports the special characters. Usually, problematic imports are related to the format of the script itself. Management Studio itself offers the option of saving with a specific encoding, which should solve the problem in the future. When saving a file for the first time (or ...


2

You need 84,260,749,312 bytes of free space to create the files necessary to restore the database. The backup file is not necessarily representative of the required size. You can only restore this backup to a drive with the required free space.


2

Not sure where you guessed at that syntax, or why you are trying to put single quotes around a column definition (which makes it look like a string literal). Here is one way to make sure that the new column has a specific type: SELECT ...other columns... , [Status] = CONVERT(VARCHAR(100), ReportType) INTO dbo.temp_AlgorithmLog FROM dbo.AlgorithmLog_OLD; ...


2

The full Express version (with SP1 included) is for installing a new instance, not for applying a service pack to existing instances. Sounds like perhaps you now have a second instance of SQL Express (no idea without seeing your configuration manager or services applet). At this point you should download Service Pack 2 (not 1) to apply to existing ...


2

When your machine is not connected to the network, it uses the cached information for authentication purposes. This is how you can take the laptop home and log on with your domain account. If you created a login for a domain account while you weren't connected to the domain and then they tried to log in with that account, it would fail because that ...


1

I would recommend signing up for a trial of PluralSight. They have a TON of content, including courses specifically aimed at 70-461 and 70-462. I have been a few of their courses and they are excellent: http://pluralsight.com/training/Courses#sql-server Also, you may want to check out the MCM Readiness videos on Microsoft's site. They are a little out of ...


1

When it comes to accessing SQL Server, always use Principle of Least Privilege. You dont need sa account to do your routine work. Also, if you have windows authentication, why do you even need a SQL Server login with user and password. From Choose an Authentication Mode When a user connects through a Windows user account, SQL Server validates the ...


1

New accounts with system administrator access can be created by other equivalent accounts but there is no way to reverse engineer the password for an existing account. You should also verify that the SQL Server you're connecting to allows for mixed authentication (Windows and SQL Server). This StackOverflow question describes how to access authentication ...


1

make sure the DB recovery mode is simple. alter the column as VARBINARY(MAX). then try copying the data in to completely new table. check the new table size using sp_spaceused "tablename" if you satisfied with unused space of table then check the unused space of DB using same commond w/o specifying table name, that space is still with DB and not release ...


1

SQL Server Express doesn't have the SQL agent (the service gets installed but you can't enable it) so you won't see any of this section in SSMS. If you want to schedule tasks you will have to use SQLCMD and the windows task scheduler. This article explains how to schedule backups in this way, but the same principle can be used to run any SQL script: How ...



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