for SQL Server 2005, major build version 9.00.xxxx.

learn more… | top users | synonyms (2)

10
votes
4answers
2k views

Is there any benefit of a primary key that comprises all columns of the table?

I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need ...
9
votes
1answer
2k views

SQL Server TempDB behaviour in large memory environment

Reading over In-memory SQL Server log file reminded me of a question I had a short while ago. We have a SQL Server that has 512GB of RAM, the main database is (only) 450GB. We see quite a lot of ...
8
votes
1answer
238 views

Most efficient way to get stats on an entire SQL Server database

What I'm looking to do is review our databases and lock down any auto-shrink settings, as well as get a handle on which databases/tables are highly fragmented. Is there a particular script I can run ...
6
votes
2answers
578 views

TempDB Version Store cleanup

From what I can find the version store will only clean up versions that are older than the oldest active transaction. Question: Is the oldest transaction database specific or will SQL Server keep all ...
6
votes
1answer
484 views

TempDB Contention with 24 physical tempdb files on 48 core AMD Opteron box

I just received the following notification about an instance of SQL Server 2005. The instance has a core-to-tempdb-file ratio of 2:1 for a total tempdb file count of 24 files. Contention should not ...
6
votes
2answers
3k views

Recover accidentally deleted rows from a table

My client has accidentally deleted all the records of a table from a test database. This test database is in SQL Server 2005 Express and we don't have a backup. Is it possible to restore the deleted ...
6
votes
2answers
8k views

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

I am using SQL Server 2005 Express. In a scenario, I added Begin Transaction command just before an INSERT statement in a stored procedure. When I executed this stored procedure, it locked the entire ...
6
votes
6answers
2k views

SQL Server 2005 Huge DB issue

My DB files are growing very fast ( designer is not me and just a weekly report makes 7 GB growth ). And finally, I don't have enough space in the disk. And number of un-reported weeks counting up. ...
6
votes
6answers
1k views

Which is better: identity columns or generated uinique id values?

I am in middle of a debate about whether it is better to use identity columns as a primary key on a DB table or have a generated value manually created. I am arguing for the sql managed identity ...
6
votes
1answer
561 views

adding an order by to this query returns faster than without, why?

Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen? SELECT TOP (500) ...
5
votes
1answer
980 views

How can I fix “Cannot perform a differential backup … a current database backup doesn't exist”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. The problem is, the differential backups don't always seem to ...
5
votes
2answers
2k views

Data type for storing an array of flags (a bitmap/bit array)

