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)

1
vote
1answer
141 views

Clear missing index DMVs

How can I clear missing index DMVs such as dm_db_missing_index_group_stats, dm_db_missing_index_groups and dm_db_missing_index_details? I don't have an option to restart the server or taking it ...
3
votes
1answer
775 views

How to avoid Forced reboots using unattended upgrades to SQL Server 2014

Upgrading seven instances from SQL Server 2008R2 to 2014 via the ADMINISTRATOR command line using a configuration file. The upgrade runs fine but when I move on to upgrading the next instance I get ...
1
vote
0answers
204 views

SQL Server 2008 Linked Servers Consistently Dropping Connections

I have set up a Linked Server between 2 SQL Server 2008 R2 databases (both 10.50.1600). The linked server worked normally for several weeks until I received reports that users were not able to hit ...
1
vote
1answer
94 views

SSRS executionlog having rowcount 0

I'm reviewing some execution logs for long running queries in SSRS. A common thread I'm seeing is reports that seem to take a few hours to process, with a rowcount of 0. I'm using the following query ...
-2
votes
1answer
315 views

Find difference between two database and output them in a script sql [closed]

I had a SQL Server 2008 R2 database. I have dump it and made some mods in a copy. So, I have two databases: 1) the older version 2) the new version I need a script SQL to update the number 1) to ...
0
votes
1answer
153 views

SQL Server 2012 Enterprise: Copy Databases to New SQL Server, Same Domain, Users, Etc

Am having trouble with what seemingly should be an easy process. I'm a SQL Server n00b, so please be gentle and very explanatory with your answers. Currently, I have a SQL Server 2012 cluster ...
0
votes
1answer
179 views

Identity/Primary Key columns pattern for performance in SQL?

I was discussing about database layout/design with a friend and I got some questions about performance and best practices. I'm mainly a software programmer, so I may lack "common sense" regarding ...
-1
votes
2answers
60 views

What happens when play with constraints [closed]

I'm inserting data from one DB to one other on another instance by using the following method: Create insert into scripts from the source DB. TargetDB: DROP constraints, insert data and Create ...
2
votes
2answers
262 views

Programmatically determine whether a database was shut down cleanly or whether it required crash recovery

There are various ways to shut down the SQL Server service (I can think of at least: SHUTDOWN, SHUTDOWN WITH NOWAIT, stopping the service (including bugs), killing the process, power loss). An ...
2
votes
1answer
794 views

Listing expired SQL logins

For audit purposes, we need to list all SQL logins with their creation and expiration dates. Is there a way to script this for all of my SQL Servers, rather than performing the task manually through ...
2
votes
1answer
165 views

Powershell Output Help

