SQL Server 2012 (major build version 11.00.xxxx). Please also tag sql-server.

learn more… | top users | synonyms (3)

0
votes
1answer
373 views

Prevent gaps while using Identity/sequence object in sql server

I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is ...
1
vote
1answer
195 views

How to get more information from dm_exec_query_stats

I am running SQL server 2012 on windows azure VM (windows server 2012) I suspected that some of my web appliactions are attacked via SQL injection, So far i could not trace the relevant text with my ...
4
votes
0answers
159 views

PowerShell Move-Item command made SQL and Windows entirely unresponsive [closed]

A LUN expansion completed overnight on our 15k FibreChannel VMAX SAN from 4 to 13 terabytes. This morning I attempted to move a 4.5 terabyte file from a temporary SATA LUN to the newly expanded LUN ...
2
votes
0answers
613 views

Identifying if spid on SQL Server is in rollback after low disk space

On a test box I wanted to get an idea of how long adding an identity field would take to a table with roughly a half billion rows. The process ran for about 10 hours and we noticed it was getting ...
2
votes
2answers
974 views

Detached database file permissions

I have a script that detaches databases for archival when they meet certain criteria. The script is executed by SSIS under the local Administrator account by the Ghost of DBA Past. Per design it (by ...
7
votes
4answers
7k views

How does SQL Server determine precision / scale?

I'm running SQL Server 2012 SELECT 0.15 * 30 / 360, 0.15 / 360 * 30 Results: 0.012500, 0.012480 This one is even mor confusing to me: DECLARE @N INT = 360 DECLARE @I DECIMAL(38,26)...
4
votes
1answer
267 views

How can “HierarchyID” type work when “CLR” is disabled?

The following statement sp_configure 'clr enabled' returns: name minimum maximum config_value run_value clr enabled 0 1 0 0 I assume this means there are no CLR ...
0
votes
1answer
184 views

Select complete next row based on the current row

Hi i have a complicated condition. I have a table lets say 'Test' ID Partner Type Amount 143854 CSTC Purchase -0.81 144029 CSTC Purchase -0.69 144030 CSTC Purchase -...
15
votes
3answers
17k 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.
0
votes
2answers
671 views

SQL Server 2005 to 2012 Replication, cannot access replicated data

I have created publication (snapshot, small table with 10 rows) on MS SQL Server 2005. Then, I created push subscription on MS SQL Server 2012. When I try to select data form table, process takes ...
0
votes
2answers
843 views

Disaster Recovery Options for Sql Server

I wanted to see what suggestions you might have for Disaster Recovery Options based on the following criteria. Currently running SQL 2012 standard edition We have 18000 databases (same schema across ...
3
votes
1answer
422 views

Possible reasons for really long database recovery

This is cross-posted at SSC, but restore is now going on 6 hours. Hello, Overnight I ran restores with NORECOVERY for a 5tb database, along with several other 250GB - 1.3tb databases. I did that ...
1
vote
1answer
51 views

SMSS will start for one user, but not another?

We have a SQL Server 2012 and SMSS 2012 installed. The C:\ recently ran out of space due to a misconfiguration, and now we have 1 user who cannot start SMSS they receive the error: "Cannot find one ...
1
vote
2answers
232 views

Is there any way to set stored procedure options like ANSI_NULLS to ON or OFF outside of creation?

I'd like to set ANSI NULLs and quoted identifier properties on a stored procedure after it has been created. Is this possible? For some context, I'm using Visual Studio Database Projects / SSDT, and ...
0
votes
1answer
118 views

Access not reading extended parameters

We have an ADP Access 2010 project, linked to a MS SQL 2012 server. I am trying to set up a table that has a combo box to select appropriate values, which are supplied by another table. A table ...
0
votes
1answer
372 views

Cannot connect with OLE DB

OLE DB connection... Can anyone suggest me on this. Authentication with windows security works in 2012-2005 "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False; ...
0
votes
0answers
102 views

sql server linked server does not consider indexes [closed]

We have a query with a join between two tables. The first is in the database and the second is in another listener, connected by a linked server. I am executing this query in SQL Management Studio ...
3
votes
1answer
75 views

Is tlog backup on replica clearing VLFs on the primary?

Is it true that a transaction log backup made on the secondary database is clearing the inactive VLFs on the tlog file on the primary database (in case of Availability Groups)? I was not able to find ...
2
votes
2answers
987 views

Is SQL Server Thread Safe?

If I need to update same row with two concurrent thread then is it possible in SQL Server? Suppose I have one table that contains Id and Name columns and now I want to update the third number Id with ...
1
vote
3answers
147 views

How to force SQL Serve *not* to maintain a log file

I have a situation that I need some advice to overcome. I have a SQL Server 2012 database that has been set to Simple recovery model. At times, my SQL Server transaction log file grows out of ...
1
vote
0answers
2k views

“Failure occured while attempting to connect to the Principle server” from CLR procedure

I have a Windows Service running under a domain account which calls a stored procedure which then calls a CLR stored procedure. Initially I had a problem where database ownership chaining wasn't ...
2
votes
1answer
1k views

How to obtain SQL feature list from the command line

Is there a command at the CLI or in PowerShell that will list the components installed for SQL? I'm looking for something like the Feature List you can get from running the discovery report from ...
0
votes
2answers
3k views

Can't see some stored procedures in sys.procedures

Documenting a database and find that I cannot see stored procedures in sys.procedures, even though I can see them in Object Explorer. select p.name, x.value, p.modify_date from sys.procedures p left ...
1
vote
1answer
80 views

Trouble backing up remote database from local server

I have 2 servers LOCAL and REMOTE. REMOTE has a database 'remotedb'. From REMOTE the following statement backs up the database just fine. BACKUP DATABASE [remotedb] TO DISK = N'C:\Temp\remotedb.bak'...
1
vote
1answer
515 views

What is the possibility that mirroring a database will damage, corrupt, or erase either the primary or target database? [closed]

The last time my company had tried to mirror some databases, everything worked great until one morning they came in and the primary database in one of the mirroring schemes was just blank. They had ...
10
votes
2answers
979 views

What is the logic behind ISNUMERIC for certain special characters?

The ISNUMERIC function has some unexpected behavior. The MSDN documentation says: ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0. ...
2
votes
2answers
1k views

Performance/Health checking queries for a user with Read permissions

I am a software developer that only had read access to our prod database (of course). Occasionally we have what looks like performance issues on our server when my DBAs are not available. Is there ...
2
votes
1answer
8k views

RESTORE failing with OS error 32, file being used by another process

I am writing a set of procedures for (partially) automating DB deployment for a customer, which mostly works fine, but sometimes a RESTORE command is failing with the OS error 32, about a file being ...
3
votes
1answer
506 views

SQL Server 2012 Transparent Data Encryption and indexes

Does SQL Server TDE allow a query like this: select * from Patient where lastname = 'Smith' to occur without a full-table scan if the lastname column is indexed?
8
votes
0answers
899 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. ...
0
votes
1answer
155 views

Reporting Services DBs not appearing in Management Studio, but can be queried?

I've set up a server with native reporting services, connected to a database server which hosts its two databases. I'm trying to run a backup of the two databases so I can add them to my availability ...
0
votes
1answer
173 views

SQL Server sometimes uses a lot more cpu for query

I have a stored procedure with a quite heavy SELECT statement that includes five joins and uses FieldName LIKE '%searchterm%' on multiple columns. This query usually finishes in about 2-5 seconds. But ...
0
votes
0answers
176 views

Mirroring failed over - created 2 principal database and will not allow me to drop one of them

For some reason my mirrored servers triggered an automatic failover. I have 2 databases that both think they are the principal server, one is "Principal, Synchronized/In Recovery" and the other is "...
1
vote
1answer
181 views

Best practice for testing database disaster recovery [closed]

This is regarding database disaster recovery testing. The production server has 20+ databases. I'm developing procedures for regular testing of disaster recovery and am wondering what is the best ...
3
votes
1answer
2k views

How to find the active transactions in VLF?

Is there a way to find the T-SQL statement that is part of the active transaction that is blocking the reuse of the transaction log file VLFs? I am performing tlog backups on every 15 minutes, but ...
10
votes
2answers
12k views

Check progress of alter index reorganize / rebuild

How can I check the progress / status when I submit an alter index reorganize / rebuild ?
2
votes
1answer
235 views

sys.dm_db_index_physical_stats is extremly slow

I have a database that is around 4.5TB, since we have parallel inserts (to reduce daily load time) on one table (partitioned by month) the clustered index on this table tends to be heavily fragmented. ...
2
votes
2answers
485 views

Possible to optimize SELECT query involving XQuery functions selecting from elements with arbitrary number of child elements

Suppose I have the following XML data, where <root> can have an arbitrary number of <child> children <child> can have an arbitrary number of <grandchild> children <...
2
votes
1answer
2k views

Database Properties Error - sys.spt_values missing but dbo.spt_values works?

We have SQL2012 SP1 installed on several servers and have run into an issue on one instance trying to view the database properties in SSMS. (This happens with both SSMS 11.0.5058.0 and 11.0.3128.0.) I ...
0
votes
2answers
391 views

SQL Server Core - Installed SQL Server features

When using a traditional installation of SQL Server one has the Installed SQL Server features discovery report. Googled and found no info if some equivalent was available for the core installation. ...
3
votes
2answers
31k views

Why can't we access SQL Server when a long query is running in Management Studio?

We have a SQL Server Database (2012 Standard edition). The database is accessed through an ASP.Net web page which displays the count of databases in different tables on page load. This work fine ...
2
votes
1answer
520 views

Cube Processing vs Database Processing

We have a SSAS database (SQL Server 2012 Enterprise) with 4 cubes in it. 1 of the cubes is significantly bigger than the other 3. We want to process the 3 smaller cubes at regular intervals during the ...
1
vote
1answer
605 views

Finding the Table and Column Based on a Known Value

I've inherited a rather large SQL Server 2012 database -- about 2TB, tens of millions of rows in some tables, and about 350 different tables. I have basically no references nor documentation. There's ...
2
votes
0answers
495 views

Sql Server 2012 Disk I/O Too high

I am running SQL Server 2012 on windows azure VM Windows server 2012. This is a live Database, about 250GB, with high usage. I applied some best practice articles for the temp-db long ago, and also ...
0
votes
1answer
1k views

Throw and RaiseError of level 16 do not cause alert to occur

The next alert has been created: USE [msdb] GO /****** Object: Alert [Notify] Script Date: 09/09/2014 11:38:31 ******/ EXEC msdb.dbo.sp_add_alert @name=N'Notify', @message_id=0, ...
2
votes
3answers
1k views

How to check history of primary node in AlwaysOn HA environment?

How do I see history of failovers of the primary node in cluster? I'm trying to figure out at what point secondary node started to act as primary. We are running Windows Server 2012 with SQL Server ...
3
votes
2answers
404 views

Restore database into new DB without replacing the file structure

Our Network Admin has setup a MS SQL 2012 server and wants the DBs setup so the database files are located on 4 different drives. My problem here is when I try to restore a DB from a different system ...
4
votes
1answer
255 views

Combining full-text and scalar index

Let's say we have a database of 12 million names and addresses that need to be searchable using full-text, but each row contains an integer value too, let's say COMPANYID. The table contains around ...
1
vote
2answers
93 views

SQL logins access

I am trying to set up a set of SQL logins (one for each developer) on a server who can have access to a given set of databases, currently the setup is as follows: Login 1: db_owner of databases 1, 2, ...
0
votes
1answer
282 views

Attempt to add DQS Cleansing Connection Manager to SSIS results in error

I reopened an SSIS project in SSDT-BI (with VS2013 Shell) which had been dormant for ~3 months to see the dreaded red "x" had appeared on my DQS Data Flow Component: I attempted to replace the ...