This tag is for the 2005 version of Microsoft's SQL Server.
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 ...
3
votes
3answers
302 views
Need to add a Table Level Constraint to prevent bad data - Constraint is not working
I have a table that has a flag that can be set to 0 or 1. I need to make sure for every claim record in that table that the flag is only set 1 once. So in other words I may have multiple records for ...
3
votes
1answer
89 views
How can I find out which statistics are evaluated by the optimizer?
My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. ...
6
votes
1answer
165 views
Large SQL server advice
Copied my question from StackOverflow after advice from a fellow user:
I'm dealing with a quite large SQL Server database and encountering a few problems.
The database is quite new and I've been ...
5
votes
4answers
93 views
Syncing Database for Server Transition
I'm transitioning from an old web/sql box combined to a much more robust solution on a completely different host (800hosting to RackSpace).
Our application has very high uptime requirements. It runs ...
8
votes
2answers
311 views
Strategy to handle an SQL Server DB with too many files (BLOBs) in it?
Scenario:
SQL Server 2005 database servicing an ASP.NET application (on a separate web servers).
Database:
DB has about 5GB of "normal" data in it, and about 15GB of "files" (e.g.: a 200k PDF stored ...
7
votes
3answers
713 views
Table partitioning for archiving data
Scenario:
two databases: DB_A and DB_Archive with one very big table called
tableA.
every day, records older than 60 days are deleted from DB_A and moved
to DB_Archive mainly to leave thing ...
2
votes
2answers
308 views
What could trigger that a login created from a certificate is not visible in metadata
I am trying to create a login and grant it rights to run and unsafe assembly but the check on server_principals does not return the login when it already exists. The script works perfectly fine on my ...
4
votes
3answers
794 views
SQL Server 2005/2008 system timezone
I work on a ship that has multiple SQL Servers, both 2005/2008 versions.
As the ship moves from area to area so does our timezone. Sometimes we can jump ahead one hour and then back again.
My ...
2
votes
1answer
336 views
SQL Server 2005 delete corrupt table
We are having a database in which one table name dbo.ONOA is corrupt. We need to delete this table. Please let me is there any way to force delete the table?
12
votes
2answers
642 views
SQL Server how to get around the transaction log filling up when updating a column to an int
I have a SQL Server 2005 table called BRITTNEY_SPEARS_MARRIAGES and it has the following columns:
MarrigeId tinyint,
HusbandName varchar(500),
MarrigeLength int
Now I have another table ...
4
votes
3answers
121 views
Schemas and user rights
I have 2 schemas, lets say sch1 and sch2. Each owned by different user.
I do want to grant SELECT rights on sch2.MyTable (but just this one object) to sch1 procedures/views AND allow to reference ...
4
votes
1answer
278 views
Index defrag takes forever to run
We have an Agent job that runs every morning at 2a.m. to defrag the top 5 biggest tables. Usually it takes around 20 mins to complete. It has stayed this way for several years. However, recently (last ...
1
vote
1answer
195 views
Is it possible to do an in-place upgrade of SQL Server 2005 Standard to SQL 2012 Enterprise?
At the moment, I have a SQL 2005 Standard box. I'd like to upgrade it to SQL 2012 Enterprise in-place. When I say in place, I mean an install similar to Office 2010, e.g. it removes the older (Office ...
4
votes
2answers
333 views
SQL Server parallelism for DML operations
I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements mostly likely in a data warehouse environments when query cost exceeds cost threshold ...
6
votes
1answer
9k views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
3
votes
1answer
183 views
Check Index Fragmentation on SQL Server Audit Queues
I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a ...
1
vote
1answer
309 views
How to avoid duplicating information in a series of nested SELECT statements?
I'm working on an SQL query that puts together customer history based on past purchases (the industry is venue ticketing). The report will be LARGE, something like 80-90 columns, with one column per ...
4
votes
3answers
83 views
SQL Server Table Move Stalled
I'm running a data migration. The process is moving data from a query on a SQL Server 2005 database to a SQL Server 2008 R2 database single table destination.
The source query is a join between 2 ...
-1
votes
2answers
1k views
This version of Microsoft SQL Server Management Studio Express can only be used to connect to SQL Server 2000 and SQL Server 2005 servers
I have already SQL Server Management Studio 2008R2 on my local machine.
I just installed SQL Server Management Studio 2005 and wanted to connect for the first time.
After going through the following ...
0
votes
2answers
119 views
How to move all non-unique and non-clustered indexes to a different filegroup?
I want to move all indexes to a separate hard drive. To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup).
Now I want to move ...
1
vote
1answer
140 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 ...
3
votes
1answer
755 views
Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different
Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size.
Select
...
3
votes
2answers
818 views
How badly do SQL Compilations impact the performance of SQL Server?
I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so.
I whipped out SQL Profiler ...
2
votes
3answers
210 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.
1
vote
1answer
88 views
Create a Table only Schema
We use JPA to create the objects that map to our database tables, however whenever it starts looking at our views the entire entity generation process grinds to a halt.
Is there a way in SQL Server ...
4
votes
2answers
939 views
SQL Server 2005 SP3 Memory Errors when plenty of memory is seemingly available
I have a SQL 2005 SP3 box installed on Windows 2003 R2 SP2. The box has 64GB of Memory with AWE enabled (and /PAE in the boot.ini) as well as lock pages in memory enabled and Max Server Memory set to ...
1
vote
2answers
122 views
Unable to connect SQL Server 2008 from SQL Server 2005 DB
I have SQL Server 2005 on my work station and on my Server, we have SQL Server 2008. When I tried to connect to SQL Server 2008 from my SQL Server 2005 edition, I am receiving the following error:
...
4
votes
1answer
409 views
Is there a way to setup a user to run SPs, but not access the underlying data tables?
We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not ...
2
votes
1answer
625 views
UPDATE Statistics with full scan
Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior.
a) takes any locks during the entire ...
2
votes
0answers
87 views
Lock timeout on user tables traced back to system processes
I have a problem where users are reporting intermittent errors in an application that is using a SQL Server 2005 database. Nothing jumped out at me at first glance so I took a look at ...
1
vote
1answer
556 views
SQL Server Log shipping standby db stuck restoring
I have 2 servers on SQL Server 2005 SP4. When the secondary server catches up with the log files it is switches into standby mode, but after a while it seems to be 'stuck' in restoring.
There were ...
2
votes
1answer
278 views
SQL Server 2005 - Map login to pre-existing user
The short of it is that we have several databases that are synchronized between two failover instances. The primary contains logins that are mapped to the users in the databases, and everything's ...
1
vote
1answer
120 views
Sql Server Measuring Query Memory Consumption
I am optimizing a stored procedure in sql server 2005, and while I know of several ways to measure timing differences, I am not quite sure of a good way to measure memory consumption differences.
How ...
1
vote
1answer
66 views
SQL 2005 Replication Create/Delete Tables, Views, and Procs
I have replication setup on a couple SQL2005 servers.
According to http://msdn.microsoft.com/en-us/library/ms151870(v=sql.105).aspx replication works when changing an item.
How do I have ...
5
votes
1answer
291 views
SQL Server 2008 Multi Page Allocation
When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer ...
2
votes
1answer
1k views
Create a view with dynamic column headers based on original table row data? [closed]
Given the two sample tables here:
Tickets Table
ID User Description
------------------------------------
0 James This is a support ticket
1 Fred This is a ticket too
Properties ...
3
votes
1answer
74 views
How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008
We have recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This upgrade included some publications, where all tables are published with a default conflict resolver based on the ...
3
votes
2answers
144 views
SQL database backups methods-tools-procedures
Currently, I am investigating some way to more efficiently backup 4 Production SQL Databases (3 different servers/instances) and would like to know if anyone else has had the problems we are having ...
1
vote
1answer
39 views
Matching a smaller piece of text in a local variable to a larger piece of text that I have pulled in a query
I think this is a simple question, but google searching for 30 minutes was mostly wasted time as all I can find is matching a variable to a 'randompieceoftext'.
Anyways, suppose I have a local ...
1
vote
1answer
126 views
SQL 2005 Replication - How do I get the DA Profile MaxBCP option to work with a Transactional Publication?
I have two publications on a database. One is a transactional publication, the other is a snapshot publication. We have a stand-alone Distributor (separate from the publisher) and both publications ...
5
votes
1answer
265 views
Why is the page life expectancy 0 (zero) on SQL Server 2005
We have been wanting to know the page life expectancy for our SQL Server. So we look at the counter using Perform. The value is ZERO and never changes. I thought there must be some bug, so I checked ...
2
votes
2answers
311 views
Query intermittently fails with “The multi-part identifier could not be bound”
I've got Schrodinger's query here; you can run it once successfully, then 5 times in a row, and it works fine. Change anything in the query, even whitespace, run it again, and it might stop working ...
8
votes
3answers
370 views
Fix for orphan user 'guest'?
What can be done, if anything, when the special guest user is orphaned (not linked to any login)?
For one of my databases (SQL Server 2005), executing the following lists the guest user as an ...
5
votes
1answer
283 views
Connecting to SQL Server using cached credentials
While migrating from SQL authentication (user-password) to integrated security I ran into an unexpected combination of requirements.
SQL clients are services running under a LOCALSYSTEM or ...
1
vote
1answer
63 views
how to config SSRS 2005 email setting with google apps
My Company recently switches email service to google apps (asp.google.com). all the current subscriptions in SSRS2005 are configured to MS exchange server and no longer workable. Do someone knows how ...
3
votes
1answer
78 views
Is it imperative that I backup my index files?
I have a few different instances (SQL 2000, 2005) with indexes placed in a separate filegroup. Do i need to ensure that this group is backed up, or will it be rebuilt by a restore of of the database?
7
votes
1answer
227 views
Schema qualification necessary for query plan reuse?
While reading this article about Plan Caching in SQL server, I came across a tidbit I was unaware of:
... for reuse it is necessary that the objects that the batch references do not require name ...
2
votes
1answer
447 views
SQL 2012 Upgrade Advisor cannot detect SQL Server
I have an instance of SQL 2005 on a Windows 2003 server that I want to analyze using Upgrade Advisor. I can access SQL Server remotely via SSMS, so I assume necessary ports are open & user ...
1
vote
0answers
111 views
Database model problem from MS Access to SQL Server 2005
Good Day,
I am a C# programmer but I have a problem with a database model on SQL Server 2005. I have a client who told me to rebuild his database from Access to SQL Server 2005 and actually I did but ...
