This Tag is for SQL Server 2008 R2, major build version 10.50.xxxx

learn more… | top users | synonyms

7
votes
3answers
534 views

Verifying password policy on existing users

I've recently come into an environment where a lot of databases logins do not have the enforce_password_policy flag enabled. An upcoming audit is necessitating the verification of these logins' ...
0
votes
1answer
34 views

Replication - Initialize with Snapshot when using filtered rows

We have a large database that is being Transactionally replicated (one way) between sites. The Publication has a date based filter so that it doesn't replicate old data. I need to add a new ...
6
votes
2answers
141 views

How to move a fairly large tsql table from one server to another server?

I have been working on thinking of ways to move a 40gb table from one SQL Server box to another SQL Server box. They are at separate companies without an intranet linkage and obviously without linked ...
1
vote
5answers
60 views

Getting Data Back after Wrong Data Restore in SQL Server

I would like to inquire if there is an possible way to return lost data that stored in a Sql server Database after making a wrong database restore? I know it is extremely hard to get a positive ...
0
votes
0answers
56 views

Decimal Data Type as Primary Key

One of my tables is set up like this: create table dbo.Source ( testID decimal (10,3) PRIMARY KEY , column 2 somedatatype , column 3 somedatatype ) Whenever I perform SSIS into this table ...
0
votes
2answers
46 views

Sudden Slow Reports [closed]

we have reports on our report server that are running slow all of a sudden. They were working fine and then all of a sudden over the weekend they started to process incredibly slow. It appears that ...
0
votes
1answer
77 views

Log Shipping -Monitor Server Down - Copying happening but not restoring on secondary

I have set up a few log shipping instances, but I am not a DBA. This weekend something happened for the first time - the log shipping Monitor Server went down temporarily due to electrical issues ...
1
vote
1answer
454 views

How to get the return value from a stored procedure executed with sp_executesql

I need to call a stored procedure in another database and check the return value. The name of the other database can vary so I'm trying to use sp_executesql, but I haven't been able to find how to get ...
1
vote
0answers
38 views

tempdb contention issue and excessive pagewrites/s

Still removing the rust from my SQL Server skills, so please bear with some stupid questions here and there... Customer has a SQL Server 2008 R2 running in an Azure VM with 8 cores and 14GB RAM. ...
0
votes
1answer
41 views

SQL Server 2008 R2: From a SQL Server Log file, how do I find out what is causing an event

Problem: The attached image is a screen shot of the SQL Server log showing an event that happens every Tuesday at around 1:20 AM. This event is causing the transaction log backup chain to break ...
1
vote
2answers
78 views

Different table size on sp_spaceused and standard report “Disk Usage by Top Tables”

I ran sp_spaceused and Disk Usage by Top Tables standard report for a table. The results for sp_spaceused are: name rows reserved data index_size unused SomeTable <1301755> ...
1
vote
1answer
27 views

Permissions to Create DB's and be able to perform any function only on those DB's

I am looking for a way to allow users to create databases which only they and SA accounts will have access to (Perform any function; creating/deleteing tables, deleting the DB, etc). For example, ...
0
votes
1answer
59 views

SQL Job to be run earlier on last work day of month

Right now, I have a SQL job (within SQL Server 2008 R2) that dumps a complex select statement into a table every weeknight (excluding weekends) and it works great. Due to our company's Month End ...
0
votes
0answers
36 views

Powershell error: “Cannot convert the ”[DBADEV\SQL2008r2]“ value of type ”Microsoft.SqlServer.Management.Smo.Server“ to type… ”

Error I'm getting from Powershell: Cannot convert argument "srv", with value: "[DBADEV\SQL2008r2]", for "SqlBackup" to type "Microsoft.SqlServer.Management.Smo.Server": "Cannot convert the ...
1
vote
1answer
30 views

Need advice on Primary and Clustering Keys of a table

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table: Data from multiple databases will be consolidated in this table for the purpose ...
4
votes
2answers
224 views

External Memory Pressure

I have a few "gaps" in my understanding of external memory pressure. I've found quite a bit of helpful information online, including this info from SQLSkills.com. SQL Server has a built-in ...
0
votes
1answer
29 views

Setting a default principal server in SQL Server 2008

