This tag is specifically for the 2008 version of Microsoft SQL Server.
4
votes
4answers
7k views
Replicating a remote MySQL database to MS SQL Server 2008
I want to replicate the contents of a MySQL database to a MS SQL Server 2008 database.
Is this possible? Can anyone outline the steps required in order to achieve this?
Thanks.
3
votes
3answers
2k views
Stored procedure executes too long
We use SQL Server 2008. When I am watching in SQL Server Profiler I found some queries that take for example 20 seconds. The same query (in a stored procedure) when executed in SQL Server Management ...
3
votes
2answers
2k views
How can I help SQL Server recognize my view column is NOT NULL-able?
I have the following indexed view defined in SQL Server 2008 (you can download a working schema from gist for testing purposes):
CREATE VIEW dbo.balances
WITH SCHEMABINDING
AS
SELECT
user_id
...
2
votes
1answer
75 views
Prevent users from using power pivot excel connections to a database
A couple of weeks ago my boss connected to our production database via excel and was able to query the database. Because of how we're doing security to the database (AD groups) he should have access ...
2
votes
2answers
486 views
IN Clause causes Execution plan to change from Nested Loops to Hash Match
I'm tuning a query and have discovered some behaviour I'm not clear about.
If I remove the WHERE IN clause the query runs in 3 seconds instead of 3 minutes.
There only 7 rows returned in the ...
2
votes
1answer
204 views
Is staggering reindexing jobs a good strategy?
I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.
...
2
votes
2answers
971 views
Reducing Key Lookups
I am using SQL server, and I have been looking closely at the concept of key lookups,
...
2
votes
1answer
225 views
SQL Server 08: Union over while
I've done some reading on this, and I keep seeing people say it's a bad idea, but hear me out!
I have a stored procedure which needs to find a series of timeslots over a number of days. Due to the ...
2
votes
1answer
317 views
What is the best practice for backing up a database in Simple mode?
I am trying to create a SQL Job for backing up my database. I want to have:
A weekly full backup
A daily differential backup
Something that will clean up backups that are older that 14 days
I ...
1
vote
2answers
266 views
Can I send a string over TCP using T-SQL?
I've set up some alerts, based on this article
Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
-3
votes
1answer
410 views
How database administrators can see my requests to MSSQL server?
I'm a MS SQL 2008 user. I have access to some tabes. I need to request few colums from table as I usually do. But I need to do it onse (for example) in 5 seconds and system administrators shouldn't ...
7
votes
2answers
135 views
Capturing datetime of change in SQL Server CDC
So we've started exploring using change data capture on one of our production databases. We'd like to know the datetime of each change. Reading through walkthrough's and tutorials etc it seems that ...
7
votes
1answer
1k views
noexpand hint for non-enterpise edition and performance
I have to use Indexed views to reach performance. As I can see from this comparison table standard edition does not support indexed views. But BOL says:
Indexed views can be created in any
...
7
votes
1answer
4k views
Breaking and Restoring a mirror
I have to do some maintenance on a secondary node of a mirror. Since this is the result of the site becoming slow while the mirror re-established after a brief network outage I want to be safe and ...
6
votes
3answers
304 views
Restrict access for Windows administrator
As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows ...
6
votes
1answer
273 views
Attempting to tune a Sharepoint site using SQL Server Waits and Queues
I'm pretty new to this performance tuning with Waits and Queues thing - fascinating, but also not always all that intuitive...
Right now, a customer of mine has a SQL Server 2008 64-bit Enterprise ...
6
votes
2answers
403 views
Do indexes consume memory?
I am just starting to learn about memory usage on SQL Server. When using the query in the answer to the question SQL Server 2008 R2 "Ghost Memory"?, I discovered that a single database is ...
6
votes
2answers
233 views
Why select max partitioned column is much slower than indexed one?
I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity.
However the following ...
6
votes
2answers
990 views
SQL Server VARCHAR Column Width
Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do.
I ...
6
votes
2answers
479 views
Database locking issues?
I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
5
votes
1answer
623 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 ...
5
votes
1answer
570 views
How to get online the database which has missing/corrupted FILESTREAM files?
I've corrupted the files related to FILESTREAM in a database.
The .mdf and .ldf files are still intact, but when I try to get the database online, it complains, saying that the files related to ...
5
votes
1answer
689 views
Changing ANSI_NULLS setting on a table
Can we use the following query to update ANSI_NULLS option.
sp_configure 'allow updates', 1
reconfigure with override
update sysobjects set status = status | 0x20000000 where name = 'tblClient'
...
5
votes
2answers
628 views
Using totals on aggregates to improve performance
I have two tables: details and totals of these details.
Details (Slow solution):
select
OrderId = r.OrderId
, TotalQty = SUM(r.Quantity)
, ...
5
votes
3answers
11k views
Read XML file in SQL Server 2008
How can I read an XML file and store the data in XML to our table in SQL Server 2008?
4
votes
2answers
300 views
SQL Server 2008 R2 SP1 - Tempdb “Available free space” showing negative value
Please help as this issue is driving me nuts...
Tempdb has four data files & one log file. They are all located on the same dedicated drive.
When I right click on tempdb in SQL Server Management ...
4
votes
5answers
74 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 ...
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 ...
4
votes
2answers
134 views
Checking a wide table for nulls [duplicate]
Possible Duplicate:
Test if any fields are NULL
I am new here. I have a table with 70-odd columns. Is there a way I can check the number of rows with at least one null value in any column ...
4
votes
6answers
3k views
SQL Server log shrinking Issue [duplicate]
Possible Duplicate:
Why Does the Transaction Log Keep Growing or Run Out of Space?
I have a 300 GB database log in our SQL Server. I want to shrink all the logs but it is not allowing me to ...
4
votes
1answer
684 views
Why does it take so long to calculate an Execution Plan?
One of our customers has just upgraded to a new server.
For a particular stored procedure the first time you execute it, is taking over three minutes to run. Subsequent runs are less than 1 second.
...
4
votes
1answer
293 views
how to improve performance by using or not using table variables
I have a table variable:
DECLARE @to_process TABLE
(
[Id] [bigint] NOT NULL,
[SequenceId] [bigint] NOT NULL,
...
)
INSERT INTO @to_process
( Id
, SequenceId
...
)
SELECT
...
4
votes
2answers
330 views
SQL Server configuration / specification recommendations and advice!
I am about to deploy a new server at work as follows:
Dell T610
2 x Xeon 2.53 Quad core
24 GB RAM
1 x Perc H700 RAID controller with 1GB Non Volatile memory
8 x 300GB 15k SAS Hard drives
I am looking ...
4
votes
2answers
3k views
How to set full text catalog location SQL Server 2008/2012
I want to upgrade my SQL Server 2005 database to SQL Server 2012. In SQL Server 2005 you can set the location of the full text catalog when you create it. But in SQL Server 2012 (and in SQL Server ...
4
votes
5answers
5k views
User Permissions Messed Up following a Backup -> Restore Operation
I had to move several SQL Server 2008 databases to our new db server so I backed them all up (to .bak files), copied these files over to the new box and restored them (all done using SQL Management ...
3
votes
2answers
3k views
SQL Server 2008 R2 Management Studio : “Evaluation period has expired”
I've spent several hours trying to fix this issue so far with no success. I had installed SQL Server 2008 Evaluation Edition. Out of fear it might expire & create problems for me I installed SQL ...
3
votes
1answer
112 views
In-memory SQL Server log file
I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services. Its data comes from another SQL Server instance which is ...
3
votes
1answer
358 views
Why do we have to use SWITCH in table partitioning?
I'm a beginner in SQL.
I'd like to know what is the importance of SWITCH in table partitioning?
I've created one table partitioned month-wise, with a total of 12 partitions.
The ranges are:
...
3
votes
2answers
370 views
Connecting to a Remote SQL Server 2008 Instance
I work with SQL Server 2008 at my current job and the way I connect to the (backup) server where The SQL Server 2008 instance is installed is by doing a remote desktop from my local work machine into ...
3
votes
1answer
172 views
Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it
The standard format for beginning a dialog is like this:
Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE initiator_1
TO SERVICE 'target_1'
ON ...
3
votes
3answers
308 views
SQL JOIN Syntax in MS SQL
I have been taught in my MSSQL classes, this is how to join two tables
select * from FirstTable A
JOIN SecondTable B
on A.ID= B.ID
Now in my professional life, I came across JOIN queries like ...
3
votes
1answer
286 views
querying a linked PostgreSQL database in SQL Server 2008
We will be deploying an application with a Postgres back-end and need to provide report writing capabilities in SQL Server. I am exploring options for real-time or near real-time reporting in this ...
3
votes
4answers
999 views
What's a good tool for visualizing SQL Agent jobs?
I find the built-in MS SQL Studio tools for managing Agent jobs to be a little frustrating. What tools out there have you found helpful?
Offhand, there are three things I'd like to see in such a ...
3
votes
2answers
199 views
Archive a single giant table from a SQL 2008 database - filegroup backup?
I have an extremely large table (400GB data size, inside a 2.5TB database) that is no longer needed. Before we drop it, however, our client contract says we need to archive it to tape in case in needs ...
3
votes
1answer
1k views
To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question
We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to ...
3
votes
2answers
1k views
SQL Server 2008 T-SQL select hanging, but not dead locked
This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
3
votes
1answer
540 views
Removing Instances and SQL Server Express edition and going to Standard/Developer/Enterprise
I would like to move from SQL Server 2008 R2 Express to SQL Server 2008 R2 Developer or Enterprise.
I just noticed that I have the following services running:
SQL Server (MSSQLSERVER)
SQL Server ...
3
votes
1answer
173 views
Minimum rowcount for filtered index?
Creating an index always has trade-offs. Whenever you insert, update or delete, it has to perform those operations on the index as well. This is all pretty commonly known.
But with the advent of ...
3
votes
2answers
476 views
A query submitted from different applications has differing DOP
I have a query which selects from multiple views and is fairly I/O heavy. If I execute this query using Management Studio it uses parallelism across most of the 16 CPUs and completes in under 10 ...
3
votes
1answer
541 views
How to fix discrepancies between publisher and subscription data?
I have a transactional replication between two SQL Server 2008 SP1 servers, both on Windows 2003 Server. I discovered that there are discrepancies between some of the tables of the main database and ...
