This tag is for the 2005 version of Microsoft's SQL Server.
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 ...
2
votes
1answer
243 views
Solve conflict between affinity mask and I/O affinity mask?
I executed this on one of our SQL Server machines:
EXEC sp_configure 'show advanced options', 1
And then executed RECONFIGURE. This presented me with the following error message:
The affinity ...
0
votes
0answers
24 views
SQL services wont start due to incorrect drive letter [duplicate]
Possible Duplicate:
SQL services wont start due to incorrect drive letter after windows restore
SQL services wont start due to incorrect drive letter after windows restore
Had a server ...
2
votes
1answer
296 views
emergency-mode repair failed, nothing to do?
A production server got I/O disk error's I tried to fix it with dbcc but it got unexpected errors and crashed after a few minutes. so I took the file down and copied it over to a new machine.
I have ...
4
votes
1answer
4k views
SQL Server: index creation date
In SQL Server 2005 and above, how can I find when an index was created?
2
votes
2answers
117 views
Is it OK to use WITH INIT for my Transaction Log Backup to avoid having it grow too large?
I'm still struggling to understand SQL's backup jobs.
We currently have a SQL Server 2005 instance using the FULL recovery model.
We do a full database backup every week.
BACKUP DATABASE ...
4
votes
2answers
147 views
Job fails overnight intermittently because of a windows authentication failure to log on to SQL Server. How do I troubleshoot?
The failed job is a Litespeed transaction log backup, but also happens to fulls and diffs occasionally.
This same setup works on other servers without issue, and it's only a few times overnight that ...
0
votes
1answer
67 views
Get percentage free space on database volumes
I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.
However, this only gives me an absolute ...
0
votes
2answers
154 views
Is there a SQL Server equivalent to “OVERRIDING USER VALUE”
While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries.
Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to ...
1
vote
0answers
163 views
FOR XML EXPLICIT problems in SQL Server 2005 Express
SQL Server 2005 Express Service Pack 2 w/Advanced Services
Windows 2003 R2
I recently migrated a database from MSDE 2000 to MSSQL 2005 that is used for a tourist website that can be displayed in one ...
9
votes
3answers
2k views
Is it possible to restore sql-server bak and shrink the log at the same time?
We have a bak file from a client that we have transferred to our developer offices for problem investigation. The backup is currently 25GB and the restored database is about the same size however it ...
2
votes
2answers
497 views
SQL Server drop and create all users in every DB
I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far:
USE [Maintenance]
GO
IF EXISTS (SELECT * FROM ...
1
vote
1answer
86 views
SQL Server 2005 Blocking Issue
I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on ...
5
votes
2answers
265 views
How can I run multiple concurrent versions of my application against the same database?
Background: I have multiple concurrent versions of my application running in my production environment. Depending on the user account that's used, the user will have access to a different version of ...
2
votes
1answer
66 views
can you run sql server 2005 and sql server 2008 r2 in mirror mode?
Must all db servers be running the exact same version to enable mirroring without a witness server? How about with a witness server in place?
2
votes
3answers
148 views
Performance impact of Splitting Table across files
EDIT: I think the original question (below) was not worded very well, and probably caused confusion. The basic question is:
If I have a SQL Server 2005 database using a single .mdf file and I change ...
6
votes
1answer
707 views
Upgrade SQL Server 2005 to SQL Server 2012
I have a test environment set up with the following:
Virtual Machine (Hyper-V)
Windows Server 2008 R2 SP1(x64-bit)
Windows SQL Server 2005 Developer Edition SP4 (x64-bit)(default instance name)
1 ...
0
votes
1answer
442 views
How to split the comma separated list in the stored procedure
I am working on small module in which we pass the column name as comma-separated string to a stored procedure. In the stored procedure, we want to separate the column names out.
0
votes
3answers
1k views
Max connections SQL Server Express could have?
I have SQL Server 2005 Express edition installed on one of my client's machine, and lots of other users are also connected to that SQL Server through the C# application that I have provided them as an ...
0
votes
0answers
26 views
Max connections sql server express could have? [duplicate]
Possible Duplicate:
Max connections SQL Server Express could have?
I have sql server 2005 express edition installed on one of my client's machine, and lots of other users are also connected ...
5
votes
2answers
134 views
Deleting duplicate records when using text datatype
I have a large (~678,000 rows) table storing emails, and I need to delete duplicate records that match the fields of the email: to, from, subject, body, as well as the foreign key record_id.
Normally ...
7
votes
2answers
197 views
Finding delete or update records
I have had three incidents in recent months where records in a table that have either been deleted or values updated to zero across a whole table. We have a team of four people who have the ...
0
votes
0answers
45 views
Error message installing SQL Server 2005 Enterprise on my Windows 7 Enterprise
I have a problem installing SQL Server 2005 on my Windows 7 Enterprise 32 bit operating system.
The installation never completes. The error log file states I need to have IIS installed (I do and it ...
6
votes
2answers
284 views
How do I create a user for SQL Server whose queries (any/all) are time limited?
Is there any way to create a database user in SQL Server 2005 so that none of their queries are allowed to exceed a certain time limit (for e.g 20-30 seconds) or all queries are limited to a certain ...
12
votes
2answers
165 views
Does firing off an indefinite WAITFOR increase the log file size?
In the last release of my app, I added a command that tells it to wait when something arrives in the Service Broker queue:
WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)
The ...
3
votes
2answers
246 views
sp_send_dbmail fails when mailing queries, but no error returned, query just hangs
I have an issue SQL Server 2005.
sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment).
No error message is given / reported, the query just keeps executing but ...
2
votes
1answer
145 views
what is difference between last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats?
what is meaning of last_worker_time and last_elapsed_time in DMV sys.dm_exec_query_stats and what is differnce between them?
when I fire below query
SELECT TOP 20
qs.last_worker_time, ...
9
votes
3answers
3k views
What can cause a mirroring session to timeout then failover? SQL Server 2005
We have two production SQL Servers running SQL Server 2005 SP4 with cumulative update 3. Both servers run on physical machines that are identical. DELL PowerEdge R815 with 4 x 12 core CPUs and 512GB ...
1
vote
2answers
170 views
Size of TempDB Microsoft SQL Server 2005
Are there drawbacks to having a large tempdb in a SQL Server database? Assuming the disk is not full.
I am creating large temporary tables in a couple of queries. Once I am finished running queries, ...
2
votes
2answers
344 views
Get disk space percentage free with SQL Server 2005?
I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.
However, this only gives me an absolute ...
2
votes
2answers
301 views
Securely Send Database Mail via an Insert Trigger
I have spent about 30 hours so far trying to get sp_send_dbmail to work. I finally got SOMETHING to work, but I have serious doubts as to its security. I would like know a method which is better yet ...
4
votes
2answers
979 views
How can I backup a large SQL Server database without impacting performance?
We've had reports of queries running slowly or timing out early in the morning, and the only job I see running that I think could affect this is our database backup job.
The database itself is about ...
4
votes
1answer
118 views
Why does WAITFOR (RECEIVE FROM QUEUE) show up in the monitor incorrectly?
I kick off the following command in my app:
WAITFOR (RECEIVE CONVERT(int, message_body) AS Message FROM MyQueue)
When I run `sp_who2 active', I get Status=SUSPENDED and Command=DELETE
Note: ...
4
votes
1answer
333 views
How to restore replication after server crash
We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor.
Then the RAID on the ...
3
votes
2answers
113 views
Select blocking issue [closed]
The database was not responding to simple select statements like
select * from table
I noticed in sys.dm_os_waiting_tasks that one session is blocking, blocking session was pointing to a stored ...
2
votes
1answer
267 views
Displaying parent attributes alongside child attributes
SSAS - Parent Child hierarchy - displaying other Parent attributes alongside Child Attributes
For example:
Employee Table
+-----+-------+------+---------------+
| KEY | NAME | ROLE | MANAGERKEY ...
4
votes
1answer
1k views
ETL: extracting from 200 tables - SSIS data flow or custom T-SQL?
Based on my analysis, a complete dimensional model for our data warehouse will require extraction from over 200 source tables. Some of these tables will be extracted as part of an incremental load and ...
5
votes
2answers
103 views
New Cluster Testing - Best Practice
We've finished setup of a 4-node SQL Server 2005 cluster. We are using Windows 2008 R2 as the underlying OS.
We are looking for suggestions on a set of tests we could perform to test failover of the ...
1
vote
0answers
68 views
SQL server Load Balancing
SQL server user access cut down from 8pm - 9am.
During this period our SSIS scripts would run to load data(from Oracle/mainframe/Access) to sql, large database(s) & too much data - mortgage ...
6
votes
1answer
199 views
SQL Server 2000 to 2008 to 2012
We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
1
vote
1answer
101 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:
...
6
votes
2answers
191 views
Risks of changing to ARITHABORT ON
I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion ...
2
votes
1answer
140 views
Possible Pitfalls of Setting Up Kerberos to Permit Cross-SQL Database Access Via AD Trusted Connection
We are hoping to the implement Kerberos on our Active Directory (2003, Functional Level 2) so that our SQL Server 2005 databases can communicate with one another when a client is using Active ...
4
votes
3answers
399 views
How can I inspect the security settings of a SQL Server Endpoint?
I am in the process of creating a TEST environment for our SQL Server development staff.
In production we have 3 SQL Servers, SQL01 contains several databases that are mirrored to SQL02. SQL03 ...
4
votes
3answers
400 views
Logging query text without Profiler
I'm in the process of porting an old, semi-broken ASP.NET/SQL Server application to PHP/PostgreSQL. Among the torments^H^H^H^H^H^H^H^Hchallenges I'm faced with is that the reporting is done by a ...
1
vote
1answer
203 views
How can I stop SQL from validating an OPENROWSET query when its not going to be executed?
I have a problem where a stored procedure is throwing an error because it's trying to run/validate an OPENROWSET query inside it that it doesn't actually have to execute.
The stored procedure that ...
7
votes
1answer
1k views
Why does my SQL Server database triple in size after deleting millions of records?
We have two database tables ErrorLog and Audit which have both grown fairly large over the last couple of years. So in an effort to reduce the size of the database, I have to write a script to clean ...
1
vote
0answers
265 views
Can the service-broker cause SQL Server to crash when using SQLDependency
I have C# application that uses SQLDependency to get updates from a message table. I have tested it quite extensively and worked great in the development environment but when I ran it on the ...
3
votes
3answers
1k views
Unable to get DBMail working on SQL Server 2005
I have SQL Server 2005 SP3 (4053) running on a Windows Server 2003 R2 virtual machine. When trying to send a test dbmail message, either from the SSMS GUI or using sp_send_dbmail, I get the ...
2
votes
2answers
100 views
Want to know details and meaning about the trace of SQL Server
I referred to this article: http://msdn.microsoft.com/en-us/library/ms186265.aspx.
And trace my SQL Server on production machine for months.
EventId of most of the traces were: 10，12，14，17
The MSDN ...
