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)

6
votes
1answer
460 views

LOCK error after creating a clustered index

I just created a clustered index on a 1MM row table (page and row locking turned on). I now try to run queries and I get this error: "The instance of the SQL Server Database Engine cannot obtain a ...
8
votes
3answers
739 views

I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?

Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as ...
5
votes
1answer
386 views

Inline table-valued function vs inline sql

I'm seeing some odd behavior in trying to tune an inline table-valued function. Specifically, if I take the guts of the function and it with the same parameters as I'd have called the function with, I ...
3
votes
1answer
674 views

Normalized Table Structure for Logging

How can I normalize a table that has four columns for logging the latest activity for a single record: Created On Created By Modified On Modified By We have dozens of tables, all with these 4 ...
1
vote
0answers
150 views

Distribution Agent running but subscription does not exist?

I have an issue where in my replication topology, the distribution agent is running, and says there are no commands to distribute. However, checking the undistributed commands, it shows there are a ...
1
vote
1answer
96 views

Monitor Sql Server 2000

I was wondering if there is a free tool to monitor state of SQL Server 2000. I would prefer a web tool. I would like to see if some database is accessible, or are there any problems on server, etc. ...
3
votes
2answers
663 views

Cannot generate reports from SQL Management Data Warehouse

I'm running SQL Server 2008 R2 and have installed the MDW on one server and have a Data Collector collecting and uploading the server activity, query results, and Disk activity data to the MDW. When I ...
1
vote
2answers
335 views

Size of TempDB Microsoft SQL Server 2005

Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full. I am creating large temporary tables in a couple of queries. Once I am finished running queries, ...
17
votes
2answers
2k views

Optimising plans with XML readers

