Skip to main content

Questions tagged [sql-server-2008-r2]

SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.

Filter by
Sorted by
Tagged with
99 votes
4 answers
397k views

SQL server databases stuck in restoring state

I have a Sharepoint server. We had an issue with our backup tool and now some of my databases are stuck in restoring state! Is it possible to stop the restoring process? and also, How can I make sure ...
Pooya Yazdani's user avatar
60 votes
5 answers
42k views

Why does ALTER COLUMN to NOT NULL cause massive log file growth?

I have a table with 64m rows taking 4.3 GB on disk for its data. Each row is about 30 bytes of integer columns, plus a variable NVARCHAR(255) column for text. I added a a NULLABLE column with data-...
PapillonUK's user avatar
55 votes
1 answer
40k views

Is there an analog of GETDATE() that returns DATETIME2

According to MSDN, Getdate(), GetUtcDate(), and CURRENT_TIMESTAMP all return DATETIME. I ran a short test, which confirms that: CREATE TABLE #t(T DATETIME2(7)); GO DECLARE @i INT ; SET @i=1; WHILE @...
A-K's user avatar
  • 7,294
51 votes
2 answers
296k views

Best way to get last identity inserted in a table

Which one is the best option to get the identity value I just generated via an insert? What is the impact of these statements in terms of performance? SCOPE_IDENTITY() Aggregate function MAX() ...
AA.SC's user avatar
  • 4,073
51 votes
4 answers
15k views

What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2?

My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other. Our ...
usr's user avatar
  • 7,340
51 votes
3 answers
61k views

How to make sqlcmd return an ERRORLEVEL other than 0 when the .sql script fails?

I'm running sqlcmd from a batch file and I was wondering how to make it return an ERRORLEVEL other than 0 when something goes wrong with the backup.
leeand00's user avatar
  • 1,712
51 votes
5 answers
113k views

How export a sql server 2008 diagram to PDF filetype?

I want to have an export from my database diagram to PDF or image types. How can I do this? I worked with SQL Server 2008 R2.
Hamid Talebi's user avatar
51 votes
5 answers
34k views

Data obfuscation in SQL Server

What is the best practice for Data Obfuscation in SQL Server? We'd like to use masked Production data in our UAT system. If we want to do it quickly, and with a higher level of obsfucation, what ...
Sky's user avatar
  • 3,684
48 votes
2 answers
41k views

What is the actual behavior of compatibility level 80?

Could somebody provide me with a better insight about the compatibility mode feature? It is behaving different than I expected. As far as I understand compatibility modes, it is about the ...
souplex's user avatar
  • 833
43 votes
5 answers
315k views

Why is SQL Server consuming more server memory?

SQL Server is consuming 87.5 % of my server RAM. This recently caused a lot of performance bottlenecks such as slowness. I researched this issue. One common solution I could find on the internet is to ...
kombo's user avatar
  • 657
42 votes
1 answer
79k views

SIMPLE or FULL recovery model for databases?

When should I use the full recovery model and when should I use the simple recovery model for databases? I always used the full recovery model because it is the default, but today i encountered this ...
MicBehrens's user avatar
42 votes
3 answers
82k views

Execution Plan Basics -- Hash Match Confusion

I am starting to learn execution plans and am confused about how exactly a hash match works and why it would be used in a simple join: select Posts.Title, Users.DisplayName From Posts JOIN Users on ...
Kyle Brandt's user avatar
  • 2,345
41 votes
6 answers
14k views

Why does adding a TOP 1 dramatically worsen performance?

I have a fairly simple query SELECT TOP 1 dc.DOCUMENT_ID, dc.COPIES, dc.REQUESTOR, dc.D_ID, cj.FILE_NUMBER FROM DOCUMENT_QUEUE dc JOIN CORRESPONDENCE_JOURNAL cj ...
Kenneth Fisher's user avatar
41 votes
3 answers
10k views

difference in execution plans on UAT and PROD server

I want to understand why there would be such a huge difference in execution of the same query on UAT (runs in 3 sec) vs PROD (run in 23 secs). Both UAT and PROD are having exactly data and indexes. ...
Kin Shah's user avatar
  • 62.3k
41 votes
1 answer
38k views

SLEEP_TASK Wait Type in SQL Server - What does it indicate?

I haven't seen the SLEEP_TASK wait type before, and today I seem to be getting a ton of them. I'm not the official DBA, just a SQL Server developer who knows some DBA stuff. We upgraded our servers ...
JNK's user avatar
  • 18k
40 votes
3 answers
5k views

Unexpected scans during delete operation using WHERE IN

