All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag like sql-server-2016 if that is relevant to the question.

learn more… | top users | synonyms (2)

0
votes
2answers
1k views

SQL Server 2012: Full Text Catalog taking up all the disk space

We have created full text catalog and index for a pretty large table. The column on which the index is being created has a datatype of varchar(max). The problem started as soon as we started ...
0
votes
0answers
8 views

Replication from Enterprice to Standard edition

Can I define transactional replication while Publisher and Distributor are in Enterprise Edition and Subscriber is in Standard Edition? What I need to consider in case it is possible ( I already know ...
1
vote
1answer
173 views

Best way to transfer a few tables from reserve server to production

We have two MS SQL Servers (2012 Standard Edition on Windows 2012 Server) which are located in the same subnet. One is production server. Another is reserve server. Currently there is a ...
0
votes
1answer
22 views

AlwaysON AG synchronous commit mode disconnected transaction log effect and mitigation

I have an AlwaysON AG with one primary and one secondary replica in synchronous commit mode. The databases in the AG all have autogrowth disabled (standard policy within organisation). There is ...
2
votes
1answer
41 views

Creating a helper UDF or stored procedure and limiting external access

I'm looking for a recommended best practice for dividing functions and stored procedures into a 'public API' and 'private implementation details'. I'm working on a security system and I want client ...
1
vote
2answers
32 views

Execute stored procedure remotely using linked server

We have 3 SQL Server 2012 instances, 2 of them we're not db_owner. Some of our stored procedures use DMV and sys views and stored procedures that only work when executed locally. I'd like to ...
0
votes
0answers
24 views

T-SQL for creating staging tables with indexes for partition switch operations

I have some partitioned tables in a SQL Server 2012 environment. Is there an easy way to make the create table statement along with its indexes and constraints so I can partition switch to them on ...
0
votes
0answers
25 views

Table optimization in SQL Server with huge data [on hold]

I have a table in one of my databases which has 20 billion rows and keeps on increasing daily. Even if I do a select *, it takes a lot of time to fetch all records. There s only one primary key in ...
3
votes
1answer
14 views

Can SSDT deploy SQL server service broker objects?

Can SQL Server Data Tools (SSDT) deploy sql server broker objects? (So contacts, message types, services, queues and so on.)
0
votes
1answer
25 views

Database consists of a lot of unused columns and every table has same schema

I am a web developer so I don't know a lot about databases. The company I joined recently has a very mature Desktop ERP built in .NET and SQL Server, they are providing services to huge corporate ...
1
vote
2answers
26 views

How can I perform a full-text CONTAINS call across a list of phrases?

I have a list of words/phrases in a table phrases. I have another table with a full-text catalog indexing the description column. I want to populate a table results with a record for each phrase, and ...
4
votes
1answer
234 views

Replication throw an error after failover mirror partner is down in SQL Server

Received an error mentioned below after failover when mirror partner is down. Error : to fail over to a database which is not configured for database mirroring. code: 22037, text: 'Invalid ...
0
votes
1answer
187 views

SSRS ReportServer_XXX.log is giving incorrect CPU count

I am currently investigating an issue wherein sometimes the SSRS just suddenly stops accepting requests until we do an IISRESET command. This happens after we added an additional processor on the ...
0
votes
0answers
22 views

Replication Subscribers and AlwaysOn Availability Groups (SQL Server)

AlwaysOn Availability Groups with Replication Subscribers is running successfully with one publisher using below command.. -- commands to execute at the publisher, in the publisher database: EXEC ...
2
votes
1answer
216 views

CPU Time Slower in SQL Server 2014 than in SQL Server 2008

We’re upgrading a virtualized database server from an old SQL Server 2008 R2 Standard (vmware) to a new shiny SQL Server 2014 Standard server (hyper-v). We were doing some performance testing and to ...
3
votes
2answers
212 views

Updating large table worked before, now taking hours

BACKGROUND: I am using a non-production SQL Server to manipulate a large data set and run a few basic calculations. The original data file I have, which is about 30M rows, is missing records (not NULL;...
0
votes
1answer
18 views

How to generate list of dates — ie every day for the past 2 years? (sql server)

We're using a business intelligence system and need to load a list of dates so we can flag them as 'last week' or 'last 12 months' or some dynamic value. I'm wondering what's the simplest way to ...
0
votes
0answers
10 views

Can a snapshot be “disconnected” from the app logon, to keep ONLY the immediate changes?

Can a snapshot be "disconnected" from the app logon, to keep ONLY the immediate changes before that disconnection, without changes subsequent to the disconnection being written to the snapshot? I want ...
0
votes
1answer
28 views

OVER and PARTITION BY for db size change

I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the ...
17
votes
1answer
32k views

SIMPLE or FULL recovery model for databases?

When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default, but today i encountered this ...
3
votes
2answers
2k views

Differentiating tables and views in INFORMATION_SCHEMA.COLUMNS

I have a view which selects information from INFORMATION_SCHEMA.COLUMNS, which seems to give me both columns and views. Is there an easy way to tell which rows are columns and which are views? Should ...
0
votes
1answer
23 views

Updating and/or inserting several rows in the same SQL statement

I am implementing an account "ability" type thing - a users account is given the abilty to do one or many things in the system, like (simple examples) cancel orders or pay invoices. I have a table ...
0
votes
0answers
94 views

Cannot Update a System Collection Set

I'm trying to tweak the "Server Activity" Data Collection set as outlined in this blog post (I wrote the post, btw. Sorry--it's kinda long). It is working in SQL 2008 R2 and also in SQL 2014. However,...
-5
votes
0answers
19 views
0
votes
1answer
12 views

In SQL Server and using only client side encryption requests, can I use a different certificate for each client?

Based upon the UI dialogue box in SQL Server Configuration Manager, if I right click 'Protocols for server_name', it appears that I can only select one certificate to use. My experience so far tells ...
0
votes
1answer
60 views

SSIS - The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I am trying to use explisit transactions within SSIS. The database I am working with does not have the DTC enabled, so I cannot use SSIS' inbuilt transaction handling. I have execute sql tasks to ...
4
votes
1answer
175 views

Scenario of huge SQL Server Database backup

I use SQL Server and have a huge database with large compressed backup size (about 250 GB). The backup scenario of this database is: Create full backup at the first day of each month. Create ...
1
vote
2answers
167 views

Get first and last record for each date and calculate their difference

I need to calculate the DATEDIFF in seconds between the First record for each date with EventCode = 1 and the last record for each date with EventCode = 2. I have posted a sample data set for 2 days ...
-4
votes
0answers
26 views

How to create dynamic view [on hold]

How to create dynamic view for the below code. I am getting error must declare @top1table DECLARE @top1table varchar(50) DECLARE @top2table varchar(50) DECLARE @top3table varchar(50) set @...
1
vote
3answers
52 views

Need to intentionally create blocking processes for testing

My team and I have an issue where we need to be able to identify processes that are blocking other processes and kill them. There are a ton of scripts that are freely available to perform these ...
0
votes
0answers
21 views

Finding records only when all ids present in a group

Having these two tables id | val TableA -------- 1 | A 2 | B 3 | A 4 | B 5 | A 6 | C 7 | D id_A | data TableB ----------- 1 | a 2 | b 3 | c 5 | d 7 | e First I ...
0
votes
1answer
29 views

How can I delete a file from the primary filegroup?

In dealing with fragmented disk space, I added a second data file to one of my databases. Most of the data is now in the the second file, and I'd like to drop the first to simplify things. However, ...
7
votes
1answer
2k views

SqlPackage does not pick up variables from profile

I want to upgrade a database using .dacpac and sqlpackage.exe here is how I run sqlpackage: SqlPackage.exe /Action:Publish /SourceFile:"my.dacpac" /Profile:"myprofile.publish.xml" The ...
0
votes
1answer
192 views

How to verify if a SQL Account is still used?

We have a SQL Server Login on a SQL Server 2008, and no one seems to know if the account is actually being used or not. It is assumed it is being used, as it is configured in a Linked_Server ...
1
vote
1answer
22 views

Configuring a non-nullable foreign key uniqueidentifier with a default Guid.Empty value that exists in foreign table

Project Details: MS SQL 2008 R2 I have already asked what amounts to one half of the question here, but I am hoping someone in this forum can answer the other half, involving MS SQL server ...
0
votes
0answers
25 views

Retrieve a set of values for each id if atleast 1 value is present in SQL Server

I want the following group to be repeated for every projectid if there is atleast 1 row of value present and other row values should be NULL I want the following group to be repeated table1 id ...
0
votes
1answer
40 views

Update empty values with null

I create a Job that runs the query below for all databases in a server in order to update empty values with null in all columns for all tables. The problem I'm facing is this query literally killed ...
12
votes
1answer
386 views

SQL Deadlock on the same exclusively locked clustered Key (with NHibernate) on delete / insert

I have been working on this deadlock issue for quite a few days now and no matter what I do, it persists in one way or another. First, the general premise: We have Visits with VisitItems in a one to ...
3
votes
2answers
179 views

.NET SQLCLR Assembly not working in SQL Server 2016 (Error msg 10314)

I am migrating a database application from Windows 2008 R2/SQL Server 2008 R2 to Windows 2012 R2/SQL Server 2016 that uses a third party .NET CLR assembly to parse strings. The error I get is: An ...
0
votes
0answers
13 views

Design: Available domain database

I'm building website and service that will allow user to check if given domain is available. Right now I'm thinking about good way to store result from whois, but i dont want to store whole response ...
0
votes
0answers
12 views

MS SQL 2014 SP1 on Windows 10 - service doesn't start automatically

I've upgraded Win 7 to Win 10 with SQL 2014 SP1 Express x64 on it and the SQL Server service doesn't start automatically after machine restarts, even though it is configured to start so and it had ...
1
vote
1answer
22 views

How to Create Indexes Or Manage Performance For Complex Queries Before a Release?

Last night we had a major release that involved a massive data dump and some complex queries. We semi-anticipated the slowness and were able to react quickly to creating some new indexes in SQL ...
0
votes
2answers
38 views

Is it good to create new indexes for better performance - SQL Server [duplicate]

Hi I have a SQL Stored Proc that used to take 45 seconds to execute. Then I run the Execution Plan under SQL and saw some high cost steps: Then I right click that and select: And finally I ...
0
votes
0answers
10 views

AlwaysOn unusual behavior: sqlagent job rurns 2ndary while pointing to Primary?

We have SQL Server 2012 with AlwaysOn along with a bi-weekly scheduled sql server agent job that seems to also runs on the SECONDARY while pointing the PRIMARY's tables inserting the records twice. ...
1
vote
2answers
244 views

SQLServer - how to move tables between instances?

I'm not very familiar with SQLServer and, thus, I inadvertently created a database in a fashion I thought it would work. In SQL Management Studio, I right clicked the instance and created a xpto ...
0
votes
3answers
272 views

Availability Group how to determine last Failover time

I'd just like to know if there is any way to query when the AG group failed over. eg - this is the Primary Replica now, but I'm pretty sure it was the secondary yesterday? How can i find when the ...
0
votes
1answer
26 views

Querying SQL Server extended properties for tables/views and its columns

I need to get metadata information about tables/views and its columns in SQL Server. When I run the query separatelly for tables/views (sys.tables) or columns (sys.columns) I can get the extended ...
0
votes
2answers
40 views

SQL Server Storage for multiple data files

Recently, our company wants to add 4TB on the drive size for SQL Server. We have two drives now which have different size and data files. In order to make sure our SQL Server has the best performance, ...
3
votes
1answer
31 views

How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON

Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query ...
7
votes
4answers
9k views

Attempt to fetch logical page (5:65424) in database 2 failed

I am getting following SqlException on calling a stored procedure - Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to ...