This tag is for SQL Server 2012, major build version 11.00.xxxx.

learn more… | top users | synonyms (3)

9
votes
3answers
7k views

Truncated 200GB table but disk space not released

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.
9
votes
3answers
7k views

Can I connect to a SQL Server 2000 instance from SQL Server Management Studio 2012?

I would like to update my development machine to SQL Server 2012 but I still manage some (very old) SQL Server 2000 machines. Wil my 2012 SSMS be able to connect to those SQL Server 2000 machines? ...
9
votes
3answers
353 views

Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems. To my ...
9
votes
1answer
978 views

Why 0 is equal to empty string?

I need some help in finding why the following T-SQL statement returns 1 (true): SELECT IIF( 0 = '', 1, 0) I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is ...
9
votes
1answer
300 views

What is the different between compression on the PK vs. on the Table?

Data compression can be set on the table: CREATE TABLE dbo.SomeTable( SomeId [bigint] NOT NULL, OtherId [bigint] NOT NULL, IsActive [bit] NOT NULL, CONSTRAINT [PK_Some] PRIMARY KEY ...
9
votes
2answers
1k views

Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased. Many questions I've seen both here and at http://stackoverflow.com use natural keys instead of surrogate keys based ...
9
votes
3answers
6k views

How best to maintain SQL log file sizes

I'm somewhat of a new DBA and I'm managing a SQL Server 2012 instance that has a fair amount of activity. I'm running in Full Recovery mode because we need point in time recovery. Right now, I'm ...
9
votes
2answers
3k views

How to flatten results of a table with two related “many” tables?

I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them. I have the following tables (somewhat abbreviated ...
9
votes
10answers
11k views

Sql Server Management Studio slow opening new windows

After upgrading both sql server and management studio from 2008 to 2012, the management studio 2012 is very slow when new query windows and dialog boxes are opened. Even right click on tables is slow. ...
9
votes
1answer
1k views

SSIS 2012 Create environment variable fails

I'm working a script to port an Environment from one server to another. I am running into an issue calling catalog.create_environment_variable wherein I get the error "The data type of the input value ...
9
votes
2answers
159 views

SARG cardinality estimate, why not full-scan?

Why there is no full-scan (On SQL 2008 R2 and 2012)? Test data: DROP TABLE dbo.TestTable GO CREATE TABLE dbo.TestTable ( TestTableID INT IDENTITY PRIMARY KEY, VeryRandomText VarChar(50), ...
9
votes
1answer
28k views

How to uninstall SQL Server Management Studio 2012? [closed]

How to uninstall SQL Server Management Studio 2012? ControlPanel's "Uninstall or change programs" (in Windows 7 Prof) gives a row of entries: while internet search results always tell: "click ...
9
votes
1answer
2k views

Using SQL Server 2012 Standard with Bitlocker

We're trying to use Bitlocker to secure an SQL Server 2012 database. We don't have any issue getting bitlocker to work... the issue we have is that once the drive is locked, SQL Server can no longer ...
9
votes
1answer
5k views

Upgrade SQL Server 2005 to SQL Server 2012

I have a test environment set up with the following: Virtual Machine (Hyper-V) Windows Server 2008 R2 SP1(x64-bit) Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name) 1 ...
9
votes
2answers
4k views

SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource: Choosing Row Versioning-based Isolation Levels ...
9
votes
2answers
639 views

Improving the performance of STIntersects

Table T_PIN has 300,000 pins and T_POLYGON has 36,000 polygons. T_PIN has this index: CREATE SPATIAL INDEX [T_PIN_COORD] ON [dbo].[T_PIN] ( [Coord] )USING GEOGRAPHY_GRID WITH (GRIDS =(LEVEL_1 = ...
9
votes
2answers
587 views

Best way to defrag/compact a database for archival purposes

We've got an SQL Server instance that's used for email archiving (courtesy of a 3rd party archiving package). Every so often, the software is rolled over to a new empty database. We've done this ...
8
votes
2answers
7k views

Sql Server 2012 data warehousing and different versions

With Sql Server 2012 there are 3 flagship editions: Enterprise Edition, Business Intelligence, Standard. The full comparison between the three: ...
8
votes
2answers
240 views

Are “out of the row” fields read when a clustered index is used?

I know that when VARCHAR(MAX)/NVARCHAR(MAX) columns are used the data is stored out of the row - the data row will have a pointer to another location where the 'large value' is stored. I have the ...
8
votes
3answers
5k views

Can I create a user defined table type and use it in the same transaction?

When I execute the following (in management studio, GO will separate the commands into batches) use tempdb begin tran go CREATE TYPE dbo.IntIntSet AS TABLE( Value0 Int NOT NULL, Value1 Int ...
8
votes
3answers
2k views

Get transactions that failed or were never committed

Is there anywhere I can look to find failed transactions in SQL server 2012?
8
votes
2answers
1k views

What effect will reducing the size of a varchar column have on the database file?

We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes ...
8
votes
1answer
498 views

Re-run a specific actual query plan

I have captured an actual query plan for a specific query. After this I've changed a few things around (including updating the statistics) and re-ran that specific query. Now the actual query plan ...
8
votes
2answers
1k views

In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package

In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package" Doing a search I found this page on MSDN which states: The value of 1 signifies ...
8
votes
3answers
163 views

IF statement not skipping TempDB when Looping through Databases with sp_MSForEachDB

[SQL Server 2012 SP2 EE] Why does the following script gives me an error relating to tempdb? exec sp_MSForEachDB ' IF ( (select database_id from sys.databases where name = ''?'') > 4) ...
8
votes
2answers
31k views

Installing SQL Server 2012 Express fails with error “ Could not find the Database Engine startup handle.”

I'm attempting to install SQL Server Express 2012 w/ SP1 x64 on Windows 7 SP1 x64. This machine has previously had SQL Server Express 2012 installed. This instance was upgraded to SQL Server 2012 ...
8
votes
2answers
376 views

Shouldn't SQL Server Support RANGE?

As someone who develops websites and does a bit of SQL Server, it seems a no brainer to me that SQL Server should support a simple clause that indicates the results should only include a specific ...
8
votes
5answers
9k views

Freeing Unused Space SQL Server Table

I have a table in SQL Server Express with a lot of unused space. I need to free up space in the database. | NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | ...
8
votes
3answers
1k views

SQL Server query slow when paginated

I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012: select Id FROM dbo.Person WHERE CONTAINS(Name, '"John" AND "Smith"') order by Name Executing this query alone ...
8
votes
2answers
534 views

SQL Server : how to create user with access only from specific HOSTNAME or IP addres

I have one stupid app which I rely on, which has hardcoded connection string inside. To increase security of my SQL Server I'd love to make SQL users same as one hardcoded inside app, but I want to ...
8
votes
1answer
2k views

Can Availability Groups provide seamless failover (with no query failures)?

I have been testing the Availability Groups feature in SQL Server 2012 and am finding that there is approximately 15 seconds of down time when the primary server fails over to the secondary server. ...
8
votes
1answer
15k views

Detecting the locked table or row in SQL Server

I'm trying to understand/learn how to track down the details of a blocked session. So I created the following setup: create table foo (id integer not null primary key, some_data varchar(20)); ...
8
votes
1answer
638 views

Sequence is reusing

I have a sequence that generates tracking numbers for objects in my system. It had been working fine for quite some time. Last week we noticed that it was starting to re-use values. What seems to ...
8
votes
2answers
2k views

Creating index on computed field: string or binary data would be truncated

I have a table Foo with the following fields: ID bigint not null identity(1,1), SerializedValue nvarchar(max), LongValue as TRY_CAST(SerializedValue as bigint) Now I want to create an index on ...
8
votes
1answer
353 views

Are the cost percentages in this SQL Server plan over 100% for a valid reason?

I'm looking through the plan cache, looking for low-hanging optimization fruit and came across this snippet: Why are many of the costs listed above 100% ? Shouldn't that be impossible?
8
votes
3answers
14k views

Upgrade process from SQL Server 2000 to SQL Server 2012

I have a SQL Server 2000 database that I would like to upgrade to SQL Server 2012 (running on a different server). I don't think there is a direct upgrade path, but if there is, what is it? My ...
8
votes
2answers
130 views

Getting a realistic query plan when partitioning with low volumes of data

We're using partitioning to reduce the amount of blocking our OLTP system experiences due to locks, with a partition scheme that splits the working tables into 100 partitions based on a customer id. ...
8
votes
5answers
25k views

Error 3154 while restoring a backup using WITH REPLACE

I have SQL 2012 with SP1 installed on my computer. I made a backup of a database test.bak. I have a database with the name test2 which is the same database, but the data changed. I want to restore ...
8
votes
1answer
3k views

Optimal placement of tempdb, mdf and ldf files in SQL Server 2012 on SSD's?

I realize this is probably a very open ended question and the answers could vary, but what is the optimal placement for the tempdb, mdf and ldf files in SQL Server 2012 when talking SSD's? ...
8
votes
1answer
746 views

Restoring differential backup creates DEFUNCT log file?

Here is my problem. I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore. I can do a full restore without a problem, but when ...
8
votes
1answer
2k views

Is SQL Server data compression categorically good for read-only databases?

Some literature on SQL Server data compression I read state that the write cost increases to about four times what would normally be required. It also seems to imply that this is the primary downside ...
8
votes
2answers
1k views

Handling time zones in data mart/warehouse

We are starting to design the building blocks of a data mart/warehouse and we need to be able to support all time zones (our clients are from all over the world). From reading discussions online (and ...
8
votes
3answers
9k views

Best practice for tempdb log file

I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file. Here's the strategy that I'm presently using with my ...
8
votes
1answer
193 views

How to terminate a suspended SQL Server transaction waiting for IO_COMPLETION?

We have a transaction that has been running for more than 5 hours. We are running out of disk space. The session has been killed but it is still waiting for IO_COMPLETION. Actually the wait_type just ...
8
votes
2answers
5k views

SQL Server database design for “archived but available” data

We have this large database (>1TB) that we intend to "shrink". The database revolves around one main entity, let's call it "Visit". For discussion, let's say it is a database for a medical practice. ...
8
votes
1answer
284 views

High PAGELATCH_* and WRITELOG waits. Are they related?

We are seeing very high PAGELATCH_EX and PAGELATCH_SH wait types along with high WRITELOG waits. I've diagnosed the query causing the PAGELATCH waits and can eliminate them by reducing the insertion ...
8
votes
0answers
678 views

AlwaysOn Availability Group error after Windows Update in environment without a failover cluster

Our SQL Server service was down this morning which brought down some of our websites. When I went to check the Windows Event Viewer I saw the following errors: Script level upgrade for database ...
8
votes
0answers
372 views

Troubleshooting THREADPOOL Waits [closed]

From everything I have read, THREADPOOL waits occur when a task is waiting for a worker thread. A high amount of THREADPOOL waits typically means you are running out of available worker threads. ...
7
votes
2answers
3k views

Is there a better option than Union All for multiple selects from the same row?

Example Schema: CREATE TABLE [dbo].[Base]( [ID] [int] IDENTITY(1,1) NOT NULL, [Option1ID] [int] NULL, [Option2ID] [int] NULL, [Option3ID] [int] NULL, [Option1Name] [varchar] NULL, [Option2Name] ...
7
votes
3answers
7k views

Restrict update on certain columns. Only allow stored procedure to update those columns

I have sensitive price columns that I would like to have updated only through a stored procedure. I would like all code or manual attempts to alter values in these price columns to fail if it is not ...