Questions related to Microsoft SQL Server. If your question is version specific please tag it specifically for that version as well. Does your SQL Server question belong here or on another site? Click the about the sql-server tag link on the line below.

learn more… | top users | synonyms (4)

61
votes
9answers
52k views

Dump Microsoft SQL Server database to an SQL script

Is there any way to export a Microsoft SQL Server database to an sql script? I'm looking for something which behaves similarly to mysqldump, taking a database name, and producing a single script ...
54
votes
7answers
5k views

What are the drawbacks of running a database inside a virtual machine? How do I overcome them? [closed]

Running anything inside a virtual machine will have some level of performance hit, but how much does it really impact the performance of a database system? I found this academic reference paper with ...
37
votes
22answers
13k views

PowerShell? Do you use it? Can you show me some cool system administration things I can do with it?

I keep reading everywhere that PowerShell is the way of the future. When it was first released I did a whole bunch of virtual labs, but since then I still haven't used it in a production environment. ...
36
votes
18answers
756 views

Poor internal database - replace it or chuck hardware at it?

So - we have an internal company database, the usual kind of stuff: manages clients, phone calls, sales deals and client agreements/schemes. It's an Access 2000 front-end, and an SQL Server 2000 ...
32
votes
5answers
1k views

Pay no attention to that SAN behind the curtain

Once upon a time, I built my own SQL servers, and had control over drive configuration, RAID levels, etc. The traditional advice of separation of data, logs, tempdb, backups, (depending on budget!) ...
31
votes
7answers
36k views

How do I reduce transaction log backup size after a full backup?

I have three maintenance plans set up to run on an Sql Server 2005 instance: Weekly database optimisations followed by a full backup Daily differential backup Hourly transaction log backups The ...
27
votes
7answers
14k views

Is it safe to have SQL Server auto-shrink turned on?

There are many SQL Server options that can be enabled for databases, and one of the most misunderstood ones is auto-shrink. Is it safe? If not, why not?
25
votes
7answers
7k views

SQL Server disk design on an ISCSI SAN

Its standard practice to separate log and data files to separate disks away from the OS (tempdb, backups and swap file also) Does this logic still make sense when your drives are all SAN based and ...
25
votes
5answers
27k views

SQL Server (2005/2008): Does full backup truncate the log in full recovery mode

I've just read through a lot of MSDN documentation and I think I understand the different recovery models and the concept of a backup chain. I still have one question: Does a full database backup ...
22
votes
8answers
23k views

Will disabling hyperthreading improve performance on our SQL Server install

Related to: Current wisdom on SQL Server and Hyperthreading Recently we upgraded our Windows 2008 R2 database server from an X5470 to a X5560. The theory is both CPUs have very similar performance, ...
20
votes
7answers
7k views

Is it OK to run perfmon on production servers? And why?

Or should perfmon be limited to a Dev/QA server with load tests that simulate production activity? I'd like to run perfmon for two days (like Sql Server master Brent Ozar suggests) to get an overall ...
20
votes
6answers
57k views

How to rename a SQL Server 2008 instance?

How can I rename a SQL Server 2008 instance without reinstalling? For example, if the db is referenced as "MySQLServer\MSSQL2008", how can I rename to "MySQLServer\SQL2008"?
19
votes
8answers
62k views

How do I determine the size of my SQL Server database?

Basic: what's the size on disk of my MS SQL Server DB? More: can I quickly see where the data is? i.e. which tables, logs, etc
19
votes
9answers
42k views

SQL Server to sql server linked server setup

Please explain what is required to set up a SQL Server linked server. Server A is SQL 2005 windows logins only Server B is the same (SQL 2005 windows logins only) Server A runs windows XP Server B ...
17
votes
6answers
39k views

what are named and default instance?

what are named and default instance? what is the difference between them? why they are used?
17
votes
12answers
2k views

Is there a good way to backup a petabyte of data and store it?

I'm starting to see clients with hundreds of terabytes of data (in SQL Server installations). As the total volume of data in some enterprises approaches meaningful fractions of a petabyte, I'd like to ...
17
votes
8answers
144k views

How do I get this SQL Server ODBC Connection working?

