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)

31
votes
7answers
38k 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 ...
30
votes
7answers
18k 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?
12
votes
4answers
17k 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)? ...
26
votes
5answers
30k 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 ...
21
votes
7answers
8k 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 ...
12
votes
23answers
16k 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 ...
15
votes
2answers
7k 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 ...
4
votes
8answers
3k views

MS SQL Layout for best performance

We have purchased a new server to act as a MS SQL backend. I am curious to know what the best setup would be for it. The server is a Dell R710 it has 6 hard drives 2x 74GB 15k and 4x 146GB 15k ...
1
vote
3answers
5k views

How do I identify slow queries in sql server?

I've found long, complicated instructions like this when googling for the answer to this question, an brief reference links such as in this post. I'm looking for a succinct a procedure as possible to ...
12
votes
2answers
7k 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 ...
8
votes
2answers
2k views

Correct value for fill factor for clustered indexes with surrogate identity keys

I have a large table which has a clustered index with an identity primary key. I'm deciding on the correct value for the fill factor for this table to minimise page splits. We maintain indexes using a ...
3
votes
4answers
6k views

Does restoring a SQL database from backup rebuild its indexes?

Does restoring a SQL database from backup rebuild its tables and indexes from scratch? Or does it keep it in the same internal physical order it was in at the time of backup? We're using SQL 2000 ...
8
votes
5answers
7k views

Maximum size of SharePoint content databases

Another question from talking to the SharePoint gurus while teaching MCM yesterday. The SharePoint guidelines are that content databases above 100GB are not supported. Without getting into the reasons ...
7
votes
1answer
2k 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 ...
7
votes
10answers
16k views

Configuring SQL for optimal performance… SSD or HDD?

Does anyone know of any comparisons that show how SSDs compare to HDDs for performance in a SQL environment? I'm trying to understand what type of performance benefit might be gained by moving to ...
7
votes
7answers
4k views

Is there anyway to determine who dropped a table?

A table in the production database has 'mysteriously' disappeared. Does anybody know of any way to diagnose what the heck happened to it? And who did it? Edit 1: This is an internal app, with weak ...
2
votes
1answer
1k views

what should be monitored for baseline sql server performance

What are the baseline perfmon counters for monitoring SQL Server and what do they mean?
12
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 ...
7
votes
9answers
11k views

MS SQL Server slows down over time?

Have any of you experienced the following, and have you found a solution: A large part of our website's back-end is MS SQL Server 2005. Every week or two weeks the site begins running slower - and I ...
7
votes
3answers
7k views

Current wisdom on SQL Server and Hyperthreading?

Lots of articles out there (see Slava Oks's original SQL 2000 article and Kevin Kline's SQL 2005 update) recommend disabling hyperthreading on SQL servers, or at least testing your specific workload ...
7
votes
4answers
14k views

What's your best practice Recovery Model for SharePoint databases

Paul Randal asked some really nice questions about best practices for SharePoint SQL databases. Today, while helping a customer to maintain SharePoint installation he asked me a question about best ...
5
votes
3answers
2k 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 ...
4
votes
4answers
789 views

Creating a SQL server Maintenance Plan without the wizard

Is there a way to generate a SQL server maintenance plan that will be the same as the one generated by the wizard, but without going through the wizard dialog. I need something that can be created ...
3
votes
6answers
26k views

SQL - an error occurred during the pre-login handshake

Until yesterday evening, I was able to connect to my server from my local machine. Now, I get the following error: A connection was successfully established with the server, but then an error ...
3
votes
1answer
674 views

Possible disk IO issue

I've been trying to really figure out what my IOPS are on my DB server array and see if it's just too much. The array is four 72.6gb 15k rpm drives in RAID 5. To calculate IOPS for RAID 5 the ...
3
votes
4answers
779 views

Recommended SSD Setup

I'm building a new server which will be running a text search service for our online web services. This service requires fast IO so I am trying to further reduce any potential bottlenecks. I would ...
3
votes
2answers
424 views

How do you backup a database within SQL Server 2005 from the command line?

I need to create a scheduled task that will backup a database within a SQL Server 2005 instance. Is there a command line argument that I can use to achieve this?
3
votes
5answers
1k views

mysqldump for sql server

I have used mysqldump on a mysql database lots of times and am familiar with how it works. Does anyone know if there is a similar tool that could be used on a sql server database. I know sql server ...
2
votes
1answer
13k views

SQL Server setup media does not support the language

I'm getting the following error when i try and install MSSQL 2008 on Windows Server Standard 2008 R2:
2
votes
5answers
20k views

How can I synchronize MS SQL Server databases?

