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
1
vote
1answer
147 views

DBCC SHRINKFILE performance

I have a production SQL Server 2008 with 8 TB spread over several databases. I've deleted about 85% of the data in each database and MUST recover the disk space. The final SUM of all USED_SPACE will ...
2
votes
1answer
209 views

SQL Server 2008 Version of rows between… preceding and current row

Being a beginner, I'm having a hard time coding this particular scenario in SQL Server 2008 As you can see, The SUM column for July 2017 for example is equal to the following: August to Dec 2016 + ...
1
vote
1answer
124 views

Inefficient Indexes on Primary Keys

So I was cleaning some indexes,with some queries I made, and some that I found over the internet with ineficient indexes ( lots of updates and no seeks, scans, etc.) But something that I'm not sure ...
1
vote
1answer
29 views

Validity period of an open ssrs report

We have SSRS 2008 r2 rdls. I open an SSRS report and keep it open in my browser for more than 30 mins. If I now click any drill downs either through an action or go to report. I don't want the report ...
1
vote
1answer
95 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
199 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
78 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 ...
4
votes
2answers
2k 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),...
2
votes
0answers
188 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
449 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
48 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
1answer
352 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 ...
2
votes
1answer
70 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
32 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
124 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
2answers
107 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
52 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
69 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
53 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
72 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
261 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
25 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
264 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 ...
2
votes
1answer
1k 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
1k 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
142 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
71 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
2answers
5k 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
49 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
39 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 ...
6
votes
1answer
338 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
124 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
30 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
90 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
31 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 ...
6
votes
1answer
6k 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
550 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
365 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
134 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 ...
2
votes
1answer
561 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
198 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
280 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
60 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
157 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
180 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
69 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
196 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 ...
4
votes
2answers
2k 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 ...
2
votes
2answers
48 views

Do I need to keep SQL Server Profiler interface open?

I'm running SQL Server Profiler on SQL Server 2008R2. I opened Profiler, gave it the required settings (including a stop time 24 hours from now), and then left. Profiler was running from my desktop ...
2
votes
2answers
1k views

Unable to fully drop a database snapshot

I have a job that drops and re-creates a snapshot, it's currently failing when trying to create the database as it says the database already exists. The database however does not have an entry in ...

1
3 4
5
6 7
76