Microsoft's SQL Server is a relational database management system (RDBMS) that runs as a server providing multi-user access to a number of databases. This tag is for questions on the 2005 version

learn more… | top users | synonyms

31
votes
7answers
37k 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 ...
13
votes
6answers
32k 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
7answers
5k 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 ...
11
votes
2answers
13k 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 ...
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 ...
9
votes
2answers
11k views

SQL Server 2005 / 2008 - multiple files / filegroups - how many? Why?

I'm a developer at heart - but every now and then, a customer doesn't have a decent DBA to deal with these issues, so I'm called in to decide.... What are your strategies / best practices when it ...
9
votes
8answers
29k views

SQL Server restore a backup results in an error

I have a database in dev (SQL Server 2005 on Windows Server 2008) that I need to move to prod (SQL Server 2000 on Windows Server 2003). My process is as follows: Login to dev, open SQL Server ...
9
votes
2answers
262 views

Can I fit an entire DB in memory?

My database is about 1gb in size (according to the mdf file of my db). My database server has 4gb of ram. Looking at the memory consumption on the computer while active, it sits at around 85% used ...
8
votes
3answers
9k views

How to detect if full text search is installed in SQL Server

I need to find out if full text search is installed on SQL Server 2005. I don't have permission to log into the box and fire up Setup to check. However I can run SQL Server Management Studio and ...
7
votes
4answers
759 views

SQL Server live database backup strategies

I'm trying to come up with a backup strategy for SQL Server 2005. I am thinking of doing full backup once a week, differential backup once a day and transaction log backup every 15 minutes. The ...
7
votes
4answers
4k views

Best way to backup a SQL Server database nightly?

What is the best way to backup a SQL Server 2005 database nightly? I'm looking for a solution/strategy that would make the database backup file, and place it in an "outgoing" directory. We're ...
7
votes
1answer
337 views

SQL Server 2005 Performance Audit

Do you guys have any good step by step type links for performing an SQL Server 2005 Performance Audit? Something like this link would be great but for SQL Server 2005.
7
votes
7answers
253 views

SQL Server Security Crackdown

I've been working a job for 2 years now as a SQL Server DBA for a city. I was brought in to support an existing DBA, so I've slowly taken everything in and have been working for a while, trying to ...
7
votes
1answer
4k views

Cancel Database Take Offline

I accidentally tried to take offline a DB via the context menu command > Tasks > Take Offline. The process is ongoing now and no new connection can be made. How do I cancel this process?
7
votes
3answers
3k views

SQL Server - Cluster vs Mirror for high availability?

I have been doing research into various high availability options for SQL Server 2005. With regard to high availability, what circumstances would favor clustering over mirroring as an option? From ...
7
votes
7answers
15k views

My templog.ldf is huge (45gb), What if anything should I do?

I've a SQL 2005 installation and my templog.ldf file keeps growing to consume all the free space on the drive it's on. Sometimes it'll stop with a few mb free but sometimes it goes further, this being ...
7
votes
8answers
3k views

MSSQL Large Delete

I have a table within a MSSQL database that contains more than 100 Million records spread over about 100 days worth of data. I need to purge some of this data based on its date which is an indexed ...
7
votes
9answers
10k 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
2answers
1k views

What are the implications of the SHOWPLAN permission on SQL Server 2005?

A user wants the SHOWPLAN permission in order to use Execution Plans to tune a query. What are the implications of this permission. Is it safe to grant a user? I've seen the security note here, and ...
6
votes
7answers
20k views

How much RAM does a server actually need?

I have a quite a few servers deployed around the world. They are running Windows 2003 x64 with SQL Server 2005 x64 with 6 GB of RAM. The boxes do not have the best (or even an acceptable) ...
6
votes
4answers
17k views

SQL Server 2Kx: How to move a database to a different partition in the local file system?

SQL Server creates its databases in the system program files directory structure. I want to separate the database files away from the system partition. Assuming the database is already created and ...
6
votes
7answers
3k views

How do I automatically run nightly backups for Microsoft SQL Server 2005?

I don't see any built in mechanism for scheduling nightly backups in SQL Server 2005. What tools are available to perform this task and how reliable are they?
6
votes
4answers
3k views

Why “Begin Transaction” before “Insert Query” locks the entire table?

I wrote a stored procedure to insert a record. I added "Begin Transaction" just above the Insert Query and execute the query. I noticed that another application that was showing a web page with ...
6
votes
4answers
31k views

SQL Server Installation: Is it 32 or 64 bit?

Recently I was performing an OS upgrade on one of our DB servers, moving from Server 2003 to Server 2008. The DBMS is SQL Server 2005. While reinstalling SQL on the new Windows installation, I went to ...
6
votes
13answers
6k views

Move SQL-Server Database with zero downtime

Is it possible to move a sql server 2005 db to a different server running sql server 2008 without any downtime? The system is 24/7 and has to be moved to a different server with different storage. We ...
6
votes
2answers
5k views

How to attach mdf files from a different SQL Server Install?

Hi I have mdf and ldf files from a different SQL Server install from a machine i dont have access to now, and would like attach them to my current install to get at the data. When I try this, I get an ...
6
votes
2answers
6k views

Testing that SQL Server 2005 is listening for FreeTDS

What is the best way to test that SQL Server 2005 is listening on an IP and port? Some Background: I'm trying to connect a Unix box to SQL Server 2005 using freetds. However tsql keeps telling me ...
6
votes
1answer
69 views

Copy SQL Server Databases

