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
2answers
4k views

How to properly clean Merge Replication Setup

What is the proper way to completely clean Merge Replication setup ? I have tried to delete the subscriptions, then delete the publication then "Disable Publishing & Distribution" & delete ...
5
votes
2answers
393 views

Given two known statements, how can I replicate deadlock?

I have two statements (an update against every row in one table (call it table A) and a delete on another table that looks up rows in table A) that I know are causing occasional deadlocks. It seems ...
-1
votes
1answer
486 views

Deleting large chunks of data

create table Grid( grid_id, //other columns ) //grid_id is primary key create table grid_info( grid_id, colulm_id, column_vale ) //grid_id is foreign key ...
2
votes
2answers
2k views

Update performance: clustered versus covering index

I have a simple, 3-column table with about 50 million rows in it. This table gets about 5,000 inserts/updates per second, and perhaps 20 queries per second are executed against the table. The table ...
2
votes
2answers
7k views

How to avoid empty rows in SSIS Excel Destination?

Does anyone have a way to avoid empty rows when using SSIS to export to Excel. Here's a simple example of one data flow task: OLE DB Source: Data Conversion (to handle the annoying UNICODE / ...
5
votes
1answer
4k views

Need to divide single column into multiple columns

My table has data with a single column: 1 2 3 4 5 6 7 8 9 10 And I want output like this: 1 2 3 4 5-first row 6 7 8 9 10 - second row Another has two columns like this: Column 1 ...
0
votes
1answer
246 views

how I can create check sum to validate no changes at int string pairs on table

Business case is. 1)Users print document on paper 2)users carry printed paper to Boss, Boss sing on paper 3)after that users going to administrator to digitally sign that paper. My task is to help ...
6
votes
2answers
859 views

SQL Server Replication Alternative Software

We have used SQL Server Replication for a long time and had some issues with it that sometimes we needed to reinitialize subscriptions to fix some issues & other times we needed to destroy the ...
-2
votes
1answer
422 views

Errors keeping stored procedures through restore from backup [closed]

We are running SQL Server 2008 R2 on two identical servers. One, called LIVE, is our production environment. The other, called QA, is our dev enivornment. In order to check backup integrity and run ...
6
votes
1answer
3k views

How do these snapshot isolation level configurations interact on a SQL Server 2005 instance?

I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is: Transaction ...
6
votes
3answers
11k views

Transaction Log maintenance when switching to Simple Recovery

Background: I recently inherited 50+ SQL Servers with 450+ databases. The nightly backups are roughly 8TB and, needless to say, we're using more disk space than we'd like. All of the databases are ...
17
votes
2answers
10k views

Stored procedures vs. inline SQL

I know stored procedures are more efficient through the execution path (than the inline sql in applications). However, when pressed, I'm not super knowlegeable about why. I'd like to know the ...
8
votes
4answers
56k views

High CPU usage on SQL server - Slow queries [closed]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
3
votes
0answers
44 views

High CPU usage on SQL server - Slow queries [duplicate]

Our MS SQL Server is using about 95% of the CPU-power. After a server (hardware) restart, or a SQL-Service restart, the usage is 0% and slowly increases over the course of 1-3 days. Depending on how ...
2
votes
2answers
3k views

How to find memory usage by table? [duplicate]

I want to discover how much memory is being used by each table. Is this information available in DMVs or elsewhere?
4
votes
2answers
65 views

Weird SQL error

am seeing the error logs below on my SQL server, I'm not sure if they are related to trace flag 3604. I also see something to do with memory dump as well. Please advice. 15:15:53.81 spid594 ...
2
votes
1answer
646 views

Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?

We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough. Table definition (with columns omitted ...
0
votes
1answer
219 views

SQL Server automatic transaction log truncation

Have looked through msdn but cant find answers to these: Any help much appreciated: In simple recovery mode: Is there any TSQL or alternative functionality that will provide a notification either ...
1
vote
2answers
2k views

How to count number of consecutive events matching status of last event?

In MS-SQL Server 2008, I have a table of events in the following format: Controller | ExecutionTime | Result 1 | 2012-09-24 09:00:00 | 0 1 | 2012-09-24 09:01:00 | ...
10
votes
3answers
704 views

Is backing a website by a SQL Server 2012 OLAP cube considered reasonable?

I have been tasked with architecting a solution for a large retail chain. They want to allow each of its 1.2 million customers to log on to a web site to see the distribution of recent purchases ...
2
votes
1answer
292 views

SQL restore duration

I am trying to determine the duration of backup restore. I executed a few restore command to restore some backups located on network share drive. Here is command and summary, RESTORE DATABASE [test] ...
3
votes
1answer
700 views

Insert performance with Geography column

I've been tasked with inserting data into a SQL Server table with a geography column. I've found that my times for doing inserts (same data 1.5 million rows) go up increasingly. I started out with no ...
3
votes
1answer
10k views

LDAP in SQL Server 2012

In our current system, we query the LDAP using SQL Server 2008R2 but are moving to SQL Server 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of ...
5
votes
2answers
3k views

Recommended distributor architecture for transactional replication?

We are planning to create a Transactional replication on 2 nodes of SQL Server 2008 R2, each node will be a publisher and a subscriber. What is the best practice in order to have this scenario done? ...
0
votes
1answer
2k views

EXPORT specific columns to INSERT statements

Is there a way to export specific columns and a fixed number of rows from a table to INSERT statements? I am trying to generate these for unit testing based on a large production table so using ...
0
votes
2answers
770 views

Shrink transaction log while transaction in progress

I have a several-GB import into my SQL Server 2005 (Standard Edition) database. The import is separated into 4 parts, each part in its own transaction. As you can guess, the transaction log files ...
7
votes
3answers
18k views

Bulk Insert Through Network

Can someone help me with these? BULK INSERT DATABESE01.dbo.TABLE01 FROM '\\COMPUTER01\FOLDER01\TextFile.txt' WITH ( FIELDTERMINATOR = ' ', rowterminator = '\n', tablock ) The error ...
1
vote
1answer
49 views

Joining data from legacy database

I'm trying to figure out how to write this query. Table 1 - Booking BOOKING_ID | BOOKING_DATE | etc... 1 | 2013/01/01 | 2 | 2013/01/01 | Table 2 - BOOKING_PROCEDURE_TYPE ...
2
votes
2answers
12k views

IDENTITY_INSERT is set to OFF?

Here is my script of my db , CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Code] [nvarchar](25) NULL, [Name] [nvarchar](25) NULL, [dob] [date] NULL , CONSTRAINT [PK_MyTable] ...
0
votes
2answers
94 views

