Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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

1
vote
1answer
32 views

What does WITH FORMAT do on a DIFFERENTIAL backup?

I could find a lot of questions about this. This is a really good question, exactly what i'm asking here. Down there, a guys asks the question with: you can have compressed and uncompressed on the ...
2
votes
1answer
30 views

How to execute a query based on the condition if a column exists?

I want to run the following query: CREATE TABLE dummy AS SELECT x, y, z FROM table1 WHERE y!=0 then I want to do the following: if z column has a non-null entry do: 1. copy the content of z into A ...
2
votes
1answer
58 views

Is database partitioning only created to get a better performance? and why should one partition a table, and a database? [closed]

I was asked if SQL Server can handle huge databases ( for them a huge database is 100GB). I know it can. I worked with 4tB databases already. But I would like to ask, thinking about really huge ...
1
vote
1answer
38 views

How to set the first row of CSV data as column name in SQL Server?

I am using BULK INSERT to read a csv file in SQL Server. Is there a way to keep the first row as column name while I am reading the file? If not, after reading the data from the csv (from second row),...
0
votes
0answers
16 views

SQL Server 2008 R2 SSRS Failure sending mail: At least one of the From or Sender fields is required, and neither was found

I am trying to set up a subscription for a SSRS report in SQL Server 2008 R2 and I am getting the following message: Failure sending mail: At least one of the From or Sender fields is required, and ...
1
vote
0answers
22 views

fn_trace_gettable empty default trace rollover files

For some time now I've been using fn_trace_gettable to automatically collect and report certain events on the server. Function is being called with following parameters: ::fn_trace_gettable('C:\...
0
votes
0answers
19 views

Linked Server Provider Options - Do I need to restart the SQL service (or server) after modifying any of these options?

Under Server Options -> Linked Server there are the Providers that each of my linked servers use. If I right click on a specific Provider and modify the Provider Options, such as "Dynamic parameter" ...
-2
votes
1answer
35 views

Query for max CPU, Memory and System Queue length

I am looking for a query to find maximum CPU, Memory and System Queue length for last month. Any help would be greatly appreciated.
0
votes
0answers
38 views

Identity value in a restored database is different from the value in the backed up database

I am replicating (using backup instead of snapshot) from 2008r2 to 2016 and it seems to be working fine except for a few tables where the restored table identity value is less than the backed up id ...
0
votes
1answer
37 views

SP_WhoIsActive - RESOURCE_SEMAPHORE_QUERY_COMPILE

Had an issue with one of our production servers earlier today. Many transactions were suspended with RESOURCE_SEMAPHORE_QUERY_COMPILE wait. Have sp_whoisactive data logged to table every minute. Below ...
0
votes
0answers
37 views

Log file size increasing exponentially [duplicate]

We have db in full recovery mode , taken full backup and taken backup of log file then shrink the log file but after 24 hour size of log file increasing exponentially . Please check following sql ...
2
votes
0answers
27 views

Query involving TVF very slow on SQL Server 2008

I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because ...
0
votes
1answer
27 views

Really Small details about storage ( data size ) on columns

