SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

learn more… | top users | synonyms (2)

2
votes
1answer
7k views

How to trace the exact query fired in sql server

I am facing a problem with performance tuning. We are using an application called ARAS (one of the leading PLM software) which is using Sql server as the back end. Every time we find some data using ...
5
votes
3answers
5k views

Is there any time where we should avoid cascading delete?

In SQL Server 2008, there is a Primary table which is linked to three other child tables by 1 to many relationship. So, we are thinking of using Cascading delete in the primary table, so that all the ...
3
votes
2answers
727 views

Configure Email to be sent on restoring a database

Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.
3
votes
1answer
3k views

Best way to migrate a database involved in bidirectional replication

I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2. The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In ...
1
vote
1answer
608 views

Optimising a query of an SSRS dataset

Consider the query given below: SELECT * FROM ((SELECT c.cust_id , c.username , REPLACE(qs.seg_type_ref_key_02, 'N/A', 'Non Vip') casino_group, ...
7
votes
4answers
710 views

Are there any problems with deploying an SQL Server database to a production server by taking a backup?

This is a slightly loaded question in that I have already assumed that the described scenario is wrong. A DBA is deploying an application I have written that includes an MS SQL Server 2008 database. ...
1
vote
1answer
644 views

Slow query and Insert with trigger

I have vehicle tracking system application based on PHP, SQL Server 2008 R2 Enterprise Edition with XEON HP Z800 Server. My database is growing day by day in millions and it makes it very slow to ...
1
vote
1answer
80 views

User mapping problem during deployment

I have a development machine with a new database I have designed. The server has a login L1 and the database has a user U1. L1 is mapped to M1 and tables have permissions set up for U1. Authentication ...
2
votes
2answers
591 views

SQL Server 2008 R2 : A Possible indexing strategy for the given schema?

We have these four tables: Store ( row bigint, id uniqueidentifier, name varchar ) Products ( row bigint, id uniqueidentifier, storeID uniqueidentifier, productname ...
1
vote
0answers
547 views

How to shut down clustered SQL Server 2008 R2 with NOWAIT?

Is it possible to shutdown with nowait on a clustered instance? I want to delay recovery to when the instance starts, and I want to shut down as quickly as possible without issuing checkpoints to my ...
3
votes
1answer
1k views

Dynamic file name for file import

I'm trying to figure out a way that I can pass a filename into a stored procedure that can import a file. However, after setting up the SQL dynamically, the object can't be found. DECLARE @fileName ...
2
votes
1answer
2k views

Need Help Executing a Remote Task from SQL Server Agent

There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues. We can solve this if ...
2
votes
2answers
185 views

Optimize Query of Count Less Than

I would like to optimize querying in SQL Server of a table to know whether it has a count of records less than a certain amount. I don't need to know the complete count, just whether it is less than, ...
1
vote
1answer
2k views

Unable to drop non-PK index because it is referenced in a foreign key constraint

I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable. I noticed there is an ...
1
vote
1answer
611 views

Replication is missing a specific set of rows on subscriber

So I've got two servers, we'll call them Server A and Server B. Server A has a table, Table 1, which gets replicated to Server B. Server B has a table, Table 2, which gets replicated to Server A. ...
4
votes
2answers
1k views

Trace flag and which need to be turned off and Why

In SQL server there are so many trace flags. Why some of them need to be turned off. Some where i saw that trace flag 8017 need to be turned off. So i want to know which trace flag need to be tuned ...
1
vote
0answers
487 views

Allow developers to run database maintenance

We use the database solution by Ola Hallengren. We're trying to give our developers a way to run the maintenance stored procedures after they do an ETL. Our developers have locked down permissions. We'...
0
votes
0answers
46 views

Data in tables after DROP DATABASE and recreate [duplicate]

Just came into the 'feature' I had no idea will ever happen. We have two servers for development - S1 and S2. We most often use S2 and S1 went out of date. Let's assume that S1's database DB1 stays at ...
7
votes
2answers
243 views

'Cleanse' a SQL Server database file created with Instant File Initialization enabled?

I have Instant File Initialization enabled on our SQL Server, so the 'empty' part of the database file is not zeroed before being allocated. My understanding is that this means that the file could ...
23
votes
2answers
3k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
7
votes
4answers
13k views

EF Code First uses nvarchar(max) for all strings. Will this hurt query performance?

I have some databases created using Entity Framework Code First; the apps are working and in general I'm pretty happy with what Code First lets me do. I am a programmer first, and a DBA second, by ...
1
vote
4answers
4k views

restore could not start database

Restore failed for Server 'servername'. (Microsoft.SqlServer.SmoExtended) System.Data.SqlClient.SqlError: RESTORE could not start database 'ECP'. (Microsoft.SqlServer.Smo) The database is created,...
3
votes
1answer
9k views

Passing column names dynamically to UNPIVOT

I have a table with following data First Second Third Fourth Fifth Sixth 2013-08-20 2013-08-21 2013-08-22 2013-08-23 2013-08-24 2013-08-25 And ...
3
votes
1answer
645 views

Improve performance of transactional replication

I 'm testing implementing transactional replication . The replication works fine if I insert/update handful of rows. The problem occurs when I have a sql job running which deletes/updates & ...
1
vote
1answer
539 views

Non-clustered index with all columns for “select * from”?

I have a simple SELECT * FROM MyTable WHERE DataDate = '18-AUG-2013' query on a table that contains 340 columns and 3.4M rows. Running estimated Execution plan in SSMS (Ctrl-L) suggests I create a ...
3
votes
2answers
858 views

Truncated copy of a DB for a developer

I have an access to a DB (SQL Server 2008 R2). The size of the DB is about 40GB. I am a developer and I'd like to have a local copy of that DB to be able to work offline. Is there an easy way to get ...
1
vote
2answers
1k views

Error in running SQL Server script

I copied oldDatabase as newDatabase. Modified newDatabase's schema. Meanwhile, oldDatabase's data are processing and updating. I want to copy oldDatabase's data to newDatabase. What I have done ...
1
vote
2answers
896 views

Run Multiple Scripts In One Transaction Across Multiple Servers [duplicate]

We have deployment scripts that we need to run on databases that are spread across multiple servers. One script only runs on one database, but the scripts depend on each other. We are looking for a ...
0
votes
1answer
651 views

Renaming Sql Server database unusual result

This is some behaviour I observed from renaming databases. What is the cause of this? The example is simplified for the purposes of this question. CREATE DATABASE [Database_A] GO CREATE DATABASE [...
0
votes
3answers
1k views

Setting up SQL Server service broker from one database to multiple databases

I would like to set a SQL broker service whereby DatabaseA sends the same data to DatabaseB and DatabaseC. In this case, all databases reside on the same instance. I have looked at the following ...
0
votes
1answer
1k views

Extract time portion of Datetime2(7) in hhmmssfff format

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this: DECLARE @mdate AS DATETIME2(7) SET @mdate = '2012-03-15 10:13:27.5437431' SELECT LEFT(...
3
votes
1answer
247 views

Execution Plan Regeneration

we have a database server with Sql Server 2008R2 that runs some queries every day; now these queries are fast (I suppose Sql Server has in memory some good execution plans). If we restart the ...
0
votes
3answers
941 views

How can I insert '&' symbol in data

I have a source table where one of column has data like: Column SD SD .. .. JK Jk .. .. I want to load this data into staging in format of: Column S&D S&D .. .. J&K J&k .. .. ...
5
votes
1answer
374 views

Distributed transaction and Read Committed Snapshot

Distributed transactions are not supported for snapshot isolation level in SQL Server 2008 R2. But what about read committed snapshot?
1
vote
1answer
389 views

Avoiding table level locking [closed]

I am maintaining a queue in a database table. An SSIS package checks the queue every two minutes. If it finds anything then it runs a job. I am experiencing some sort of table level locking. The ...
5
votes
1answer
12k views

How to keep the structure of the Tablix when there is no data to show

We have a SSRS report which shows the result of a dataset in a tablix. If the query in the dataset returns no data, the header of the Tablix gets displayed but not the tablix cells below the header. I ...
2
votes
3answers
475 views

What Are the Log File Names?

I moved my database files. When I go attach the MDF file by itself, SQL Server will tell me that it can't find the log files. How do I go about asking the MDF what files it is expecting before ...
1
vote
0answers
2k views

Cannot connect remotely to a named instance

So, I have two servers (ServerA and ServerB) that I've set up with Windows Server 2008 R2 and SQL Server 2008 R2. My goal is to replicate between these two in a failover fashion (with ServerC as a ...
4
votes
1answer
6k views

Large table and poor performance - what to do next?

We have a Windows 2008 R2 / SQL Server 2008 R2 (Standard) server that is used to host a single database. The database itself mainly consists of a single large table containing both live and historical ...
3
votes
1answer
2k views

Restoring database to UNC path on local drive

When I try to restore a database using a restore command with a local UNC path: RESTORE DATABASE [dbname] FROM DISK = N'\\PC91\D\backup.BAK' WITH FILE = 1, MOVE N'test' TO N'\\PC91\D\dbname.MDF', ...
12
votes
2answers
31k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
4
votes
2answers
722 views

Create a new database in SQL Server 2008 R2, but it is not Blank

When I try to create new database in SQL Server 2008 R2, it was created completely, but it is not a blank and empty database, there are many tables that come from another database. I also tried ...
3
votes
1answer
252 views

Mathematical method for spotting irregularity in numbers flow

I am trying to build an engine which is going to predict orders for items inside shop. Three main variables are: Time-dates (x-axe etc 1.1.2013,1.2.2013 etc I do calculation one per day) Available ...
7
votes
1answer
318 views

Auto Update Statistics Duration and Interruptions

Our production SQL Server 2008 R2 sometimes begins to perform slowly: all queries begin to work too slow (and sometimes all queries just hang and wait something then continue). This usually lasts ...
5
votes
2answers
3k views

Disk Space Considerations for Rebuilding a large Clustered Primary Key Index

We have a table in our database with over 200 million rows and a Clustered Primary Key Index which is over 100GB. The Primary Key is only so large because many more millions of rows have been archived ...
3
votes
1answer
1k views

How do I reclaim reserved space in the sysjobstepslogs table

I have a 36GB msdb database in my SQL Server 2008 R2 database server. From what I have read this is past the point of being gigantic. When I look at the tables in the msdb database the ...
1
vote
3answers
5k views

Convert all ntext columns to nvarchar(max)

I'm trying to create a stored procedure to convert all ntext columns in my database to nvarchar(max). This is the code ALTER PROCEDURE [dbo].[usp_SL_ConvertNtextToNvarchar] AS BEGIN SET NOCOUNT ON;...
2
votes
1answer
532 views

Moving away from CmdExec steps in job without losing logging detail (SQL Server 2008)

My department currently relies heavily on CmdExec steps in SQL Server Jobs, called using a SQL Server Login. For example osql /e /n /S SERVER01 /U USER /P PA$$$ /d DATABASE_01 /i "\\LOCATION\OF\SQL\...
6
votes
3answers
2k views

Domain group as single login, but still many different database users?

We want to use Windows Authentification on SQL Server 2008 within Active Directory. Is it possible to use an AD Group as a LOGIN to the server, but then to map the actual AD USER to a database user? ...
3
votes
1answer
1k views

Distributed - across multiple databases or servers - Transactions

I have some doubts related to transactions in SQL. I perform below steps. Connection 1 open for DB1 database Begin transaction for Connection 1 Run the commands on Connection 1 Connection 2 open for ...