I have wriiten the following SQL script to execute using powershell. cls foreach ($svr in get-content "demo.txt") { $con = ...
0
votes
2answers
188 views

what is a good use case for splitting user table by role

I have a requirement where there are primarily 5 roles in the system. Each of these roles increase in hierarchy from junior to senior most role. Each junior role is a subset of its immediate next ...
0
votes
2answers
146 views

Writing Conditions in Left join sql [closed]

I want to know why these queries give different results Select * FROM Table1 T1 Left Join MyTable MT ON MT.studentId = T1.StudentId AND T1.class = 7 Select * FROM Table1 ...
2
votes
1answer
68 views

Having problems with concurrency while trying to lock a record in custom messaging system (Queue)

We are building our own custom messaging system and are having concurrency issues. Here are the rules: A process (EXE) console application locks 3 records and returns them No other process running ...
0
votes
2answers
2k views

Adding Last 12 Months to a row in a table (SQL Server)

I want to add the last 12 months to a SQL Server table, something like the following: FixedText Jan-14 Feb-14 Mar-14 Apr-14 May-14 Jun-14 Jul-14 Aug-14 Sep-14 Oct-14 Nov-14 Dec-14 Jan-15 Please can ...
7
votes
4answers
1k views

At What Point Should I Split or Partition a Very Large but Simple Table

Our site has some large but simple (INT, INT, DATE) tables for stats. Each table has up to 300,000,000 rows, and gets bigger every day. The hosting provider has suggested that we split or partition ...
3
votes
1answer
134 views

How to create a Relation between a table and a many-to-many relation table

I am buildng a new database inside an Sql server 2008 r2.I have the following tables & business rules:- Skill that have predefined levels assigned to it, such as junior, senior, manager. Staff ...
2
votes
1answer
111 views

Resource cost of a blocked query

I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking. The reports are not business-critical, and they are frequently killed to alleviate the blocking. ...
1
vote
1answer
1k views

Add a column with Not Null [duplicate]

I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column . When I'm adding this I am getting the following error: Unable to ...
4
votes
2answers
2k views

How to remove identity specification for multiple tables

I am having total 109 table in Database and now i am migrating to Amazon server with identity insert and now i want to remove auto increment column specification for all tables. How set auto increment ...
7
votes
1answer
3k views

Why not rebuild indexes with page count <1000?

I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most: SELECT dbschemas.[name] as 'Schema', dbtables.[name] as ...
1
vote
0answers
234 views

Import a directory of dbf files to sql server

I am trying to import .dbf files using for each container in SSIS. But i keep getting errors to map the files and other errors. Here is what did. Use for each container. Use a data flow task in ...
4
votes
2answers
1k views

Join on dm_db_index_physical_stats very slow

I'm running a small query below in a MS Dynamics AX 2012 database and it runs more than 5 minutes which I had to cancel and it is showing PAGEIOLATCH_SH wait type. The database data file is 560GB and ...
1
vote
0answers
52 views

Error 18456 State 38 on Deleted Databases [closed]

I deleted some databases but a service account keeps trying to access them. This is filling up my SQL Server errorlog file. I used profiler and got the names of the databases, but I do not know how ...
4
votes
2answers
100 views

Store Repeated Data

I have a table (Key tinyint, Value bigint). There are a billion rows for each distinct Key value and only about 100 distinct Key values. Is there a way to not have to store Key in the every row ...
13
votes
2answers
530 views

Paging performance with customizable sorting over many millions of rows

In our application we have a grid where users can page over a large number of records (10-20 million). The grid supports sorting in ascending and descending order in a number of columns (20+). Many of ...
4
votes
1answer
159 views

Schemas and user permissions

When our custom, home-grown CMS creates data tables using the GUI, it creates them against the site-specific user schema (i.e. sitename) instead of the dbo schema. The issue is that our ...
1
vote
0answers
424 views

There was an unknown error applying the filestream settings. check the parameters are valid. (0x80070002) [closed]

I am getting the following error while trying to configure FileStream in SQL server 2012. There was an unknown error applying the filestream settings. check the parameters are valid. (0x80070002) ...
11
votes
2answers
508 views

Understanding statistics, execution plans, and 'ascending key problem'

I'm trying to better understand (conceptually) the relationship between statistics, execution plans, stored procedure execution. Am I correct in saying that statistics are only used when creating the ...
2
votes
1answer
775 views

Why can't trigger be dropped from database?

One of the databases I manage is provided by my company's vendor which we restore in order to pull data out for our data warehouse. I'm writing a stored procedure to go through the database users and ...
9
votes
2answers
941 views

Using indexed views for aggregates - too good to be true?

We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g. SELECT ...
2
votes
1answer
247 views

How to suppress activation procedure output from the error log?

I found so many below informational messages (bulk messages with no any error) are logged in the SQL Server error log. The activated proc 'x' running on queue 'y' output the following: 'z' I ...
2
votes
1answer
202 views

How to update commands in Ola Hallengren's SQL Server jobs?

I am looking for an easy way to update the jobs that get created from Ola's MaintenanceSolution.sql script without having to completely delete and re-create them or having to modify each job ...
0
votes
0answers
1k views

Unable to connect to or create SQL Server database using asp.net [closed]

In my asp.net project I have SQL Server database with some tables. Unfortunately my PC was not properly shut down and and after that I am unable to connect to my database from Visual Studio (I am ...
21
votes
7answers
2k views

What is the most efficient method of performing the FIZZBUZZ test in SQL Server?

This may not be completely on-topic, however it's a slow day here. Is there a more efficient method of obtaining a list of numbers from 1 to 49 with a column containing the words FIZZ when the number ...
1
vote
3answers
374 views

Building year to date aggregations using SQL

I need some help with building a monthly aggregation using only SQL. Imagine the following table: TranID DateCode Account Value 1 20140101 1 5 2 20140106 1 -3 3 ...
1
vote
1answer
179 views

What do these parameters mean at the beginning of my SQL Server query plan?

I'm looking at query plan in SQL Server Mgmt Studio and I see something like this: (@P0 nvarchar(4000), @P1 nvarchar(4000)) Update... What does the type mean beside the parameters? For this example ...
0
votes
2answers
768 views

Check if database has changed to determine need to perform backup

I want to check SQL server database is modified or not. Reason to check database is modified is for backup(sql database backup) purpose. So I will be doing a full backup first and if any data ...
0
votes
0answers
26 views

Login failed for user 'x' [duplicate]

We receive an error Login failed for user 'x' sometimes from our application. We are 100 % sure the login is working because we checked it by manual login from SSMS as well as from the application. ...
1
vote
2answers
275 views

Determine whether there is any blocking while deleting large number of records [closed]

I would like to know whether there is any way to determine why it is taking too much time say 3 minutes for deleting 50K records from a database hosted in Sql Azure. I have clustered index on primary ...
7
votes
2answers
1k views

Can rebuilding indexes cause worse performance after the rebuild is finished?

We have a customer database that is heavily fragmented - practically every table with more than 1000 pages has >95% fragmentation. Fill factors are set to sensible values, but page space usage is ...
1
vote
1answer
513 views

Index rebuild log growth

How much log index rebuild will generate.I remebered reading rebuild index should generate same amount of log file as table size.But my tests are showing otherwise.We need this estimate since we are ...
2
votes
1answer
2k views

SQL Server Agent Error 1053

A few months back I installed SQL Server 2008R2. At the time of installation I used 1.Mixed Authentication 2.NT Authority\NetworkService as the SQL Agent user name, and the same user name for all ...
3
votes
1answer
192 views

Trace Flag 3688 not working on SQL Server 2014

We are using some third party tools that fill up the SQL Server logs with entries like: SQL Trace ID 1 was started by login "sa". SQL Trace ID 2 was started by login "XX\xxxxx". SQL Trace stopped. ...
2
votes
1answer
100 views

Can I grant multiple remote LocalService accounts access to a SQL Server database?

I am designing a multi-environment Windows-based software product that will extensively employ Windows services running as LocalService to perform data access operations. All of the data access ...
1
vote
0answers
21 views

AlwaysOn SQL Agent Jobs [duplicate]

We are running AlwaysOn on three replicas. The primary and first secondary are synchronous for failover purposes. The second secondary is a read-only asynchronous server for reporting. The only thing ...
12
votes
1answer
524 views

What is the cause of this problem with CONVERT()?

Consider the following two statements: PRINT CONVERT(NUMERIC(38, 0), 0x0100000001, 0); PRINT CONVERT(NUMERIC(38, 0), 0x0100010001, 0); Both statements return -1; isn't that incorrect since the ...
0
votes
0answers
9 views

Secondary Clustered DTC will not work when Primary goes offline - SQL Server Issues [duplicate]

I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, ...
2
votes
1answer
351 views

Sql Server 2008r2 Replication Snapshot Agent Hang

I am setting up a new merge publication, but I can not get the snapshot agent to run successfully. In fact, I can not run any snapshot agents for any of the other publications on this server now. ...
3
votes
1answer
419 views

Secondary Clustered DTC will not work when Primary goes offline - SQL Server Issues

I've been working on setting up clustered environment with a two new DTC roles, DTC1 and DTC2. I set up DTC1 which allows both clustered VM's and SQL Server Instances to function correctly. However, ...