Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [sql-server]

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.

2
votes
2answers
331 views

What is wrong in debunking myths about clustered indexes?

What is wrong in code. or its results, illustrating that clustered index is evil [1]? and how to de-debunk, i.e. to return to customary myths and best practices? [1] Debunking myths about ...
9
votes
2answers
1k views

Clustered indexing is now must - why?

Earlier, there were non-conclusive to me debates / discussions on whether to (always) engage / avoid clustered indexes. Well, I understood that they are to be used sometimes with proper + specific ...
2
votes
1answer
452 views

SQL Server Assertion Error

Does anyone have a solution to this problem? SQL Server Assertion: File: , line=10850 Failed Assertion = 'GetLocalLockPartition () == xactLockInfo->GetLocalLockPartition ()'. This error may be ...
4
votes
2answers
535 views

Bizarre crash in T-SQL - what is it?

What would be a typical situation under which the following error would occur, and what does it mean? Also note that I find myself disconnected from the server when the query fails (but I have not ...
1
vote
2answers
4k views

Repair a SQL Server 2000 MDF file

I had a SQL Server 2000 MDF on a SQL Server 2005 server (moved from prior version), there was a hardware failure. After sending harddisk, it was supposed the mdf / ldf files were recovered, but I am ...
5
votes
2answers
410 views

SQL Filegroup degrading performance

On our dev boxes, our database resides entirely in the PRIMARY filegroup, and everything works fine. On one of our production servers, recently upgraded from 2005 to 2008, we noticed it was ...
5
votes
2answers
9k views

SQL Server 2008 DBCC Problems

We have a database Ms DBCC CHECKDB (MS) Or ALTER DATABASE MS SET SINGLE_USER DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE MS SET MULTI_USER Error message Msg 0, Level 11, State 0, ...
7
votes
3answers
77 views

Does SQL Server guarantee accurate data storage? If so, how?

This question is specific to SQL Server, but other DB replies welcome. Read below for some history. My short question is, how does SQL Server guarantee that when I request "ABCDE" to be written to ...
5
votes
3answers
140 views

Interpreting an execution plan

I need some help analysing some simple t-sql execution plans. I have a table [User] with the following 3 columns: Id(Primary Key), Username(nvarchar(50)), FirstName(nvarchar(50)) I have Created an ...
31
votes
3answers
43k views

What is the difference between Shrink Database and File?

DBCC ShrinkDatabase() DBCC ShrinkFile() Do I need to run both DBCC commands in order to shrink the database? What is the difference between these two above?
10
votes
9answers
14k views

Shrink a database below its initial size

I've got a SQL server 2005 dev database that is a 30GB copy of live. We have deleted some data that is not needed in dev, which brings the data file space used down to 20GB. So we have about 33% ...
2
votes
1answer
2k views

Exclude .LOG file while restoring a database

I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but ...
14
votes
3answers
9k views

SQL Server - Temporary vs. Physical Tables

A movement is afoot in my place of employ to move away from using #temp tables and instead to use permanent physical tables with SPIDs. Whenever one would have previously INSERTed INTO a #temp table, ...
11
votes
3answers
15k views

Performance hit using CAST in T-SQL

We have a SQL generator that emits SQL conditional statements generically for specified fields (which for the sake of discussion: we will label as myField). If myField is of type NVARCHAR, we can ...
4
votes
1answer
842 views

DBCC CHECKDB WITH DATA_PURITY gives out of range error

I have restore a SQL Server 2000 database onto SQL Server 2005 and then run DBCC CHECKDB WITH DATA_PURITY and I get this error: Msg 2570, Level 16, State 3, Line 2 Page (1:19558), slot 13 in object ...
2
votes
1answer
322 views

SQL Server DB Restore Clustered to Single Node?

Can you restore a DB from a SQL Server 2005 Clustered Environment (Prod) to a single server (single node) (SQL Server 2005) in Dev/Sys? Cheers, Conor
7
votes
3answers
9k views

Howto use Windows Authentication with SQL Server 2008 Express on a workgroup network?

I have two computers running SQL Server 2008 Express: c01 and c02, I setup both for remote connection using windows authentication. Worked fine for c02 but not for c01. This is the error message I'm ...
20
votes
5answers
5k views

Using IF in T-SQL weakens or breaks execution plan caching?

It has been suggested to me that the use of IF statements in t-SQL batches is detrimental to performance. I'm trying to find some confirmation or validate this assertion. I'm using SQL Server 2005 ...
5
votes
3answers
3k views

Should I be regularly shrinking my DB or at least my log file?

My question is, should I be running one or both of the shrink command regularly, DBCC SHRINKDATABASE OR DBCC SHRINKFILE ============================= background Sql Server: Database is 200 ...
4
votes
3answers
501 views

Shrink database after removing extra data [duplicate]

We have a need to fit a database in 4GB in order to use SQL Server Express Edition. I started from a 7GB database, and found a lot of unneeded records, and deleted them. After shrinking, the database ...
7
votes
1answer
152 views

Is there a performance hit by breaking out tables into different schemas in SQL Server?

Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server. For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and ...
3
votes
3answers
721 views

Is there an equivalent of “OPTION(RECOMPILE)” or “WITH RECOMPILE” for an entire connection?

I'm curious. Is there any way to prevent SQL query execution plans from being cached for an entire connection's duration? Can I run a command that basically says, "use OPTION(RECOMPILE) for all ...
3
votes
3answers
5k views

Undo Database Restore in SQL Server 2008

I accidentally restored a SQL Server 2008 database with a wrong backup. Is there any way to undo this and restore to previous version?
4
votes
2answers
209 views

Database Mirroring of SQL server

I have two databases that are mirrored to another server using database mirroring. The mirror server has to be down for some reason for few days. Now, the production server is having principal ...
5
votes
1answer
48 views

How do I list the database files contained in a SQL Server 2005 backup file using T-SQL?

How do I list the database files contained in a SQL Server 2005 backup file using T-SQL? For instance: The restore process will create: bob.mdf bob.ldf etc...
13
votes
3answers
107 views

Which is faster join

Which is faster SELECT * FROM X INNER JOIN Y ON x.Record_ID = y.ForignKey_NotIndexed_NotUnique or SELECT * FROM X INNER JOIN Y ON y.ForignKey_NotIndexed_NotUnique = x.Record_ID
71
votes
6answers
59k views

Why would SET ARITHABORT ON dramatically speed up a query?

The query is a single select containing a lot of grouping levels and aggragate operations. With SET ARITHABORT ON is takes less than a second, otherwise it takes several minutes. We have seen this ...
4
votes
2answers
102 views

query plan caching (sql server)

Is there a measure of time frame as to which the execution plan of inline queries will cached?
18
votes
3answers
62k views

Grant permissions to run an SQL server job

I have a job on my MSSQL server 2005, that I want to allow any database user to run. I'm not worried about security, since the input to the job's actual work comes from a database table. Just running ...
0
votes
1answer
1k views

Sql Server merge replication primary key violations

Using Sql Server 2008. Pretend I have a publisher and 2 subscribers A and B. The replicated table has a guid on it and also a primary key that is not the guid. Frequently, updates happen at both ...
3
votes
4answers
528 views

Help improving SQL Server 2005 query execution time

One of my stored procedure has long execution time (average around 4 to 7 minutes). I want to make it run faster. I am looking at the execution plan and two things are contributing most of the cost. ...
8
votes
8answers
2k views

Granting SA privileges for Developers on the development box

In spite of our vehement protests, our management has decided that the development team must be granted 'sa' rights on the development server. The catch is that we, the DB support group are still ...
5
votes
4answers
1k views

SQL Server restore from transaction log

In SQL Server 2005, if you have a database with no backup - but it is running in full recovery mode - is it possible to "roll back" some deletes? If yes - how?
14
votes
5answers
8k views

Attach/detach vs. backup/restore

I need to transfer the database (as a whole) to another server, to make a duplicate database to setup another test environment. I have two choices: Make a full backup at source server/restore at ...
0
votes
1answer
1k views

SQL Server 2008 - Restore Database With RESTART

I am trying to restore a database from a backup file on a SQL Server 2008 instance using the following SQL command: BEGIN RESTORE DATABASE [MyDataBase] FROM DISK=@db_location WITH ...
6
votes
1answer
3k views

SQL Server Migration restore backup vs copy data and log files

I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. I've read from a few different sources that I can either: Perform a backup of the database on SQL ...
7
votes
3answers
65 views

What is not persisted in a database backup / restore? (SQL Server 2005)

If I take a backup of a database on a SQL 2005 Server, and then restore that backup to a different (new) database on the same database server instance, what things will not be persisted (things like ...
13
votes
4answers
220 views

SQL Server 2005/8 Query Optimisation Hints

I'm looking at educating a team on writing better SQL Server queries and was wondering what people's best hints were for improving performance. For instance I once had a DBA who insisted that count(*)...
29
votes
5answers
47k views

Load Balancing with SQL Server

What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programmatic access. Is this built in or does a solution have to be purchased? I'm trying to ...
5
votes
4answers
65 views

How do I guarantee data is the same after upgrading SQL Server?

I have to upgrade a database from SQL Server 7 and SQL Server 2000 to SQL Server 2008. Additionally I have to be able to prove in a court of law that the data pre-migration is exactly the same post-...
6
votes
3answers
8k views

Problem Connecting Report Server to Report Server Database in SSRS

I have configured Reporting Services on one Server and have the ReportServerDb and ReportServerTempDb on other server (Future requirement of scale out deployment). I am connecting to these servers ...
6
votes
1answer
567 views

SQL Server 2005 Express in VMware causing very high CPU load

I'm having the problems described in KB937745 - very high CPU usage and the Application Log is reporting something like this: The client was unable to reuse a session with SPID SPID, which had been ...
5
votes
1answer
209 views

Change SQL Server 2005 from Standard Edition to Workgroup

I have been tasked with performing an edition change from SQL Server 2005 Standard Edition to SQL Server 2005 Workgroup Edition, on Windows Server 2003 Standard Edition. Due to a previous failed ...
40
votes
3answers
54k views

N prefix before string in Transact-SQL query

Would you tell me, please, when should I use N prefix before string in Transact-SQL query? I have started to work with a database where I don't get any results using query like this SELECT * FROM ...
41
votes
4answers
34k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
19
votes
5answers
9k views

SQL Server - separate database for reports?

On our SQL Server, we have a database for each of our web apps. For reports, we use Reporting Services and all report data (including report parameters) come from stored procedures. The stored ...
7
votes
5answers
671 views

Improving SQL Server performance on VMware under Linux

I have a Linux server in a small company (3 users) acting as a domain server and running two VMs under VMware: one has SQL Server 2000 on it and one has SQL Server 2005, in each case running on Win2k....