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.

Filter by
Sorted by
Tagged with
10
votes
10answers
16k 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
10k 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
17k 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
957 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
343 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
6k 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
510 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
160 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
921 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
223 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
50 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
240 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
78
votes
6answers
70k 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
103 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
74k 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
537 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
9k 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
68 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
242 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
57k 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
69 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
9k 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
571 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
218 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 ...
41
votes
3answers
56k 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 ...
49
votes
4answers
43k views

What is lock escalation?

I was asked this question at an interview and had no answer. Can anyone here explain?
19
votes
5answers
10k 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
716 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....

1
549 550 551 552
553