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
0answers
193 views

How to gracefully handle a source database restore from backup in a data warehouse

We are facing a challenging situation with our data warehouse source databases. Frequently these sources databases will be restored from backups. There is a high turnover of data entry persons who ...
7
votes
2answers
766 views

Seek predicate not using all available columns

I have a strange query compilation issue which is hard to reproduce. It only happens under high load and cannot be easily repeated. There is a table T with columns A,B,C,D. There is a non-unique ...
4
votes
4answers
748 views

Changing the representation of NULLs in pg_dump plaintext output

I'm trying to convert a large-ish Postgres DB (500 GB) to SQL Server 2012. After investigating a few third-party tools and being disappointed in features, performance, or both, I started pursuing a ...
17
votes
4answers
29k views

Formatting T-SQL in SSMS 2012

According to this Microsoft document: http://msdn.microsoft.com/en-us/library/ms174205.aspx I am supposed to be able to use ctrl+K then ctrl+D to format my SQL documents in SQL Server Management ...
2
votes
2answers
4k views

Finding the last command issued by a blocking process

I've developed an interface from ERP SAP Business One to warehouse management, and am facing problems concerning database locks. My program includes direct write operations into the ERP database (a ...
0
votes
1answer
2k views

What are safe characters to use in SQL Server Full Text Search? [closed]

I've been playing with full text search on SQL Server 2012. I must say my attempts to target special characters in search queries were futile - SS does not understand them. What's more, sometimes the ...
2
votes
3answers
2k views

Replication Options with data subsets

We have an application that makes of of a SQL Server 2012 (ServerA) database. Some data that we need to display, though, comes from another database, but it's SQL Server 2008 (ServerSOURCE). The ...
0
votes
2answers
374 views

Update primary SQL Server in mirrored environment using transaction logs from old environment

I'm currently migrating a large (~40gb) database to a new environment. The new environment is a HA/mirrored set up with primary, mirror and witness nodes. I'm backing up the database in the old ...
4
votes
1answer
1k views

Running functions in parallel

This is for SQL Server 2012. We have some import processes for FTP files that are picked up and read into a staging table, from there we massage/check the data before moving into production. One of ...
5
votes
2answers
9k views

Create table group sub-folders in Management Studio

When I was exploring the master database in SSMS I noticed that under the Tables folder there is another folder called Systems Tables that houses a bunch of tables. Is it possible for us to create a ...
1
vote
1answer
602 views

SQL Import/Export defaults to byte stream on export

So, I've done some research and I can't seem to figure this out. I'm not sure if it's some setting that I'm missing or what. For the basic info, running SQL2012, 64 bit, all that good stuff. I ...
9
votes
6answers
37k 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 ...
2
votes
4answers
2k views

SQL Server grant permissions to STANDBY database

So, first: the setup. We have SQL Server 2012 (ServerA) running in domain A. We have SQL Server 2012 (ServerB) running in domain B, separate subnet, no trusts. These are completely separate domains ...
7
votes
1answer
2k views

Estimated vs Actual rows differences (actual much smaller than estimated) - sort

I'm running a query that is processing some nodes out of an XML document. My estimated subtree cost is in the millions and it seems it all comes from a sort operation sql server is performing on some ...
3
votes
2answers
7k views

Transaction Log Maintanance While Using AlwaysOn Availability Group

We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012. Server and AG Configuration as below: SQL12_SRV1 --> AG1(PRIMARY) - SYNC -->> DBTest SQL12_SRV2 --> AG1(SECONDARY) ...
0
votes
2answers
1k views

SQL Server Maintenance Plan TSQL query to log the backup databases

Does anyone know how to use the TSQL task in SQL Server 2008 Maintenance Plan to log (insert) the databases that where backed-up? Imagine a Maintenance Plan that does the backup for DB1,DB2 and DB3. ...
2
votes
1answer
426 views

SQL Server connectivity and script execution using PowerShell

I am designing a PowerShell script that can execute some sql scripts in a folder. Actually this must be done remotely. I am able to connect to the particular server remotely using: $SqlConnection = ...
0
votes
0answers
40 views

How can I use Query notification and what are the requirement? [duplicate]

I want to use Query notification in my app to get the notification from database for updated data, so that app will fire the query to get updated data instead of monitoring the database for every 5 ...
2
votes
0answers
538 views

How to push the data from database?

I want to push the data from database to application instead of application pull the data.I have installed ms sql server and apache tomcat server. I have my application in apache tomcat, here I made ...
0
votes
1answer
2k views

Permissions on sys.syslogins

I've got the following scenario: ( loggend in on SQL-Server with full admin-privileges ) Creating LogIn, User, Role and associate them. IF NOT EXISTS( SELECT "name" FROM "master"."dbo"."syslogins" ...
1
vote
1answer
876 views

Count number of common column names among all tables in a database

Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over ...
30
votes
2answers
29k views

What is the actual behavior of compatibility level 80?

Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected. As far as I understand compatibility modes, it is about the ...
0
votes
2answers
1k views

SQL Server AD groups membership seems cached - how to flush to test? [duplicate]

I am configuring a new, fully AD group based security scheme on an existing SQL Server 2008. When I change the group to which I belong to test my configs, it seems that there is quite a delay between ...
2
votes
1answer
735 views

Will certain Database Compatibility cause performance degradation?

I'm running a variety of SQL Servers from 2005 to 2012 across several machines. I have many of these databases running in SQL Server 2000 compatibility mode. Will using an older compatibility in a ...
4
votes
2answers
1k views

SQL server agent SSIS error