I've got a query like the following: DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN ( SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL ) ...
Mark S. Rasmussen's user avatar
40 votes
7 answers
247k views

List all permissions for a given role?

I've searched around all over and haven't found a conclusive answer to this question. I need a script that can give ALL permissions for an associated role. Any thoughts, or is it even possible? ...
elgabito's user avatar
  • 831
38 votes
2 answers
14k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
BlueChippy's user avatar
36 votes
5 answers
8k views

Why is there still a varchar data type?

Many of my databases have fields defined as varchars. This hasn't been much of a problem since I live and work in America (where the only language that exists is "American". ahem) After ...
RLH's user avatar
  • 927
36 votes
18 answers
108k views

How can I compare the schema of two databases?

Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customer's site. We are experiencing problems with crystal reports running some ...
user1571430's user avatar
36 votes
4 answers
6k views

Still wrong to start the name of a user stored procedure with sp_?

One of my co-workers named a stored procedure in our SQL Server 2008 R2 database sp_something. When I saw this, I immediately thought: "That is WRONG!" and started searching my bookmarks for this ...
user avatar
35 votes
4 answers
20k views

Slower SQL Server performance after allocating more CPU and RAM

We have SQL Server 2008 R2 (10.50.1600) running on a virtual Windows 2008 R2 server. After upgrading the CPU from 1 core to 4 and the RAM from 4 gb to 10 gb, we've noticed the performance is worse. ...
Jeff's user avatar
  • 505
35 votes
2 answers
99k views

Decode Base64 String Natively in SQL Server

I have a varchar column in a table in SQL Server that holds a Base64-encoded text string, which I would like to decode into its plain text equivalent. Does SQL Server have any native functionality to ...
GWR's user avatar
  • 2,817
34 votes
5 answers
89k views

Difference between Full backup and Copy-only full backup

I saw in the SQL Server Central thread Does a full backup truncate the log? that full backup does not truncate the log: No. Neither Full or Differential backups truncate the transaction log. - Lynn ...
IT researcher's user avatar
34 votes
1 answer
66k views

Change length of varchar on live prod table

I have a MS SQL Server 2008 R2 DB server currently in use with a production app. A new enhancement to the app now requires a varchar(100) column in a table to be increased in length. Can the ...
anataliocs's user avatar
33 votes
4 answers
84k views

EF Code First uses nvarchar(max) for all strings. Will this hurt query performance?

I have some databases created using Entity Framework Code First; the apps are working and in general I'm pretty happy with what Code First lets me do. I am a programmer first, and a DBA second, by ...
Nate's user avatar
  • 1,697
33 votes
5 answers
100k views

The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values

One of INSERT INTO script is written as following. INSERT INTO tableName (Column1, Column2,....) VALUES (value1, Value2,...), (value1, Value2,...),.... Following is the error we are facing on ...
Aasim Abdullah's user avatar
33 votes
1 answer
59k views

Cannot execute as the database principal because the principal "dbo" does not exist [duplicate]

I restored a backup of a database from SQL Server 2008 R2 to SQL Server 2012. When I try to access a particular page of my application, I get this error: Cannot execute as the database principal ...
Liquid's user avatar
  • 433
33 votes
2 answers
78k views

Is it useful to have the SQL Server instance root directory on a separate drive?

I know it's possible to change many of the default paths when installing SQL Server, and generally when I do an install I change the data and log folders to be on separate drives (typically D and E), ...
adhocgeek's user avatar
  • 473
32 votes
2 answers
41k views

What Problems Does an Eager Spool Indicate

Currently running on SQL Server 2008 R2 I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up. My understanding of spooling ...
Nick Vaccaro's user avatar
31 votes
4 answers
33k views

Adding columns to production tables

What's the best way to add columns to large production tables on SQL Server 2008 R2? According to Microsoft's books online: The changes specified in ALTER TABLE are implemented immediately. If the ...
sh-beta's user avatar
  • 609
31 votes
3 answers
8k views

What is a deterministic method for evaluating a sensible buffer pool size?

I'm trying to come up with a sane way to understand if the max server memory (mb) setting is appropriate (either should be lower, or higher, or stay the way it is). I am aware that max server memory (...
Hannah Vernon's user avatar
  • 70.2k
31 votes
2 answers
14k views

I/O requests taking longer than 15 seconds

Usually our weekly full backups finish in about 35 minutes, with daily diff backups finishing in ~5 minutes. Since tuesday the dailies have taken almost 4 hours to complete, way more than should be ...
Mark S. Rasmussen's user avatar
30 votes
4 answers
56k views

Why does transaction log continue to grow in Simple recovery mode with nightly backups

