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

learn more… | top users | synonyms (1)

9
votes
3answers
1k views

Snapshot Replication Retention

I've got Snapshot replication set up on my SQL Server 2008 production server, and I just noticed that there are snapshots ranging back to a year ago in the snapshot folder. How can I change the ...
9
votes
6answers
2k views

SQL Server Checklists

Following up from my other question, I would like to start thinking about what I should take a look at on daily/weekly/monthly bases in terms of alerts. I am hoping to be able to see problems coming ...
9
votes
1answer
7k views

Varchar(Max) field cutting off data after 8000 characters SQL Server 2008

I have a field to store some data, the field is declared as varchar(Max). To my understanding this should be storing 2^31 - 1 characters but when I enter some content over 8000 chars it cuts the rest ...
9
votes
1answer
2k 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 ...
9
votes
2answers
2k views

How large is “too large” for SQL 2008 Web Edition?

For our soon-to-be-hosted application I need to choose between SQL Server 2008 Web edition and SQL Server 2008 Enterprise edition. There isn't any "must-have" functionality: the application currently ...
9
votes
1answer
483 views

Workspace Memory Internals

Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation ...
9
votes
2answers
179 views

SQL SERVER Storage of TinyInt

In SQL Server, why is a tinyint stored with 9B in the row. For some reason there seems to be an additional one byte at the end of the NULL bitmap mask. USE tempdb ; GO CREATE TABLE tbl ...
9
votes
2answers
2k views

Restore bak file to smaller mdf and ldf database files

I have a legacy database whose nightmarish lack of design I will not get into here, but the files on the server are (relatively) enormous. I have: MyDatabase.mdf: 24.8GB MyDatabase.ldf: 114.6GB This ...
9
votes
2answers
279 views

Is there a set based way to load/read a tree branch with HierarchyId

I am playing around with HierarchyId, and I have not figured out a set based way to do the following: insert all subtree at once retrieve all subtree at once This question is related to my ...
9
votes
1answer
616 views

Does SQL Server 2008 store the creation date of execution plans?

We recently upgraded an application we use, which involved modifying the schema for the database. These changes could have forced cached execution plans to be discarded. If SQL Server was forced to ...
9
votes
4answers
1k views

What is the best way to monitor long running queries in SQL Server?

I have to run quite a few long-running queries (rebuilding indexes, updating large sets of data) for my database. Is there an alternative to running the query in SQL Server Management Studio and ...
9
votes
1answer
3k views

Are there any risks to granting users SQL Server SHOWPLAN permission?

I'm doing some performance tuning on a large SQL server 2008 database, and the IT group is unwilling to give SHOWPLAN permission. In the past, "Show Execution Plan" has been the most effective way to ...
9
votes
1answer
2k views

When should you specify PAD_INDEX?

So, you can use FILLFACTOR to leave space in leaf index pages. Specifying PAD_INDEX also leaves space in intermediate nodes. What scenarios should you specify PAD_INDEX, and what benefit does it ...
9
votes
1answer
485 views

Slot Array and Total Page Size

I continue to read in many forums and on many blogs that a page is comprised as shown below: Page Size: 16 x 512B = 8192B Page Header: = 96B Maximum In_Row Row: = 8060B This leaves ...
9
votes
1answer
595 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 ...
9
votes
4answers
2k views

Query becomes drastically slower when wrapped in a TVF

I have a fairly complex query which runs in just a few seconds on its own, but when wrapped in a table-valued function, it's far slower; I've not actually let it finish, but it's run for up to ten ...
9
votes
1answer
516 views

query optimization: time intervals

In the main, I've got two kinds of time intervals: presence time and absence time absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
9
votes
1answer
622 views

Scalar function used in calculated column, what's the cleanest way to update the function?

I want to update a scalar function in our database. This function is used for several calculated columns in a key table so I get a dependency error if I try to update it. I can explicitly remove the ...
9
votes
2answers
406 views

Hard limitation for merge replication article count?

A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
9
votes
0answers
3k views

Fast SQL query with * but extremely slow with specific columns? [closed]

We have a query that we're running on SQL Server 2008 in the Management Studio. I can't give the exact query details, but let's say it's equivalent to this: SELECT * FROM MyView a (nolock) INNER ...
8
votes
5answers
2k views

Do empty columns take up space in a table?

I have table that holds from very basic info. Just a title and a few date fields. There's one field called comments which is varchar(4000) Most of the time we leave it blank, but some times will enter ...
8
votes
2answers
2k views

Why does “SELECT POWER(10.0, 38.0);” throw an arithmetic overflow error?

I'm updating my IDENTITY overflow check script to account for DECIMAL and NUMERIC IDENTITY columns. As part of the check I compute the size of the data type's range for every IDENTITY column; I use ...
8
votes
3answers
6k views

SQL Server Max Memory not limiting use of RAM

I would like your input on this. I have a sql server 2008r2 Ent. Ed. 64bit with 16 cores and 64GB RAM. There is one instance of SQL server patched fully as of 20111014. The max ram is set to 60000MB. ...
8
votes
5answers
2k views

Query Running Differently on SQL 2005 vs SQL 2008R2

