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)

8
votes
2answers
18k views

High CXPACKET and LATCH_EX waits

I am having some performance issues with a data processing system which I am working on. I have gathered wait stats from a one hour peroid which show a large amount of CXPACKET and LATCH_EX wait ...
1
vote
1answer
156 views

SQL-Server Custom Database Role

I have a created custom Database Role called Managers. Added db_owner to this role. I would like to Deny DROP Database to this custom Role. But the DENY Drop DATABASE TO Managers is not working Any ...
2
votes
1answer
49 views

SQL Server 2012 + SQL Server 2008R2 on the same VM

I can't immediately find reference to it, but I remember reading somewhere that it's best practice not to mix versions of SQL Server on the same host. I believe the rationale was that often times, ...
0
votes
1answer
791 views

SQL Server: multiple failover cluster instances with disjoint storage

This is for SQL Server 2012. I currently have two datacenters that have separate SANs. I'm using failover cluster instances (FCIs) for server redundancy, and then AlwaysOn Availability Groups (AGs) ...
6
votes
1answer
794 views

OLE DB Sybase Connection Failing

I have a connection to a Sybase database which we stage in a SQL Server 2008R2 database. I can connect the the database and the connection tests out fine. I am using an OLE DB source with a Sybase ...
1
vote
2answers
206 views

Backing up my SQL Server System Databases

I'm trying to create a recurring scheduled job to fully backup the master, msdb and model databases, I'm using the Maintenance Plans for SQL Server 2008 R2, in the "Specific Databases to choose from ...
0
votes
2answers
1k views

How can I speed up this query on table with 65 million records

