Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. It originated from the Sybase SQL Server codebase, which is why both products use the extension of SQL called Transact-SQL (T-SQL).

learn more… | top users | synonyms (2)

5
votes
2answers
1k views

What are the differences between leaf and non-leaf pages?

I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
1
vote
0answers
356 views

Collect CPU, reads and writes when replaying SQL Profiler Trace

I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to ...
3
votes
0answers
135 views

Conflict handling, SQL Server 2008 R2 Peer to Peer

I have a Peer to Peer replication topology set up between two SQL Server 2008 R2 servers. I was doing some testing on fault tolerance and conflict handling. I have conflict detection enabled, and ...
3
votes
2answers
120 views

SQL server performance and tuning

I am working on a system, there is a cisco load balancer that can handle 40K requests. There are two app servers. There are 12 or so web services. and there is a SQL server with 2TB of data. ...
3
votes
4answers
204 views

PRIMARY KEY CONSTRAINT fundamentals

Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ? The RDBMS that is used is SQL Server. In fact, ...
1
vote
2answers
194 views

Database Design question, calculate or save financial positions

As the question states i'm currently internally strugling witha database design question. I work as a software developer (and i tend to do everything database related aswell since we're rather small) ...
2
votes
1answer
4k views

IF NOT NULL then UPDATE else keep the value of the field

I think I am somehow close to get it work, but for some reason I still get errors. I have the following UPDATE query: UPDATE DeviceAttribute SET Details = CASE Name WHEN 'Accessories' THEN ...
1
vote
3answers
1k views

How do I determine if a database is the principal in a mirroring setup?

I have two database servers Server1 and Server2, configured with mirroring. A single database, MirrorDB, is mirrored. There is another database on Server1 named OtherDB which is only present on ...
3
votes
2answers
756 views

Migrate SQL Server database to multiple files (shrinkfile emptyfile vs log shipping)

I have a SQL Server 2008 database in production that we are moving onto a new server. The current database has a single ~400GB .MDF file. The new server will be running SQL Server 2012, and we are ...
0
votes
1answer
555 views

Is Reorganizing Indexes required after ShrinkDatabase or can I just reogranize index with fragmentation more than 10%?

I am a new DBA to a SQL 2005 production Report server. My predecessor created a Maintenance Plan with 4 tasks: CHECKDB; SHRINKDATABASE (N'DB1',10,TRUNCATEONLY); it seems to be running a REORGANIZE on ...
2
votes
2answers
78 views

View Disc Usage

Is this statement correct? Behind the scenes SQL Server actually stores the data in the view as a physical table which must be updated if any of the data behind the view changes I did some ...
2
votes
2answers
1k views

SQL Server service shutdown due to 'bad?' SSL cert

This morning it transpired that SQL Server 2012 service running on Win2012 had shut itself down. When I logged in as the server admin and tried to access SSMS, after entering my credentials I got the ...
2
votes
1answer
273 views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
-1
votes
1answer
459 views

Conditionally INSERTING columns into Table

using SQL Azure and like to insert a data to a table using dynamic Query. No of columns in select statment is dynamic. It will be based on some conditions. Ex: Salary, Code. How can I create a ...
3
votes
1answer
79 views

Adding column from SSMS wizard in SQL Server

Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process: Create a New ...
2
votes
0answers
140 views

How to speed up SQL Server RIGHT JOIN on 100MM+ Records - use TVF?

I use a table insert to UNION a CTE of converting transactions to visits (converting or not) and then summarize the visit data (no details, just pages visited & txns completed) to get funnel ...
1
vote
1answer
109 views

Join Calender Table

I have a Datatable and a Calendartable. I do a join on this tables to get sequential dates if there is no data for this day. My problem now is that there a several id's and i need a count for each ...
2
votes
3answers
295 views

Why is SQL running the same query longer from another connection? [closed]

Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely? Dont jump to any conclusion just yet, let me explain what I mean... Here is the setup: A Windows ...
4
votes
1answer
67 views

Under what circumstances can a division expression reduce the number of rows in a query's result?

