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

learn more… | top users | synonyms (2)

7
votes
1answer
2k views

Is there a more helpful way to detect lock acquire/release stats in SQL Profiler?

Got a few deadlocks happening on our live server, i know, it sucks right. Anyway, i'm trying to figure out how to see what SQL code is causing the locks (or holding it for too long). I've added all ...
3
votes
3answers
2k views

Is it good idea to change Simple Recover Model into Bulk-Logged on ETL system?

We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set ...
3
votes
2answers
5k views

How to monitor SQL Server data and log file growth?

I'm trying to find a way to monitor data and log file size from SQL Server databases (within an SQL Server instance). I would not want to use third party tools, but simply pure SQL or PowerShell. I ...
2
votes
2answers
69 views

Best index for this scenario

There is a table in our database tblCustomText( ParentID int NOT NULL, CustomFieldID int NOT NULL, Value char(50) NOT NULL, Log_CreateDate datetime NOT NULL ) (ParentID+CustomFieldID) is ...
2
votes
2answers
3k views

Why the query scan all the partitions?

I have the following partitioned table create table T (Month char(6), ID int, .... primary key (ID, Month)) on psMonth(Month) The partition function is CREATE PARTITION FUNCTION [pfMonth](char(6)) ...
9
votes
3answers
8k views

Emulate a TSQL sequence via a stored procedure

I have a requirement to create a stored procedure which emulates a TSQL sequence. That is it always gives an increasing distinct integer value on every call. In addition, if an integer is passed in ...
10
votes
2answers
2k views

Is there a way to force an index to stay in memory with SQL Server 2008?

I've got a table with several millions rows, from which I need to run some queries from time to time. First query will usually be quite slow (around 10s), and subsequent queries are usually way faster ...
2
votes
2answers
864 views

What effect does serializable isolation level have on DDL-statements?

I'm using Red Gate SQL Compare to create a release script based on differences between SVN and a database. This results in a script containing a bunch of table- and procedure-changes and it works ...
1
vote
1answer
98 views

Trying to figure out when a table was dropped? [duplicate]

Possible Duplicate: Determining how a schema change occurred? Using SQLServer 2008, I see in my schema a particular table was dropped. I am trying to figure out when / why etc? Is there ...
4
votes
2answers
5k views

How to check when statistics was last executed?

We've been having a number of issues with our indexes lately which our DBA team has attributed to statistics not having been run recently. This has made me wonder - how can I check if statistics have ...
1
vote
1answer
105 views

backing up selected tables in a database

We need to transport database backups over a lower bandwidth connection. We only want the data of specific tables, not the whole database. In SQL Server 2008 R2, how can this be done? I am attempting ...
4
votes
1answer
135 views

Filtered indices: Why include the filtered-on field?

This just drives me mad. Consider a simple table with irrelevant columns removed: create table boxes ( row_id int not null identity(1,1) primary key, location varchar(15) null, dismantled bit ...
0
votes
1answer
279 views

FTS - Index rebuild causing CPU issues (SQL Server 2008 R2)

I am encountering some critical issues on SQL Server 2008 in a production environment; We have a database that contains a FTS index; we also have a maintenance job that rebuilds the FTS index and ...
2
votes
1answer
7k views

YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back?

I have moved the domain of my SQL Server and while everything is mainly working the date format for queries seems to have changed. So if I run this: select convert(datetime,'2012-02-13') I get ...
6
votes
2answers
3k views

SQL Server Profiler is available for SQL Server 2008 Dev Edition?

I just upgrade from SQL Server 2008 Express with advance services to Dev Edition. I wonder whether SQL Profiler is available for the Dev Edition? Please let me know how to access that if it is ...
1
vote
1answer
91 views

SQL Server many active create procedure processes

While troubleshooting performance issues on a SQL 2008 instance, I've noticed there are several SPIDs which appear to be executingcreate procedurestatements for already existing procedures. Is this ...
6
votes
2answers
547 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 ...
3
votes
1answer
336 views

Checking lock granularity

I understand in SQL Server there are various levels of granularity for locks...RID, Key, Page, Extent, Table, DB. Forgetting about lock escalation, I just want to check the default level of lock ...
1
vote
1answer
348 views

Effect on performance on retrieval of image column from a table with half a billion records on remote server

I require to loop through different servers to collect data in a temporary table. The table accessed on these servers have about half a billion records. To retrieve the data from these tables, I ...
2
votes
1answer
2k views

Database restoring taking too long

I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible. ...
0
votes
1answer
653 views

Using View filters for tenants in SQL-Server 2008

I'm currently creating a Multi-tenant data architecture in a SaaS BI-product. The product are using shared database and shared schema, but with tenant view filters and tenant data encryption ...
1
vote
1answer
358 views

Query optimization: Which is better in my case - CURSORs or a Set-based approach?

First of all, I can describe the schema of the table, but my table itself is around 80GB so I am hoping some expert eyes can look at any obvious optimizations that can be performed. I am carrying out ...
6
votes
3answers
466 views

How are my SQL Server constraints being bypassed?

We have found a handful of rows in our DB that violate an active constraint. How is this possible? The constraint is active, as we can't just manually add a row that bypasses this constraint. ...
2
votes
2answers
229 views

How to use READ_COMMITTED_SNAPSHOT

I'm having a database which is used heavily. A lot of writing/reading is done simultanosly. Therefore I've decided to use snapshots to reduce/avoid deadlocks. I'v turned on READ_COMMITTED_SNAPSHOT, ...
5
votes
1answer
138 views

Find out which columns must be specified in insert-statement

Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail. I wrote this query to find out which ...
16
votes
1answer
8k views

Should I use many single field indexes, instead of specific multi column indexes?

This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection". I'm working with an existing SQL Server (2008) application that has a number ...
2
votes
3answers
526 views

SQL Server 2008r2 replication from multiple sources

I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven ...
3
votes
2answers
495 views

Performance differences between executing via Linked Server vs. SSMS

We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in ...
2
votes
2answers
444 views

Looking for a way of moving millions of record without impacting the transaction log

I need to move around 10 million records out of a table into a new "optimized" table, but I need to keep the database online. What would be the best method in doing this without impacting the ...
2
votes
1answer
452 views

Is there a way to view the body / contents of a system function in sql server 2008

For the sake of leaning and exploring, I'd like to look at how some of the system functions are built. Is there a way to do this in sql server 2008 or R2? I've tried sp_helptext 'Sum' but I get an ...
1
vote
3answers
95 views

Need to figure out what FKs point to a specific table

In SQLServer 2008 r2. I need to figure for a specific table what FK's in other tables point to it. I try: SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS But this does not give me what I ...
5
votes
1answer
1k views

How does objectproperty() figure out if a function is deterministic in sql server?

How does sql server determine if a function is deterministic or not? Consider the following function, I think it's deterministic (as it's ultimately an integer operation), but sql server returns 0 ...
3
votes
1answer
680 views

