This tag is specifially for the R2 version of Microsoft's SQL Server 2008.
5
votes
0answers
100 views
Msg 14027, Level 11, State 1, Procedure sp_MSrepl_addarticle [table] does not exist in the current database
Before setting up SQL Server snapshot replication, I backed up the database from ServerA and restored it to ServerB. Both servers are running SQL Server 2008 R2 SP1.
In trying to setup snapshot ...
5
votes
0answers
772 views
Deploying dacpac application to SQL Server 2008 R2
I cannot get dacpac working on my installation of MS SQL Server 2008 R2. I am using Visual Studio 2010 Shell with MS SQL Server Data Tools to build a project into a dacpac file. However, when I try to ...
4
votes
0answers
273 views
Impact of changing the DB compatibility level for a Published replicated DB from 90 to 100
I have a SQL Server 2008 R2 server with a bunch of published databases that are currently operating under compatibility level 90 (2005).
The subscription databases are also SQL Server 2008 R2, ...
4
votes
0answers
147 views
Initializing Transactional Replication From Backup
There is an option when setting up a publication for replication to Allow Initialization From Backup. We have been creating replicated databases for several years now and have always initialized from ...
3
votes
0answers
60 views
When SQL Server uses Multi Page Allocation
In SQL Server 2008 R2
I always wonder what can be consumer of MPA (multi page allocation) in SQL Server? I know data page is always 8K so is there a situation where data/index page make use of MPA? ...
3
votes
0answers
108 views
How do I know if my database partitioning is done well?
I have a sneaking suspicion that the guy who wrote the partitioning functions at my place of work did a fairly poor job. For certain queries (maybe 20% of them) we see incredible performance boosts ...
3
votes
0answers
72 views
Key Lookup and Full-text index
I have the following query:
SELECT T2.Title
FROM TitleTable T1
INNER JOIN TitleTable T2 ON T2.FKID1 = T1.FKID1
WHERE T1.FKID2 = @ID_PARAM1
AND T2.FKID2 = @ID_PARAM2
AND ...
3
votes
0answers
150 views
Why can't I read my transaction log backup file using fn_dump_dblog?
I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.
After backing up the transaction log, I attempt to read it ...
3
votes
0answers
138 views
MS DTC errors on SQL Server 2008 R2 but not SQL Server 2000 running on difference instance
I am receiving errors when I try to run a distributed query against different SQL Server 2008 R2. At first I thought this might be cause the servers have been set up on a VM using a clone but after I ...
3
votes
0answers
129 views
Why is my CPU usage increase after setting database to read-only on SQL Server 2008 R2
I administrate a reporting database.
We run all the denormalization process on a DB1 daily then we copy all the data to an another database DB2 on the same server which is queried by web applications ...
3
votes
0answers
228 views
SQL Server 2008 R2 clustered index issue
Last week, we came up with a strange issue on Clustered index. We are using SQL Server 2008 R2 in our organization.
A stored procedure is written for our application which contains a cursor in it. ...
3
votes
0answers
144 views
Login failure error log when querying Central Management Server group
I am querying a group under a Central Management Server, which results in the following being logged to the SQL Server Error Log.
Error: 18456, Severity: 14, State: 38. Login failed for user
...
3
votes
0answers
534 views
What Problems Does an Eager Spool Indicate
Currently running on SQL Server 2008 R2
I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up. My understanding of spooling ...
2
votes
0answers
39 views
The Login is from an untrusted domain - domain setup with computer level login
I have a Sql Server 2008 R2 database I'm attempting to connect to using ADO.NET using integrated security in the connection string.
The database resides on a 64 bit Windows Server 2003 machine. The ...
2
votes
0answers
35 views
Is it possible to have a valid backupset with null position?
In database restore process I need to rely on position column exists in backupset record to restore the latest backupset.
Restore the latest backup on the database
I noticed sometimes position is ...
2
votes
0answers
461 views
Error 9001, Severity: 21, State: 1 The log for database 'DB_NAME' is not available
I have a SQL Server 2008 R2.
I got a mail that the backup had failed, I opened the SQL log viewer and started browsing.
And i found these two errors a bunch of times.
error 1:
Date ...
2
votes
0answers
155 views
Simple Select * on SQL Server 2008 R2 table of 1812 rows is taking 3 minutes to load
A query on a table of 1812 rows is taking over 3 minutes to execute. Is there a reason this should be happening?
If I execute a
SELECT *
statement on any other table the results pop back ...
2
votes
0answers
68 views
Find out who/what logged in via a DAC connection
My SQL Server just died due to a DAC connection being established. I need to find out how/who or what caused this connection? Is this logged anywhere? Can anyone shine any light on this?
2
votes
0answers
66 views
Fastest way to determine size of groups of FILESTREAM objects
We have a sql FILESTREAM enabled table, and are wondering what the fastest way is to determine the size grouped by a common criteria, as well as the sum of all objects.
To date, we have been using ...
2
votes
0answers
133 views
Auditing - parameter values not being written to log
I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
2
votes
0answers
82 views
SQL Server and updating windows firewall rules
Any ideas how should I proceed if I would like to update the firewall rules (essentially add ip-s to existing firewall rule).
The step of exporting ip-s to text file and then using command line to ...
2
votes
0answers
69 views
In KB2658214, how does SQL Server know that “correlated AND predicates” are actually correlated?
While diagnosing SQL Server 2008 R2 queries with poor cardinality estimation (despite simple indexing, up-to-date statistics, etc.) and hence poor query plans, I found a perhaps-related KB article:
...
2
votes
0answers
6k views
SQL Server named instance - can connect remotely, but not locally
I have an instance of SQL Server on my machine.
Machine: Bert01
SQL Server instance: Bert2008
I connect to it using SSMS putting Bert01\Bert2008 in the Server name box, using sa and the .
When I ...
2
votes
0answers
158 views
How to get SQLCMD to output errors and warnings only?
How can you get SQLCMD, when executing a SQL script file, to just output any errors or warnings it encounters?
I essentially dont want information based messages to be output.
2
votes
0answers
630 views
Reconcile users with logins using 'sp_change_users_login'
Sql Server 2008 (and 2005):
I have a Sql Server instance, let's say, InstA, in a Domain (called AD). In that I have a domain Windows Authentication Login, called AD\Log1 that has an associated user, ...
2
votes
0answers
215 views
What is causing all of the msrepl_tran_version columns to become out-of-sync between locations in transactional replication setup?
I have been having a recurring issue with transactional replication setups at the firm I work for. We use SQL 2008 R2. Seemingly without warning or any replication errors, all of the ...
2
votes
0answers
72 views
ProcessID Owner with third party app
I am having problems trying to audit a third party app with a SQLServer backend.
The app manages users and user access via a SQL table, using a single SQL login to access the databases.
I am trying ...
2
votes
0answers
1k views
Reconnect mirroring server SQL2008 R2
The failover server has status (Mirror, Disconnected, In recovery) and principal server has status (Principal, Disconnected).
What is the process to reconnect these servers?
1
vote
0answers
33 views
Slow Response: Initially it's slow and on subsequent restart of the host it works normally
We have SQL Server 2008 R2 32 bit installed on the following hardware:
CPU: Intel Xeon E5310 @ 1.60 GHz
RAM: 4 GB
HDD: Raid
Server: HP Proliant 120
OS: Microsoft Windows Server 2003 R2, Standard ...
1
vote
0answers
61 views
connect sql server using windows authentication in another pc
I am having sql server 2008 r2 express installed in a PC. I have some other PC which are connected to this pc in a local network.Now those PC connect to my pc's sql server using sql authentication. ...
1
vote
0answers
114 views
sql server full text search - fuzzy searching?
Is there a way to configure fuzzy searches in sql server full text search. Meaning if I search for a term called POWDER, I must get matches (i.e. strings) which contain any variations of it within an ...
1
vote
0answers
105 views
Why Am I Getting Deadlock - Please Read Trace
can anyone help me?
i'm getting deadlock on this trace, i'm using symmetricds and i can't understand why i'm getting a deadlock on a certain table.
I'm using sql server 2008 r2, ...
1
vote
0answers
86 views
Collect CPU, reads and writes when replaying SQL Profiler Trace
I'm trying to replay a trace on a test environment with SQL Profiler in order to benchmark a database prior to making some performance tweaks. I intend to replay the same trace at various stages to ...
1
vote
0answers
88 views
Troubleshooting Database Mail issues and Service Account permissions in SQL Server
I am having difficulties troubleshooting a Database Mail issue. When I try to stop and start the service using sysmail_start_sp and sysmail_stop_sp, I see a critical error in the Administrative Logs ...
1
vote
0answers
119 views
SQL Cluster Resources Disappear After Removing Disk Resource
In the process of removing an usused disk resource from cluster, I would follow these steps: 1) remove dependencies on the disks by the SQL Server resource, 2) take the disk resource offline, 3) ...
1
vote
0answers
31 views
Fulltext indexes and issuers with server startup
I have a windows server for host about 4500 data bases for same application. And this application have 34 fulltext catalogs on it, and the CHANGE_TRACKING for this fulltext index are AUTO.
And the ...
1
vote
0answers
139 views
jTDS + stored procedures + prepareSQL = nesting level error?
Situation
A (Tomcat) Java web application using jTDS to connect to a MSSQL 2008 database. This Java application for 99% executes MSSQL stored procedures using user input.
Problem
The jTDS driver ...
1
vote
0answers
68 views
Distribution Agent running but subscription does not exist?
I have an issue where in my replication topology, the distribution agent is running, and says there are no commands to distribute. However, checking the undistributed commands, it shows there are a ...
1
vote
0answers
103 views
SQL Server BCP imports, but table still has 0 records
I ran the following command from the command line on SQLServer 2008 R2
BCP C4L.dbo.districts in C:\TEMP\districts.sdf -f C:\TEMP\districts_import.fmt -T'
No errors were reported and it ran as ...
1
vote
0answers
54 views
Map records to a time interval
I have two tables:
SamplesTbl contains samples every 5 seconds of a bike ride
RxTbl contains the prescribed stages and other values of a bike ride
SamplesTbl
SamplesID int
SessionNum int
StageNum ...
1
vote
0answers
208 views
Login failure when running a SSIS package from a SQL Server job
I have a SSIS package that migrates data from a Sybase database to SQL Server. The connection to the source database has been made through an ODBC connection.
If I run the package directly on my ...
1
vote
0answers
88 views
Silently corrupted table after power-related shutdowns, records missing, *scrambled*?
Have a ssvr 2008 db, about 10gb. About 50 tables, 5 or so are frequent read/write, the rest are weekly write (append only), occasional read.
The server came up and down a couple times over the past ...
1
vote
0answers
123 views
Configuring SQL Server 2008 R2 Change Data Capture with SSAS
I'm completely new to SQL Server Analysis Servers (SSAS) so you'll have to excuse my ignorance. I've been running through the MSDN SSAS tutorial get a handle on how to use SSAS.
We have SQL Server ...
1
vote
0answers
108 views
hStmt/cursor error caused by faulty data?
Could a busy hStmt and invalid cursor errors be caused by faulty data?
ODBC and the software that connects to the database is correct and working - i have ran a test with a clean database (that has ...
1
vote
0answers
271 views
SQL Server Database Mirroring : strange client application behavior upon failover
I am putting a HA SQL server environment up based on three SQL Server 2008R2 machines and database mirroring.
I'll name them :
principal.company.intra
mirror.company.intra
witness.company.intra
...
1
vote
0answers
190 views
Pros/Cons of Using Table/Index Partitioning in SQL Server
We have a number of tables that were partitioned primarily to reduce problems when purging data (via sliding-window partitioning / truncate). I have found a number of caveats that come with ...
1
vote
0answers
265 views
Why not use SQL_Latin1_General_CI_AS for a global system?
What would be some really good reasons to not use the SQL_Latin1_General_CI_AS collation on a system which handles English, German, Japanese, and Chinese data?
I'm having a really hard time finding ...
1
vote
0answers
188 views
Create stored procedure to select the data?
I have four tables user, shops, offer, merchant in that id is unique and present in all table
shop
------
id shops
1 shop1
1 shop2
1 shop3
user
-----
id name
1 abc
merchant
...
1
vote
0answers
157 views
SQL Server 2008 R2 DeadLock on query Notification
for the first time, I'm running into a deadlock on ...The deadlock graph doesn't help me much except the fact the query notification system seems to responsible.
<resource-list>
...
1
vote
0answers
45 views
Drill down report links fall outside cell
Apologies for the vague title, not sure how to summarise the problem...
I'm creating a dashboard report showing server state for a group of SQL servers, the problem is that the links for the drill ...