Before immediately marking as duplicate, I have read Mike Walsh's Why Does the Transaction Log Keep Growing or Run Out of Space?, but I don't think it gave an answer to my situation. I looked through ...
DerekCate's user avatar
  • 438
28 votes
2 answers
41k views

Can you use COUNT DISTINCT with an OVER clause?

I'm trying to improve the performance of the following query: UPDATE [#TempTable] SET Received = r.Number FROM [#TempTable] INNER JOIN (SELECT AgentID, ...
Kenneth Fisher's user avatar
28 votes
5 answers
56k views

Add article to transactional publication without generating new snapshot

Using SQL 2008 R2 transactional replication with pull subscribers, when we add an article, I'd like to avoid having to create an entire snapshot (the db is ~80 GB, so this takes hours). From this ...
user175528's user avatar
28 votes
2 answers
30k views

How to write a query which finds all circular references when a table references itself?

I have the following schema (names changed), which I cannot change: CREATE TABLE MyTable ( Id INT NOT NULL PRIMARY KEY, ParentId INT NOT NULL ); ALTER TABLE MyTable ADD FOREIGN KEY (ParentId)...
cubetwo1729's user avatar
27 votes
2 answers
72k views

How to partition an existing non-partitioned table

I have an existing table with data: dbo.Test (col1,col2,col3....) ON [PRIMARY] I need to change this table to be partitioned like this: dbo.Test(col1,col2,col3....) ON Ps_Date(Col2) How I can I ...
343's user avatar
  • 273
27 votes
3 answers
26k views

Does the partition key also have to be part of the primary key?

I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, ...
AngryHacker's user avatar
  • 1,941
26 votes
8 answers
50k views

Trying to find the last time that a value has changed

I have a table that has an ID, a value, and a date. There are many IDs, Values, and dates in this table. Records are inserted into this table periodically. The ID will always stay the same but ...
SqlSandwiches's user avatar
26 votes
2 answers
41k views

Changing a column from NOT NULL to NULL - What's going on under the hood?

We have a table with 2.3B rows in it. We'd like to change a column from NOT NULL to NULL. The column is contained in one index (not the clustered or PK index). The data type isn't changing (it's an ...
Randy Minder's user avatar
  • 2,002
26 votes
3 answers
115k views

How can I tell how many Cores SQL Server is actually using?

I have two servers running SQL Server. Server 1: SQL Server 2008 R2 Express (4 core) Server 2: SQL Server 2012 Developer Edition (8 core) As far as I am aware SQL Server 2008 R2 Express should only ...
user avatar
25 votes
2 answers
37k views

Need to understand parallel query execution error

Today we experienced a degradation in performance on our production sql server. Durring the time this occurred we logged several "The query processor could not start the necessary thread resources ...
Lumpy's user avatar
  • 2,109
25 votes
3 answers
8k views

Should I stop transaction log backups during a full backup?

I have two SQL Agent jobs which are scheduled to run at different intervals. The first job runs a full backup once a day. The second job runs transaction log backups every fifteen minutes. As the ...
8kb's user avatar
  • 2,629
25 votes
7 answers
126k views

SQL Server not starting at Windows startup but starts manually

My SQL Server instance's (SQL Server 2008 R2 Express) service has Startup Type = Automatic. The past couple of times I've restarted my computer the service has failed to start on its own, but it ...
Keith's user avatar
  • 535
25 votes
1 answer
143k views

Find all the queries executed recently on a database

[I am a beginner level T-SQL Programmer] [..and hopefully I am on the right stack exchange site] I would like to get a list of all the queries I executed (in the least, those I executed today since ...
Prashanth Tilleti's user avatar
24 votes
4 answers
24k views

Merge statement deadlocking itself

I have the following procedure (SQL Server 2008 R2): create procedure usp_SaveCompanyUserData @companyId bigint, @userId bigint, @dataTable tt_CoUserdata readonly as begin set ...
Sako73's user avatar
  • 435
24 votes
2 answers
79k views

How to check for Non-Ascii Characters

What is the best way to check if a VARCHAR field has Non-Ascii Characters? CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255). I tried using PATINDEX and have run into the following issue. ...
Gerhard Weiss's user avatar
24 votes
4 answers
21k views

Choosing the right algorithm in HashBytes function

We need to create hash value of nvarchar data for comparison purposes. There are multiple hash algorithms available in T-SQL, but which one the best to choose from in this scenario? We want to ensure ...
Sky's user avatar
  • 3,684
24 votes
3 answers
10k views

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil ...
Kenneth Fisher's user avatar

1
2 3 4 5
76