This tag is for the 2005 version of Microsoft's SQL Server.
7
votes
1answer
323 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
2answers
486 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
666 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
93 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
2answers
952 views
Is it possible to backup and restore part of a database in sql-server?
We have a sql-server 2005 database that we regularly transfer from our client site to ours. This takes a long time because we don't have a direct connection and have to transfer the file over their ...
5
votes
3answers
1k 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
3answers
161 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
397 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 ...
3
votes
2answers
135 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 ...
3
votes
1answer
354 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
248 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
2answers
205 views
Maintaining referential integrity in a booking system
I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.
I have 3 tables (simplified for this question).
Table 1- CourseSize table- this determines the ...
3
votes
2answers
126 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
1answer
491 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 ...
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 ...
3
votes
2answers
703 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'),
...
2
votes
1answer
173 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
209 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
1answer
1k 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 ...
2
votes
1answer
551 views
Are resource_semaphore_query_compile waits causing 100% CPU on server?
Of late, the cpu usage on the db server is consistently at 100%. Even at 2 in the night when there is almost no traffic on the website and the load is negligible.
While trying to find the root ...
2
votes
2answers
932 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
1k 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
92 views
Follow up to certificate sharing question
Follow up to the post Sharing certificates encryped by password between DBs and instances
I created a certificate with a password
CREATE CERTIFICATE testcert
ENCRYPTION BY PASSWORD = 'test123'
...
1
vote
1answer
139 views
Hard Drive Configuration for my setup
I have a fairly large transactional database (100GB) with a lot of users. The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun ...
1
vote
2answers
442 views
Restore System Databases from MDF/LOG files
We recently experienced a hardware failure that caused our SQL 2005 server to take it in the dump. The good news is that we have the MDF and LOG files for all the databases, the bad news is that we ...
1
vote
1answer
221 views
Health checking via Database Mail in SQL Server
I have a few Sql Scripts that I would like to run every day on my Sql Server(s). How can I query multiple times and have the output similar to the following:
Health check for <server>
Query ...
0
votes
1answer
77 views
How can I install a provider for linked server?
I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database.
I would like to use this same ...