Executing the query from here to pull the deadlock events out of the default extended events session SELECT CAST ( REPLACE ( REPLACE ( XEventData.XEvent.value ...
5
votes
3answers
345 views

Reducing the size of a database

SQL Server newbie here. I'm a MySQL guy. I'm having a look at something for a client in their 2008 SQL Server, and need some advice. Whoever designed the database chose to log insane amounts of stuff ...
1
vote
1answer
140 views

Transposing data

I have a table with 31 columns which contain values for each day in a month and I want to create a tranpose table like the example below. Real data in the database is really big so I don't expect to ...
2
votes
1answer
391 views

Error deleting more than 1 row

I have a stored procedure that is supposed to delete several rows from a table. It does not work and gives me the error: The transaction ended in the trigger. The batch has been aborted I tried ...
5
votes
1answer
294 views

Why am I missing some output?

Archiving to avoid ID Exhaustion We are about to run out of IDs in one of the tables of our OLTP logging system. It's my job to find a way to 'archive' the existing row data in the full table and in ...
5
votes
2answers
539 views

SQL Server SMO and PowerShell formatting

I have probably been looking at this for too long to figure this out... What is the easiest way I could get this exported to a CSV format? Would it be better to dump this to a table in a database ...
5
votes
2answers
273 views

SQL Server 2008 R2 database is suspect, tried it all. Final destination?

This night my hosting provider made a "hard reset" on my VPS hosted in their farm. And one of most important databases (SQL Server 2008R2 database) is now in Suspect mode. My provider has backups ...
2
votes
2answers
732 views

Accessing a network share via a SQL Server 2012 Availability Group

If you have a SQL Server Cluster called CLS, with machines CLS1 and CLS2, accessing: \\CLS\network\path is equivalent to accessing either \\CLS1\network\path or \\CLS2\network\path depending ...
5
votes
2answers
897 views

Bulk Data Loading and Transaction Log

im currently working on a project which bulk import data from flat files(csv) about 18 differents files each linking to a specific table through some store procedure. I followed the steps as advised ...
1
vote
0answers
150 views

SQL Server BCP imports, but table still has 0 records

I ran the following command from the command line on SQLServer 2008 R2 BCP C4L.dbo.districts in C:\TEMP\districts.sdf -f C:\TEMP\districts_import.fmt -T' No errors were reported and it ran as ...
5
votes
1answer
479 views

Recovery time effects of Availability Groups

We did different kinds of basic Tests and AlwaysOn passed in many tests. We finally did a heavy write test for AlwaysOn and it gave surprising results. The actual Test details are here, the goal is ...
2
votes
1answer
1k views

Error in restore database

I use this query in SQL Server 2008 R2 to restore DB1 database: RESTORE DATABASE [DB1] FROM DISK = N'D:\new.bak' WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10; The following error ...
1
vote
0answers
27 views

database sharding - handling common/universal tables [duplicate]

Possible Duplicate: sql server database sharding - what to do with common data / non sharded data We have a very large scale enterprise level database. As part of our business model all web ...
6
votes
3answers
6k views

How can I backup a large SQL Server database without impacting performance?

We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job. The database itself is about ...
8
votes
3answers
582 views

sql server database sharding - what to do with common data / non sharded data

We have a very large scale enterprise level database. As part of our business model all web users hit our web servers at the same time each month which in turn hammer our sql box. The traffic is very ...
6
votes
1answer
2k views

Timeout expired error while using copy database wizard

Microsoft made me believe that SQL Server 2012 Copy Database Wizard is the most optimal way of copying a SQL Server 2000 database to SQL Server 2012. After struggling a few hours, I was able to get ...
3
votes
3answers
633 views

How to delete large amount of data from a sql table?

We ran out of disk space and we have a table with about 10million records. We'd like to delete the old records which are about 2million. What are the impact of just deleting these using a simple ...
4
votes
2answers
2k views

How do I configure SQL Server 2012 so that it can restore and see files in my user account?

I have an SQL Server 2012 instance running as a service on my computer, and according to the service page it logs on as account "NT Service\MSSQLSERVER". However I can't see that account name ...
3
votes
1answer
1k views

SQL Server 2008 Setup Error 0x80070490

I am trying to install SQL Server 2008 x64 on Windows 2008 R2 and keep getting the following error: SQL Server Setup has encountered the following error: Element not found. (Exception from ...
16
votes
1answer
5k views

MERGE a subset of the target table

I am trying to use a MERGE statement to insert or delete rows from a table, but I only want to act on a subset of those rows. The documentation for MERGE has a pretty strongly worded warning: It ...
4
votes
1answer
320 views

Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?

I kick off the following command in my app: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE Note: ...
13
votes
2answers
301 views

Does firing off an indefinite WAITFOR increase the log file size?

In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue: WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue) The ...
4
votes
1answer
111 views

Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?

Expressions like NOT IN (1,2,3) are valid in a where clause: SELECT foo FROM bar WHERE ((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22)) However if I want to move an elaborate expression ...
1
vote
1answer
109 views

Track access to database

How can I see who has accessed the database, viewed the tables or executed SELECT, for SQL Server 2008 R2?
2
votes
3answers
3k views

How to avoid using the IN clause in sql query used in a stored procedure

Is there a better way to write the SQL other than using the IN clause in the below given SP. When i am using this IN clause, i suffer a performance dip due to large number or records involved in the ...
1
vote
2answers
181 views

Automatic create SQL database and table relationship

For SQL Server 2008, what is the best way to automatically "draw up" the Database relation ship and table relationship too? Is there any way to visualize database with this.
4
votes
1answer
1k views

How to restore replication after server crash

We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor. Then the RAID on the ...
1
vote
1answer
1k views

Copy data between two databases periodically [duplicate]

Possible Duplicate: How to update one database from another? I need to know how to copy data across to another SQL Server database periodically. Both databases are in the same SQL server; I ...
3
votes
2answers
2k views

Procedure cache (plan cache) size

I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding ...
1
vote
2answers
198 views

XML Large DataBase is the best? [closed]

Is the best way to implement a large database is XML? Or is there a better way? 1 - reduce the time complexity 2 - Reduce the amount of traffic between the tables And other factors ... Which ...
1
vote
2answers
167 views

2012 Transactional replication not delivering new articles

I have added a new article to an existing push publication with the following code sp_addarticle @publication='statics', article='FlightBaggageMarkup_tbl',source_object='FlightBaggageMarkup_tbl' ...
3
votes
1answer
622 views

Profiler TextData output is null

I am doing a trace on a database and dumping the trace realtime into a table. The reason I'm doing a trace is I want to see all the queries that are being executed against the database. ...
5
votes
1answer
2k views

ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?

Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
11
votes
2answers
2k views

Deadlocks From Locks on Same Temporary Tables In Different Processes

I have found a deadlock that appears to show something I thought was impossible. There are two processes involved in the deadlock: 1. process8cf948 SPID 63 Performing an ALTER TABLE on temporary ...
3
votes
2answers
170 views

Select blocking issue [closed]

The database was not responding to simple select statements like select * from table I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored ...
-1
votes
1answer
129 views

Covering Index isn't being used and I don't know why

We have this complicated query that I'm trying to make "better" until we move it to pull from a data warehouse. I need a solution that's "good enough" for now and I think I'm about 2-3 indexes away ...
0
votes
1answer
169 views

avoid insert xml duplicate data

how can to prevent duplicate inserts for every Execute? insert into IQ (XMLDT)values('<row>sdsdf</row>')
2
votes
1answer
361 views

Non-clustered Index Update not displayed in execution plan [duplicate]

Possible Duplicate: Nonclustered Index Insert I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution ...
5
votes
1answer
1k views

Performance tuning a cascading delete with many foreign keys

I have a delete query that is taking a long time. Looking at the execution plan, I see that most of the estimated cost in the delete query is in a section of the data model that had a lot of data ...
4
votes
1answer
3k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...
14
votes
1answer
10k views

How do I attach a database in SQL Server?

Some common questions relating to attaching databases in SQL Server: What does it mean to attach or detach a database? How do I detach a database? How do I attach a database? What ...
2
votes
1answer
527 views

What does it mean if the spt_monitor - table in SQL Server is empty?

What does it mean for my environment if I find that the spt_monitor table in SQL Server is empty? What is this table, how is it used and what populates it? I am using SQL Server 2005. My ...