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)

2
votes
3answers
206 views

Use of IMPERSONATE permissions in SQL Server?

During some reading, got to know about the Impersonate permissions. From what I've read, it is more like creating a copy of the user with all the permission levels under a different name. I understand ...
2
votes
1answer
378 views

Is there a way to get SQL Server capcity constraints dynamically

I have some code which creates database tables dynamically, and I want to ensure that people don't try to do things that result in me trying to create a table with more columns than what SQL Server ...
0
votes
0answers
17 views

Clustering SSAS

We are using both SQL Server 2012 and 2014 always on. Here is my issue: we have an application that I have set up to use Availability groups(2014), however the issue I am having is that it uses ...
1
vote
2answers
42 views

SQL Server Always On or Mirroring & Log Shipping

We have two data centers with a 2 ms ping time between them connected by a site to site VPN. In the primary data center we have 2 servers, in the secondary data center we have a single database server....
1
vote
2answers
143 views

PERCENT_RANK does not distribute over 100

I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way: select Term, Frequency, percent_rank() over (order by Frequency desc) ...
-1
votes
2answers
67 views

How to PIVOT/UNPIVOT if I don't know how many columns will it have?

I have this simple query: I want to list something like this: Unidade1 \ PLaca1 \ Placa2 \ Placa3 Unidade2 \ Placa1 \ Placa2 But I don't know how many placas a unidade will have. I was ...
3
votes
1answer
44 views

FastTrack Data Warehouse -E startup parameter