I need to store a bit array for each record of a table, supporting the following operations: Testing if a bit is set, and setting a bit (using SQL) Querying and setting the value using ADO 2.8 (not ...
5
votes
5answers
569 views

SQL Server Backup Process Tuning

I am working for a small company that has 20 full time web sites. And another 20-30 micro sites. We are having issues with the sites freezing every day after the SQL Server Agent backup is running. ...
4
votes
3answers
822 views

How can I write a proper query to JOIN all of these tables without duplicates?

Let's say I have four tables: TABLE: players COLUMNS: id, first_name, last_name TABLE: passing_stats COLUMNS: id, year, passing_yards (several other passing columns) TABLE: rushing_stats ...
4
votes
1answer
1k views

SQL Profiler - Scripting a trace with HostName filter

I have a stock of scripted trace definitions that I use for different levels of diagnostics, none of which filter by HostName. I needed to filter traffic by host today so: Fired up Profiler Created ...
2
votes
2answers
357 views

Check Index Fragementation Query

I'm completely stumped. I'm a relatively new SQL Server DBA so I'm at a loss with the following situation; hoping maybe someone had an idea. I have a new production SQL Server (Windows Server ...
-1
votes
0answers
213 views

All users missing after a detach/reattach [closed]

After detaching and reattaching a database to do a copy operation, all of the user objects are now missing from the database (I should add that the copy failed; according to the logs, there was an ...
15
votes
5answers
3k views

Why are periodic restarts required to keep my instance performing well?

We've got a production DB server on SQL 2005. Everything runs normally for a while, but after a couple weeks we see a notable performance drop. Only restarting SQL Server brings performance back to ...
7
votes
2answers
274 views

Why does this cursor produce results in the incorrect order?

I am writing some dynamic SQL to identify and, perhaps if I'm feeling crazy enough, automatically convert my NONCLUSTERED indexes into CLUSTERED indexes. The line ORDER BY 1,2,3 DESC; in the SQL ...
7
votes
1answer
369 views

Full Plan Cache Behavior

In SQL Server 2005, what happens when the plan cache fills up? I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, ...
6
votes
3answers
5k views

Converting RTF in a text column to plain text in bulk

I have a legacy system with about 10 million rows in a table. In that table there is a column of type text, most of them are standard text but about 500 thousand rows have RTF markup in them. I need ...
6
votes
2answers
548 views

Most efficient way to return multiple aggregates in a single stored proc?

What is the best or most efficient way to get multiple aggregate result values? Basically I have an email app and wanted to get all the numbers of messages for each type of folder (inbox, sent, ...
6
votes
2answers
986 views

Displaying query plans in Server Management Studio

Another SQL server question: I have a simple query that gives me the most CPU intensive SQL since the counters were reset: select top 10 sum(qs.total_worker_time) as total_cpu_time, ...
5
votes
1answer
1k views

How do I shrink the physical Transaction Log file when it's the principal in a mirror?

We setup database mirroring over the weekend, and forgot to re-enable the job that backs up the transaction logs. When I came in this morning, the transaction log had ballooned to 58GB, and was taking ...
5
votes
4answers
9k views

SQL Server database size didn't decrease after deleting large number of rows.

I'm no good in SQL, but I've got a database to maintain. There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had ...
5
votes
3answers
2k views

Which problems arise, declaring the size of all varchar parameters as max in stored proc?

Looking at this question, it seems, that other sites too have the problem of increasing column sizes in the process of time. Increasing just the size of a column in a table is a rather simple task. ...
4
votes
1answer
128 views

Is ASYNC_NETWORK_IO wait type anything to worry about?

In looking at the list of sprocs that take long time to execute, one stands out as causing the most wait. However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the sproc transfers ...
4
votes
2answers
281 views

Can I manually reset the “internal status flag for a full database backup”?

We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day. This worked last weekend, however this weekend the differential ...
4
votes
3answers
185 views

how to copy data from SQL Server table to excel Spreadsheet thru Linked Servers

I try to copy data from a table in SQL Server 2005 into a excel spreadsheet. Since that sheet is linked to some external charts, so I have to overwrite the existing data with the updated data from SQL ...
4
votes
3answers
548 views

Why is Row Overflow not working on SQL Server 2005

I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an ...
4
votes
1answer
637 views

Need help with ASYNC_NETWORK_IO

We have a SQL Server 2005 server that is serving up 20 websites, one of which is our custom CMS, and a few other micro sites. I am new to this and just started looking at the databases here. The ...
4
votes
2answers
837 views

SQL Server 2005 : How to use pivot instead of a bunch of case statements?

Please show me how I can modify this SQL select to use pivot instead of all the case statements. SELECT t.ProjectId, COALESCE(MAX(ExecutiveChampion), 'n/a'), ...
3
votes
3answers
135 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
3
votes
2answers
586 views

How can I find databases with a large number of virtual log files?

I have several databases on a production server that are in the hundreds of gigabytes, and have many thousands of transactions running through them on a daily basis. Almost all these databases are ...
3
votes
1answer
609 views

Why do we have to use SWITCH in table partitioning?

I'm a beginner in SQL. I'd like to know what is the importance of SWITCH in table partitioning? I've created one table partitioned month-wise, with a total of 12 partitions. The ranges are: ...
3
votes
2answers
354 views

SQL Server 2005: Full-text search space requirements

Is there a way to calculate the physical drive space required for a full-text search catalog? Space is cheap, but I'd like to know what I'm getting into. I'm looking at a table of about 200 articles ...
3
votes
2answers
516 views

Sharing certificates encryped by password between DBs and instances

Question: I am trying to create a single (self signed) certificate and deploy across many DB's and instances. I create and backup the cert with: USE MASTER CREATE CERTIFICATE DavesCert ENCRYPTION ...
3
votes
1answer
2k views

moving large number of tables to different filegroups

An existing database has a large number of tables stored in the PRIMARY filegroup. I want to move theses tables and their indexes on different filegroups automatically, depending on the "prefix" of ...
3
votes
2answers
152 views

SQL 2005 Permission Precedence

Very simple question - I have two database roles (Basic, Admin). Say I explicitly deny the Basic role from deleting from table A and grant that to the Admin role. If I am a user who is in both ...
3
votes
3answers
2k views

Port number change

We are planning on setting a static port for SQL Server 2005/2008 failover clustering. Any guide on what port to choose/select for 4 nodes of each cluster?(Active/Active) I am also thinking some ...
2
votes
1answer
288 views

Best way to migrate a database involved in bidirectional replication

I have two SQL Server 2005 that are to be migrated to SQL Server 2008 R2. The original pair of servers contain a database that are involved in bidirectional transactional replication between them. In ...
2
votes
2answers
158 views

How to join multiple tables (SQL) to find the difference between an order and shipped quantities?

I have a collection of orders that were submitted to an internal ERP, which were processed and partially shipped. The table hierarchy is as follows: Order table contains OrderReference(PO), OrderID ...
2
votes
1answer
201 views

Active Directory domain being shutdown— Do I have to update my DBO accounts?

I manage a SQL Server machine (with SQL Server 2000 and 2005 installed) that was first created on an Active Directory domain that we are transitioning away from and are about to shut down. All of my ...
2
votes
3answers
269 views

Capture all queries that use hints

How can I capture or list all queries that use hints and have executed since the last instance restart? I am using SQL Server 2005 Standard Edition.
2
votes
2answers
2k views

An inconsistency was detected

Am running asp.net application.Am searching records.if i click the view button i got this error, an inconsistency was detected during an internal operation in database(database name) on ...
2
votes
1answer
3k views

Running an SSIS package without the SSIS service installed

On a SQL Server 2005 instance in our DMZ, we have BIDS installed but not the SSIS service. If I have an SSIS package on that server, can I still schedule to run it without SSIS service? I tried ...
2
votes
1answer
2k views

Location of Maintenance Plan's Back Up Database Tasks information (SQL Server 2005)

I would like to know where in the database or on the file system the information about Back Up Database Task in the Maintenance Plans Tasks. I can find the Job in msdb.dbo.sysjobs I can find the ...
1
vote
1answer
45 views

Procedure to wholly migrate SQL Server 2005 to a new server

I'm tasked with 'rebuilding' a database server, because the old one is dying. To reduce potential for the new server f**king up, we had decided to rebuild the server as similar as possible to the old ...
1
vote
2answers
69 views

Linked Server Has Run Out Of Memory

So I have a stored procedure that calls an AS400 Linked server. Now, the stored procedure will execute successfully. However, it is used in a SQL Agent Job and each time the job runs, it gives the ...
1
vote
1answer
300 views

Defrag or Backup+Restore

I have an MDF file that was autogrown from 1MB to 40GB in 10% increment. The file is heavily fragmented now, and I want to defrag it with minimum downtime. I have a plan that I am not sure will work: ...