sys.sysprocesses versus DMVs

I've seen a couple of blog posts where fairly knowledgeable sounding MVPs are still using sys.sysprocesses rather than the recommended DMVs: sys.dm_exec_connections, sys.dm_exec_sessions and ...
3
votes
2answers
2k views

Keeping “SQL Trace was started” and “SQL Trace stopped” from filling up my SQL logs

I have a SQL2008 server that runs a custom application that starts and then stops a SQL trace every 60 seconds (it does some reporting and summarization on the trace data). The problem is that my SQL ...
1
vote
0answers
89 views

Managing SQL 2008 database [closed]

We have a vendor application that creates a new database monthly for only few tables. Vendor doesn't have solution. Is there any other way besides partitioning as partitioning require enterprise ...
2
votes
3answers
144 views

Document versioning

I want to store in SQL Server 2008 XML files, but in a way that versioning is supported. For example, I have a XML document and I save it in the DB(in the XML datatype column). After that, I edit it. ...
1
vote
1answer
9k views

Getting deadlock information easily

I know to check for live deadlocks select * from sys.sysprocesses where blocked>0 However, that is only for deadlocks that are current. How do we check for deadlocks from the say the last 24 hours? ...
2
votes
2answers
3k views

SQL Server 2008, switching to simple recovery mode

Currently our transaction log is 50 GB; impressive huh? I want to switch to simple recovery mode and remove the transaction log entirely. Do I need to stop any user activity and detach the database. ...
5
votes
3answers
808 views

Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting?

SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below: When CDC is enabled on a table, SQL Server creates another table (so-called cdc table ...
3
votes
1answer
531 views

Maintaining replicated data in a staging-to-warehouse environment

I am trying to come up with a way to move metric data from the staging server to the data warehouse servers. I have a bunch of dimension tables that gets populated during the ETL process (on the ...
2
votes
2answers
1k views

How do you block ad-hoc distributed queries from specific sql server instances?

For one reason or another, a particular development SQL Server is unable to openrowset or opendatasource to production servers, but can hit other dev servers. I've tried sqloledb and sqlncli ...
1
vote
2answers
831 views

SQL Server 2008 R2 - user disk quota

Is it possible to apply restrictions on how much data a user / login may store in a database? I found no way to apply such restrictions to schemas, for instance. If there is no built-in mechanism ...
30
votes
9answers
74k views

Passing array parameters to a stored procedure

I've got a process that grabs a bunch of records (1000's) and operates on them, and when I'm done, I need to mark a large number of them as processed. I can indicate this with a big list of IDs. I'm ...
2
votes
0answers
233 views

Change tracking on, but columns not tracking

In SQL 2008, I have change tracking turned on both on the database and table levels, with column tracking turned on at the table level. The CHANGETABLE(CHANGES...) function returns the expected ...
2
votes
1answer
137 views

How to improve this query (using table variables or not?)

I'm having a bit of problems with these tables... TableCost ( TableCostId int, Cost numeric, MonthsId int ) and Taxes ( Percentt numeric, MonthsId int ) I have to calculate a new value by ...
5
votes
1answer
1k views

What is the optimal way to design an approval queue and audit trail for entities in SQL Server 2008 R2?

Simplified Explanation: I have three main tables: customer, phone, and customer_phone to resolve the many-to-many. I need to ensure that only admins can insert/update these tables without approval. ...
3
votes
1answer
158 views

Is it possible to install a single instance of sql server on a server with 2 NICS and 2 different subnet?

I have a server that has 2 NICS and each is connected to 2 different subnet. Is it possible to connect to this Sql server in each of the different subnet? If so, what special procedure do I have to ...
1
vote
3answers
445 views

SQL Server 2008 eval expired

I have (more than one instance) some of my experiments with SQL Server 2008 eval (why eval --> brutal bureaucracy about using licenced version for testing or short life-cycle experiments), and after ...
2
votes
1answer
209 views

Varchar to date in SQL Server 2008 [closed]

I have varchar data in database table which stores date in DD-MM-YYYY format. I want to select that varchar in MM-DD-YYYY format. I'm using select convert (varchar(30), (select CONVERT(datetime, ...
7
votes
1answer
578 views

Adding SPARSE made table much bigger

I have a generic log table, about 5m rows. There's a "strongly typed" field that stores event type, and a bunch of "losely typed" columns that contain data relevant to the event. That is, meaning of ...