Questions tagged [sql-server]
All versions of Microsoft SQL Server (not MySQL). Please also add a version-specific tag, like sql-server-2016, since it is often relevant to the question.
29,397
questions
2
votes
2answers
341 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 ...
asked Nov 7 '10 at 20:20
Gennady Vanin Геннадий Ванин
6262 gold badges8 silver badges18 bronze badges
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 ...
asked Nov 5 '10 at 15:14
Gennady Vanin Геннадий Ванин
6262 gold badges8 silver badges18 bronze badges
2
votes
1answer
514 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
623 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 ...
6
votes
2answers
556 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
10k 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, Line 0: ...
7
votes
3answers
87 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
158 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 ...
36
votes
3answers
57k 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
10answers
17k 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
11k 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, ...
12
votes
3answers
18k 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
988 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
360 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
10k 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
7k 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
4k 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
512 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
183 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
1k 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
6k 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
233 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
52 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...
12
votes
3answers
308 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
87
votes
6answers
76k 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
105 views
query plan caching (sql server)
Is there a measure of time frame as to which the execution plan of inline queries will cached?
22
votes
4answers
83k 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
2k 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
540 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?
15
votes
5answers
10k 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
4k 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
70 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
244 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(*)...
31
votes
6answers
62k 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
71 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
10k 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
580 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
222 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 ...
42
votes
3answers
58k 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 ...
52
votes
4answers
47k views
What is lock escalation?
I was asked this question at an interview and had no answer. Can anyone here explain?
21
votes
5answers
11k 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 ...
8
votes
5answers
779 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....
