This tag is specifically for the 2008 version of Microsoft SQL Server.
1
vote
1answer
105 views
Error on creating database in SSMS
I am using SQL Server Management Studio 2008. I am connect to localhost and I want to create a database. When I click on New Database, type the name and click ok, I get the message:
Index was ...
4
votes
1answer
107 views
In Change Data Capture (CDC), why lsn is used instead of timestamp?
I am learning to use CDC in SQL server 2008, I have read that LSN is always incremented and is based upon timestamp, so why LSN is required, if it is calculated from timestamp which can be used as ...
1
vote
1answer
85 views
SQL Server 2008 search joining query
I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.
for example,
M_MessageMessageId=========
...
2
votes
2answers
70 views
Can the OUTPUT clause create a table?
I'm doing an update like this:
UPDATE dbo.Table1
SET BirthDate = b.BirthDate
FROM Table1 a
JOIN Table2 b
ON a.ID = b.ID
And I want to use the OUTPUT clause to back up my changes.
UPDATE ...
1
vote
1answer
67 views
SQL Server replication conflicts after migration from 2000 to 2008
I got a suggestion over at Stackoverflow to post here....greatful for any and all help.
Please bear with me I think this might take a while to explain. For many years now my company has hosted a ...
7
votes
3answers
229 views
SQL Server Sandbox
I'm attempting to set up a sandbox for our report developers to their work in. My current plan is to "reset" the database every evening but I'm not sure how to go about doing so. What I mean by reset ...
2
votes
6answers
377 views
SQL Server split mdf into multiple files
I have a database called example.mdf with a total size of 1GB which suffers from performance issues. I checked the allocated hardware and it is higher than required, I double checked the design and ...
5
votes
1answer
68 views
RESOURCE_SEMAPHORE_QUERY_COMPILER waits in Microsoft SQL Server 2008 Enterprise
Could fragmented indexes on Microsoft SQL Server 2008 Enterprise cause RESOURCE_SEMAPHORE_QUERY_COMPILER waits?
The last two weeks one of our applications has had downtime due to ...
0
votes
2answers
136 views
Resume Logshipping after Primary database restored
We have Database (D1) as a primary database and Log shipping setup to the secondary database called (LS_D1). Now what we are doing is taking backup of D1 database and restore it on D2 database. We do ...
0
votes
1answer
95 views
sql server-percentage calculation
Sample data :
LINE_NO E_FIELD F_FIELD G_FIELD HSA_STATUS FAMILY CACHE_FAMILY Count
23053B 00000 00000 00000 S SUMMIT WER 43
23053B 00000 00000 00000 T ...
0
votes
2answers
47 views
When Creating a Foreign Key Relationship
When creating a primary/foreign key relationship there are the following options:
Check Existing Data On Creation [Yes/No]
Enforce for Replication [Yes/No]
Enforce Foreign Key Constraint [Yes/No]
...
2
votes
1answer
257 views
Communication link failure for some queries to linked server
I am seeing the following error in SSMS (server has Windows Server 2008 and Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 installed) when running some queries against the linked server, in ...
1
vote
6answers
66 views
Performance about this simplified where-condition query
I have a query in SQL Server 2008 like this:
SELECT
a.type,
a.name,
a.startDate,
a.endDate,
b.key AS key,
b.is_locked AS is_locked,
...
0
votes
1answer
106 views
SQL Server 2008 SP2 randomly slow down query
I am using SQL Server 2008 SP2 and I'm facing an issue with a particular query.
It is taking 4 sec to 4 min in execution. Please suggest the possibilities.
There is no scope of query optimization. ...
1
vote
3answers
136 views
When to periodically RESTART SQL SERVER 2008 R2? [closed]
So I've read that it's really a bad practice to restart SQL Server on a production environment..
Does restarting SQL Server speed it up?
Automatically restart SQL Server 2008
Why are periodic ...
1
vote
1answer
50 views
Appropriate Action for Dead Locks / Blocking
I've setup my databases so that anytime a procedure is blocked for more than 45 seconds the database notifies the DBA email. Is it bad practice to setup a way to auto kill the process that is doing ...
0
votes
2answers
230 views
SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs
After doing some research I cannot seem to find an answer to this question.
Background
I am attempting to setup a backup plan that fits the following three requirements:
Reliability of backups, ...
0
votes
2answers
93 views
SQL Server 2008 Restore
I'm trying to restore from a database backup to SQL Server 2008. The backup came from a different server. I'm executing the following query:
RESTORE DATABASE Database_Name
FROM DISK = ...
2
votes
1answer
144 views
sysadmin cannot drop login when ddl audit trigger enabled
I have a DDL trigger defined for database- and server-level events in SQL Server 2008R2 (Standard) which exists to log all DDL events into an audit table in an admin database. It's only function is ...
3
votes
3answers
179 views
Investigating high SQL waits, especically LCK_M_U
Recently I have been struggling with SQL server performance and although we have fixed a huge multitude of basic errors in the config it is still performing badly. To clarify it is not overall ...
3
votes
2answers
82 views
Update strings for all cmdexec Jobs
Does anyone know if it's possible, (and furthermore advisable) to update all cmdexec strings in jobs on a server?
We'd like to change our logging location, and in principle I'd think this could be ...
4
votes
5answers
72 views
SQL Server in a state of confusion: does the database exist or doesn't it?
Got a really weird, annoying problem.. Somehow the instance of SQL Server 2008 R2 running on our server has gotten somewhat corrupted.
First, we noticed that the database we created yesterday was ...
2
votes
2answers
98 views
What does wait_resource LOG_MANAGER mean in SQL Server 2008?
Let's say we have two Stored Procedures A & B within the same database.
Stored Procedure A:
Executes for a long time
Has transactions within
Makes Calls over Linked Server
Stored Procedure ...
3
votes
2answers
86 views
SQL Server Schemas ownership
I'm currently learning SQL Server 2008 and have come across schemas and there is one thing I don't understand about them that they have to have an owner; why can't they own themselves?
My background ...
1
vote
1answer
127 views
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server
I'm trying to establish a linked server from SQL Server 2008 R2 to an Access database. The Access database is not password protected. As I have seen in other posts (specifically this one), this is ...
4
votes
2answers
186 views
What are the differences between leaf and non-leaf pages?
I've been running some index usage reports, and I'm trying to get a definition of Leaf and Non-leaf. There seem to be both Leaf and Non-leaf Inserts, Updates, Deletes, Page Merges, and Page ...
0
votes
1answer
140 views
Getting timeouts and backup errors after mirroring and log shipping is setup
OS is Windows Server 2003 32 bit. SQL Server is SQL Server 2008 SP3 32 bit
Today I finished configuring database mirroring and log shipping for 3 databases on this SQL instance and started receiving ...
3
votes
1answer
256 views
SQL Server 2008 R2 replication high delivery latency
I am seeing an unusually high delivery latency between our distributor and subscribers and i do not understand why.
We have in this configuration 3 sql servers using transactional push replication to ...
5
votes
1answer
606 views
High Buffer I/O SQL Server 2008 R2 causes?
Am trying to troubleshoot performance on an SQL server that we have for our document management server and today performance has hit an all time low. Looking at the MDW I setup recently I can see a ...
1
vote
1answer
562 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 ...
6
votes
3answers
237 views
Right Join vs Left Join - My joins are broken and I don't understand why
http://sqlfiddle.com/#!3/ef71e/1
I rather drastically cut down the fiddle, but I think the intent shines through. [Def] is a [DefaultClassification] table, and [Cls] is a [Classification] table ...
0
votes
0answers
15 views
Can't choose which date sql server 2005 backup to read in sql server 2008.
I am trying to restore a database from sql server 2005 to sql server 2008. In Management studo 2008, I am pointing to a hard drive copy of the backup file for the database, and am getting only one ...
3
votes
1answer
53 views
Adding column from SSMS wizard in SQL Server
Today, I am reading through Pinal Dave's blog about adding column in SQL SERVER....One thing make me surprised that while adding column from SSMS wizard, it goes from following process:
Create a New ...
4
votes
1answer
50 views
Under what circumstances can a division expression reduce the number of rows in a query's result?
I'm experiencing a problem with a query that I've written.
Given a table with the schema:
create table somevals(division varchar(100),
subdivision varchar(100),
...
1
vote
1answer
336 views
Command line export and import CSV in SQL Server 2008 R2
I have a database xyz and 100+ tables and has to perform the following operations on the command line:
I need to create CSV of all tables in one go.
CSV with column separator ,, fields enclosed by " ...
3
votes
2answers
69 views
SQL Server 2005 Database move and rationalisation - Recommended maintenance processes
We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.
I inherited this database and it came to ...
4
votes
1answer
425 views
SQL Server 2008 query planner failing after index drop & recreate
Recently we ran a script to our production DB that would dynamically drop and recreate hundreds of indexes as filtered indexes. While this script had run perfectly in all other previous tests, now ...
8
votes
1answer
159 views
Strategies for organising SQL Server with large amount of RAM
We now have a new server for our DB and amongst other things we have 128GB of RAM available (previously I had 16GB) . I know SQL Server is very good at managing it's resources, but I was wondering if ...
4
votes
2answers
190 views
SQL Server 2008 can't repair consistency
I have a problem with a SQL Server 2008 database.
Launching
DBCC CHECKDB
I get this error:
SQL Server detected a logical consistency-based I/O error: incorrect
checksum (expected: ...
0
votes
1answer
128 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|
...
0
votes
1answer
63 views
SQL Server weird issue with indexes and query planner
We had a bunch of crashes and restorations a few days ago and following those, the SQL Server DB has been acting strangely. We know that there were some issues with the failover clusters due to which ...
1
vote
1answer
77 views
Query to determine a count by rolling interval
I'm looking at orders and trying to simulate some messageing that we are doing in the query so that Marketing can get a base line for how many responses they might expect if we changed the interval of ...
3
votes
1answer
63 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 ...
2
votes
0answers
51 views
How can we achive the replica of databases for SQL Server?
I have been working in VB6 to VB.NET migration project.In VB6 project they have individual databases (access db .mdb) for every user now we made that to centralized database using SQL Server.
In the ...
1
vote
1answer
73 views
table design to track significant events associated with an item
An Orders table I'm dealing with at present in design phase has about 10 or more columns.
Each OrderID in the Orders table will have certain significant events or milestones. The number of Orders ...
3
votes
1answer
179 views
Schema design: Use of association (aka: bridge/junction) table vs foreign key constraint with composite index containing a non-key field
This is an inventory database for IT assets. The models used are trimmed in order to focus on the problem at hand. Using SQL Server 2008. Thanks for taking the time to read and for any input you can ...
0
votes
4answers
114 views
Update Table A row if Table B row is changed
Is is anyhow possible to tell the database to execute a little script, if a row is changed in a table?
Scenario:
The database constantly monitoring Table B to see if rows are inserted, updated or ...
1
vote
1answer
213 views
Creating indexes with t-sql scrips vs rebuild indexes in maintenance plan
I’m using SQL Server 2008 and I am running several (15) scripts each day to bulk insert to tables. Each script drops the table at the beginning. I create indexes in the scripts. Some of the scripts ...
1
vote
0answers
98 views
Why would running DBCC FREEPROCCACHE result in different data being returned? [closed]
I experienced a situation where a query run directly in SSMS was giving a different result set than the same query run from code on a web server, using the same database.
After spending a lot of time ...
2
votes
0answers
145 views
Why do I get “The initial snapshot for publication is not yet available.”?
We are using Transactional Replications with updatable subscriptions. 1 publisher, 1 subscriber.
SQL Replication started encountering a constraint error on 3/6 at 10AM because people at two different ...