Home rolled gridview search results shows rows that are supposed to be ignored [closed]

Note: I tried posting this on Stack Overflow but didn't get any good bites. I'm still researching this but wanted to see if any db czars can assist here. I'm in the process of trying to troubleshoot ...
3
votes
1answer
2k views

Linked Server to Mirrored Database Fails

I've setup up a mirrored database on two servers Server1 and Server 2. On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database. I'm using ...
4
votes
3answers
4k views

Best practices to encrypt all the data

I'm working in a project, where the client is afraid their own employees (ie.: junior I.T. guys who the client does not trust) querying the database to get sensitive information. The database is a ...
4
votes
1answer
1k views

Subquery returned more than 1 value

In my database I have two tables: projects and comp_types. When a new project is created a trigger is hit which inserts a concatenated value in a column in the projects table. A fully working SQL ...
3
votes
1answer
2k views

Is there any way to retrieve “Messages” in SQL Server through JDBC?

By "Messages" I mean the Messages tab in the following figure, which includes "Warning", "n rows affected" and in the figure below, the execution time. In JDBC, ResultSet class is used to retrieve the ...
1
vote
0answers
40 views

dbo.M7_* objects (stored procs) in my production database. What product are these from?

I have inherited a database from a predecessor who is long gone... There are about a hundred stored procs in the database that all start with dbo.M7_ They appear to be something to do with source ...
0
votes
3answers
101 views

Access to tables in SQL Server

I would like to know if there is a table where accesses to tables are saved, per user or per system. I'm using both SQL Server 2005 and 2008.
5
votes
2answers
5k views

Frequency distributions segmented by a field

Some background: I have a sample population data file. Each record in the data file has a frequency weight (FIELD NAME: wgt) indicating how many times the records needs to be replicated to get the ...
3
votes
2answers
114 views

User permissions for Microsoft SQL Server

Please advice to give permission for a SQL server user. Conditions are, this user must be able to create,delete,modify,drop the tables/view/procedure on user default schema(not dbo) not in any other ...
3
votes
1answer
679 views

Does the week day number changes according to localization in SQL Server?

Does the weekday number changes according to server/database regional configuration in SQL Server (2005+)?
0
votes
0answers
110 views

How to/advice for hosting a multi-user database on the cloud with specific objectives

I have developed an application that users will be able to download and create an account with through our company. This application will always be connected to the internet. I have been testing the ...
3
votes
0answers
2k views

Severity 016 Backup virtual device fail

My backup on one of my servers is failing once a week. This is the alert I'm getting: DESCRIPTION: BackupVirtualDeviceFile::PrepareToFreeze: failure on backup device ...
0
votes
2answers
398 views

History from SQL Agent Jobs (SQL Server 2005)

how can I able to see all available records from SQL Agent Jobs, because in SSMS is dispayed only reduced number of records are these records stored in system databases, not only in plain log files ...
2
votes
2answers
62 views

Combining multiple queries

I have 2 queries given below. Now I want to have one quey by combining the two queries so as to get the table with columns (Tag Number, Primary Name and Discoverd Name) Query 1: select RT.[TagName] ...
-3
votes
2answers
535 views

What are the advantages of sequences in SQL Server 2012? [closed]

I upgraded my SQL Server 2008 R2 to SQL Server 2012, and I had noticed a new feature was added: Sequence. I don't know the reason of adding sequences when there is an identity property. Can someone ...
6
votes
2answers
927 views

Is there a limit to how many credentials SSMS can remember?

Using SQL Server Management Studio that came with SQL Server 2008 R2, I make connections to the same server using a number of different sets of SQL Server Authentication credentials. On my development ...
1
vote
1answer
210 views

SQL Server giving intermittant connection failures

I have a SQL Server 2000 running on Windows 2003 Server that is giving me intermittent connection failures. The user connections are getting closed on waiting but mature connections. Meaning when ...
3
votes
1answer
9k views

Fastest way to insert 30 thousand rows in SQL Server

I have to insert 30000 ids in a temporary table ##MyTempTable that has only one column (ID int) and I would like to know which way I could do it fastest. I have tried 30000 normal inserts insert ...
2
votes
1answer
46 views

Preferred Methods of copying a new database from Development to Production

I am wondering what are preferred methods to copy a new (Development) database into a new environment. The database will be empty, so essentially I am copying only the schema of this database and not ...
2
votes
1answer
1k views

How to partition a very large table with limited db space?

I have a very big table. I want to partition it, but I cannot. Database size: 1TB, Free space 200GB Table: Size: 165 columns (row lengh 4216 KB, no LOBs), 500 million rows, 600GB of data. ...
2
votes
0answers
483 views

Auditing access to a linked server

I have a linked server with SQL Server configured with the security option "For a login not defined in the above list, connections will: Be made using the login's current security context". What I ...