Note: I've obviously changed the server names and IPs to fictitious ones. Here's what's going on. I've got a server, which I'm calling MYSERVER, running Microsoft SQL Server Express 2005. Right on ...
16
votes
6answers
7k views

SQL Database physical file fragmentation

I know that there are really three kinds of fragmentation that I need to be concerned about as a DBA: Index Fragmentation in the SQL data files, including clustered index (table) fragmentation. ...
16
votes
6answers
949 views

Resources for the accidental DBA

Within the Microsoft platform, most of the enterprise level programs (SharePoint, any of the System Center apps, any of the Dyamics apps, etc) all run on top of SQL Server. For administrators of ...
15
votes
4answers
12k views

How can I tell if Snapshot Isolation is turned on?

In SQL Server 2005/2008, how can I tell if Snapshot Isolation is turned on? I know how to turn it on, but I can't find the incantation to get google to tell me how to query the state of the Snapshot ...
15
votes
2answers
76k views

I cannot connect to my local SQL Server 2008?

I've just installed SQL Server 2008 and I cannot connect to it with SQL Server Management Studio. My intention is to just let my local windows user be authenticated but I am not totally sure how to do ...
15
votes
2answers
5k views

Windows LocalSystem vs. System

http://stackoverflow.com/questions/510170/the-difference-between-the-local-system-account-and-the-network-service-accou tells: Local System : Completely trusted account, moreso than the ...
15
votes
3answers
2k views

How to exclude indexes from backups in SQL Server 2008

Our nightly full (and periodic differential) backups are becoming quite large, due mostly to the amount of indexes on our tables; roughly half the backup size is comprised of indexes. We're using the ...
15
votes
3answers
22k views

Setting Low Disk Space Alerts on Windows Server 2008

I was wondering if there is an easy way to trigger an e-mail alert on Windows Server 2008 when any logical disk partitions become low on space. I have 2 SQL servers that have come close to running ...
14
votes
7answers
2k views

When is the right time to introduce high availability for web site?

When is the right time to introduce high availability for web site? There are many articles on High Availability options. It’s not that obvious however WHEN is the right time to switch from single ...
14
votes
2answers
4k views

Setting Trace Flags “Permanently” in SQL Server?

We need to use SQL Server Trace Flag 7646 to help mitigate some full text blocking, but I was dismayed to find that the trace flag "unset" itself, probably when the database server was rebooted. I've ...
14
votes
3answers
599 views

Combining SharePoint and Database Mirroring

This isn't a how-should-I-do-this question, just to set the stage. It's a what has your experience been? Please read through the whole question before quickly replying. I spent the day yesterday ...
13
votes
3answers
12k views

Database 'XXX' is in transition. Try the statement later

im trying to connect to a sql server database i get this error Database 'XXX' is in transition. Try the statement later. i cancelled a long query earlier today but for some reason i can't get the ...
13
votes
3answers
37k views

What is the difference between SQL Server standard and web edition?

Is there any technical difference between these 2 editions or is it just how they are licenced?
13
votes
6answers
31k views

How do I change the collation of a SQL Server Database?

I'm trying to standardise all databases on a single collation - Latin1_General_CI_AS (the standard collation). I have some databases that are in SQL_Latin1_General_CP1_CI_AS. I know I can use ALTER ...
12
votes
23answers
14k views

What is the simplest and fastest way to transfer large file through a Windows network?

I have a Window Server 2000 machine running MS SQL Server that stores over 20GB of data. The database is backed-up every day to the second harddrive. I want to transfer those backup files to another ...
12
votes
3answers
2k views

DBA who is worried reorganizing or rebuidling indexes may cause data loss?

