Tagged Questions
3
votes
2answers
67 views
If I update a column record in a table, will indexes that do NOT have this column in it be affected?
In terms of performance if I have a table like so:
CREATE TABLE [TESTDATA].[TableA](
[Col1] [nchar](5) NOT NULL,
[Col2] [nchar](2) NULL,
[Col3] [float] NULL
CONSTRAINT [TableA_PK] PRIMARY ...
-2
votes
1answer
37 views
Create a Leveling of Intermediaries [closed]
I have a table called Intermediary, which have some columns like
CREATE TABLE Intermediary
(
IntermediaryPK INT ,
IntermediaryID NVARCHAR(20),
IntermediaryName NVARCHAR(200),
IntermediaryTypeID ...
4
votes
3answers
73 views
memory used by Locks
I am kind of curious, one of SQL 2012 enterprise edition with 128 GB of RAM size of database is 370 GB and growing, amount of memory used by locks (OBJECTSTORE_LOCK_Manager) memory clerk showing ...
1
vote
1answer
85 views
SQL Server Memory for Backup Buffer
In SQL Server 2012 memory for backup buffer comes from buffer pool or non buffer pool (VAS)? Is this behavior changed between SQL server 2008 R2 and SQL 2012?
EDIT: Does MAX Server Memory ...
-1
votes
4answers
77 views
Dropping and recreating indexes [closed]
I most often come in a situation where the users move there database from one server to another and immediately after they move they face performance problems(slowness).
I somehow manage to rebuild ...
-1
votes
3answers
124 views
How to restore SQL Server 2008 R2 backup to SQL Server 2008 [duplicate]
I am unable to restore a backup from a 64-bit instance onto a 32-bit instance.
Source Server:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
Destination server:
Microsoft SQL Server ...
0
votes
1answer
54 views
Can I use the database and query it while full-text indexing?
I need to index my database, but I need to query it while it is indexing, it may take a long time, can I do that?
2
votes
2answers
113 views
Total Memory used by SQL Server (64 bit)
My knowledge on the subject suggests that perf counter SQL Server:Memory Manager: Total Server Memory only gives you buffer pool memory. There is a column called physical_memory_in_use in a DMV named ...
5
votes
2answers
168 views
Maximum memory setting in SQL Server
I'm running SQL Server 2008 and a web based application, on a single dedicated server, with only 2Gb of memory available.
As is noted elsewhere, SQL Server regularly takes up to 98% of physical ...
2
votes
3answers
133 views
Working Linked Server Query fails in sp_send_dbmail
Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ...
1
vote
1answer
46 views
Publishing stored proc execution in transactional replication
In SQL 2008 R2, I am going to update a table which will affect 25 million rows, table is currently replicated (transactional replication). To minimize the impact on replication can I create a stored ...
4
votes
1answer
149 views
Indexes file damaged, was on ramdisk
I have put some of my indexes into a filegroup that contains one file, that file is on the ramdisk, the performance is great, but the problem is that the file was deleted incorrectly (the file ...
0
votes
1answer
201 views
How to find which tables and views a user has access to?
We have a SQL Server 2008 database and have restricted all tables and views away from a specific user ID. Over the years we have granted back tables and views one at a time based on user need.
We ...
1
vote
1answer
76 views
dead lock when updating
Update Operation.TrTable
Set RecordId = RecordTABLE.newRecordId
From Operation.TrTable tr
Inner Join (
SELECT r.Id AS newRecordId, r.KeyM AS MappingKey
From ...
3
votes
1answer
145 views
Automatic database file creation based on database file size in SQL Server 2008 R2
Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. ...
1
vote
1answer
58 views
Would I lose data if I change a column from varbinary(8000) to varbinary(max)?
Is there any data loss if a table is being altered to change an existing column from varbinary(8000) to varbinary(MAX)?
0
votes
4answers
121 views
Update Table A row if Table B row is changed
Is is anyhow possible to tell the database to execute a little script, if a row is changed in a table?
Scenario:
The database constantly monitoring Table B to see if rows are inserted, updated or ...
2
votes
1answer
349 views
How to login with Windows Authentication mode after domain name changes in SQL Server?
I am using SQL Servre 2008 R2. I recently changed my domain name and after that when I try to login to SSMS using Windows authentication, I get an error
Login Failed For User UserName
Microsoft ...
2
votes
1answer
603 views
Active/active SQL Server Clustering in Installation Center (setup.exe)
I like to know which option to choose in the setup.exe in SQL Server 2012/2008? What I have here is Windows Active/Active Cluster setup with one quorum and one msdtc configured. I installed my first ...
3
votes
0answers
168 views
Why can't I read my transaction log backup file using fn_dump_dblog?
I'm using this blog on sqlskills.com as a guide to practice finding the relevant LSN in a transaction log backup for point in time recovery.
After backing up the transaction log, I attempt to read it ...
1
vote
1answer
133 views
SQL Server Error 833 On LDF File
I'm getting error 833 on my ldf file and i'm getting it a lot.
Please see the following. When this happens, my application is hang already.
I know error 833 has been reported a lot, but what does it ...
1
vote
1answer
71 views
SQL Server 2008 - get current LOCK_ESCALATION on particular table
Is there a way to find the current setting for LOCK_ESCALATION on particular table? Can it be found through system views?
3
votes
1answer
523 views
Does this mean the 'public' has full select, insert, update, and delete access?
I'm trying to create a new DB user in SQL Server 2008 R2 that can only select from one SQL View. But no matter what I try when I login as this new user I can select from any table. I ran this query: ...
3
votes
3answers
176 views
SQL Server 2008 R2 Restore a back up to a new database
I have a full back up of a database, I want to update some records in some table in the current database from its old backup without affecting other records.
What is the best way to do this?
...
9
votes
1answer
394 views
query optimization: time intervals
In the main, I've got two kinds of time intervals:
presence time and absence time
absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap ...
3
votes
2answers
345 views
Procedure cache (plan cache) size
I'm developing an application with many, dinamycally-assembled, complicated SQL queries. I'd like to keep the query plans in the procedure cache for as long as it is possible. To prevent discarding ...
0
votes
1answer
65 views
SQL Server Installation choices
I'm installing SQL Server 2008 R2 which would be helpful in
A. querying database.
B. Analysis Services
C. Reporting services and BIDS (SSRS)
D. Powerpivot in Sharepoint
During installation I get ...
2
votes
1answer
131 views
Shrink a large log file for a “Throw Away” database
I have a copy of my prod database on a development server. I am using it to test a deployment script.
But when I run it, it says that the log file is full.
I checked and it is at 10 GB (Almost the ...
1
vote
1answer
123 views
Microsoft Synchronizer Framework
I'm planning to synchronize a pc that has SQL Server 2008 running on it, and the server has a SQL Server 2008 R2 running on it. Are there going to be any problems with data loss or compatibility?
0
votes
2answers
76 views
Getting notification when log truncation is delayed
Is there any way to notify DBA proactively when transaction log truncation has been queued multiple times however for some reason (it could be an open transaction, replication, currently running full ...
4
votes
1answer
211 views
SQL Server Dynamic Memory management
I am reading a book on SQL 2008 internals and troubleshooting by Christian Bolton, Brent Ozar and a old book written by late Ken Henderson and I am getting confused and curious about SQL server's ...
0
votes
3answers
2k views
SQL SERVER installation error for any version 2008 r2 express edition
I am struggling since yesterday regarding the error, first my SSMS expired for some reason & i was not able to get it working. I was not abel to unstall all the soofware related to SQL Server ...
3
votes
2answers
4k views
SQL Server 2008 R2 Management Studio : “Evaluation period has expired”
I've spent several hours trying to fix this issue so far with no success. I had installed SQL Server 2008 Evaluation Edition. Out of fear it might expire & create problems for me I installed SQL ...
1
vote
0answers
95 views
Silently corrupted table after power-related shutdowns, records missing, *scrambled*?
Have a ssvr 2008 db, about 10gb. About 50 tables, 5 or so are frequent read/write, the rest are weekly write (append only), occasional read.
The server came up and down a couple times over the past ...
0
votes
1answer
102 views
SQL Server Mirror failover on failing storage
Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible.
...
0
votes
1answer
49 views
UTC date in DMV dm_broker_queue_monitors
It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so?
I am wondering how to get local time from this view? I can get current time offset, but that is ...
2
votes
3answers
2k views
Unable to start SQL Server Instance 2008 R2 - DB file corrupt
I was not able to start the SQL Server 2008 R2 production DB instance. After reading the log file error message is
" The log scan number passed to log scan in database ‘master’ is not valid. This ...
2
votes
2answers
528 views
Fast XML, slow XML
Our database update Windows application needs to transfer some data between two databases as part of the process for a certain one-time update. I chose XML as the intermediary to move the data.
The ...
0
votes
0answers
195 views
Cannot change the Delete Rule of a self referencing foreign-key-relationship to cascade delete
I have the following table
[dbo].[myObj](
[ID] [int] IDENTITY(1,1) NOT NULL,
[fk_ParentID] [int] NULL
)
and I have a relationship from fk_ParentID to ID.
I want to change the Delete Rule in ...
0
votes
2answers
103 views
Auto Script DB Objects for SVN
My Boss requires that we keep up to date scripts of all database objects in svn. This results in constantly trying to find the object in the current script and copy and pasting the changes.
Is there ...
3
votes
1answer
302 views
Multi-tenant database using SQL Server 2008?
I am designing a database using a shared schema containing the "security views" to which all logged tenants will be directed.
I read the msdn article on Multi-tenant Data Architecture. I understand ...
3
votes
1answer
1k views
Deny access to information schema in SQL Server
I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server.
I found this thread from 2008
It shows a way how to deny access on ...
0
votes
2answers
155 views
SQL Server 2008 R2 Remote Connection issue
I have installed SQL Server 2008 R2 in mixed mode, but currently issue is I can't use SQL through ip nor ip with port, even I have enabled TCP/IP and open port in firewall too.
I tried to telnet ...
1
vote
1answer
367 views
how to capture the data using DMV's in sql server 2008 r2?
Hi i am working on sql server 2008 r2,
i have a situation where i wanted to capture the DMV's data into a table, i would like to know when DMV's tables get refreshed or data flushed out. because as ...
2
votes
2answers
424 views
SSMS 32bit can't connect SSIS 64bit
I can't find the solution and searching for info doesn't give desired results
Environment:
Server: W2K8 EE 64 bit + MSSQL SE 2K8 R2 64 bit
Client: Windows XP Prof 32 bit
Using SSMS on client ...
2
votes
0answers
137 views
Auditing - parameter values not being written to log
I am using auditing to track usage of some procs in the database. In the log I can see a record for the Procedure being run and, immediately afterwards,the SQL statement that is being run. The ...
-3
votes
1answer
230 views
Why does a VIEW with a CLUSTERED index not use the index?
I have a view that has a clustered index along with other non-clustered indexes.
A simple SELECT query does not use any of the indexes even when I select an indexed column.
What could be the ...
0
votes
1answer
826 views
Error restoring backup in sql server. Old server version
When trying to restore a backup file (.bak) to SQL Server installed on my machine, I get the following error (steps that I follow to restore are: Right Click Database ---> Tasks ---> Restore)
Error ...
5
votes
2answers
499 views
Best value for fill factor in index
I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also ...
5
votes
2answers
463 views
Create index view with self join
I use SQL Server 2008 R2 and know that in Indexed View cant have self join.
I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