select hash_id, hash_value, substring(T.cri, 1, 7), substring(T.cri, 9, D2.Pos-9), substring(T.cri, D2.Pos + 1, D3.Pos - D2.Pos - 1), substring(...
12
votes
4answers
29k views

How to view an encrypted view or stored procedure

I am working on a third party database. When I try to view the definition of a view by right clicking, CREATE TO and then to NEW QUERY EDIT WINDOW, I am getting an error: This property may not ...
0
votes
2answers
329 views

SQL Server restore replicated database

I have setup a transactional replication on a sql 2008 database. Then I backup the database.I got following error when I try to restore the database. 'Exclusive access could not be obtained because ...
18
votes
4answers
38k views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
3
votes
2answers
520 views

Ola Hallengren with database mirroring

I am about to setup Ola Hallengrens database maintenance plan. We have our database mirrored and I was just wanting to know if I need to run the script on both my Sql Server instances or only the ...
0
votes
2answers
458 views

why stored procedure performance is very high compared to plain select query

I have a table with more than 2 million rows, with needed indexes. When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, ...
0
votes
1answer
66 views

SQL query optmization [closed]

This is one thing I have been trying to read from few days. I am not getting any satisfactory explanation anywhere. Tried surfing some blogs and videos in youtube. Appreciate if anyone one can throw ...
0
votes
3answers
127 views

How to script data in deterministic order

When I script data in my database in SSMS Tasks->Generate Scripts and then select data-only it turns out that the insert statements are generated in different order each time when even a smallest ...
1
vote
2answers
836 views

SQL Server 2014 CTP 2014 expiration date

I installed SQL Server 2014 CTP2 on a server and I have been testing the new features in SQL Server 2014. On the web site where I down loaded the software I notice the statement "expires on March 31, ...
0
votes
1answer
169 views

Rebuild Index in system database

I use SQL Server and have multiple indexes in system database whit index fragmentation 30% or more. I don't know that SQL server manage system database index automatically or I want to manage them. ...
1
vote
2answers
85 views

Known vulnerabilities for when a guest executes a stored procedure on SQL Server

I have a production database with crucial business information. Meanwhile, my company is involved in a project with some other company and we have to exchange some information on a regular basis. How ...
4
votes
1answer
179 views

If user owns a database fixed role are they the admin of the role?

I have SQL Server 2012. When I create a user in "Owned schemas" screen one of the option is fixed database role. If I own a fixed database role: What permissions am I granted? I am the admin of ...
2
votes
2answers
1k views

Where is the list of attached databases stored?

I know database instance names are stored in the registry in subkeys of HKLM:SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL and the path to master.mdf is stored in HKLM:\SOFTWARE\Microsoft\...
2
votes
2answers
3k views

Reduce DB size - SQL Server, SSMS

One of my databases was growing in size a few gigs every day. That was very unusual since it is still in development phase. After small research i discovered that the Even Log table was almost the ...
0
votes
1answer
54 views

Who and when were changed Database default location settings?

I need to know when and who changed SQL Server Database default location settings. How can I do that?
2
votes
1answer
56 views

Transaction Log Backups on SQL Server

Running SQL2008 R2 SP2, database is in FULL Mode Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I ...
24
votes
1answer
13k views

GO After every T-SQL statement

What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have ...
3
votes
0answers
186 views

Significant PLE Drop [duplicate]

This morning at 4am we experienced a huge drop in Page Life Expectancy on a new sql server 2012 box. No jobs (backup etc) ran at this time and traffic was very light compared to say 20:00. Current ...
0
votes
2answers
61 views

Which one is better way to insert data? [closed]

I have two cases case create proc createEmployee @firstName varchar(255), @lastName varchar(255) = null as insert into Employee_Tbl values (@firstName, @lastName) case create proc ...
2
votes
0answers
144 views

logon trigger to send an email

I want to create a simple login trigger. basically if user = test , then send an email to me. CREATE TRIGGER ServerLogon ON ALL SERVER WITH EXECUTE AS 'test' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()=...
3
votes
1answer
1k views

How to pinpoint root cause of excessive log file growth

The log file stays pretty small (~500MB) until about 11PM, and at that point it begins growing until no disk space is available. The log file reached it's maximum size at 12:39AM, so the growth took ...
4
votes
1answer
360 views

How can a connection for one DB block a connection to another DB in SQL Server?

We have an interesting scenario. For weeks, we have been experiencing 'outages' on SQL Server 2008 -- where our websites have slowed to a crawl and mass timeouts for all customers who have DB's ...
0
votes
4answers
2k views

What can cause Reads to slow down on SQL Server 2008R2?

Our primary DB is 15GB in size. The server has 16GB of RAM. We are using the simple recovery model. Lately we've noticed that our custom apps lookups - just basic reads with adhoc SQL statements - ...
5
votes
3answers
775 views

SQL Server- Table partitions on SSD

I have a table in my database that has about 250m rows. I'm new with databases but I have read up about partitioning the table to increase performance. One of the important points that I seem to ...
2
votes
2answers
178 views

SQL Server - Recursive “find all FK connections for ID through the entire DB” query

I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. ...
1
vote
1answer
314 views

What is the purpose of a user's default database?

As I understand it the default database is specified so that in case you don't specify one in the connection string, it will know where to start. Is this correct? I've never seen a connection string ...
2
votes
1answer
13k views

How to convert sql server table data into MS Access(.mdb) file

I have SQL Database table with 6664055 rows, I want to convert this SQL Server table data to MS Access format(.MDB), How Can I do this?
-2
votes
1answer
65 views

What objects can be affected in SQL Server 2012 by a DDL statement [closed]

Are they any objects that could be CREATEd, ALTERed, or DROPped in SQL Server 2012 other than the following list: Tables Stored procedures Views Functions Triggers Indexes P.S. I'm only interested ...
0
votes
0answers
69 views

Table Field to Reference Field in Other Tables

Suppose I want to map a set of input values to database table fields E.g. employeeFirstName --> emp.fName employeeLastName --> emp.lName ... --> ... In theory I can create a ...
5
votes
2answers
1k views

SQL Server 2000 - 'Performance: Deadlock'

We had to restart our SQL Server today, we had made no changes to it. When it came back up we immediately started getting this error from the server DATE/TIME: 2/27/2014 3:09:31 PM ...
4
votes
2answers
304 views

Spatial index not used

I have a table with a geometry column. For one record, there is only one Point stored. A spatial index has been created but queries searching for the nearest location do not use this index resulting ...
3
votes
4answers
2k views

indexing on multiple nvarchar(max) columns

I have multiple nvarchar(max) columns in my DB named ShipperName,ConsigneeName, ProdDesc,BillOFLading. What I need to do is to have UNIQUE INDEX for the combination of these columns. As I am failed ...
0
votes
2answers
171 views

Having DB Mirroring and Replication together

Can anybody help me understand why do one go combination of Database Mirroring + Replication. Why can't I just have Database mirroring for high availability of my database. What is the need of ...
-5
votes
2answers
443 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly 2,5,...
1
vote
1answer
72 views

Backups in Availability Groups

In case of async commit setup and the backup preference set to secondary replica. I have observed, in case of high unsent log size, log backup on secondary replica starts failing for that database ...
1
vote
0answers
372 views

What's is the best way to get incremental changes from production to incrementally changed development database?

I came to a new dev team where all development is on the same (instance) of an SQL Server (2005) database once in 1-2 days restored from a current full backup of production database replacing a dev ...
2
votes
2answers
409 views

Aliased Column name in SQL Server

Sometimes, when developing in a hurry, devs make poor choices on column names. Changing them can be a challenge, having to stage code, and time migration perfectly. I'm told that Oracle has a way ...
9
votes
1answer
296 views

Estimating IO Requirements for Bursty Usage

We have an application that queries a SQL database periodically throughout the day. There are periods of zero or only light activity, interspersed with individual requests for relatively large ...
2
votes
2answers
2k views

SQL server 2012 restore wizard freezes

I have a development laptop with SSMS Express 2012 with a 2012 db instance and a 2008 db instance. Have been using this configuration for over a year. Suddenly I am unable to use the restore winzard....
12
votes
1answer
729 views

SQL Server 2008 R2 index rebuild fails with severity 17

Occasionally during our index maintenance, the job will fail with a SEV 17 error where enough space can not be allocated for the object it is rebuilding. The database is laid out as such: Data_file1 ...
3
votes
1answer
164 views

Avoiding Deadlocks

We currently have a trigger-based system in place that does the following: When records in certain tables are modified, a trigger fires that makes a field NULL in a varying number of records in ...
2
votes
2answers
102 views

Replication between SQL Server 2005 - 2005

I'm hoping to get some help with a SQL Server 2005 replication issue. In short, in replication monitor, (On the Publisher), on the 'Distributor to Subscriber History' tab, I see two lines under '...
0
votes
1answer
387 views

SQL Server 2008R2 - Stored Procedure with 2 updates causing deadlocks

I have a web api web service that calls an authentication stored procedure. This stored procedure calls a different stored procedure that does 1 update: UPDATE ...
1
vote
1answer
74 views

how determine the best path that satisfies a condition

declare @flat table ( id int not null, percentage int not null, value float not null ) insert into @flat (id, percentage, value) select 1,10,0.333 union all select 1,20,0.4 union all ...