I'm experiencing a problem with a query that I've written. Given a table with the schema: create table somevals(division varchar(100), subdivision varchar(100), ...
1
vote
0answers
179 views

Troubleshooting Database Mail issues and Service Account permissions in SQL Server

I am having difficulties troubleshooting a Database Mail issue. When I try to stop and start the service using sysmail_start_sp and sysmail_stop_sp, I see a critical error in the Administrative Logs ...
13
votes
5answers
2k views

Handling concurrent access to a key table without deadlocks in SQL Server

I have a table that is used by a legacy application as a substitute for IDENTITY fields in various other tables. Each row in the table stores the last used ID LastID for the field named in IDName. ...
0
votes
1answer
2k views

Can't connect to SQL Server Windows 7 [closed]

TITLE: Connect to Server Cannot connect to localhost. ADDITIONAL INFORMATION: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was ...
0
votes
2answers
4k views

Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server

This is going to be a long story I'm afraid ;-( I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some ...
2
votes
1answer
474 views

SQL Server cluster questions please

We have SQL Server 2008 R2 cluster active/active/active (a, b, c) and three instances run on this environment. We did a test on one of the instances, and failover test. Manual failover from node c ...
1
vote
0answers
208 views

INSERT BULK in SQL Profiler without values

I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated: SQL BatchStarting: insert bulk table_name <column_list> but there is no ...
1
vote
1answer
710 views

Alternative tools to export Oracle database to SQL Server? [closed]

I've got an Oracle database that I need to export (schema and data) to SQL Server. I am trying the Microsoft SQL Server Migration Assistant for Oracle, but it is horribly slow, grossly inefficient ...
6
votes
1answer
226 views

how do I insert a default row?

If I create a table with an identity column as primary key, and all the other columns have default values, for example create table rr (id int identity(1,1) primary key, dt datetime default ...
4
votes
1answer
849 views

SQL Server 2008 query planner failing after index drop & recreate

Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
8
votes
1answer
258 views

Strategies for organising SQL Server with large amount of RAM

We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if ...
10
votes
4answers
3k views

What is a good, repeatable way to calculate MAXDOP on SQL Server?

When setting up a new SQL Server 2012, I use the following code to determine a good starting point for the MAXDOP setting: /* This will recommend a MAXDOP setting appropriate for your machine's ...
2
votes
0answers
85 views

Can the same database be log-shipping secondary and primary at the same time?

Here is my scenario: Database DB1 on Server1 is log shipping primary in data center. Database DB1 on Server2 is log shipping secondary; Server2 is in remote location. Logs are shipped from data ...
4
votes
2answers
1k views

SQL Server 2008 can't repair consistency

I have a problem with a SQL Server 2008 database. Launching DBCC CHECKDB I get this error: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: ...
0
votes
1answer
637 views

SQL update set column = @ parameter

doeas anyone knows how to update a table where column = @parameter WHERE SELECT Basically I have a table with the following values. Table name is UserAttribute ID|Name |Properties| ...
4
votes
2answers
248 views

Two corrupted indexes found by DBCC CheckTable

I'm wondering if this problem is solvable by dropping/recreating the two indexes. Both are rarely used, nonclustered, non PK, non FK indexes. Here is the dbcc output: Table error: table ...
4
votes
2answers
511 views

Track grant permission on tables

I'd like to see if there are any changes on the permissions granted for a specific table. For example, I don't want to have the login 'John' have the SELECT permission on the table Invoices. If ...
3
votes
1answer
439 views

Primary key with “NOT FOR REPLICATION” option

I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION". I'm no DBA, but surely in most databases, a record without a ...
1
vote
1answer
101 views

Public Database for Mobile Application

What are the proper steps to hosting a public database. Currently I a building a mobile application with Adobe Flash Builder. I have used Microsoft SQL Server, My SQL as well as WAMP Server. All of ...
1
vote
2answers
283 views

SQL Server Profiler 2005 on remote SQL Server 2008 Express?

Is it able to trace remote SQL Server 2008 using SQL Profiler 2005? If yes, anyone knows what else should I do, if during loging in appeares this message "Unable to locate trace definition file for ...
1
vote
1answer
572 views

how to add attachment(text file) to database mail?

I have scenario Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates I can send a mail to client that job is ...
4
votes
1answer
72 views

Why is this user “Admin” created in all databases?

When I create database in SQL server Management Studio, user with name "admin" is also created for all databases: Why this user is created? And how I can change this behavior?
5
votes
2answers
406 views

In SSIS 2012, what is the purpose of flagging a package as an “Entry Point” package

In the Visual Studio designer you can right click on a SSIS package and designate it as an "Entry Point" package" Doing a search I found this page on MSDN which states: The value of 1 signifies ...
6
votes
3answers
2k views

Stop SQL Server service(s) before defragmenting drive?

Our production SQL Server 2005 database's data files live on a separate physical drive, which Microsoft Windows 2003's Disk Defragmenter tool reports as 99% fragmented. We scheduled a task to ...
1
vote
2answers
520 views

SQL Server deadlock too long [closed]

I have a really weird problem with a deadlock between a business process and a monitoring job. The both were involved on a deadlock that lasted around 5.5 hours. I don't know why SQL Server took so ...
1
vote
1answer
166 views

Effects of enabling service broker

I would like to use SqlCacheDependency to update a dataset in real-time, but we have not enabled the service broker on our production database. Could enabling the service broker have any adverse ...
0
votes
1answer
83 views

What index to add in case of join by two optional fields

I have query similar to the one below, it joins two tables by field which can have NULL value. The result matchs the data if both tables have the same data or consider the condition as optional. ...
0
votes
1answer
857 views

Granting permissions only on a set list of objects

I have a SQL Server 2005 database with a large number of tables in the dbo schema. I now created a new schema (call it myschema) that only has three table-valued functions and two stored procedures in ...
2
votes
0answers
75 views

How to safely take an SQL job out of the schedule?

There is a table | jobID | status | startTime | endTime | and a job, that is scheduled to run every X seconds, it will take the first undone job ID, put it in stored proc and run it. Stored proc takes ...
5
votes
1answer
668 views

Find transactions that are filling up the version store

we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases. Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be ...
2
votes
1answer
256 views

SQL Merge Replication: Cannot resolve conflict

I have a SQL Merge Replication over HTTP (Replisapi.dll). I'm not very familiar with the internas of the replication conflict solving because it was always working so far. Now I have two conflicts in ...
1
vote
1answer
352 views

SQL Azure: Drop Constraint and Index

This may be a stupid question, but I'm not a DBA and just wanna make sure of what I'm doing before I make a mistake. Here is my question: When dropping Constraints, are their indexes dropped as well? ...