I am looking to "move" databases to different servers with minimal disruption to data and service. These databases vary in size from 5GB to 140 GB. I've seen and even used some of the various data ...
6
votes
2answers
110 views

What are the basic tasks that need to be done to keep an SQL Server 2005 database healthy

I have a SQL Server 2005 database that is large in every sense. It is complex, it has a lot of data (relatively, but given the lack of blobs, it is still quite large) and it is heavily accessed. What ...
6
votes
2answers
363 views

Data from a table in 1 DB needed for filter in different DB

I have a Win Form, Data Entry, application that uses 4 seperate Data Bases. This is an occasionally connected app that uses Merge Replication (SQL 2005) to stay in Sync. This is working just fine. ...
5
votes
2answers
6k views

Clustered indexes versus non-clustered indexes?

I am in charge of a smaller DB 300+ hundred megs 100'ish tables about 45 users hit it throughout the work day. Mostly reads, but a fair number of updates and inserts. I have been slowing learning the ...
5
votes
5answers
1k views

SQL2005 Stuck in Single Mode User

Hey currently have a database stuck in single mode after setting it to single mode to attempt to try take if offline which I wish I never did.... I tried ALTER DATABASE DB SET MULTI_USER but get ...
5
votes
3answers
9k views

Is there a way to run sqlcmd to get query output without showing the number of rows affected?

I am running a simple query in SQL Server 2005 and want it to export to a file via sqlcmd. I was hoping to get the results in a csv format without the headers and the query metadata (how many rows ...
5
votes
6answers
14k views

SQL Server 2005 Service Pack 3 won’t install

I am trying to install SQL Server 2005 Service Pack 3 and it keeps failing. Comes back with the following: Microsoft SQL Server 2005 - Update 'Service Pack 3 for SQL Server Database Services 2005 ENU ...
5
votes
2answers
6k views

Using SQL Server 2008 Management studio to connect to SQL Server 2005 databases

I have a shared hosted SQL Server 2005 at an ISP, that I can connect to from SQL Server 2005 Management Studio. However, I cannot connect from SQL Server 2008 Management Studio. I log on with SQL ...
5
votes
4answers
508 views

Upgrading Sql Server 2000 to 2008

At my place of employment we have been thinking about upgrading from SQL Server 2000 to the latest (2008). We know that the DTS packages will have to be dealt with since the newer server software no ...
5
votes
3answers
79 views

Discover intended Foreign Keys from JOINS in scripts

I'm inheriting a database that has 400 tables and only 150 foreign key constraints registered. Knowing what I do about the application and looking at the table columns, it's easy to say that there ...
5
votes
3answers
3k views

How to search an entire MS SQL 2005 DB for a value?

How can I search an entire MS SQL 2005 DB for a value? For example 'ABC12345'. Thanks.
5
votes
3answers
2k views

Do I need to split a database to filegroups? It's 30gb now

I'm in the process of changing a database server at my company from Windows 2000/Sql Server 2000 to a Windows 2003 R2/Sql Server 2005 now. It holds 30 databases, each of the databases is about 7gb in ...
5
votes
2answers
6k views

How to monitor activity on a single table using SQL Server profiler

I have a table which uses and identity seed for its unique identity column. We have noticed some massive jumps in the identity value which we are not able to explain. The table has 20,000 odd rows, ...
5
votes
0answers
352 views

Database Backup and Full-Text Search

I am running SQL Server 2005 EE. When trying to perform a full database backup I recieve the error message: "Failed to pause catalog for backup. Backup was aborted.BACKUP DATABASE is terminating ...
4
votes
7answers
2k views

My database using sql server 2005 express is over 4GB, I have no money, am I screwed?

Any thoughts of how to solve this? I tried creating another database and putting some tables there, but actually there is just 1 big table with almost 3 million rows, this table is using all the ...
4
votes
5answers
7k views

How to find when the stored procedure was deleted and who deleted it?

Actually, one of the critical jobs failed while executing. In the error message it was found that the failure is because it was missing a stored procedure. Now how do I find out when was the stored ...
4
votes
2answers
223 views

Installing .NET 1.1 over later versions: Safe?

I have a server that is running all versions of .NET Framework, from 2.0 up to 4.0. An application I need to deploy won't run correctly on later versions, and was originally compiled for 1.1. Is it ...
4
votes
5answers
362 views

What is the best way to move 20+ databases to a new database server? SQL 2005

Current database server: SQL Server 2005 - Windows Server 2003 New destination database server: SQL Server 2005 - Windows Server 2003 Enterprise - VM Ware image Current database server has 20+ ...
4
votes
5answers
266 views

Justifying a memory upgrade

My employer has over a thousand servers (running SQL Server 2005 x64 and a couple of other apps) all across the country. And in my opinion they are all massively underpowered for what they need to ...
4
votes
3answers
1k views

Crossover cable in addition to normal network connection on servers?

I have 2 servers, both with Windows 2003 R2 Each have 2 NIC ports that are 10/100/1000 They are both connected to our LAN + joined to the domain (1 NIC port free on each server) The problem is that ...
4
votes
2answers
5k views

Running SQL server 2005 and 2008 on same machine?

Running SQL server 2005 and 2008 on same machine ? Is this possible/practicable ? It's only for developers and not very resource intensive - is there anyone out there who's done it ? Would be ...
4
votes
3answers
3k views

Transaction Log filling up on SQL database set to Simple

We have a database on a SQL 2005 server that is set to Simple transaction mode. The logging is set to 1 MB and is set to grow by 10% when it needs to. We keep running into an issue where the ...