Stack Exchange Network

Stack Exchange network consists of 175 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]

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.

123
votes
9answers
166k views

How to dump a Microsoft SQL Server database to a 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 ...
66
votes
7answers
13k 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 ...
53
votes
4answers
187k views

Get a list of SQL Server Agent Jobs

I've got an extensive selection of these to add to a spreadsheet and don't want to go through by hand. What it the T-SQL command(s) to generate a list of SQL Server Agent Jobs?
49
votes
3answers
199k 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?
44
votes
22answers
19k views

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

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. ...
43
votes
16answers
8k views

Early signs of a bad sysadmin

We're about to get our first sysadmin to look after a multitude of SQL Servers which have previously been awkwardly looked after by a mixture of the developers and IT support. It's long overdue, and ...
43
votes
7answers
41k 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?
41
votes
6answers
54k 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 ...
39
votes
18answers
875 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 ...
38
votes
7answers
51k 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 ...
35
votes
5answers
2k 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!) ...
34
votes
4answers
40k 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 ...
32
votes
6answers
125k views

what are named and default instance?

what are named and default instance? what is the difference between them? why they are used?
31
votes
6answers
4k views

How can I prevent accidentally jacking with a production database?

Just recently, I had a developer accidentally try to restore a database to production, when he should have been restoring to a staging copy. It's easy to do, given that the db names are similar, i.e., ...
31
votes
6answers
56k views

How to check progress of DBCC SHRINKFILE?

Is there a way to find out the progress of DBCC SHRINKFILE statement? Here is how I was running it dbcc shrinkfile('main_data', 250000) I am running above statement on both SQL Server 2005 and ...
31
votes
1answer
62k views

What can user do with VIEW SERVER STATE permissions?

In SQL Server 2008 there is a permissions VIEW SERVER STATE. What rights this permission give to user? What SQL Server mean by SERVER STATE?
30
votes
11answers
347k 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 ...
29
votes
8answers
152k 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
29
votes
3answers
40k 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 ...
29
votes
7answers
84k 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 ...
28
votes
7answers
16k 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 ...
28
votes
8answers
37k 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, ...
28
votes
11answers
5k views

Things every SQL Server DBA should know

What things should every SQL Server database administrator know? Books, blogs, tools, you name it.
28
votes
6answers
80k 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"?
27
votes
7answers
8k 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 ...
27
votes
5answers
50k 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 ...
25
votes
9answers
60k 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 ...
24
votes
3answers
189k views

Install SQL CLR types & Report Viewer 2012 on Sql 2008 server

I'm in the process of installing a client's ASP.net site on a Windows Server 2003 box running Sql 2008. The site uses Report Viewer 2012, but when I attempted to install it on the server, I got the ...
24
votes
2answers
22k 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 ...
22
votes
2answers
19k views

Windows LocalSystem vs. System

https://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 ...
22
votes
3answers
89k views

“The wait operation timed out” when running SQL Server in Hyper-V

I'm running SQL Server (2012) on a Hyper-V instance. It has plenty of resources and 25% reserved of the total resources, the VHD is placed on a very fast SSD drive for quick response times. Every now ...
20
votes
2answers
30k views

How do I limit MS SQL Server memory usage?

I've heard MS SQL Server takes up as much RAM as it can to cache results. Well, it's not leaving enough bargaining room for our little server's RAM. How do I change the settings to limit the amount ...
19
votes
12answers
3k 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 ...
19
votes
5answers
49k views

mdf file cannot be overwritten when restoring a database in SQL Server

I got a databse A. It has some data in it. I created a backup for A as A.bak file. Then I create a new empty database B. And then I try to restore B from A.bak. But the SQL Serve tell me the following ...
19
votes
4answers
5k 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 ...
19
votes
6answers
11k 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. ...
19
votes
5answers
69k 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 ...
18
votes
9answers
110k views

Can't connect to SQL Server using 'sa' account, what I'm missing?

I'm trying to log in to a SQL Server 2005 Express instance using the following command: osql -S .\INSTANCE -U sa -P password I get the error: Login failed for user 'sa'. I can connect just fine ...
18
votes
6answers
22k views

Is there a way to determine the version of SQL Server that was used to create an MDF or BAK file?

I have a local copy of a SQL Server DB stored as an MDF file. Is there a way to tell what version of SQL Server was used to create that file?
18
votes
5answers
39k views

What happens during a live SQL Server backup?

Some of my coworkers were surprised when I told them that I can back up an SQL Server database while it's still running and wondered how that's possible. I know that SQL Server is capable of backing ...
18
votes
3answers
31k views

How to update a CLR Assembly without dropping assembly from SQL Server

How can i update a CLR function (or procedure) assembly dll without having to drop and re-create the assembly in SQL Server (2008 R2)? As it stands now if i update an assembly (e.g. to add a new ...
18
votes
2answers
147k 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 ...
17
votes
1answer
8k views

Alias to SQLEXPRESS

I have developer edition from SQL Server 2008, but now I have a piece of software with a hardcoded connection string: Data Source=.\SQLEXPRESS;Initial Catalog=db;User Id=sa;Password=1234; Now I ...
17
votes
4answers
19k views

In SQL Server, how do I create a login for an existing user?

I restored a database from one SQL Server instance on to a different SQL Server instance. The database allready has users added to it. But they don't have Login's for the new SQL Server instance. I ...
16
votes
3answers
27k views

Timeout saving table in SQL Server

I'm trying to add a column to a table with much data in SQL Server 2005, using SSMS. So I browse to the table, select Modify, and add the new column. Then, when I press Save, I get the following ...
16
votes
8answers
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 ...
16
votes
6answers
1k views

Resources for the accidental DBA [closed]

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 ...
16
votes
6answers
42k 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 ...
16
votes
1answer
3k views

Delete performance for LOB data in SQL Server

This question is related to this forum thread. Running SQL Server 2008 Developer Edition on my workstation and an Enterprise Edition two-node virtual machine cluster where I refer to "alpha cluster". ...
15
votes
1answer
41k views

How do you get the minimum and maximum memory allocation of a SQL instance using T-SQL?

I'm managing about 90 SQL Server instances and need a query to determine how much memory has been allocated to each instance. Please note, I'm not asking how to set it, just to see what it's currently ...