Questions tagged [sql-server-2008-r2]
SQL Server 2008 R2 (major build version 10.50.xxxx). Please also tag with sql-server.
3,783 questions
-1
votes
1
answer
108
views
SQL Server 2008 R2: Rebalance files of a database to a same size
I have a SQL Server 2008 R2 database which consists of 4 physical files:
DB_0.mdf (200 GB)
DB_1.ndf (300 GB)
DB_2.ndf (500 GB)
DB_3.ndf (1300 GB)
No tables of this database have clustered indices.
...
0
votes
1
answer
38
views
Transactional replication Snapshot agent not starting
I am trying to consider all the options before setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL ...
- 13
1
vote
1
answer
23
views
SSMS Version of 2008R2 Publisher not compatible with 20012 Subscriber
I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
- 13
1
vote
0
answers
35
views
Remove Transactional Replication Manually or Via SSMS?
I am setting up transactional replication on my 2008R2 server to 2012 distribution server to 2012 Subscriber server. All the three servers are on FULL recovery mode. Log backup is taken every 15 ...
- 37
1
vote
1
answer
28
views
SQL Server 2008R2 - data file grows back after shrinking and available to shrink again
After shrinking of datafile there is around 180Gb of free space on hard drive (prior to operation shrink file dialog also shows that it is possible to free up around 180Gb of space). After 1-2 days ...
- 113
1
vote
0
answers
38
views
Execution plan showing high hash match [closed]
i have a sql query running from database 1 that joins table company on to a view
SELECT DISTINCT RTRIM([K].[cmpCompanyCode]) AS [cmpCompanyCode]
FROM [dbo].[view] AS [K]
JOIN [database2].[dbo].[...
- 11
2
votes
0
answers
73
views
Views that were created in SQL Server 2008 R2 are encountering compatibility issues when re-created in SQL Server 2016 with identical definitions
We've created a view in 2008 R2. Provided below are the definitions for both the base table and the view.
Base table:
CREATE TABLE dbo.test(
fcrecid char(25) NOT NULL,
fctype char(1) NOT NULL,
...
- 21
0
votes
0
answers
56
views
Elapsed time of the query is abnormal in SQL server
I am trying to find the elapsed time of the query in SQLSERVER using the view, sys.dm_exec_requests. For some queries the elapsed time is more than an hour, most of the queries coming up when using ...
-1
votes
1
answer
86
views
sp_BlitzWho while executing gives error message as 'Incorrect syntax near the keyword 'COALESCE'.' in SQL SERVER 2008
To know the spids which are causing slow down/blocking each other; I am trying to use 'sp_BlitzWho' by brilliant Brent Ozar; however on executing on SQL SERVER 2008; am getting message as below.
Msg ...
-1
votes
1
answer
257
views
DMV sys.dm_exec_procedure_stats sometimes returns weird (incorrect?) values in successive object snapshots
I'm working on a small project to monitor activity and execution metrics of user stored procedures. Periodically (every 10 minutes or so), I'm taking snapshots of the sys.dm_exec_procedure_stats DMV, ...
- 3
0
votes
1
answer
454
views
MS SQL Login failed for user 'sa'.[CLIENT: <local machine>]
I changed the password of sa using the command [ALTER LOGIN WITH PASSWORD]. Then , it keeps showing the below message in fail log every minute.
Login failed for user 'sa'. Reason: Password did not ...
- 1
0
votes
1
answer
96
views
Table structure where name changes to be retained
Example Situation - order table with client list and products. In below example table , when client name/company changes (company name may change) when updating records need to retain old company name ...
0
votes
1
answer
262
views
SSAS Backup to Remote Location
I followed the steps in the best answer on this question - How can I dynamically back up all SSAS databases on a given instance? - and can successfully create backups of our BI Cubes to a local drive ...
- 101
1
vote
1
answer
107
views
Delete records in different tables
I need to delete the records where UserIndex = 1 and ItemNumber = 5202 in all three tables, all in a single query. I am using SQL 2008 R2.
Table UserInfo1
UserIndex
Itemnumber
ItemCount
1
5202
99
1
...
0
votes
0
answers
209
views
Dealing with Corruption
I started getting Error 823 alerts from my SQL Server. When I checked SQL Server error log, I found lots of this:
A read of the file 'path\to\my\file.ndf' at offset 0x00000d5f850000
succeeded after ...
- 371
2
votes
1
answer
105
views
What is trace flag 1605?
What is trace flag 1605 in SQL Server 2008 R2? I haven't been able to find any documentation on it so far.
- 9,682
1
vote
0
answers
30
views
SA disabled and no other user has access to master db [duplicate]
I have inherited an old SQL Server 2008 R2 where SA login is disabled. No other logins gives access to master db, so I cannot really gain access to administrative stuff (create new logins, change ...
- 111
0
votes
0
answers
312
views
Inplace upgrade SQL Server 2008R2 to 2019 while retaining IP and computername using Start-DbaMigration?
I am running existing legacy applications in production using an older version of SQL Server 2008 R2 Standard Edition, my goal here is to perform an inline/in-place upgrade to SQL Server 2019 Standard ...
0
votes
1
answer
1k
views
Is it possible to grant EXECUTE permissions on a custom function without compromising read-only access?
We have an ancient database instance - Microsoft SQL Server 2008 R2 SP1 - that is running an (also obsolete) business-critical application. I know this is horrible, but for now it's not my call.
Long ...
- 103
0
votes
0
answers
509
views
SQL Server - Exclusive access could not be obtained because the database is in use
Took backup of database from SQL Server 2008 R2 Enterprise and trying to restore it on a database on SQL Server 2019 Enterprise but getting error:
Exclusive access could not be obtained because the ...
- 451
0
votes
1
answer
1k
views
Intall SQL Server 2008 On Windows Server 2016
We update our Server from Windows Server 2008 to Windows Server 2016. And we use SQL Server 2008 R2 on the old server. I read from the website said that SQL Server 2008 is compatible with Windows 7, ...
0
votes
1
answer
638
views
Sql Dump growing so fast
permission to ask. I have a SQL dump with very fast file growth. what should I do to stop it?
The solution I have done so far is to delete the file.
2
votes
1
answer
908
views
How do I fix the Failed to retrieve data for attaching mdf file is which not primary database file
I'm using SQL Server 2019 in Windows 10 Enterprise and Administrator user
I have an MDF file and I want to attach it, and it has full access in the form of Everyone Full Control , when I try to attach ...
- 33
1
vote
0
answers
116
views
MSSQL Server Database Trigger Permissions - issue after migrating from 2008 to 2017 Server
I have some triggers set up on a database (Dynamics NAV) across various tables for Insert, Update, Delete. The triggers write to a separate database to log the DB, Table, RowId and date/time. The ...
- 11
0
votes
2
answers
67
views
How to substruct some table rows from another related table rows and get the SUM of the totale result
I have two table (Orders, Payments)
they are 1 to many relationships (1:N)
One order can have many payments
Table 1: Orders
id
total_price
1
1000
2
2000
3
3000
Table 2: Payments
order_id(FK of ...
1
vote
0
answers
122
views
High parallel tempdb disk usage
We recently added some secondary tempdb files to reduce allocation contention, this seems to have worked; however we are now getting lots of slowdowns related to I/O (WRITELOG and SQLTRACE_FILE_BUFFER ...
0
votes
1
answer
1k
views
Cannot Insert it into in my table for Special characters when ANSI_WARNINGS is Off even with nvarchar
I want to insert a data row into my table as name: DSKWOR00 , but I get this error :
String or binary data would be truncated.
so I turned ANSI_WARNINGS OFF and it inserted the row but the result ...
-3
votes
2
answers
273
views
cant attach mdf file because of suspect mode
Please I need Help
i cant attach Mdf Database , As last time it was suspect , i made detach and i cant now attache it again .
and i recieve this message
An exception occurred while executing a ...
2
votes
1
answer
1k
views
Why SERIALIZABLE level does not lock the table for other session in SQL Server
I want to Prevent concurrency in my table for Delete, Insert, Update, and Select
so I tried to use SERIALIZABLE for the table before transation, but that didn't lock the table for other processes! how ...
-2
votes
1
answer
6k
views
How to Get all Dates Between Two Dates in SQL
I want to display all the dates between two dates for a particular record
Table.
JWDID
FromDate
ToDate
Quantity
100
2022-06-01
2022-06-05
1000
101
2022-06-03
2022-06-03
1500
102
2022-06-29
2022-07-...
- 5
0
votes
2
answers
1k
views
Changing table name from upper to lower case
Can I alter a table name in upper case to lower case? Would it be reflected in its references also?
My project is already done.
- 1
3
votes
1
answer
2k
views
Users time out (or it's very slow) authenticating to SQL Server, but RDP authenticates immediately
The authentication timeouts and slowness happen for both local SQL logins and Windows authentication using AD. Rebooting "masks" the problem for a while, but after 6-8 hours, we see them ...
- 658
1
vote
1
answer
90
views
Safely delete on-disk backups and change behavior
I have inherited a server running SQL Server 2008 R2 and it keeps running out of space. After doing some digging with WinDirStat, I found that it is almost entirely due to the SQL Server backups ...
- 11
0
votes
2
answers
61
views
Recovering space on a restored and pruned SQL Server database for testing purposes
I have a requirement to regularly take a backup of a set of tenant databases (all same schema), and restore them in our UAT environment, then clear down most of the client data, leaving only a few ...
- 299
1
vote
0
answers
78
views
SQL Server 2008R2 - Cannot View DB Properties in SSMS or Query Database Files
I am trying to view the properties of a database but it fails in SSMS with below error in screenshot.
I searched online for solutions but none have worked. These include change db owner to sa. The ...
- 1,414
2
votes
1
answer
615
views
SSIS: How to export more than 4000 characters to Excel
I have the following basic setup in SQL 2008 R2. This does not work however as I always get the errors
Error 1 Validation error. SQL to XLS: SQL to XLS: Columns "Original" and "...
- 194
0
votes
1
answer
56
views
How do I fix this SQL server query 2008r2
I'm trying to update type from table Titem column type and set it to new id, while having it to check if user id owns those 2 items(oldid which we are updating, 2nd id which is just for checking if ...
- 79
-4
votes
1
answer
241
views
Select INTs from start of string separated by a character (pipe). Update other columns using these INTs
In the above screen shot, the Description column has many special characters. We want before | number update in T1 column and | after number in T2 column. Please suggest the proper syntax.
If there ...
7
votes
3
answers
2k
views
ALTER TABLE ADD COLUMN in batches but with NEWSEQUENTIALID()
I have a large table, but not huge (less than 2 million rows on aging hardware), and when adding a non-nullable column to an existing table, I usually follow the structure set out here to avoid ...
- 299
0
votes
1
answer
471
views
Missing rows in table after restore database from MDF and LDF with SQL Server 2008
I have got two files, test.mdf and test.ldf, with sizes around 200 Mb and 200 MB.
I restore the database using "Attach Databases" options, all is ok, but I found that there are a lot of ...
- 113
0
votes
1
answer
175
views
SQL Server Access Database in Filesystem
We have a Windows Server 2008 which is offline. We no longer have access to the server OS, or any GUI tools, but we do have a backup of the entire C:/ of this server.
This server used to host an SQL ...
- 177
0
votes
0
answers
946
views
If I rename a table with dependencies, and then create an synonym of the old name pointing to the new table name will the dependencies still work?
In an attempt to remove some downtime to my users, I want to redirect them to a back up table while I'm repopulating the production table. The tables in question have been in place for years so my ...
- 9
-1
votes
2
answers
857
views
1
vote
2
answers
609
views
Replication alternative
We are using SQL express that does not support DB replication to other servers.
1- is there any way/free tool that enable us to perform any type of replication?
2- do you have any idea to share the ...
- 11
0
votes
0
answers
157
views
SQL 2008r2 : The database is not accessible
Is there something that I am missing?
Newly created database with 2 users. Both have the same permissions. SQL01 gets error "The database DB01 is not accessible" on browsing through SSMS.
...
2
votes
1
answer
1k
views
Watch and log query from a user on SQL server?
I need to find a way to log all scripts and queries (SELECT, INSERT, UPDATE, DELETE and EXEC) from a user.
Context: I want to check what kind of queries my support are doing on the database. At this ...
- 163
0
votes
1
answer
4k
views
How do I produce the relationship diagram in Microsoft Sql Server?
How do I produce the relationship diagram in Microsoft Sql Server?
Here is a visual of what I am talking about here:
- 131
0
votes
1
answer
469
views
Log backup Job disable and enable during FULL backup -Sql Server 2008R2
I am planning to test the Sql server migration of Production databases but i am thinking to stop TLog Backup job during Backup and Restore as I don't need TLog backup and my Backup and Restore process ...
- 11
0
votes
1
answer
493
views
SQL server: Any user can access database instance without any grants [closed]
User created from Active directory can access all instances of databases in SQL Server without any grants.
Was shocked to see this behaviour on our environment.
Example.
We have a database named MyDB
...
0
votes
0
answers
886
views
SQL Server Performance of a user defined table type
I have a stored procedure that is being passed a User Defined Table Type to retrieve 1 to many records back from a table that is filtered down by a join instead of building out dynamic sql to generate ...
- 1,781