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

learn more… | top users | synonyms (2)

4
votes
1answer
50 views

SQL Server Memory Determination

We will be modifying a SQL Deployment in our Hosted Environment. We have a solution which is currently configured to use one Application (and not SQL) System Database (which holds Security and System ...
1
vote
0answers
63 views

How do I enable modify column order in Datagrip [closed]

How do I can enable the up and down buttons for column order in DataGrip? I'm using DataGrip 1.0, Build #DB-143.1410, built on December 15, 2015 with a SQL Server 2008 database. As an example, for ...
4
votes
2answers
64 views

Performance of a self join in a temporal table with a single day's difference between end_date and start_date

Title says it all really. A pretty standard temporal table design has a start_date and an end_date where the end_date is the day before the next record's start_date. In this case, there is an ...
1
vote
1answer
29 views

Data Collector Component Name Doesn't Change After Updating SQL Server Name

I cloned a SQL Server, and changed the name of the machine and SQL Server, but the old name continues to show up as a component name in the disk usage data collector, which I believe is what's causing ...
0
votes
0answers
33 views

Can I install 32-bit SQL Server 2008R2 drivers on Windows Server 2012?

We have a 32-bit app built using Powerbuilder that needs to connect to SQL Server 2012, but the version of Powerbuilder used by the application can only connect to SQL Server up to version 2008R2. We ...
0
votes
0answers
20 views

General capacity guidelines for transaction log and tempdb

Are there general guidelines for the initial size of a transaction log size in SQL Server? Is there a percentage of the total database size that can be estimated for this?
5
votes
1answer
98 views

Monitor table permission changes

I need a solution to monitor table permission changes: how can I implement monitoring of every changes to granted permissions for a specific or any user on a specific table in SQL Server 2008 R2?
0
votes
1answer
45 views

If my Data, Log and Tempdb drives are on the same LUN, are they using 1 physical drive?

My SQL Server Data, Log and TempDB resides on different logical drives. But when I check the drive properties under Hardware tab, their location are located on the same Port, Bus, Target ID and LUN. ...
4
votes
2answers
34 views

How do I get a graph of request traffic history in MS SQL Server 2008 R2

We're having difficulty with an application. One of the potential causes on the table is a regular spike in database traffic overloading the database, causing DB-dependent services to back up. I ...
6
votes
2answers
176 views

DBCC Check DB issue

I have database of 99GB on this database when I am running DBCC Checkdb('IssueDB') It gives error as: Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while ...
3
votes
2answers
415 views

Why is WITH NOLOCK running as Serializable

I ran the following query: SELECT session_id,CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 ...
1
vote
1answer
78 views

Efficient way to store multi choice in a column with few options

I am looking for advices on how to handle multiple choices in a column? By way of example lets say selecting a fruit for lunch. Options are Apple or Pear. And you are 100% sure those two options ...
10
votes
2answers
329 views

Good reasons to use SELECT… WITH XLOCK?

I am facing some re-occurring deadlocks, one of which is a Keylock and contains a SELECT query with XLOCK hint that becomes the deadlock victim. The other statement is an INSERT into one of the tables ...
0
votes
0answers
11 views

Why is Echo statement in SQL query not writing in text file? [duplicate]

Following code is used for writing a number in the text file but instead it is writing "Echo is ON" into the text file. SET @CMD = 'ECHO '+@Ver+'>'+@Vercheck Print @CMD EXEC xp_cmdshell ...
1
vote
2answers
58 views

My Ola Hallengren nightly backup job failed but It works fine when I execute the job manually by right clicking

I have been using Ola Hallengreen maintenance plan . It has been working great for longtime . Recently one of my nightly backup job failed repeatedly with an error: Sqlcmd: Error: Microsoft SQL ...
3
votes
0answers
71 views

Query with sp_executesql returns nothing while query without it returns the result [closed]

I'm using MS SQL Server 2008 R2 through ORM in .NET application. This ORM uses ADO.NET, so any query will be wrapped in exec sp_executesql. One of the generated queries returns nothing, but it ...
1
vote
4answers
86 views

UNION xp_cmdshell

I'm trying and failing to do something a follows select 1 union xp_cmdshell 'whoami' Is there anyway to get this work? The simpler the better i.e. one statement.
2
votes
3answers
91 views

Suddenly cannot access SQL Server database

My SQL Server 2008 R2 database suddenly cannot be accessed. When I try to view it in SQL Server Management Studio, it appears something like below: It cannot be detached and attached. I need to ...
1
vote
1answer
50 views

Move extended property comments to create table comment

I want to convert / import extended properties of table columns of a lot of tables to simple comments next to the table definition scripts columns in the table definition of a new SSDT database ...
3
votes
2answers
57 views

RIGHT() works in a SELECT but not in an UPDATE? [closed]

I am trying to run a script which does some work including stripping the leading two characters from the string inside each row of a column. To do this I am using RIGHT() and it works fine when I use ...
5
votes
1answer
40 views

SQL Query not throwing error even if the entered scale value is higher than specified

I need to capture numeric values without truncation. But the output value is getting truncated even though I specified the Scale as '2'. Column 'A' datatype - numeric(5,2) Case 1: Entered value: ...
3
votes
1answer
73 views

Kick a single user out of the database

I've seen multiple answers about kicking all of the other users but is there a way to stop the process id and kick just one user out of the database. Eg. user A B and C are connected to the database ...
-2
votes
1answer
33 views

Calculate the fee with conditions

I have two tables 1 is fee-structure and 2 is student-info, in fee-structure I have define all classes fee like.. Class tuition-fee swimming-fee cricket-fee etc. Class wise and in student-info I ...
1
vote
0answers
69 views

Performance measuring on complex query

Now that I have overworked a very very complex sql statement by going over all those little parts and investigating on their missing indexes, index design, queries column order, join vs. exists... I ...
4
votes
2answers
635 views

Replace special characters in a column with space

I'm trying to write a query which replaces the special characters with space. Below code helps to identify the rows. (alpha-numeric characters, comma and space is valid): SELECT columnA FROM tableA ...
3
votes
1answer
36 views

Are indexes bloated if using nvarchar field sizes that are much bigger than needed?

I've been provided with a database where it appears the designer has used much larger field sizes than are needed. Commonly nvarchar (128) fields have been used but the data being stored in them is ...
4
votes
2answers
640 views

How to reduce huge CXPACKET & LATCH_EX (ACCESS_METHODS_DATASET_PARENT) wait times?

Problem We have been experiencing high levels of user disruption due to SQL timeouts accross our systems since the beginning of the year. The SQL-Server instance in question has very high CPU usage ...
4
votes
1answer
50 views

How long does it take for powershell to restore a SQL Backup?

I executed this Powershell script to backup a Database, Drop that database and then restore it with a different name: $srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)") ...
1
vote
0answers
49 views

