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)

5
votes
3answers
11k views

SQL Performance issue after migration from SQL Server 2005 to SQL Server 2012

Yesterday we migrated a SQL Server 2005 database to SQL Server 2012. We made a full backup and restored it on the new server. Problem now is that the database on SQL Server 2012 is much slower than ...
0
votes
0answers
109 views

Create replication sql server for DWH [closed]

I have a DWH and I need to make an update once a day from STG Database to DWH I need use replication in SQL SERVER, How I do this?
0
votes
1answer
302 views

Monitoring sql server

This question is related to monitoring of SQL Server. I want to send an email alert with all open transactions on SQL Server in HTML report whenever there is sustained 50-60% CPU usage for more than ...
0
votes
1answer
170 views

sql server disk I/o question [closed]

I have a question on disks. How much I/O can a normal disk and a SAN disk can handle? I have heard about SAN disks should be able to handle upto 200-300 mb/sec, how much does a normal disk can handle ...
1
vote
0answers
129 views

Phantom insert query

I have a log table which I have to clean up a bit because it is growing very fast. While deleting some historical data I reached count zero for that condition on which I have deleted the entries. 5 ...
4
votes
1answer
185 views

Every query plan statistic says my query should be faster, but it is not

I have a bit of a odd situation. I have two queries that produce the same result, according to SSMS and SQL Sentry Plan Explorer the first query should cost 95% and the 2nd query should cost 5%. ...
1
vote
1answer
167 views

Why does sp_rename not affect the output of sp_helptext?

create procedure Eg_Proce as begin select * from po; end go exec sp_rename 'Eg_Proce','Example_Procedure' After renaming procedure sp_helptext 'Example_Procedure' The definition of the ...
2
votes
1answer
248 views

Most efficent DELETE Method

