Tagged Questions
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 ...
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
85 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 ...
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 ...
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
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 ...
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 ...
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 ...
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 ...
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 ...
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
504 views
IF NOT NULL then UPDATE else keep the value of the field
I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' THEN ...
0
votes
3answers
81 views
How to check if database is in mirrored mode?
I have two database servers A and B where I have databases set up in mirroring namely X. There is another database on server A named Y which is only present on database server A. Y database has a ...
1
vote
3answers
166 views
Why is SQL running the same query longer from another connection?
Here is the issue overview: Why does my stored procedure run faster when executed localy vs remotely?
Dont jump to any conclusion just yet, let me explain what I mean...
Here is the setup:
A Windows ...
0
votes
1answer
124 views
SQL update set column = @ parameter
doeas anyone knows how to update a table where column = @parameter WHERE SELECT
Basically I have a table with the following values. Table name is UserAttribute
ID|Name |Properties|
...
3
votes
1answer
62 views
Primary key with “NOT FOR REPLICATION” option
I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION".
I'm no DBA, but surely in most databases, a record without a ...
1
vote
1answer
155 views
how to add attachment(text file) to database mail?
I have scenario
Daily i run a sql job to apply a new updates to one table - this job will create one text file daily - text file contains all new updates
I can send a mail to client that job is ...
4
votes
1answer
106 views
Find transactions that are filling up the version store
we have enabled the "READ_COMMITTED_SNAPSHOT" for some of our SQL Server 2005 databases.
Now from time to time we see that our TempDB is filling up the harddisk and we suspect the version store to be ...
2
votes
2answers
80 views
Is there a way that I can export a list of all table triggers on a server?
I have two SQL Server instances that I manage. One is a SQL Server 2000 instance, and the other is 2005.
Some where, on these servers, I recall setting up a couple table triggers that were executed ...
0
votes
1answer
98 views
Restoring a differential backup in SQL server 2005
I have created a full back up of my database by executing the line below in my application:
Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & ...
0
votes
1answer
82 views
Restoring a differential backup in SQL server 2005 [duplicate]
I have created a full back up of my database by executing the line below in my application:
Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & ...
2
votes
2answers
125 views
Help with tricky update statement
I've tried writting this update statement every possible way I can think of but I either wind up producing invalid results or run into a syntax barrier.
I have two table variables:
DECLARE
...
1
vote
1answer
74 views
Consolidating indexes
I have one big table that is used to generate business intelligence cube. Currently it has around 40M rows and 55 columns. A lot of the cube dimensions are generated by running 'select distinct' on a ...
1
vote
1answer
58 views
Can I run a Report Server and a Mirror server from a working Log Shipping secondary instance?
Our setup:
City A - Primary server for logshipping
City B - Secondary server for logshipping
Now there is a lot of data being updated from A - B.
And the server in City B is underutilized, and it ...
3
votes
1answer
124 views
SQL Server 2005 Replication
I am in the process of creating Replication between 2 Remote Servers, server 1 is the Distributor and Publisher and server 2 is the Subscription.
server 1 windows 2003 server 192.168.10.1 connected ...
1
vote
0answers
46 views
SQL Server 2005 Relationship [closed]
I have SQL Server 2005. Tables have relations which each others.
Some tables are libraby. 1 table has 10 foreign relations. I dont like the Database performance, a little slowly and I dont know what ...
3
votes
0answers
34 views
SQL Server 2005 not collecting logins in the log file
I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine.
I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and ...
3
votes
2answers
110 views
Find orphaned users
In SQL Server 2005, is there a way to find users that either don't exist at the server level (an account that was deleted at server level but wasn't disassociated from databases before it was deleted) ...
8
votes
4answers
461 views
Can I launch a stored procedure and immediately return without waiting for it to finish?
We have a stored procedure that users can run manually to get some updated numbers for a report that's used constantly throughout the day.
I have a second stored procedure that should be run after ...
5
votes
3answers
197 views
Everyone warns against shrinking a database, but will it work in my case? 1 GB db with tables removed now 100 MB
Everyone is warning against shrinking a database, e.g.
SQLAuthority
But will it be a valid option in my case? The database was originally more than 1 GB,
but with a table removed from it (moved to ...
3
votes
1answer
112 views
Adding indices to views
I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table.
...
0
votes
1answer
66 views
Getting the exact edited data from a column in SQL Server
I have two Tables:
Articles(artID, artContents, artPublishDate, artCategoryID, publisherID).
ArticleUpdated(upArtID, upArtContents, upArtEditedData, upArtPublishDate, upArtCategory, ...
3
votes
1answer
76 views
Replication between SQL Server 2005 and Azure database
Is it possible to set up transitional replication between a SQL Server 2005 and a SQL Azure database?
My server is nearing end of life and I would rather migrate to a cloud infrastructure instead of ...