At my office, we have a query that is pretty ugly, but runs pretty well in production and in the development environment (20sec, and 4sec respectively). However in our testing environment it takes ...
8
votes
1answer
416 views

Why does 10^37 / 1 throw an arithmetic overflow error?

Continuing my recent trend of playing with large numbers, I recently boiled an error I was running into down to the following code: DECLARE @big_number DECIMAL(38,0) = '1' + REPLICATE(0, 37); PRINT ...
8
votes
3answers
2k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
8
votes
5answers
2k views

Are there tools to perform data comparison between two different schemas?

We are migrating our database to a new schema but want to validate the data has been moved correctly. Traditional data comparison tools can compare two databases for differences if the schemas are ...
8
votes
1answer
7k views

How to force drop database in SQL Server 2008

I'm trying to force drop a database, but after dropping the database, when I try to recreate the database, I'm getting the error cannot create file C:\Program Files.....[databasename].mdf because ...
8
votes
3answers
829 views

How do I write portable SQL that refers to a linked server?

I've got a stored procedure that refers to a linked server. In several places throughout the procedure I've got something like the following: INSERT INTO [TableName] (...Columns...) SELECT ...
8
votes
2answers
177 views

Non-Clustered Indexes - keys and nonkeys

I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated. Here's my theory from the query I've just optimised, through a process of trial and ...
8
votes
2answers
1k 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 ...
8
votes
2answers
911 views

Why can't I use NEWSEQUENTIALID() as the default value for my column?

I'm trying to create a table in Management Studio and after reading about the new (from SQL 2005 on) NEWSEQUENTIALID() function, thought I'd give it a go. This is what I'm doing: But it's not ...
8
votes
3answers
779 views

Speed impact of using varchar in SQL Server 2008

In the old days, using varchar over char was a big no no, since the varchar was stored on a different page than the rest of the row. Is this still the case today with SQL Server 2008? Will I see any ...
8
votes
3answers
219 views

When a previously-fast SQL query starts running slow, where do I look to find the source of the issue?

Background I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 ...
8
votes
3answers
977 views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
8
votes
1answer
370 views

Are SQL Server Database writes slower with Snapshot Isolation?

I have a lot of deadlocks going on in my system. I would like to use Snapshot Isolation to fix them, but my DBA has reserves about it. One of his concerns is that Snapshot Isolation slows down ...
8
votes
5answers
198 views

What is the best way to find out how many records do not have a proper phone number in SQL Server?

I have a field that people historically could use to freely supply a value. I need to identify the records which do not have an appropriate value so I can clean them. I've been looking around in SSIS ...
8
votes
1answer
193 views

What does “*password------------” mean in a profile deadlock report?

In SQL Server 2008 R2, I got several deadlock reports that have "*password------------" in the input buffer. It looks like an attack but in that case I don't know the reason or the kind of attack. ...
8
votes
1answer
174 views

Does SQL Sentry Plan Explorer count the reads in UDFs?

I have a query like this: select dbo.fn_complexFunction(t.id) from mytable t In SQL Sentry Plan Explorer, I noticed I have to run Get Estimated Plan in SQL Sentry to make the Query Plan include the ...
8
votes
1answer
17k views

SQL Server 2005/2008 UTF-8 Collation/Charset

I can't find option(s) directly to set UTF-8 rellated Collations/Charsets in SQL Server 2005/2008, same as is possible to set in another SQL engines, but in SQL Server 2005/2008 are there only Latin ...
8
votes
2answers
2k views

SQL Disk Setup Advice - TempDB, Log DB, Data file placement question

We have a very active database server with a ecclectic collection of applications running on it. Two of the busiest are a Laserfiche database that does document scanning and workflow processing all ...
8
votes
1answer
2k views

Handling exceptions in stored procedures called using insert-exec blocks

I have a stored procedure that is called in an insert-exec block: insert into @t exec('test') How can I handle exceptions generated in the stored procedure and still continue processing? The ...
8
votes
2answers
437 views

Tracking stored procedure usage

Besides using SQL Server Profiler, is there any way to track which stored procedures are being used, or at least when they were last executed?
8
votes
2answers
1k views

Do SQL Server Express Editions support CLR procedures?

Do SQL Server Express Editions (2005, and 2008) have support for installing, enabling and running CLR Stored procedures and functions.
8
votes
2answers
2k views

SQL Server Frozen Ghost Cleanup workaround needed

I have several tables with amount of rows between 5M and 1.5G Each table has its BLOB field, which size varies from 100 bytes to 30 MBytes and which is stored as 'large value types out of row' = ON ...
8
votes
1answer
249 views

What is the actual lowest possible positive REAL number

MSDN says that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column ...
8
votes
3answers
2k views

When should indexes be dropped and recreated?

We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month. For certain tables we are doing daily ETL processes and others we are doing weekly/monthly. ...
8
votes
1answer
424 views

How to get accurate query performance?

I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before ...
8
votes
2answers
796 views

SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. Server1\Dev Server1\DevIntegrated Server1\QA I have a SQL PowerShell script in the works that calls out to the OS, invokes ...
8
votes
2answers
772 views

How much “Padding” do I put on my indexes?

How do you know how much "Padding" an index should have?