SQL Server 2008 R2 service pack steps …on cluster

I have a sp3 to apply on SQL Server 2008 R2 on windows 2008 R2 cluster with four nodes and 6 instances. Node 4 do not have any resources but is possible owner for all the instances. Steps I am ...
3
votes
2answers
320 views

Is there a Maintenance Plan that checks my database for corruption?

Is there any way to make a maintenance plan check all my databases on a single instance? Is the maintenance plan Check Database Integrity Task enough ?
1
vote
1answer
54 views

SQL Server Database backup regressed to historical date

I have the following code snippet that takes backup of my production database every night - use My_database go checkpoint go use [My_database] go DBCC SHRINKFILE (N'my_db_log', 500) go backup ...
1
vote
1answer
47 views

Phantom comparison data in SQL Server Reporting Services - How do I remove it? [closed]

I'm trying to write a query that will allow me to include or exclude a filter in my where statement based on whether a parameter passed in is set to true or false. So far my statement is IF ...
7
votes
2answers
175 views

Best way of aggregating, storing and using data in SQL Server (triggers, scheduled jobs, SSAS?)

We have a table with spatial data and some attributes and need to aggregate it so it could be used in various queries. We have the mechanism to do the aggregation on-the-fly via CLR stored procedures ...
4
votes
2answers
243 views