I'm doing tests to better understand those minimal technical details about tables, pages, and etc. I'm using sp_spaceused to better understand this with INT values ( I will do a lot of more tests ...
0
votes
1answer
27 views

How can I get the last second of the last month as a default parameter value?

I am trying to get the last second of the last month as a default value for a parameter. I can get the last day of the last month from this query on social.msdn.microsoft.com last month last day ...
0
votes
1answer
32 views

SQL Server 2008 R2 Auditing

I am new to database administration, and I need to implement auditing of all users of multiple databases. Can we audit multiple users of a multiple database by creating just one audit specification? ...
0
votes
1answer
46 views

why when I change a column from DATETIME to DATE, I can't see any storage improvement?

Long story short. I have a table with more than 10millions rows with a datetime field. WE can change that to DATE ( because date uses 3bytes, and datetime 8). I thought if I: alter table table1 ...
1
vote
2answers
30 views

SQL Server 2008 R2 - How does the SQL Trigger actually work in this case?

I have a table named _Test which has the following values stored in it. Primary Key : Id I also have designed a trigger on that table ALTER Trigger [dbo].[_Test_Data_Save] On [dbo].[_Test] For ...
0
votes
0answers
48 views

Cleaning up the environment on old cluster after migrating to newer SQL Server cluster

We migrated many instances of SQL Server 2008/R2 on Windows 2008R2 and 2012 on Windows 2012 to newer Windows 2016 with SQL Server 2014 and 2016 versions. The old clusters were configured with either ...
1
vote
1answer
33 views

Is there a way to find all Logins, and all database users this login have?

I'm trying to join, every login and permissions tables. Looking over the internet but I just can't find something to list like: LOGIN | USER | DATABASE_THE_USER_HAS_ACCESS This is because I would ...
0
votes
1answer
38 views

Do I need to detach a database to attach it?

I am trying to move data from one SQL Server server to another (located on another computer). I know of the detach/attach method, but I don't want to disturb the prod environment (detaching a prod ...
0
votes
0answers
19 views

Snapshot replication from SQL Server 2012 to SQL Server 2008R2

We are using snapshot replication as a standby to replicate prod data for a month from 2012 to 2008R2 after migration from 8R2 to 2012, just as a rollback process. Using snapshot replication for 10 ...
0
votes
1answer
50 views

Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes?

I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/ In it, she briefly ...
1
vote
1answer
234 views

GRANT select on function FN_DUMP_DBLOG in SQL Server without GRANTing sysadmin

I'm trying to GRANT permission for an SQL login to access the functions fn_db_log and fn_dump_dblog but GRANTing SELECT on the function still fails with error: Msg 9010, Level 14, State 1, Line 3 ...
1
vote
2answers
49 views

Does SQL Server automatically update the foreign key relationships between tables when one of those tables is renamed?

What happens to the foreign key relationships on a table if that table is renamed?
3
votes
2answers
60 views

Making collations uniform across multiple SQL Server 2008 databases [closed]

I have been assigned to try and make collations uniform across databases. Currently, it's a mess, different collations everywhere. This is mostly solved by using collate database default in queries ...
0
votes
1answer
20 views

Delete records in Peer to Peer Replication in SQL Server 2008 R2

We have a peer-to-peer replication setup on our real time servers I want to delete records from some of the tables that are involved in the replication. I'm using a stored procedure to do this. Some ...
1
vote
1answer
384 views

The EXECUTE permission was denied on the object 'SPROC', database 'DATABASE', schema 'dbo'

I have a few users that are unexpectedly receiving this error message today when they attempt to refresh a stored procedure via Excel: The EXECUTE permission was denied on the object 'SPROC', ...
3
votes
1answer
48 views

Ensuring Uniqueness Across Columns

I'll lead with this: Perhaps the database design is wrong, so I will accept a re-design as a solution! Consider the following table: ID | TypeA | RefA | TypeB | RefB -- + ----- + ---- + ----- + ---- ...
0
votes
0answers
30 views

Is there a better way to DENY SYS schema and INFORMATION-SCHEMA to DBO user?

I could fix this issue with the following steps: Create login Create DBO user and assign it to a specific database ADD this user to the MASTER database ( create user x for login x) DENY SELECT On ...
5
votes
1answer
254 views

Actual rows read in a table scan is multiplied by the number of threads used for the scan

I'm running into quite a strange problem. I'm running the same script to generate data and do some matching later on, on an older 2008R2 instance. The last query (an UPDATE) does a single table scan ...
1
vote
1answer
104 views

SQL Server Upgrade - A new Approach for Upgrading

Microsoft's technical guide for SQL Server upgrade references two upgrade paths: in-place and side-by-side. I don't see a third, visibly more viable/convenient option, which is to do an in-place ...
0
votes
2answers
28 views

How can I track if a trace is executing?

I'm sure there's a trace here running and creating logs. I have no idea who did this in the past ( Its here since my first day in this company ). There's no trace of it on sp_whoisactive. Activity ...
2
votes
2answers
83 views

Query takes too much time (2 stages. each stage fast, together - nightmare)

MS-SQL 2008 R2 (Dell, 2 CPU) This query takes two minutes to run, which is too slow. SELECT * FROM t_events WITH (NOLOCK) WHERE order_no = 2783190 and event_no <= ( SELECT MIN(event_no) ...
1
vote
1answer
27 views

Creating a Trigger to Update Matching Records in Another Table

Morning, All - I'm currently working on some SQL logic, and wanted to get some input on it. What I'm trying to accomplish is the following (Psuedo-code): When a record is inserted into Table A - IF ...
3
votes
1answer
778 views

MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)? If the first SELECT ...
1
vote
3answers
497 views

Efficient way to move 170 GB table from production to another server

I have a 170 GB table that is no longer referenced. I want to move that table to another server. I tried these options in staging: select into 8 GB table to another db - 1.47 min SSIS package - 6 ...
1
vote
0answers
77 views

SQL Server 2008R2 SP3 c0000005 EXCEPTION_ACCESS_VIOLATION

I’ve problem with database on sql server 2008 R2 SP3 (10.50.6560.0). After I run query SELECT * FROM sys.assemblies I’ve this error: Msg 0, Level 11, State 0, Line 0 A severe error ...
2
votes
0answers
120 views

why is update from a join containing zero records taking a minute and 20 seconds?

SQL Server 2008R2. I have a simple join that returns zero rows in less than a second. When I try to run an update on that join, it should also take less than a second but it is taking one minute and ...
0
votes
0answers
24 views

SQL Server Mirroring Setup

I'm setting up synchronous mirroring and I would like to get the process down to as few steps as possible. My system does a full backup of each database once a day and differential backups every four ...
2
votes
1answer
84 views

Database Mirroring failover with CNAME

I have SERVERA and SERVERB configured for Database Mirroring starting with A as Principal and B as Mirror. I created a CNAME for SERVERA and use that in my connection string. I am not using the ...
-1
votes
2answers
46 views

TSQL Query to Remove Dates in Multiple Formats from Text Values [closed]

I have a table with list of text values that contain dates in any format types. I need a TSQL query to remove the dates from each row in the table. The date samples are below. What is the best way ...
2
votes
1answer
80 views

Replication from Amazon's RDS to Postgres via DMS errors out

I have a transactional replication from SQL Server 2008 to RDS SQL Server 2016 in 2014 compatibility mode. This works okay. I now want to replicate this RDS database via DMS to Postgresql. This errors ...
1
vote
1answer
52 views

Database Replication SQL Server

We have to do database replication with SQL Server; the two database are on different servers. We achieve replication, but our scenario is when live is down so we use replicated server as our ...
1
vote
0answers
49 views

Waiting Tasks on Mirror

I just migrated my primary's synchronous mirror to a new server and I've noticed that there are 150 to 230 waiting tasks on the mirrored instance (based on the activity monitor.) I don't recall if ...
0
votes
1answer
55 views

SQL Express bottlenecks

We have a client running SQL Express. The line of business application appears slow at times and I am trying to diagnose the cause. I suspect it may be because of the Express version. If the client ...
0
votes
1answer
32 views

Best idea to deal with historic data ( Partitioning? )

We have a database with almost 1 billion rows. 90% of this table is historical data. We don't use it, BUT if we need to use it, it needs to be right here, right now (we work with banks, so, they ...
-3
votes
1answer
65 views

calculate group by running total in sql server 2008 [closed]

I HAVE SOME SAME SAMPLE DATA IN MY TABLE ID | HOUSENO | AREAR | CURRENT | TOTAL_DEMAND | TOTAL_RECIVED ---------------------------------------------------------------- 1 | 1/10/1 | 0 | 165 ...
0
votes
0answers
15 views

Transaction Replication from multiple servers is truncating the destination table

I am trying to centralise the results of sp_Blitz from multiple servers into one centralised server using table level transaction replication. At the moment I am just testing this to make sure it ...
0
votes
0answers
43 views

Firebird ODBC Setup - Linked server from SQL Server 2008 to FireBird Database

I've been reading about this all morning. It says I need to rename FBclient.dll to GDS32.dll. I'm using a x64 server and I just can't find the correct DLL file to use in the "client" field as above: ...
3
votes
1answer
277 views

Does SQL Server 2008 R2 10.50.6560.0 Support TLS 1.2?

We recently upgraded a non-production SQL Server 2008 R2 system to SP3, and then Windows Updates installed the latest Security Update (10.50.6560.0). I see that TLS 1.2 support for 2008 R2 is ...