We are running a data warehouse based on the Fast Track architecture. One of the recommendations with Fast Track is to enable the -E parameter at SQL Server startup (increases the number of extents ...
0
votes
2answers
34 views

Passing a list to a SQL query in SSIS

I need to be able to pass a list of undefined size that will contain numbers like so ('1900'),('1920'),... to and OLE DB source in SSIS. as of right now I am trying a lot of different things that ...
0
votes
0answers
78 views

SQL query runs fast on local computers but slow on production server

I am running a fairly basic query on my local development machine which runs almost instantly. However on the server its taking around 3-4 seconds to return results. I have ran Client Statistics on ...
0
votes
2answers
34 views

Can I tell if a stored procedure has executed against a particular database?

As a result of a release a db is behaving "unusually". I suspect a stored procedure was missed during release, but my dba is telling me 100% it was executed. Is there any way to definitively prove ...
0
votes
1answer
27 views

Temporarily stop Log shipping [closed]

How can I temporarily stop log shipping --> Do the task --> Start the Log shipping ? Is it possible ?
0
votes
1answer
36 views

Shrinking Database

We have two databases in SQL Server 2005 with installed navision database in both database. Due to disk space I want to shrink old database which is not in use and having 40% free space in .mdf and 20%...
0
votes
0answers
22 views

Why does SQL Server Managed Backup create so many full backups?

I have a SQL Server 2014 database with Managed Backup configured to send regular backups to Azure storage. It's been happily churning out log backups for months now that provide full coverage for the ...
3
votes
2answers
54 views

Can an SQL Job Deletes itself after its end date?

I have created a job which will sets up its start date and end date automatically with a dynamic value. For example: Job will run for eight days and at eighth day it needs to automatically deletes ...
2
votes
2answers
34 views

Help with FOR XML EXPLICIT query

I have an audit table constructed (basically) as follows: CREATE TABLE [dbo].[AUDIT]( [UNIQUE_ID] [int] IDENTITY(1,1) NOT NULL, [TABLE_NAME] [nvarchar](50) NOT NULL, [TABLE_UNIQUE_ID] [int] NOT NULL, ...
0
votes
1answer
30 views

Creating multiple windows login on a single databae

I am able to create a window login on a database I created. This windows authentication login is based on a windows user that I created earlier, but I don't seem to know how to login into SQL server ...
0
votes
2answers
20 views

SQL server after RAID problems

We had a SQL server die due to some RAID problems. We sent the machine out to a data recovery company and they are able to recover the .MDF and .LDF files from the drives. Will I be able to use ...
0
votes
2answers
41 views

Migrate a SQL Server instance from one server to another [closed]

I have a database for my website running on AWS. My web admin has given me access to the database, and I'd like to host and manage it on my own. How do I migrate the SQL database from AWS to my own ...
1
vote
1answer
33 views

First and last records in a string of linked rows

I have been handed an (ancient) database in SQLServer format containing rental records. These records can be modified by the customer while the rental is still in flight, if they want the drill for ...
0
votes
1answer
25 views

Call to sysmail_delete_mailitems_sp keeps deadlocking

I run SQL Server 2012. I have a maintenance job that runs every night. At the end of the maintenance, I do some cleanup and call this: Begin try Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '...
3
votes
1answer
62 views

SQL database in status SUSPECT

I had a database in status SUSPECT (No items). I have not Backup. I've only .mdf and .ldf files. After reading all answer regarding this topic, I executed all commands to fix this status, but without ...
0
votes
0answers
38 views

Issues with partitioning

Looking for scale out option for my SQL server database. I have a table that has 15 million rows and it adds 10,000 rows every day. From various options available to scale out I opted for partitioning,...
0
votes
2answers
34 views

Get attribute value of XML

I went through at least 5 question on SE, but I must be doing something very bad out of frustration already. I have a stored procedure that returns an XML like this: <pmsg:Messages> <...
1
vote
1answer
28 views

Can I use SNAPSHOT for large catalogs?

I've been reading about Snapshots, and it says you can send small catalogs lets say, in a monthly routine. I liked the Idea and i will start to study about this, but can I use snapshot for large ...
0
votes
0answers
42 views

Selecting a View consumes 4GB memory but executing SQL statement of View only 70 MB

I am facing high memory consumption on my database server. During diagnosis, I found a view consuming 4 GB memory. But when I executed it's statement (select statement of view) it's taking only 70 MB. ...
8
votes
2answers
107 views

Increase a counter for each changed row

I'm using SQL Server 2008 Standard, which doesn't have a SEQUENCE feature. An external system reads data from several dedicated tables of the main database. External system keeps a copy of data and ...
1
vote
2answers
65 views

Change database loaction

I have installed new MS SQL Web Edition on C Drive and I have also another D drive on server. Now I want to change database location, like when we make new database it should be make on D drive. Can ...
3
votes
1answer
41 views

Any disadvantage to automatically changing FULL backup on AlwaysON Secondary to COPY_ONLY?

I've been playing around with Ola's maintenance scripts recently, and they are absolutely great. However, I was considering one scenario and was surprised when I found it wasn't catered for. Maybe I'm ...
1
vote
2answers
21 views

issues with creating varchar returning functions [closed]

I have an issue while creating functions in sql server, am very new to sql server Below mentioned is the code which i used, please any issues with syntax. CREATE FUNCTION Fn_Check_Customer_Credit (@...
3
votes
1answer
58 views

Clustered Index Scan (Clustered) vs. Index Seek (NonClustered) + Key Lookup (Clustered)

I have the following tables and content create table t(i int primary key, j int, k char(6000)) create index ix on t(j) insert into t values(1,1,1) insert into t values(2,1,1) insert into t values(3,...
1
vote
1answer
32 views

Remote Connection to SQL-Server for data extract [closed]

I have zero experience with MSSQL Server, but I'm trying to determine the most effective way to extract data from table into flat files from a remote location. Here's the usage case scenario: We are ...
1
vote
2answers
31 views

Maximum Attachment Size in database mail

Good Afternoon, I am trying to send an attachment via database mail. declare @mail_recipients VARCHAR(4000) = 'abc@123.com' , @mail_subject VARCHAR(500) = 'subject' , ...
1
vote
2answers
36 views

SQL Table Design Schema - To Normalize or Not?

I am considering a table schema design for a project. The design involves a Many-to-Many Relationship. The 2 related tables are: a) Class b) Subclass A Class can have many Subclasses, and a ...
0
votes
1answer
50 views

AG (SQLServer 2014) - Suspend Data Movement

I have a primary replica and one secondary. Multiple availability databases. Secondary replica is located at DR site. For DR testing, is it possible to failover to the secondary replica, make (test) ...
3
votes
1answer
230 views

How to avoid implicit conversion for an Integer column

SELECT [BusinessEntityID],[NationalIDNumber],[LoginID],[OrganizationNode] FROM [AdventureWorks2014].[HumanResources].[Employee] where BusinessEntityID = 5 I get convert_implicit() function ...
3
votes
1answer
80 views

how to find out what process(es) caused the autogrowth of a database or a database file?

I have a process(es) hungry for tempdb, but I am struggling to identify this process. any ways I could achieve this? We have been alerted as Tempdb has now grown into the space you reclaimed ...
0
votes
0answers
9 views

Provide Access to view when access on table is denied

I have a table T1 in database which contains columns C1,C2,C3 AND C4. Of which C1 and C2 contains sensitive information. Access to this table is denied to a group G1. But i need to provide access to ...
5
votes
6answers
127 views

Alternative to xp_cmdshell for emailing a report as a CSV file

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible. I know that xp_cmdshell itself poses risks, even with a proxy ...
1
vote
1answer
81 views

Where is “UNDO” in SQL Server?

Seems like a simple question but I've found conflicting information out there. Can anyone enlighten me (ideally with a rock-solid source)? Is it: Part of the transaction log, per database? Part of ...
1
vote
2answers
33 views

Performance issues with partial join query

I have two tables: dbo.allowedURLs (a unique list of all permitted domains) *** DomainName *** google.com facebook.com etc... dbo.loggedURLs (a list of URLs processed by a proxy server) *** ...
3
votes
1answer
33 views

how to change the file directory of your log and data files and what are the effects

I have inherited a SQL server 2012 instance where the file directories of the log and data files are on the same folder on the same drive(all are in the C:\ drive). My task that I will be doing is ...
0
votes
0answers
35 views

What is the VERSIONING_COMMITTING wait type in SQL Server 2012?

I have a fairly large SQL Server 2012 database in an availability group that experienced performance problems for about an hour, with the bulk of the wait in HADR_SYNC_COMMIT (waiting for synchronous ...
0
votes
1answer
35 views

Cannot restore SQL database backup to new database because new database is in use

I am attempting to create a new database from a backup file of an old database that is still in use. At one point I was able to successfully restore the database, but had to delete it later after ...
0
votes
1answer
40 views

“CREATE FILE encountered operating system error 32” when attempting to attach database

I am having trouble connecting to a database I created in SSMS. Keep in mind this is my first time working with databases. All I've done in this database is create some tables and add a few records. ...
3
votes
2answers
153 views

SQL Prove memory pressure - high Buffer Cache Hit Ratio, but low Page Life Expectancy

Our production server (2012, VM + SAN) has 32 GB of RAM, the database size is ~80GB. The application uses TempDB heavily - disk is hit ~100 MBps both reads & writes. Seeing tons of SQL ...
0
votes
1answer
21 views

Mapping 5 columns to 1 (ID, dimension) table without 5 joins?

I'm using survey monkey's API to dump survey data into a SQL data warehouse. So I'm dumping response data into a table 'resonses' thusly: survey_id, Q1, Q2, Q3, Q4, Q5 survey3944, 848948344, ...
1
vote
0answers
45 views

SQL Server: How to select a snapshot for a transaction? [closed]

PostgreSQL has a feature to select a snapshot for a transaction using: SET TRANSACTION SNAPSHOT snapshot_id Does SQL Server have a similar feature? Edit: Answer to: What is the use case? In an ...
0
votes
0answers
29 views

Query Statistics in management data warehouse empty

i am using management data warehouse form SQL Server 2012. I used it before on a 2008 instance a few years ago and everything worked good. However this time i cannot see any Query Stastics data ...
15
votes
3answers
1k views

Was Management Studio removed from SQL Server 2016 installation media?

I just tried installing SQL Server 2016 Enterprise but I noticed that Management Studio was not installed by default. I also noticed there is a separate link to SSMS in the installation guide which ...
0
votes
2answers
29 views

Always On Listener Behavior - DBs removed from group

We have multiple multi-site always on failover cluster instances in our env. We have an enforced internet outage being imposed on us and at the time the link between the data centers will be down for ...