This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (2)

1
vote
2answers
545 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 ...
0
votes
1answer
986 views

Database design : multiple category of users login into same system

In our new system we have three type of people Business owner Assistant of business owner Staff (a skilled employee) I am having following entity [Business] - include detail about business ...
8
votes
3answers
15k views

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. ...
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
2
votes
2answers
158 views

Setting up a table with a calculated column

I'm setting up a table in sql server 2008. It going to hold three columns (busTime, trainTime, carTime). I want these columns to hold the amount of time a user spends traveling per day. I then want to ...
2
votes
3answers
1k views

Loading a FACT table per day

I have a database with 13 billion rows, per day I have around 20-30 mio rows. On top of this I have one cube, one of its dimensions is DateTime that goes down to milliseconds. To load the fact table I ...
0
votes
2answers
934 views

How to find out the activity which increasing the response time of the Sql Server?

We are using SQL Server 2008 as our database server. Recently we came to know, by using a 3rd party tool, that the response time of our database server is increasing at a specific time for example at ...
19
votes
2answers
4k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
7
votes
2answers
2k views

how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?

I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than ...
2
votes
5answers
4k views

Keep transaction log size under control during batch processing

tl;dr I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of ...
2
votes
1answer
13k views

In which folder / directory does SQL 2008 store files?

Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?
1
vote
1answer
66 views

How do I determine if a database restore file should be restored to the database based on backupset?

I have a production database that is fully backed up nightly and incrementally backed up hourly. On our backup machine, we have the differential backups and full backups restored ion a regular basis ...
3
votes
1answer
558 views

Sql Server Performance Degradation in moving from 2005 to 2008

I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
6
votes
1answer
3k views

why are people advised not to update through views

During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views. Where there/Are there ...
5
votes
4answers
8k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
10
votes
1answer
870 views

SQL Server 2008 R2 Dirty reads - how non-atomic?

I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but: Can a row appear as ...
4
votes
1answer
947 views

Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

I wrote this script without COMMIT in a new Query window (Query1): BEGIN TRAN SELECT [RegionID], [RegionDescription] FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK) WHERE RegionID = 3 and in ...
1
vote
3answers
494 views

How can I know which tables “compose” a view?

I want to create a trigger on SYS.database_mirroring as it is not a real table, just a view... I can't do it. I want to know were the field mirroring_state_desc is coming from so that I can create a ...
1
vote
1answer
226 views

Create a trigger that compares a field, with a field in other table

I have 3 databases, (One principal, one Mirror and a witness) and I'm trying to make this system the most fault tolerant as possible. So I was thinking in doing some extra codding to protect me ...
0
votes
1answer
2k views

Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should ...
3
votes
1answer
163 views

SQL Server - Using spatial data

I'm using SQL Server 2008 R2 for real-time location aware service. The service mostly receives location data (lat/lon) from a user and returns locations around the user. The data is being sent from ...
2
votes
2answers
404 views

X5687 vs X5690 CPUs for a SQL 2008 R2 Server

