This tag is for the 2005 version of Microsoft's SQL Server.
3
votes
2answers
44 views
Run two SQL Agent Jobs Simultaneously (not sequentially) as the next step
At the completion of Step 1, the job currently goes to step 2 - an osql command to start a Sql Agent job on another server. However at the completion of step 1, I would also like to start a ...
2
votes
2answers
79 views
SQL Server Express - How to check if I am hitting the size limit?
I am confused, AFAIK Sql Server 2005 Express has a limit of 4GB database data size.
How can I check if my DB is hitting the size limit?
Is unallocated space the space left untill I hit the limit?
...
0
votes
1answer
27 views
File locations for Full Text Catalogs using SQL Server 2005 on VMware
I have an issue with one of our development SQL Server Servers. We have databases that use full text search catalogs. Just some background info, our servers are configured to use separate drives for ...
0
votes
3answers
75 views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
6
votes
2answers
181 views
If I fail over one database, do the others that share the same mirror endpoint fail over as well?
We have two databases setup for mirroring on a single SQL Server instance: a test database and a production database. Both get mirrored to another server using the exact same endpoints.
If I go into ...
0
votes
0answers
73 views
DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS'
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database. One of these scripts does a deletion on a user table called msw_timer_task, based on a ...
3
votes
2answers
59 views
How can I search the full text of a stored procedure for a value?
I use the following script to search the text of all stored procedures when I want to find specific values.
SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION ...
1
vote
2answers
62 views
How to backup databases to ensure data recovery
I have 15 customer databases that have minimum replication at different sites. I currently do nightly back-ups and transfer the database back-up nightly to our main server via 7zip. We recently had ...
2
votes
3answers
91 views
How can I query data from a linked server, and pass it parameters to filter by?
I have a really big query that needs to be run on multiple databases, and the results appended to a temp table and returned.
The basic syntax looks something like this:
INSERT INTO #tmpTable (Id, ...
2
votes
2answers
86 views
Restoring differential backup from SQL Server 2005 to SQL Server 2012
We are migrating a client to a new server. Their old server has SQL Server 2005. I have attempted to perform a differential backup however I received an error about the LSN chain being broken.
Please ...
2
votes
2answers
42 views
Is there a better way than the ones listed within to join data in an excel spreadsheet to a table in a SQL Server database?
Let's say I'm given a list of names and email addresses in an excel spreadsheet. I am asked to find all customers in the database who's name, and email are in that list.
I currently know of two ways ...
2
votes
0answers
29 views
How can service pack rollback file end up on non-system volume?
I'm attempting to upgrade a SQL 2005 SP3 clustered instance to SP4. During installation, a rollback event took place with the error indicating there was insufficient space on the the disk. The system ...
1
vote
0answers
39 views
When converting a table valued function to inline, why do I get a lazy spool?
I have a table valued function that I want to convert to inline to improve performance.
It sets the value of variables to the value four bit columns in one row of a table (looking up the row using a ...
0
votes
2answers
53 views
Can I copy the table structure of a #temp table to a new physical table?
I have a #tempTable that was created using
SELECT *
INTO #tempTable
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 'Excel 8.0;Database=MyFileName.xls',
'SELECT * FROM [Sheet1$]')
Is there an easy way ...
0
votes
1answer
42 views
SQL Server database files on different disk as is OS installed
My very short overview
SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server ...
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 ...
1
vote
1answer
43 views
IIS takes longer to run queries than SMSS
I'm completely stumped on this one.
We're running a pretty usual intranet app here, classic ASP frontend and SQL Server 2005 backend.
Occasionally, once/twice a week, a certain stored proc takes ~50x ...
1
vote
1answer
44 views
Limiting user access to tables based on a ROLE
I have a SQL Server 2005 database to which I would like to LIMIT access. I have done the following:
Created a database role called NO_HR
Added the tables to this role under Securables that should be ...
-1
votes
2answers
69 views
SQL Server Transaction logs
Could someone tell me the role of transaction logs in SQL Server ?
I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes ...
2
votes
1answer
83 views
Migrating from SQL Server 2005 to SQL Server 2008 R2 on a different box
We currently have a SQL Server 2005 instance hosting a lot of application specific databases (ArchiveManager, SolarWinds, Kaspersky etc). We are planning to deploy a new SQL Server 2008 R2 instance on ...
2
votes
3answers
62 views
How to take SQL Server database under this scenario?
I have a database of size 290 GB running on SQL Server 2005 (compression is not available) and disk space is z: drive 250 GB and Y: drive is 80 GB. How can I take a full backup in this scenario? How ...
2
votes
1answer
57 views
Validate Primary Key and Index Selection
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
0
votes
1answer
34 views
Does WITH SCHEMABINDING on a multi-statement TVF improve cardinality estimates?
Based on http://blogs.msdn.com/b/psssql/archive/2010/10/28/query-performance-and-multi-statement-table-valued-functions.aspx and other articles, SQL Server assumes that a multi-line table valued ...
3
votes
3answers
52 views
Which system databases should I checkdb and defrag indices?
I have SQL Server Agent (2005) jobs that periodically perform CHECKDB and a defrag (ALTER INDEX REORGANIZE/REBUILD) of any index that is highly fragmented. These are typical maintenance best ...
3
votes
2answers
205 views
Lock escalation problem on a trigger
I've inherited a SQL Server 2005 database that is getting 2-3 deadlocks a day.
I've tracked it down to a scheduled job that runs during the day and inserts into a table with a trigger.
The trigger ...
0
votes
0answers
40 views
sql server 2005 profiler
We have a overnight agent job and one of the steps runs particular slow recently. The step calls a stored procedure. I used sql server profiler to capture the trace for that proc into a table. I ...
0
votes
1answer
52 views
Understanding Deadlock graph on one table
I do not understand this deadlock graph that we are experiencing on this table (I have had to redact the exact column names) I have not had much exposure to deadlocks outside of reading material and ...
4
votes
2answers
103 views
Objects in Tempdb that are not associated with a session
I have a SQL Server 2005 instance where my tempdb i apparently holding on to objects that are not in use anymore. The database has 24gb total and is apparently unable to go below 8gb in use. For ...
0
votes
1answer
24 views
sql server 2005 deadlock error 3930
I am trying to simulate a solution for a deadlock issue happened on our production server.
Run this to create a table to be used in 2 and 3.
IF (SELECT OBJECT_ID('dbo.MyProduct') ) IS NOT NULL
...
6
votes
7answers
178 views
Main Considerations When Moving From MS Access Programming to SQL Server
First post, be gentle...
I am a 100% self taught MS Access programmer (main part of my job is programming), I am now building larger databases; still using MS Access as the UI but SQL Server to store ...
2
votes
1answer
66 views
Restoring a database back to a point in time before full backup was made
A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set ...
1
vote
1answer
90 views
SQL Server taking a lot of memory
I have SQL Server Express on my VPS(2GB RAM) with a database size of 1.5 GB that I expect will increase to 15 GB. I have noticed that SQL Server is using 1.5 GB RAM even when only small queries has ...
0
votes
0answers
43 views
Invalid Object SQL Server 2005 Express
I have 2 SQL Servers, let's call them A and B. I have a ColdFusion app that runs on server A. It connects to my database, MyDb. I needed to update the db content so I backed up the same db on server B ...
1
vote
1answer
78 views
SQL Server 2005 error while starting service
I am getting error while starting the service & errorlog is:
2013-04-04 14:50:49.14 Server Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) ...
5
votes
1answer
104 views
Unexpected Table Scan with Parameterized LIKE
I'm experiencing an unexpected table scan on SQL Server 2005 against a heap table when parameterizing a LIKE statement... but when the same value as the variable is hard-coded, the expected Index Seek ...
2
votes
3answers
106 views
Backups script completes, but doesn't back up all dbs
I'm running sql server 2005 and I've made a simple backup script that backs up all the databases on one server. The script always succeeds, but when I go to check the log or the files, I see it has ...
4
votes
1answer
79 views
Process attempted to unlock a resource it does not own
SQL Server 2005 SP4 32-Bit
I have a DBCC CHECKDB job running nightly. Last night, soon after the job started, I got the errors below. The database is NOT in suspect mode, and CHECKDB comes back clean ...
1
vote
0answers
60 views
SQL Server 2005 Unexpected Disk Usage during Backups [closed]
On our SQL Server 2005 SP4 server we have a maintenance plan in place which performs a full backup of selected databases on a nightly basis. The databases are configured in Simple recovery mode and ...
0
votes
2answers
32 views
Can you create an unrecoverable SQL Agent back log?
I have created and am using an SQL Agent for the first time. I have searched google but either the info isnot there or I am not getting the keywords correct (SQL agent + back log/callstack overflow ...
0
votes
2answers
74 views
Missing data after attaching .mdf file
Recently, one of my customers had a server crash. Unfortunately, the 3th party who was responsible for the backup was 'forgotten' to schedule a backup of the DB (I know, I should have notice that).
...
0
votes
2answers
75 views
Long time, sporadic 'String or Binary data would be truncated.' Wisdom would be appreciated
TL;DR
How can I diagnose the source of a very inconsistent, un-reproducable 'String or Binary data would be truncated.' error, when I am quite positive the problem isn't related to user data being to ...
2
votes
1answer
41 views
FOR XML is generating a empty first line
I'm parsing with flash a XML file generated by this code:
:XML ON
USE MyDatabaseName
GO
SET NOCOUNT ON
SELECT * FROM ProgramacionDia as programa order by hora
FOR XML AUTO,
ROOT ('toflash'),
...
3
votes
2answers
111 views
Identifying which values do NOT match a table row
I would like to be able to easily check which unique identifiers do not exist in a table, of those supplied in a query.
To better explain, here's what I would do now, to check which IDs of the list ...
5
votes
5answers
183 views
Run Multiple Remote Jobs
I need to manually run a job on more than 150 sql server instances (sql server 2000, remote) from a sql server 2005 instance (the local server). The job is the same on all these instances. The job ...
3
votes
1answer
137 views
Reindexing and its effects on SQL cache
Currently looking into reindexing our sql database, and cannot find any information on whether or not the procedure cache would be effected. Any help or information would be great. We are using SQL ...
2
votes
1answer
90 views
ISNULL on NTEXT causes fatal exception on SQL Server
I have a weird error that occurs on a client's SQL Server 2005 box. We have provided them with a dataview that is populated with a query with a CTE with several joins / subqueries that does some text ...
4
votes
2answers
106 views
Can't select database for transaction log backup in maintenance plan
I am running into a problem while trying to save a transaction log database backup maintenance task:
Add the 'Back Up Database Task' to the maintenance plan.
Edit the task.
Select backup type: ...
5
votes
3answers
167 views
What type of SQL Server Replication fits our scenario? Merge or Transactional?
Background
In our current setup, we have a single SQL Server 2005 instance on our remotely hosted web server. We additionally have another (non-MSSQL) database which we use for our POS system, which ...
1
vote
1answer
54 views
SQL Server 2005 encryption - What Grants should I give
I use Mixed mode Authentication and I want my database users (in the Public role membership) to have the ability to JUST execute procedures that encrypt/decrypt data (using Certificate and Symmteric ...
2
votes
1answer
89 views
How to restore a filegroup from its backup in SQL Server
I need guidance on how to restore a file group in Database from the File group backup taken from another same DB server. Is it possible to restore the filegroup by running restore DB for File Group.
...
