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
1answer
5k views

SQL Server 2008 - Run the Check Database Integrity Task with the PHYSICAL_ONLY option?

As part of our nightly maintenance, we include the Check Database Integrity Task which is an encapsulation of the DBCC CHECKDB command. It's beginning to take a considerable amount of time due to the ...
1
vote
1answer
242 views

What is a good way to query data stored in multiple identical tables split by date/time

I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples ...
1
vote
1answer
308 views

How to do a simple SELECT/UPDATE/DELETE performance test?

I have a table which might be causing excessive delays during insertion of new records. I'd like to run a test against the database, measuring how long it takes to select, insert, and delete from the ...
1
vote
1answer
2k views

How to truncate all tables with a specific name in all databases

I want to delete all tables named error_log in all of my databases that have such a table. truncate table error_log I am using SQL Server 2000.
4
votes
0answers
567 views

SQL Server Replication :: Dedicated Distributor Hardware Requirements

Does anyone know any rules of thumb (or better) with regards to hardware requirements for a dedicated distributor server? i.e. Based on estimate metrics (e.g. number of subscribers, publishers, ...
7
votes
1answer
3k views

Should I restart the instance after adjusting Memory Allocation and MAXDOP?

As far as I know adjusting Maximum server memory and Max Degree of Parallelism does not require the instance to be restarted. Will new settings always come into effect without restart?
3
votes
3answers
2k views

CROSS JOIN with temp table and CASE statement

I'm trying to CROSS JOIN a table with a view. Here is my code: SELECT * FROM ( SELECT * , CASE TypeId WHEN 1 THEN InGs828S WHEN ...
0
votes
2answers
366 views

Transfer data from database1 to database2 using task queue

We have a SQL Server legacy database, db1, to put data of Project1. With change of certain tables(insert/update) in db1, we need to transfer the changes in another postgres database, db2 (used by ...
5
votes
4answers
643 views

Recommendation for free event monitoring software

I'm seeking recommendations for free / open event monitoring tools. HPOV costs continue to multiply like so many wet gremlins, and we've had enough. There are some interesting suggestions in this ...
7
votes
1answer
7k views

OPTION FORCE ORDER improves performance until rows are deleted

I have a somewhat complex SQL Server 2008 query (about 200 lines of fairly dense SQL) that wasn't performing as I needed it. Over time, performance dropped from about .5 seconds to about 2 seconds. ...
2
votes
1answer
2k views

Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate?

We run a background task on one of our servers which polls sys.dm_exec_requests for long-running queries, gathering diagnostic information when it finds them, including wait type. I'm seeing a number ...
9
votes
1answer
2k views

Best way to re-import large amount of data with minimal downtime

I need to import about 500,000 records containing IP lookup (read-only reference) data about once a week (only three int/bigint cols). I don't really want to worry about merging the data with the ...
4
votes
3answers
166 views

Can anyone describe the step by step process of recovery , backup and restore in sql server

Can anyone describe the step by step process of recovery via backup and restore in SQL Server 2008. If possible please include some examples.
0
votes
1answer
453 views

Escaping a single quote in SQL Server 2005 [closed]

I am using SQL server 2005 and using the northwind database. I need the supplier id of Grandma Kelly's Homestead and I used this query: select supplierid from suppliers where companyname ='Grandma ...
1
vote
1answer
98 views

Where can I find guidelines for building a SQL Server machine specifically for DBCC CHECKDB work?

I'm working on defining specs for a new server to run DBCC CHECKDB scans on all databases in our portfolio (85 instances, 1300 DBs, 15 TB allocated). I have a custom application that copies dump files ...
5
votes
3answers
224 views

Difficulty in moving a SQL Server database

I'm trying to upgrade from SQL Server 2008 R2 Express to Developer. My plan is to remove the Express edition, then install the Developer edition. I have one database I need to bring over to the new ...
4
votes
4answers
38k views

SQL Server CREATE FILE encountered operating system error 5 (Access is denied.)

I'm trying to execute the following script in SQL Server Management Studio but I'm getting the error: Msg 5123, Level 16, State 1, Line 2 CREATE FILE encountered operating system error 5 ...
1
vote
3answers
92 views

Generally speaking, what pecking order should be used when tuning SQL Server?

When tuning SQL Server, what generally is the pecking order to use when trying to decide what to tune first? I'm asking in regards to an existing database that would I would have little ability to ...
3
votes
1answer
75 views

In SQL Server Replication, at what point is it recommended that one separate the distributor?

Is there a general rule of thumb for when it becomes good practice to assign distribution responsibilities to a separate server that is not also a subscriber? e.g. "At n publishers/publications ...
2
votes
2answers
597 views

Make sure two columns are sorted in the same order

I have a table like this: CREATE TABLE T ( ID INT NOT NULL IDENTITY PRIMARY KEY, CreateDateTime datetime2 NOT NULL, --other columns ) How can I make sure that ID and CreateDateTime are both ...
17
votes
2answers
4k views

SQL Server does not optimize parallel merge join on two equivalently partitioned tables

Apologies in advance for the very detailed question. I have included queries to generate a full data set for reproducing the problem, and I am running SQL Server 2012 on a 32-core machine. However, ...
2
votes
1answer
182 views

Can I move data and full text search files to another drive and change the drive letter?

My server's G: drive is rapidly running out of space. It mainly contains data files (*.mdf) for both system and user databases. The FullText Catalogs also exist on that drive. IT added a much lager ...
4
votes
1answer
129 views

unique constraint on a set

With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005? To illustrate the problem, I make an example: A tag_bundle consists ...
4
votes
1answer
1k views

Where are queries against a linked server actually processed?

How is a query against a linked server processed? Does the linked server use its optimizer when it receives a query from a remote source? There are a few scenarios about which I'm wondering: Scenario ...
0
votes
6answers
961 views

Equivalent of CHARINDEX OR IN for ints

I want the equivalent of this for ints. --Valid SQL DECLARE @users AS nvarchar(50) SET @users = 'Joe ,Bob,Fred,Tim' --1,2,8,23 select * FROM Person AS p WHERE CHARINDEX(p.sFullName,@users) > 0 ...
7
votes
1answer
196 views

Dynamic Dimension Security

I have a cube in SSAS 2012 with about 30 dimensions, one of which is Company. The Company dimension contains a list of companies, of which there are several hundred in the cube. Our security model ...
1
vote
4answers
776 views

Backup and restore of RDBMS(Oracle, DB2, SQL Server) from Perl

What could be the best approach ? Till now could figure out the following (Oracle) Calling exp.exe and imp.exe utilities in Oracle client using Java and inturn call it from Perl. Calling exp.exe ...
3
votes
5answers
906 views

Single query to return counts over different IDs in a single record

select count(title_id)as algodata from titles where pub_id =1389 select count(title_id)as binnet from titles where pub_id =0877 select count(title_id)as newmoon from titles where pub_id =0736 ...
0
votes
2answers
3k views

Doing a comparison on two csv files with no Primary Key

I'm wondering if in SSIS there is a way to compare two csv files(with the same structure) with no reference key to make a record unique. To give you a better picture from what I mean; In initial load ...
3
votes
2answers
8k views

Copying View in Management Studio

I want to copy a VIEW from one server to another. I could copy tables but I can't copy views. Is there a way to do so? Thanks
8
votes
1answer
12k views

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (MsDtsSrvr)

I have a clustered environment which has many instances in it. I have modified the xml file to allow several instances to use ssis. Everything works great when the instances are on the same node as ...
7
votes
2answers
1k views

Filtering data ordered by rowversion

I have a SQL table of data with the following structure : CREATE TABLE Data( Id uniqueidentifier NOT NULL, Date datetime NOT NULL, Value decimal(20, 10) NULL, RV timestamp NOT NULL, ...
2
votes
1answer
783 views

Export SQL Server varbinary field to string using Visual Basic script in SSIS?

I'm using SSIS to export data from a set of tables to flat files. The export is running great except for one table with a varbinary field. The export is running a Visual Basic script task to load the ...
2
votes
1answer
1k views

Implementing “ANY” aggregate operator in T-SQL

I have a database that contains department and employee tables, and I need to return the number of employees for each department. I write the following query: SELECT d.ID, d.Name, COUNT(*) ...
3
votes
3answers
9k views

Mapped drive for Microsoft SQL Server 2008

I've noticed strange issue after this weekend. Maybe someone could explain this situation. I've got SQL Server 2008 placed on Windows Server 2008 with read/write permission to catalog ( let say ...
4
votes
1answer
158 views

Shrinking Reluctant Log Files

I have several SQL Server 2008 databases with transaction logs which have gotten out of hand and filled the drive. One of these logs was 68gb yikes. I know better than to kill off my backup chain ...
5
votes
4answers
1k views

Composing the “ultimate database maintenance script”

I'm trying to compose T-SQL script that will: A) Shrink database size to the minimum possible size; B) Make integrity checks, and completely "reorganize" and optimize the database so that it is in a ...
3
votes
1answer
243 views

I have Fragmented Index on a empty table!

I ran the following query on my database to identify the fragmented indexes. However I surprised when saw the Indexes belong to some empty tables among the highly fragmented indexes! How can it be ...
6
votes
1answer
2k views

How to create a SQL Server 2008 backup without logs with mirroring

How do I create a backup of a SQL Server 2008 in a mirrored setup without any logs? We need to do this so that when we restore our local development databases, we would have the most up to date ...
6
votes
3answers
2k views

Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance

We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like PK BIGINT ID VARCHAR(50) Col1 Col2 etc So we need to insert a row in the ...
2
votes
4answers
1k views

SQl Server Password Change after installation

I have installed SQL Server 2005, but at the time of installation I forgot to change my password. Now I can access my database only in Windows authentication mode, and I want to access the database in ...
1
vote
2answers
218 views

Sql Server Linked Server

I have two sql server S1(2008) and S2(2005). S2 is added to S1 as linked server. S2 have a table Product. When I try to run the code select ProductID from Product I get the error: Location: ...
4
votes
2answers
4k views

Suppress SQLCMD messages

Would it be possible to suppress all sqlcmd messages output to the cmd window? I am running a large script and it seems to be slowed down a lot with all the Processed XXXX total records messages. ...
0
votes
1answer
2k views

Fast Recovery in SQL Enterprise Edition

Starting with SQL Server 2005 and all later versions, Enterprise Edition includes a feature called "Fast Recovery". a) Does fast recovery use multiple OS threads to perform "REDO" operation? b) Does ...
4
votes
1answer
2k views

Running queries with SQLCMD vs. Running queries with SSMS

I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran ...
4
votes
1answer
10k views

Import/export wizard code page error [closed]

Using the Import/Export Wizard, I ended up with an annoying problem: Error 0xc02020f4: Data Flow Task 1: The column "pinNumber" cannot be processed because more than one code page (874 and 1252) ...
3
votes
2answers
13k views

Cannot access any database in SQL Server 2008 R2 Express after moving workstation from domain to workgroup

My workstation that has SQL Server 2008 R2 Express for development purposes installed has been moved out of a domain into a Windows workgroup. While being part of the domain I used the account ...
2
votes
1answer
604 views

How can I keep two tables in two different databases with different schemas the same in real-time?

I have two databases A, and B, which will reside on the same server. A and B have different schemas. Database B will contain different numbers of tables, and sometimes tables with the same name will ...
13
votes
3answers
2k views

Why are queries parsed in such a way that disallows the use of column aliases in most clauses?

While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query. The MSDN docs say that the general logical ...
9
votes
3answers
2k views

How to control the order of servers listed in the SSMS connection dialog?

My current project involves a lot of database shuffling as we upgrade from SQL 2008 -> 2012, re-task hardware, repeat. One minor annoyance is the connection dialog in SSMS re-shuffling the server ...