I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive. I don't ...
2
votes
2answers
2k views

How do I interpret the log when I run DBCC TRACEON (3502, 3504, 3605, -1)

I have been using DBCC Traceon (3502, 3504, 3605, -1) because it was recommended in a blog for discovering performance issues related to I/O. I'm running MS SQL Server 2008 R2 SP1 Results in my SQL ...
17
votes
1answer
28k 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 ...
2
votes
2answers
221 views

Using Change Data Capture with a frequently changing schema

We are following an agile development process, which in this case means that we frequently make changes to the database schema, e.g. adding new columns, moving data to other columns, etc. Is there a ...
0
votes
0answers
85 views

Windows Server failover cluster [closed]

I have 5pcs. IBM System x3650(7979 - ex. ESX/VMWare, planned EOL) now with baremetal Win2012 DataCenter, every machine has the same HW and SW (inc policy). I want to create two clusters (MS SQL), for ...
1
vote
1answer
1k views

Querying Change Data Capture data

I have Change Data Capture enabled and working fine. The question that I have is how do I query the data to show users what changes occurred.
4
votes
2answers
2k views

Issue importing large file into SQL server

I am trying to import a 4gb csv file into a new table in sql server using the wizard. And when I do I get the errors below. I also included the 2nd row of the file I am trying to import without the ...
2
votes
2answers
2k views

Databases outside of AlwaysOn availability group stuck in RESTORING status [closed]

I am testing the AlwaysOn feature of SQL Server 2012. I have 2 servers set up and can successfully create availability groups and fail them over between servers. However, any databases that DO NOT ...
9
votes
3answers
3k views

Get transactions that failed or were never committed

Is there anywhere I can look to find failed transactions in SQL server 2012?
4
votes
1answer
118 views

What is the difference between these two index declarations and do I need both?

I was examining a script with 'Display Estimated Execution Plan' and it made several recommendations for indexes to add to the database. I added them and the script did indeed run much faster. Now ...
0
votes
2answers
3k views

SQL Server to Oracle export using Microsoft OLEDB Provider for Oracle

I am trying to Export Data from SQL Server to Oracle using the "Microsoft OLEDB Provider for Oracle". I am using "Export Data..." option from Task menu. I get to connect to the source database (SQL ...
3
votes
1answer
292 views

Deploying database changes in high availability environment

We are currently looking at our deploy process, which consists of an application tier and data tier for a web solution. We'd like to develop a deployment strategy that does not require downtime for ...
2
votes
1answer
2k views

Do I need to use -f to fix insufficient max server memory settings?

SQL Server Version: SELECT @@VERSION; /* Microsoft SQL Server 2012 - 11.0.2100.60 (X64) Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) */ Issue: So, I was playing ...
4
votes
1answer
24k views

How can I get my linked server working using Windows authentication?

I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs ...
8
votes
2answers
1k views

What can I add to a server to make SQL restores faster?

I have a 2.8TB SQL database (mostly data files, some 400GB of log files) that currently takes around 9 hours to restore. This database is used for testing purposes and must be deleted and restored ...
17
votes
6answers
5k views

Trying to find the last time that a value has changed

I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table. Records are inserted into this table periodically. The ID will always stay the same but ...
2
votes
2answers
371 views

How do I maintain more audit history?

I created a SQL Server audit and when I view the audit logs I only see today’s audit. I am saving it to a file and I want to store audit for one year. Is there a way to store database and server audit ...
3
votes
3answers
5k views

Nonclustered index is faster than clustered index?

Both tables have same structure and 19972 rows in each table. for practicing indexing, i created both tables having same structure and created clustered index on persontb(BusinessEntityID) and ...
4
votes
1answer
2k views

How to take partitions or filegroups out of service

I'm an experienced SQL Server DBA but new to partitioning, and I have a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows ...
8
votes
1answer
783 views

How to make trigger code execute only for a specific application?

I was wondering if it is possible to enable a trigger just for my current application and not for any sql executed against the table. The situation: There are two applications working on the same ...
1
vote
0answers
19 views

How to take partitions or filegroups out of service [migration] [duplicate]

I'm an experienced SQL Server DBA but new to partitioning, and a had a couple of questions. Using SQL Server 2008 R2 Enterprise Edition. I've inherited a large customer metrics database that grows by ...
7
votes
1answer
1k views

Does Detach/Attach or Offline/Online Clear the Buffer Cache for a Particular Database?

A buddy of mine told me today that instead of bouncing SQL Server, I could simply detach and then re-attach a database and this action would clear the given database's pages and plans from cache. I ...
0
votes
1answer
184 views

Configure SQL Server Database for High Availability & Warehousing

I have 3 DB servers (DB1, DB2 & DB3 - all on Win 2008 R2 with SQL Standard 2008 R2 in the same datacenter). Let's assume AdventureWorks as an example database. My goal: - Setup DB mirroring with ...
-2
votes
1answer
72 views

Select query using a map [closed]

CREATE TABLE grid_rows( [row_id] [int] NOT NULL, [column_id] [smallint] NOT NULL, [column_val] [decimal](18, 6) NULL ) // (row_id, column_id) is a composite primary query For a given ...
7
votes
1answer
1k views

Index usage stats DMV indicates no index activity

I'm trying to obtain information on indexes. According to several websites, the below TSQL query should join information on indexes to the indexes table, returning indexes and related information. ...
2
votes
3answers
449 views

Database design for conditional detail table

I have an expense management system for monthly expenses. Expenses are of two types: one-time monthly expenses, and daily expenses for each day of month. I have created the following tables: ...