I have the choice between: 2 x X5687 (Intel® Xeon® Processor X5687 - QuadCore (12M Cache, 3.60 GHz, 6.40 GT/s Intel® QPI) or 2 x X5690 (Intel® Xeon® Processor X5690 - SixCore (12M Cache, 3.46 GHz, ...
2
votes
1answer
117 views

Restore diff backup only on SQL Server

Is there a way to restore ONLY a differential backup on SQL Server? I have a situation where I only need to view one table that was truncated from an entire database. And instead of trying to ...
0
votes
1answer
2k views

unique key violation occuring on a unique value combination

SQL SERVER 2008 I have a mapping table that contains a deleted date column. Since the table uses a surrogate key as its unique identifier there is a uniqueness constraint on the table. This ...
0
votes
2answers
1k views

Deny drop permissions for all other users except actual administrator - SQL Server 2008

I have a database "myDB" running in a Windows Server machine. Users (coming under Administrators group) remotely access this server and log-in to this database using Windows Authentication mechanism. ...
3
votes
4answers
6k views

In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 ...
10
votes
1answer
3k views

Empty Strings: Why or when is '' equal to ' '?

Who can explain why select case when '' = ' ' then 1 else 0 end, LEN(''), LEN(' '), DATALENGTH(''), DATALENGTH(' '); yields ----------- ----------- ----------- ----------- ----------- 1 ...
1
vote
1answer
1k views

SQL Server 2008 - Cannot create index on Indexed View

I am attempting to create an index on a view. The view is contained in a different schema than the underlying table it's based on. When I attempt to create the view, I get the following error: Msg ...
4
votes
1answer
4k views

Backing up database without transaction log

When I backup a database is there a way to exclude the transaction log in that backup? Or am I thinking about this wrong and to restore the original database the content of the transaction log is ...
4
votes
1answer
244 views

Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2?

Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2? I have a database that I am detaching from SQL 2008 (not R2) and can attach this to SQL 2008 R2. Can I then detatch the database from ...
10
votes
1answer
3k views

How to find corrupt page in SQL Server 2008

I know I can execute a DBCC CHECKDB and get a status of my database. Questions How can I find if there is some corrupt data page into my database? If an error is throwndue to page corruption, where ...
4
votes
1answer
346 views

Sql Server Enterprise 2008 R2 Partition Switch with Xml Index

Ok, sql db partitioning and switching doesn't seem to be a highly discussed topic, but it is something I'm implementing. I've had success in all areas of partition functions, partition schema, file ...
2
votes
2answers
527 views

Can I implement clustering with just 2 servers and one NAS?

I have the following architecture, it consists in only 2 servers, connected to a NAS. I'm having trouble in implementing clustering because I can't have a domain controller in any of them (if I do ...
1
vote
1answer
7k views

reindex database in sql server 2008

The best way to reindex 500GB database in SQL Server 2008 Is it by using Maintenance plan? Would I face performance problems while reindexing?
3
votes
3answers
6k views

Why so many logical reads?

I have a very simple query that is showing in Activity monitor and other statistics as having the most logical reads on my entire DB server. SELECT MAX(RESULT_DATE) FROM TABLEX mm WITH (NOLOCK) JOIN ...
3
votes
1answer
542 views

Can you set up mirroring for sql server reporting services?

I have mirroring set up for our SQL Server 2008 R2 Standard database server. Questions How do I ensure high availability for reporting services if the server goes down? Is there a kind of mirroring ...
3
votes
2answers
2k views

Default SQL Server 2008 locking behaviour

I am using SQL Server 2008. What is the default lock behaviour with UPDATE? NOLOCK? ROWLOCK? PAGLOCK? How can I tell what the current level of lock is for a table? Thanks, Bruce
2
votes
1answer
171 views

SQL Server Express 2008 installation problem

I just installed SQL Server Express 2008 on my Windows 2003 server. The sql base is started, but I look in config manager and my SQL Server browser is not started. I can't start it. When I try to ...
4
votes
2answers
296 views

Same queries, different servers, different exec plans, stats are up to date

I've got the same query running on two different servers, and getting vastly different performances. I have updated all of the stats on dependent objects and it has not fixed the issue. I'm lost on ...
5
votes
1answer
193 views

Composite or Single-Field Clustering Key

My DBA and I are having a disagreement on index structure. Consider data for a medical claim... We have the Header table with fields like: ClaimId (varchar(50)), PaidAmount, MemberID... and for ...
2
votes
1answer
867 views

How to Backup and Restore Filestream Data

I am trying to backup and restore my database which includes a filestream filegroup. I backed up the database without the filestream filegroup and was interested to know what happens when you restore ...
2
votes
2answers
1k views

How to Test Log Shipping

I am evaluating log shipping as a solution to keep a permanent failover copy of our live database. It looks ideal. We are using Filestream which is part of the reason to use it over mirroring. I ...
4
votes
3answers
5k 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 ...
8
votes
1answer
4k views

Is it possible to create an alias or synonym for a database?

Is it possible to create a database alias or synonym? What I am hoping to be able to do is have NewDatabaseName aliased as OldDatabaseName to prevent having to rewrite a ton of code. Backstory: I ...
2
votes
1answer
136 views

Is execution of synonyms not logged in target database?

I've been working on an issue where a single select within a stored procedure failed due to permission-issues, and we traced it to a call to a Synonym: USE DatabaseA GO CREATE SYNONYM ...
1
vote
1answer
1k views

SQL-Server: sp_spaceused gives zero rows but big dataSize for cleaned table

After cleaning my table with DELETE FROM MyTable (and executing DBCC shrinkdatabase('MyDB') should that matter) I run the statement EXEC sp_spaceused MyTable. The results confuse me: tableName ...
0
votes
1answer
3k views

Run SQL Server Snapshot Agent automatically

I am trying to setup automatic transactional replication. Basically I have some script files that are executed after each other. They set up a replication from one server to another and this works ...
4
votes
2answers
1k views

SSAS queries time out when Windows File System Cache drops

We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the ...
4
votes
2answers
138 views

Are my Indexes Being Used?

I am using SQL server, and the person creating our database added lots of indexes on our biggest table. These indexes only include one field each. To me it seems that these indexes are mostly ...
2
votes
2answers
5k views

Reducing Key Lookups

I am using SQL server, and I have been looking closely at the concept of key lookups, ...