Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
3,778
questions
5
votes
2
answers
170
views
Does SQL Server 2008 R2 always fail with a severity level 20 error with certain large text manipulation?
When we run this on our SQL Server 2008 R2 instances, it causes a severity 20 error every time. It does not have that issue on our SQL Server 2016 instances.
Query:
DECLARE @v varchar(MAX) = REPLICATE(...
- 1,853
1
vote
2
answers
179
views
SQL SERVER ERRORLOG FILE UTILIZING THE FREE SPACE
I'm stuck up in one of the issue.
I'm Using SQL SERVER 2008 R2.
The SQL SERVER Log file is utilizing the disk space.
When ever I clear the space in the DRIVE, the SQL SERVER Error log file starts to ...
- 11
0
votes
0
answers
482
views
SSIS - DMA doesn't support migration assessment to SSIS in local SQL Server 2019
we will migrate from SQL Server 2008 R2 to SQL Server 2019 in near future, we already run DMA to analyze database, however DMA 5.2 doesn't support migration assessment type for intergration services (...
2
votes
1
answer
132
views
Why do SQL Server auto-created statistics sometimes include the primary key?
I had always understood that SQL Server auto-created statistics only include one column. In other words, they are not multi-column statistics.
However, today I came across some auto-created ...
- 133
0
votes
3
answers
722
views
Auditing of database connections in SQL Server 2008 R2
We are finally planning to close down our last SQL Server 2008 R2 server. Before we can proceed with this we need to track what users are connecting to which databases. We have tried different options ...
- 345
-1
votes
1
answer
1k
views
Increment date by 1 day based on another column's date from previous record
I am using SQL server 2008 R2 and I don´t have LAG and LEAD functions.
I have this inicial table and I need that StartDate is equal to EndDate from previous record plus 1 day.
See Result Table.
...
- 159
0
votes
1
answer
430
views
MSSQL - Insert Into adding data into the middle of the table? [duplicate]
A bit of a random one here... I've got a basic table that I'm using for temporary data inserts - and for that reason, I don't need or want a unique ID - however, I'm seeing odd behaviour! (or it feels ...
- 1
1
vote
1
answer
442
views
SQL Native Client Upgrade
Application server for CRM has SQL native client 2008 version installed. I have installed SQL native client 2012 side by side as need to connect to an sql availability group listener. How do I get the ...
- 11
0
votes
1
answer
49
views
Reasons For Slowness in SQL Server 2008 R2 Express Edition [closed]
We are observing intermittent slowness in our SQL Server. We ran sp_who2, sp_who3 stored procedures for checking CPU/IO usage but did not see any spikes/abnormal values. What else could be the ...
- 3,131
0
votes
1
answer
590
views
Install PFX certificate on SQL 2008 R2
We have a MS SQL 2008 R2 service running on a Windows 7 computer. I've developed a webapplication that runs on a Digital Ocean droplet. This droplet makes a connection with this computer through a ...
- 103
2
votes
3
answers
585
views
Sort Records in Original Order After UNION ALL
My question seems to have been worded poorly the first time around, so this is my attempt to explain it more clearly + provide table structure and data below.
Please consider table dt:
Create Table dt
...
- 23
3
votes
1
answer
2k
views
Job management: How to get the next running date by querying MSDB
Table sysjobschedules contains 2 columns next_run_date and next_run_time. With the help of sysschedules and column enable it should be easy to determine the next running date/time of a specific job.
...
- 131
-2
votes
1
answer
38
views
Index size is 598GB and database size is 609GB. whether the index contains data?
The SQL server database size is 609GB and one of the index (clustered)size is 598 GB and the particular table size is 604GB. whether the index contains data? why the index is this much big?
- 161
1
vote
1
answer
1k
views
Select Query slow for Non-clustered index
I am using SQL Server 2008 R2. Select query is slow when non cluster index is used in the table compared to table without index.
Query Used:
select * from table order by customer_name
It is too ...
- 3,131
1
vote
1
answer
521
views
Will Blocking cause timeout issue
When I check in sys.sysprocesses in SQL 2008R2, a number of blocking sessions are there. Most of them are insert, update, and delete statements, with lock-related wait types.
Will this cause time out ...
- 161
0
votes
1
answer
253
views
Another question about finding the last time a value changed
I know there is a simple answer to this question but just can't get my head round it. I have a table with datetime and int columns.
| TimeStartTz | RowNumber | RateID |
|--------------...
- 1,177
1
vote
0
answers
2k
views
Ok to remove SQL Server Compact 3.5 from a SQL 2008 R2 Server?
We still have one production SQL 2008 R2 Server. Yes, there are plans to upgrade soon, just not immediately. In the meantime, our Vulnerability reports are flagging the server as vulnerable because ...
- 111
0
votes
0
answers
155
views
SQL Server Differential Backup Size
The setup is SQL Server 2008 R2:
Full Backup - Weekly ~1.1TB
Differential - Every 4 hours ~66GB
Transactional - Every hour
What I want to know is why is the very first differential backup about ...
- 131
-2
votes
1
answer
57
views
SQL Services are off but can access and query from other server on the same domain. Why? [closed]
Using configuration manager, I disabled the SQL Server Services for a SQL Server 2008 R2 instance. Yet, I can still connect to the instance from its replacement server, a SQL 2019 box, and query the ...
- 131
1
vote
2
answers
333
views
in-place upgrade native SSRS without the SQL Server portion
We have a single server (2012R2) running SQL 2008R2 + reporting services native mode 2008R2
Can the reporting services portion be upgraded alone without the database engine?
Or can the SSRS db be ...
- 111
1
vote
0
answers
6k
views
"The client and server cannot communicate, because they do not possess a common algorithm" error
The full error message is:
System.Data.SqlClient.SqlException (0x80131904): A connection was successfully established with the server, but then an error occurred during the login process. (provider: ...
- 427
2
votes
2
answers
3k
views
Assign number incrementally, reset when value changes
declare @t table (scanCode varchar(6), dates datetime, flag varchar(1))
insert @t ( scanCode, dates, flag)
select '182086','01 Jul 2020','P' union all
select'182086','02 Jul 2020','P' union all
...
- 21
0
votes
1
answer
240
views
SQl server 2008R2 Performance issue after SP3 update
We have recently applied SP3 KB2979597 and Security Update Patch KB4057113 on Sql server 2008R2 but after that our couple stored procedures runs slow, it's taking almost 4 to 5 hours, but normally it ...
- 11
2
votes
1
answer
421
views
Can't understand difference in fulltext results - contains, contains with wildcard, freetext
I have a table with an fulltext index on the column named Filecontent.
The table has a row where content contains "W 917". For context, the content column on this rows contains much more ...
- 31
3
votes
1
answer
988
views
How can INDEX rebuilds be going parallel when MAXDOP is set to 1
I am periodically running into a THREADPOOL wait issue with a SharePoint data store of hundreds of databases using a SQL Server 2008R2 Standard Edition instance (recently migrated to an Azure VM). It ...
- 2,151
0
votes
1
answer
221
views
How to connect to Microsoft Dynamics Rms 2.0 Running sql2008r2?
Would anyone know anything about Microsoft Dynamics Rms 2.0 (2012)? We are having trouble connecting our sql server to the client side cashier (windows 10 pro) this software sunset in 2016 I believe ...
- 1
0
votes
3
answers
6k
views
How to remove database from explorer after deleting .MDF and .ldf files
I wanted to know if I can delete or remove the database shown in the object explorer after someone has manually deleted the MDF and ldf files from the database folders.
2
votes
2
answers
381
views
How to remove dbcreator server role?
I was able to add the dbcreator server role to a user of a SQL Server DB, thanks to this nice question/answer: create-database-permission
How can I REMOVE that permission? Is there a stored procedure ...
0
votes
1
answer
31
views
Change the sql table output - Rows to Columns [duplicate]
I have a result from recursive query that gives me Table1 (SQL Server 2008 R2).
I need to convert the Table1 to Table2 and I don't know the best and simplest approach to do it.
Any suggestions?
Table1
...
- 159
0
votes
1
answer
112
views
Rebuild or Reorganize index
I am using SQL server 2008 R2.
The DB is normally used from 9 am to 9 pm and the maximum db size is 10 GB.
Whether rebuild index offline causes any problem, if it is done during midnight (when no one ...
- 3,131
1
vote
0
answers
307
views
Missing tables in AdevntureWorksDB
Recently I found some old questions that involve querying data from AdevntureWorksDB across several operations(DML,DDL).
One such question was :
Create and execute a query that computes the total ...
- 131
0
votes
1
answer
259
views
SQL server row count
I had an activity for restoring a full database in SQL server 2008.
I had to restore the database will norecovery since I had to restore the differential backups as well which will be shared everyday.
...
0
votes
1
answer
101
views
SQL query speedup/optimization
I am using SQL Server 2008 R2.I have tried to improve the following query in different methods.
Method 1:
DBCC DROPCLEANBUFFERS
SET STATISTICS TIME ON
SELECT count(*) FROM [prglog].dbo.[errorlog] ...
- 3,131
0
votes
1
answer
30
views
Replication types
How can I find out, if my current replication pattern is any of following or not? I am using SQL2008R2.
1)Peer to peer replication
2)Bidirectional transactional replication
- 21
1
vote
1
answer
676
views
sp_delete_backuphistory not working
I am using sql Server 2008 R2.
We are using below query for reducing the msdb size.
use msdb exec sp_delete_backuphistory '01-MAR-2020'
When i execute this query in server whose msdb size is 15GB ...
- 3,131
0
votes
1
answer
213
views
SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn
I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2.
There are various restrictions if one wants to ...
- 21
0
votes
1
answer
61
views
Xquery Speedup/Optimization
I am using Sql server 2008 R2. To speed up the query execution in sql i have changed xquery part of it.I Have used cast method to query xml column and i got following results.
DBCC DROPCLEANBUFFERS
...
- 3,131
-1
votes
3
answers
76
views
how to count second col if first col value is null and avoid duplicate count
I'm making sales statistic report to calculate scanned documents station wise using below Query:
SELECT
RIGHT(CONVERT(varchar(11),DATEADD(MONTH,DATEDIFF(MONTH,0,InvoiceDate),0),13),LEN
(CONVERT(...
- 109
0
votes
0
answers
2k
views
Error “There is insufficient system memory in resource pool 'internal' to run this query.” even after updating to SP3 on SQL Server 2008 R2
We are getting this error :
“There is insufficient system memory in resource pool 'internal' to run this query.” everyday, and the error lasts for about a minute, after that the queries are executed ...
- 3,131
0
votes
1
answer
371
views
Can I change database compatibility level from 80 to 90 or higher with log shipping?
I have a database on SQL 2008 R2 with compatibility level 80 and I want to change it to 90 or 100. I assumed that after changing, I may find a regression in our application's performance.
Many backups ...
0
votes
1
answer
212
views
what is the impact of changing log shipping schedule timing jobs?
I have two severs primary and secondary log shipped server , after configuring it, I noticed that the latency of last copied file and last restored file increased and the alert of monitor sever fired ...
1
vote
1
answer
50
views
Where Database Alerts & Operators Exist In SQL Server?
My SQL Server database server has crashed. I have system databases backup. I will reinstall the sql server but i don't know how to find my operators, alerts and config settings. Is there a way to find ...
- 69
0
votes
1
answer
707
views
Should I install SQL Server 64 bit or 32 bit
Sorry if this question is lame.
For production purpose, i wanted to know when to install SQL Server 64 bit package and when to install 32 bit package when the client's PC has: -
32 bit OS and 64 bit ...
- 137
0
votes
1
answer
57
views
odd OS memory consumption by two nearly-identical SQL 2008 R2 instances
Instances are legacy, something we have to carry for the next 3 years, 2008 R2 64-bit, both SQL Server and Windows Server OS. Two copies on two nodes.
Both servers are set to consume "Maximum server ...
- 123
3
votes
3
answers
442
views
Select rows based on values from 1 column being the same?
An example of my database table
film id | scene id | value id
-----------------------------
0 | 0 | 7
0 | 1 | 1
0 | 1 | 0
1 | 2 | 8
...
- 33
0
votes
0
answers
133
views
Weird logging message problem
We've got a stored procedure that's running in a SQL Server agent job on multiple servers. It has the little block of code to log debugging information. It's the same code on both machines, but on ...
- 207
0
votes
1
answer
14k
views
Validation logic for Mobile Number Column in SQL Server
I am creating a validation script and need help with mobile number validation logic with below details. Mobile number must be:
10 digits in length.
Cannot begin with a 1 or a 0 (For example:...
0
votes
0
answers
40
views
Review of scheduled bulk deletion on a production database
I have a EU privacy requirement to delete production data regularly. The script runs weekly when the traffic is quiet. I wondered if there is any way to improve this script. I can't delete directly ...
- 299
0
votes
1
answer
194
views
Restrict server login by user AND machine (within or not within a domain): is it possible?
I'm trying to setup a SQL Server 2008 R2 instance, restricting access to my database so only specific users on specific machines can login there.
Let's say, we have 3 devs: John, Jill and Steve, with ...
- 103
0
votes
1
answer
1k
views
Enable sysadmin user to restart SQL server from management studio
I have SQL server login with sysadmin role, but I cannot stop and start SQL server when I connect from remote management studio, just from local server management studio. How can I enable it?
I use ...
- 3