We have some databases with index fragmentation that is > 95%. As best I can tell the indexes have never been rebuilt much less reorganized. In years. (In fairness, these tables do seem to have ...
12
votes
7answers
4k views

SQL Server - Force DB in memory?

We have a beefy Windows 2008 x64 server (4 x 4 core CPU, 32GB RAM) running SQL Server 2005 64-bit. We have a small (6GB) but very important database that is somewhat slow to access until the pages are ...
12
votes
2answers
6k views

Find out which database in SQL Server 2005 uses how much RAM

A friend of mine asked me today (trying to calm down an agitated customer of his) how you could find out in SQL Server 2005 which database uses how much memory (in the server's RAM that is) at any ...
12
votes
4answers
15k views

Recommended disk/partition setup for a SQL Server

I'm looking for some advice regarding the best way to set up my disks/partitions for SQL Server. Here are some of my major concerns: How should the SQL files be seperated (data files, logs, temp)? ...
12
votes
3answers
3k views

How do I remove a specific bad plan from the SQL Server query cache?

We have one particular SQL Server 2008 query (not a stored proc, but the same SQL string -- executes every 5 minutes) that intermittently caches a very bad query plan. This query normally runs in a ...
11
votes
4answers
910 views

SQL Server setup check list

What are the things I have to consider when I have to install SQL Server (2005/2008) on a new server? Storage setup TempDB Security What are some common mistakes made in that process?
11
votes
4answers
22k views

With SQL Server (2008), can you grant create table permission within a schema?

Working with security in SQL Server 2008, I'm trying to grant a user CREATE TABLE permissions, but only within a specific schema. Does CREATE TABLE permission only apply at the database level? Can I ...
11
votes
4answers
10k views

SQL Server 2005: There is insufficient system memory to run this query

One of our SQL Servers, which has been running stable for quite some time (years), has recently been throwing insufficient memory errors. From the Application Event Log, we see: Event ID: 701 ...
10
votes
5answers
6k views

SQL Server Express for Production Databases?

We are about to roll out a dual web/internal transactional application where each client has their own database. Each database is very small - under 50MB each, so we were wondering if it would make ...
10
votes
6answers
17k views

How to migrate from SQL Server 2005 to 2008

How do you migrate SQL Server 2005 databases to SQL Server 2008? Is it as easy as backing up 2005 databases and simply restore in 2008? Are there any compatibility issues? Is SQL Server Reporting ...
10
votes
6answers
12k views

How does changing Windows SID affect SQL Server?

Our Windows admins have identified an issue with the way that they clone OS. Apparently, some of the cloned servers end up with the same SID at the OS level. I hear MSFT doesn't support computers that ...
10
votes
3answers
4k views

How do I shrink the size of a SQL Server log file

I can't figure out how to shrink the size of the databases ldf file. The DBA says I should use backup log dbname with truncate_only And while that looks like it executed correctly in SQL Query ...
10
votes
5answers
28k views

How do I get SQL Server to release its memory?

I know SQL likes RAM, but releases it when Windows asks. I know this is how SQL is supposed to work. But I have a Windows Administrator who isn't convinced that SQL will actually return the RAM, and ...
10
votes
5answers
11k views

How to load-balance SQL Server 2008 for high-usage ASP.NET applications?

Imagine that you have a successful web application that uses ASP.NET and IIS 7. It generates many calls to a SQL Server 2008 database, and is expected to be available to the public with 99.9% uptime ...
9
votes
6answers
10k views

Reclaiming memory from SQL Server

I have an SQL Server instance who's memory usage gradually grows until windows will give it no more. It seems logical that the occasional big query result would cause the instance to grow. Is there a ...
9
votes
14answers
816 views

What Are The Reasons to Still Use SQL Server 2000?

From the questions on StackOverflow and elsewhere, I notice that there are still people using SQL Server 2000. To be honest, the greatest effect on me is that I have to remember how we used to do ...
9
votes
11answers
808 views

Free SQL Server tools [closed]

Anyone have an update to date list of Free administrative Tools for SQL Server. There is a forum post on SQLServerCentral but its pretty outdated. Please provide a link as some vendors do a pretty ...
9
votes
3answers
4k views

In SQL Server, when should you split your PRIMARY Data FileGroup into secondary data files?

Our database currently only has one FileGroup, PRIMARY, which contains roughly 8GB of data (table rows, indexes, full-text catalog). When is a good time to split this into secondary data files? What ...
9
votes
7answers
24k views

Is using integrated security (SSPI) for accessing SQL Server better for web applications?

When deploying web applications (.net) to a production environment, is it better to use integrated security or does it even matter? It seems to me that if a hacker breaks the web server, it won't ...