I must choose between three possible DELETE methods in SQL Server. The table: RawData stays at around 500GB. Here is the table: CREATE TABLE dbo.rawData ( rowId INT IDENTITY PRIMARY KEY, ...
0
votes
0answers
1k views

Login failed for user '' reason:The account is disabled sql error [closed]

i got the error after change my SQL windows authentication security login set enabled to Disabled . But after that i dont able to reset windows authentication security login please help me ...
0
votes
1answer
6k views

SQL Server 2012 T-SQL Health Check Script / Stored Proc [closed]

I'm hoping not to re-invent the wheel. I was looking for a routine server side health check script(s) / Stored Proc(s) for SQL Server 2008+ hoping they work on 2012 that provide information in ...
6
votes
4answers
330 views

Only do log backup if necessary in SQL Server

We have transaction log backups taken every few minutes from many databases. Is there a way to determine if there were any committed transactions since the last backup and only then do a log backup? ...
2
votes
1answer
203 views

Saving passport information [closed]

For starters: Our company is based in Germany and thus is based on German law. Germany now has adopted a new act regarding anti-money laundering and which information needs to be stored. Now we're ...
1
vote
0answers
187 views

Bind web service/Report Manager based on IP address AND hostname

I'm trying to get Apache installed on our main database/application server, and I'd like to give it its own IP address on a secondary NIC so I don't have to use a nonstandard port. This requires that ...
2
votes
3answers
5k views

Multiple Transaction Log files and performance impact

So I've gotten into an argument with several people regarding the number of transaction log files a give databases should have. I've seen several post saying you should only have 1 transaction log ...
3
votes
1answer
18k views

How to solve the error Msg 547, Level 16, State 0, Line 1 [closed]

Please suggest me how to solve the error How to solve the error Msg 547, Level 16, State 0, Line 1 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The ...
1
vote
1answer
369 views

DTC Timeout when trying to update oracle table through link that contains a text index

Problem Given is a SQL Server connected to an ORACLE Instance as a Linked Server. It’s using the Oracle Client OLEDB Provider. When doing an update in a transaction on a table having a CTXSYS.CONTEXT ...
2
votes
3answers
2k views

SQL Server select columns which contain data

I'm working for a client to import data out of a generic off the shelf application database. If you can imagine a database that is designed by software developers that have no concept of database ...
0
votes
1answer
506 views

sql-server optimize query with lots of null checks

I have a query such as: select something from somewhere join stuff on condition join more_stuff on condition where somewhere.sometime is null and somewhere.someone is null and stuff.whatever is null ...
0
votes
2answers
67 views

Order of update column?

This is my Table Employee. Reocrds like below id first_name last_name salary start_Date region city 1 VINOTH Martin 5890.00 2005-03-22 00:00:00.000 North CHENNAI 2 BALU Mathews ...
0
votes
1answer
106 views

How to query transfers for single source to single destination?

This should probably be easier than I'm making it, but I'm scratching my head coming up with an efficient way to query the results I want. I'm working with a table that has a source field and a ...
4
votes
2answers
1k views

How do I delete unique rows from a table with duplicate keys?

I have the following data: road | ID | other column | other column | ------------------+------+---------------+--------------+ MAIN ST | 1 | ... | ...
0
votes
3answers
282 views

Rollback the sql server data [closed]

Is it possible to restore the rollback changes to a database? One of my client has done wrong updates on database so most of the tables have been updated with wrong data. That database is having with ...
2
votes
1answer
227 views

High Query Wait Times

We are seeing the below query as regularly having very high wait stats. What does this query relate to? System tables? Is it possible to reduce it's wait time? Thanks! select @reservedpages = sum(a....
3
votes
2answers
189 views

Differential databases (not backups)

When working with virtual machines often it can be very useful to have a base read-only VM then have several smaller VM's that use that larger VM as a base and write their changes to their own ...
4
votes
2answers
2k views

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

I have created a user and granted many permissions and roles to it including SQLAgentUserRole and SQLAgentReaderRole. My application checks the status of the agent to see if it is running or not, and ...
0
votes
1answer
133 views

Short CIrcuit in WHERE clause [duplicate]

How to make short circuit in where clause, e.g: select 1 where 1=2 AND 1/0=0 In the above example 1/0=0 condition should not be evaluate as the first condition is false.
2
votes
1answer
102 views

Confused on how a value gets added with the DEFAULT constraint

A recent definition I've read about Default Constraints: DEFAULT is specified for a column to assign a column a specific value if no value is specified when a row is added. I believe I've seen code ...
0
votes
0answers
387 views

Powershell Error Handling W/ SQL Restore Jobs

I would like to rebuild many of our SQL scripts into Powershell to further integrate my department with the Windows Operations team and to open up a lot of other options. Questions: 1-preference ...
0
votes
2answers
887 views

What is the best practice for free space for a SQL server database drive?

I'm trying to determine the best practice for sizing a single database on a 120Gb data drive that will contain the LDF and MDF files. I'm assuming that I don't want to set the size to be the maximum ...
3
votes
1answer
4k views

Dropped Database Connections in SQL Server 2008R2

We are having an annoying intermittent problem where database connections from our client application on a variety of stations will get dropped. We have applications that use ODBC, Borland Database ...
9
votes
4answers
72k views

I can't connect to my servers SQL database via an IP Address

I have setup a server that runs Windows Server 2008, and has SQL Server 2008 Express installed. I can connect to the machine's SQL Server Express database via the MACHINENAME/SQLEXPRESS. However ...
1
vote
3answers
10k views

What could cause an auto increment primary key to skip numbers? [duplicate]

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
3
votes
2answers
1k views

Altering Multiple Tables

I have database with 170+ tables in mssql 2012. Some of them contain the field "UserID", varchar(9) while others do not. Due to an application redesign, I need to alter all tables in the database to ...
1
vote
1answer
91 views

Database never maintained

Just moved from wrong forum i paste here my question: I just got a new client recently migrated from SQL Server 2000 to SQL Server 2005 due to vendor application limitation to new releases. Vendor ...
1
vote
1answer
81 views

Connecting to Active Active Cluster Services

I have a two node active active cluster setup as below: NODE 1 - SQLINST1 ( with DTC1 ) NODE 2 - SQLINST2 ( with DTC2 ) I wish to now have the application connect to SQLINST2 whenever ...
1
vote
1answer
411 views

Difference between COUNT(ColName) vs COUNT(ALL(ColName))

Both query return the same output: select COUNT(id) from Employee select COUNT(All(id)) from Employee So what is the difference in SQL-Server 2008R2?
0
votes
1answer
6k views

How to insert default values to a table with not null columns?

I have several tables with different number of columns, each can be either real or int, both not null (I can define them all real if required). I would like to write a query that inserts a row with ...
0
votes
1answer
479 views

understanding (and modelling) grocery shopping lists

I am having a hard time understanding how multiple product orders are mapped out. What is the best way of modelling a shopping list for multiple customers? I am developing an C# application that ...
1
vote
2answers
1k views

Numerous failed authentication attempts in error log

I'm having a hellacious time trying to figure out what is attempting to authenticate against a particular database server, running SQL Server 2008 R2 Standard Edition. The following is the error ...
1
vote
2answers
272 views

Changing two publications at the same time causes deadlock in distribution database

I have transactional replication setup. Let's say three databases on server A replicate to corresponding three databases on Server B. The databases are identical (same structure for different ...
1
vote
2answers
2k views

SQL Server hardware configuration: Best Practices [closed]

I inherited a relatively new database server that is light on hard drives. Currently, everything - OS, SQL Server, hosted databases, etc. - is running on a single partition in a two-drive RAID 1 array....
3
votes
4answers
13k views

How To change the column order?

How To change the column order in sql server 2008 R2? Eg: My table order is Eid------Ename-----Esalary Know i want this format like Eid---Salary----Ename Thanks.
2
votes
1answer
473 views

How SQL determine page exists in Buffer pool (internal)

When we execute SQL query, then Relational Engine optimize it and pass query plan to Storage Engine which then ask for a page with actual data. If data page isn't in memory, then SQL pull those pages ...
2
votes
1answer
284 views

Transaction Log usage by transaction

Is there a way to find out how much of the transaction log a specific transaction uses? I have a database with simple recovery model and a log file with max size 500 MB. During our tests we ran into ...
0
votes
1answer
724 views

Take query results and insert into new table [closed]

So I have the following query: DECLARE @tbl TABLE(value int) *--The same function but with the number of the random numbers ;WITH Nbrs (n) AS ( SELECT 1 UNION ALL SELECT 1 + n FROM ...
1
vote
2answers
170 views

Three databases : one instance or three?

I am a new DBA working for a charity and I have project to consolidate the multiple databases scattered across the organization. I have identified the main databases that are mission critical to the ...
2
votes
2answers
2k views

Maintenance Plan Job running at additional unscheduled times?

I have a situation that has left me really scratching my head. This is a SQL Server 2008r2 instance, used as the database server for a substantial corporate SharePoint service. As my company is ...
2
votes
2answers
6k views

SQL service does *not* start, port 1433 in use

Sorry to raise if this has been asked before. The SQL Server service is defined as manual, whenever I start it I get (event viewer): > Server TCP provider failed to listen on [ 'any' 1433]. Tcp ...
1
vote
0answers
436 views

ODBC Connection Not Refreshing

I am trying to connect FedEx Shipping Manager Integration Assistant to a SQL 2008 R2 database. I have a view created that has all the necessary fields to populate in the Integration Assistant. The ...
4
votes
1answer
2k views

Do SQL Server Views update their data types automatically based on what tables feed them?

Someone mistakenly created a bunch of nchar fields in database tables. I noticed this, and after examining the issue, we're moving these fields to nvarchar types and trimming the values inside. In my ...