SQLServer:Buffer Cache Hit ratio is a good indication of POSSIBLE memory bottleneck?

I am looking for an indication (if any) that would either disregard the memory bottleneck option, or accept it, or make me investigate further. for instance: page life expectancy: SELECT ...
7
votes
3answers
356 views

what AD groups logins my user belongs to?

I am not sure if I have chosen the right title for this question. what I am really after is, given a individual windows AD user, I would like to find out the list of the windows AD groups ...
2
votes
0answers
57 views

Database Corruption - object in sys.parameters does not have a matching row in sys.objects

I'm using SQL Server 2008 R2 and I have a strange db corruption. I ran dbcc check db and the output is: Attribute (object_id=1096186) of row (object_id=1096186,parameter_id=1) in sys.parameters ...
7
votes
2answers
216 views

How to measure Key Lookups per Second on SQL Server

I am working on reducing the amount of transactions doing key lookups on our SQL Servers. First I want to define a baseline for the normal or tolerable number on that metric and as we constantly ...
-4
votes
1answer
44 views

SQL Server and msbi relation and difference [closed]

With SQL Server 2008R2, we have analysis services, reporting services, and data moving and manipulation services with SQL Server Management Studio. So why do we need SSIS, SSRS, and SSAS?
8
votes
3answers
652 views

Avoiding a divide by zero error in a computed column

How do you avoid divide by zero error in the below table? CREATE TABLE [dbo].[TblDivision] ( [Numerator] int NOT NULL, [Denominator] int NOT NULL, [Result] AS (Numerator/ Denominator) ) ...
2
votes
2answers
213 views

SQL Query is getting progressively slower

We are experiencing a very weird problem when making consecutive query calls. Not sure what info is important, so I will give as much info as I can. We have a webserver and database server through ...
-1
votes
1answer
92 views

How to retrieve and replace Null values to another column value in inner join table

There are four tables. The below i have mentioned in screenshot. From the above DeliveryOrder_TBL, NotifyPrtyID NULL then ConsigneeID should replace for that NotifyPrtyID place. How to do?? I want ...
1
vote
0answers
112 views

SQL Server Transactional Replication Distribution Database not keeping up

We have following setup at the moment: 8 Cluster servers (SQL Server 2012 Standard Edition) talking to 2,000 store servers in the field via Merge Replication. The 8 servers are replicating to a ...
5
votes
2answers
929 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() ...
1
vote
0answers
152 views

Permission Error when creating Linked Server from SQL Server to Access DB

I created a linked server from my SQL Server 2008 R2 64 bit Windows Server 2008 machine to an Access 2000 Database that resides on another server. The Linked server was created with no trouble using ...
1
vote
1answer
59 views

How can I get the whole execution log of stored procedure?

I know that how many times it was executed from execution_count in sys.dm_exec_procedure_stats.But how these values are Calculated? Is there any way to know how these values get calculated? I want ...
10
votes
4answers
465 views

Using third party tools to backup

Is there any advantages to use Third Party Tools to make Backups ? I'm not referring one in particular but I want to know if someone using them to DB greater than 200 GB. Servers are mostly MSSQL ...
0
votes
1answer
26 views

How to delete/remove the orphan user's and its schema from server

I'm new to a role as SQL Server DBA. I need help how to find out the ORPHAN user's in SQL Server 2008 R2 and how to delete/remove the orphan user's and its schema (please give the reference where I ...
3
votes
3answers
119 views

Manually create index fragmentation

Does anybody have a good approach to manually create a specific fragmentation to an index, ideally without increasing the number of rows in the underlying table? I need this for the purpose of a ...
3
votes
1answer
101 views

SQL Server - SQL Agent - Allow a Group to own a job

Scenario There are three separate groups of users in our organisations (development, support, implementation). As it stands everyone is using a single login for everything. We are starting to move ...
0
votes
1answer
41 views

Mixing Clustered Instance and Non-Clustered Instance

I'm currently managing a 2 node SQL Server Failover Cluster in Active/Passive setup. I plan on adding a new SQL Server instance which doesn't have to be on the Failover Cluster. The only documentation ...