How can I synchronize MS SQL Server databases? Is there any tool, preferably open source?
2
votes
3answers
15k views

SQL Server: Memory Usage High (7GB) + Page File Usage High (7GB) but CPU Usage (2% to10%) [duplicate]

Possible Duplicate: SQL Server memory constantly going up, from 1GB RAM used to 7GB in a week. Hello all, I am running SQL server with a lot of scripts that update large amounts of data ...
2
votes
5answers
878 views

Should the Database be on the same box as the web server or separate

Im using SQL Server 2005 and IIS6. I have each on separate boxes (Quad cpu, 4G). Client wants to consolidate on to one box. This is an Enterprise application with 400 users. Performance is good now, ...
1
vote
1answer
2k views

Can't connect to Sql Server 2008 named instance

I just installed Sql 2008 Express on a new server running Windows Server 2008. I know Sql is working properly, because I can connect to the db fine locally, on the server. I cannot connect to it from ...
21
votes
6answers
48k views

what are named and default instance?

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

Is there a secure way to allow IIS 7 in a DMZ to access a DB server behind the firewall?

Our network admins are adamant that it is insecure for our web servers, which are hosted in the DMZ, to access the DB server behind our firewall. To get round the problem, we access the data via web ...
5
votes
1answer
609 views

Virtual vs. Named SQL Instances

What are the benefits to using virtual as opposed to running multiple instances of SQL on a box?
4
votes
6answers
12k views

SQL Server 2008 login problem with ASP.NET application: Failed to open the explicitly specified database

I am running SQL Server 2008 Express Edition on Windows Server 2008 with an ASP.NET application which must access the server. The ASP.NET application is associated with an application pool that runs ...
4
votes
9answers
9k views

SQL Server 2008 failover strategy - Log shipping or replication?

We just moved from SQL Server 2000 to SQL Server 2008. We were using our own log shipping on 2000 for failover. For 2008, we need to decide to use our own log shipping, built in log shipping, or ...
3
votes
2answers
4k views

Too Many winlogon.exe LogonUI.exe csrss.exe open on server?

I just had to restart my content server because it bogged out. Everything was fine after i restarted IIS and SQL Server, but then i opened the task manager and found the following. I've never seen ...
3
votes
7answers
28k 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 ...
3
votes
3answers
1k views

What is the best practice for the database owner in SQL Server 2005?

What is the best practice for the database owner in SQL Server 2005? I have already searched this page... http://technet.microsoft.com/en-us/sqlserver/bb331794.aspx and didn't find what I was ...
2
votes
1answer
470 views

error setting up replication from mssql to mysql

I'm trying to setup replication from SQL Server 2005 to MySql 5.1.53. I am able to add the publication without any problem. However, when I go to add the subscription I get this error: OLE DB or ...
2
votes
3answers
3k views

Large insert/update in table causes timeout while SQL Server re-arrange indexes

We insert/delete about 1 million rows of a two million rows table with SSIS. We have 20 indexes on this table. Right after the SSIS finished, we're experiencing timeouts in the frontend. The ...
2
votes
2answers
327 views

mysql to ms sqlserver

How can I convert database from Mysql to SqlServer 2005?
2
votes
2answers
693 views

How do I configure SQL Server 2005 to accept a connection to a private or internal IP address on a private network?

I'm in unfamiliar territory, but here's the scenario: I have an application server and a database server, and both are on a private network. How do I allow connections from my app server to the ...
2
votes
10answers
597 views

SQL2005 vs SQL2008

We are investigating replacing an existing SQL2000 server on Windows2003 (32Bit) with new hardware and a newer version. We are looking at an application that requires at least SQL2005, but will run ...
2
votes
4answers
902 views

Transferring data between SQL Server and MySQL

We host a couple of SQL Servers where we are planning to upgrade the environment (still classic asp). We are looking whether to move to a LAMP (or WAMP) environment instead of just following the ...
1
vote
2answers
484 views

Temporary IO boost for an MSSQL server

The company that I am working for is looking at doing a data upgrade on a database to go from one version of an application to the next (Axapta 3 to Ax 2009). This process is very IO intensive and ...
1
vote
3answers
2k views

How do I properly set up SQL Server Profiler in a production environment?

In our production environment (SQL Server 2008 Enterprise), we've been having issues with CPU usage and network utilization. Based on the specs of the (multiple) machines we have and the general ...
1
vote
4answers
12k views

Permission denied when creating database in Sql Server 2008 express

Running the following (generated by Sql Server Management Studio) on Sql Server 2008 Express under Vista: CREATE DATABASE [test] ON PRIMARY ( NAME = N'test', FILENAME = N'c:\Program Files\Microsoft ...