This should be a pretty simple answer but I can't find anything on it searching the web. SQL Server 2008 R2, mirrored with a witness: Node1 (Principal) Node2 (Mirror) Node3 (Witness) If Node 1 ...
2
votes
0answers
15 views

Filegroup design for Document Table for BLOB Storage

I have a table for storing documents as VARBINARY types in my database (I'm not using filestream). My database is setup with only one data file, but I am wondering if there is a benefit to creating a ...
4
votes
1answer
211 views

Can SQL Server 2008 R2 extended events execute stored procedure or query?

I'm trying to use extended events to track queries that never complete using Event Pairing as a target. (see this question I asked a few days ago). The event pairing target should let me find ...
2
votes
1answer
186 views

How to output more than 4000 characters in sqlcmd

I've made a stored procedure restoredatabase that returns code to restore a database. The output script looks like this: restore database [DB] from disk='F:\FULL1.bak' with norecovery restore ...
6
votes
2answers
306 views

Unexpected Implicit conversion in a procedure

I have a procedure like this (simplified): CREATE PROCEDURE test @userName VARCHAR(64) SELECT * FROM member M INNER JOIN order O ON M.MemberId=O.MemberId WHERE M.Username = @userName There is a ...
1
vote
0answers
147 views

System.Data.SqlClient.SqlException (0x80131904): SQL Server detected a logical consistency-based I/O error: incorrect checksum

I am migrating client's database to my hosted environment, the client runs SQL Server 2008 R2, and we run SQL Server 2014. I am stuck on restoring one log file, which left below error message when I ...
3
votes
1answer
90 views

SQL Server index causing incorrect behavior on cursors

I have a SQL Cursor that's behaving incorrectly as soon as we added an index to a table. Here's a trimmed-down example of the query causing issue: DECLARE @CUR_CRDATE NVARCHAR(8) DECLARE ...
2
votes
0answers
50 views

Is it possible to trace queries that never complete with SQL Server 2008 R2?

Running SQL Server 2008 R2. We have some queries that typically take less than one second to complete, but, occasionally, they are taking longer than 30 seconds which causes a client timeout. ...
0
votes
1answer
40 views

copy table with all constraint

how I would copy a table with all constraints? I run select * into myNewTable from baseTable where 1=0 This copy a table but no with constraints
0
votes
1answer
45 views

Set Identity as (11 - 2) in sql table

I have a issue, I want to set the ID column, where the ID should start from 11-2 then it should auto- increment by 1 like 11-3, 11-4. I am using sql-server 2008 R2. Please help
0
votes
0answers
62 views

can not login to SQL Management studio using “SQL Server Authentication”

i am able to login to sql management studio 2008 r2 using "windows authentication", but if i chnage the Authentication type to be "Sql server Authentication" instead of "windows authentication", i ...
1
vote
1answer
50 views

upgrade of 24/7 OLTP [closed]

Our primary db is a 24/7 OLTP db on SQL2008 R2 on an Active / Passive Windows cluster. It is a VLDB and uses almost all SQL functionality (including Service Broker and Mirroring), acts a Publisher and ...
-1
votes
2answers
214 views

How to run multiple select statements in Data flow task in SSIS [closed]

I am migrating data from one database to another database using SSIS package and i need to write multiple select statements for same table depending on conditions as mentioned below. Actual Queries i ...
1
vote
0answers
34 views

Rolling back a SQL Server 2008R2 Database stored in SQL Server Express [closed]

We had an accident where a bunch of data was deleted from one of our products. The product removed a bunch of lines from various tables about 12 hours ago. There is no good backup and I try to ...
0
votes
0answers
91 views

The Local Security Authority cannot be contacted

I am not very expert with SQL Server but I have to use to for one of my application development. Issue is that when I open the PC in morning to start my work, my application couldn’t make connection ...
0
votes
1answer
37 views

SQL Server collecting SQL statements using particular index

I'd like to capture SQL statements that uses a particular index. Is there a way I can utilize SQL Profiler or extended events to do so? I know I can view the plan cache to get a gist of the queries, ...
0
votes
0answers
142 views

Database Role Membership: db_executor vs db_datareader and db_datawriter in SQL Server 2008R2?

I have a couple of users who have only public and db_executor memberships in their Server Login object, User Mapping properties in the one database (let's call it A), now that our front end ...
0
votes
1answer
50 views

Any tips to watch out for when setting one of my databases to Read Only?

Our front end application uses\ pointed to a different database now in SQL Server 2008R2 in the same instance, so to prevent users from accidentally updating data in the old production database, is it ...
1
vote
1answer
49 views

How to grant insert in a table that is in another database on SQL Server 2008 R2?

On SQLServer 2008 R2 I have 2 schemas: DatabaseA and DatabaseB. On DatabaseB I have a table named TableB. Whenever tableB is updated, a trigger will insert a value in the tableA (that is on ...
0
votes
0answers
53 views

lock request time out period exceeded. When revoking db_owner access

I am trying to revoke db_owner access of an user and I am getting this below error! I have no active transactions User (whom I want to revoke access) is not connected to any db DataBase owner is ...
0
votes
1answer
190 views

Can mutli-threading java client cause high CPU utilization of SQL Server 2008?

I am a Java Developer, not a DBA, and would like to consult about an issue encountered in high CPU utilization in the SQL Server 2008 (not in the Java app server). The Java client uses ...
1
vote
1answer
89 views

Applying Quotes Across Multiple Lines

In SQL Server Management Studio you can use the shortcut CTRL + K > C or CTRL + K > U to comment/uncomment lines in bulk. Is there a similar keyboard function that applies single quotes across ...
1
vote
3answers
98 views

Script to drop all databases except one not working

I'm trying to drop all databases but one on my test server. For this, I'm using the below script but it gives error: EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') ...
1
vote
2answers
53 views

SQLOS Resource Monitor and QueryMemoryResourceNotification Win API

Here's some info about SQLOS and the Resource Monitor I found from Jonathan Kehayias: As a general rule, SQL Server will use as much memory as you can give it, and it will not release the memory ...
0
votes
1answer
22 views

Slow Full Text When using multiple full text searches

Thank you in advance for your help. I have been going bonkers. We recently made a change to our code by adding an additional contains to the below query. This has had a real negative impact on ...
3
votes
2answers
505 views

RAID10 vs. RAID5 for data files

We are trying to evaluate the potential performance improvements that could be gained by switching an existing applications .mdf files to a RAID10 LUN vs. the existing RAID5 LUN they are living on ...
0
votes
1answer
26 views

Clustering a heap by date in stead of unique composite key

I've been working on improving the performance of a large reporting database. This database is 2TB in size, and one of the larger tables is a heap with three non-clustered indexes on it. The ...
0
votes
1answer
45 views

Best practice to recover an CRUD statement to a linked server if connection is lost

I am looking for the best practice for the following scenario. We have a CRM in our company. When an employee updates the record of a company, there is trigger that fires a stored procedure which has ...
0
votes
1answer
87 views

Recommended number of index for a table

I've a table with many columns (~70) which contains computer's configuration. It contains approximately 50 000 rows. This table is very requested and all these column too. I use to run the following ...
0
votes
0answers
20 views

Load data into central database

We have 150 databases at different locations. These databases need to be move into central database. currently, i am using transactional replication, but facing lot of issues. are there any other ...
0
votes
2answers
89 views

connect to server with IP address running sql-express instance through sql-management studio

I have my VPS server in a remote location with ip address 172.16.11.2 (for example) having two instances of sql-server express running. one instance is Microsoft SQL Server 2008 R2 (SP2) where I ...
2
votes
1answer
105 views

Differentiate SQL Server Express from SQL Server installed in a server

I own a VPS server and I was told SQL Server 2008 R2 Express edition was installed on Windows OS 2008R2. When I opened the SQL Server configuration manager I saw this: I thought I should see only ...
1
vote
0answers
153 views

SQL Server 2008 R2 Cluster Service pack 3 fail to failover node

I have a SQL Server R2 cluster on Windows 2008 R2 boxes, and today i was to install SP3. I installed it without any errors and rebooted the passive node. When i then try to failover to the passive ...
0
votes
0answers
27 views

Disk space requirements for SSAS database backup

Can anyone advise how I can gauge the disk space required to backup a SSAS database? I have a database that is as per SSMS estimate 146GB in size. The contents of the data folder amount to 143GB and ...