Tagged Questions
2
votes
4answers
84 views
When should I use the shrink option
I have seen a lot of blogs stating that shrinking is not a good habit as it will reduce the performance of the system. I agree with all those things it will lead to side effects like fragmentation, ...
4
votes
2answers
96 views
Why do linked servers have a limitation of 10 branches in a CASE expression?
Why does this CASE expression:
SELECT CASE column
WHEN 'a' THEN '1'
WHEN 'b' THEN '2'
... c -> i
WHEN 'j' THEN '10'
WHEN 'k' THEN '11'
END [col]
...
0
votes
3answers
85 views
How to find all positions of a string within another string
How can I find all the positions with patindex in a table or variable?
declare @name nvarchar(max)
set @name ='ali reza dar yek shabe barani ba yek '
+ 'dokhtare khoshkel be disco raft va ali ...
0
votes
1answer
46 views
Should database files be on different disk(s) than OS?
My very short overview
SQL Server databases are installed as virtual machines on VMWare, there are plugged a few disk areas with the same (or very similair) parameters
versions are SQL Server ...
-1
votes
2answers
69 views
SQL Server Transaction logs
Could someone tell me the role of transaction logs in SQL Server ?
I have a transaction log file, after making a lot of transactions for testing purpose I take the backup of it via command. Sometimes ...
1
vote
1answer
741 views
IF NOT NULL then UPDATE else keep the value of the field
I think I am somehow close to get it work, but for some reason I still get errors.
I have the following UPDATE query:
UPDATE DeviceAttribute
SET Details = CASE Name
WHEN 'Accessories' THEN ...
3
votes
2answers
70 views
SQL Server 2005 Database move and rationalisation - Recommended maintenance processes
We're moving a SQL Server 2005 database to a new server and upgrading to 2008, and I'm looking for some advice and reassurance that our approach is correct.
I inherited this database and it came to ...
0
votes
1answer
140 views
SQL update set column = @ parameter
doeas anyone knows how to update a table where column = @parameter WHERE SELECT
Basically I have a table with the following values. Table name is UserAttribute
ID|Name |Properties|
...
3
votes
1answer
67 views
Primary key with “NOT FOR REPLICATION” option
I have recently taken over a project, and I have discovered that in most tables the primary key has the property "NOT FOR REPLICATION".
I'm no DBA, but surely in most databases, a record without a ...
10
votes
1answer
387 views
What exactly does “No Join Predicate” mean in SQL Server?
MSDN "Missing Join Predicate Event Class" says it "indicates that a query is being executed that has no join predicate".
But unfortunately it does not seem to be as easy as that.
For example, very ...
2
votes
1answer
120 views
Inside SQLOS – Scheduling
Using SQL Server 2012 Enterprise Edition SP1 running on Windows Server 2008 R2 SP1
For each logical processor core on SQLOS has a scheduler assigned to it. Those scheduler can be viewed as status ...
2
votes
1answer
67 views
can you run sql server 2005 and sql server 2008 r2 in mirror mode?
Must all db servers be running the exact same version to enable mirroring without a witness server? How about with a witness server in place?
2
votes
2answers
512 views
SQL Server drop and create all users in every DB
I would like to drop and recreate every user in a database without having to manually do this for 500 users. I have this so far:
USE [Maintenance]
GO
IF EXISTS (SELECT * FROM ...
0
votes
1answer
131 views
SQL Server 2005 on Windows 7 along with SQL Server 2008 R2 Express edition
I've installed SQL Server 2008 R2 Express edition on my Win 7 notebook. Using since an Express edition this doesn't have SQL Server profiler enabled. Thus it's hard for me to debug my SQL Server 2005 ...
1
vote
0answers
70 views
SQL server Load Balancing
SQL server user access cut down from 8pm - 9am.
During this period our SSIS scripts would run to load data(from Oracle/mainframe/Access) to sql, large database(s) & too much data - mortgage ...
6
votes
1answer
204 views
SQL Server 2000 to 2008 to 2012
We are migrating from SQL Server 2000 to 2008 now. We are making lot of changes to code, scripts because there has been a lot of change since 2000 to 2008. While making these changes, What I want to ...
3
votes
1answer
2k views
ODBC Data Source SQL Server Connection - Login Failed For User
I have moved my database from an SQL 2005 to a server with SQL 2008.
I am now trying to create an ODBC Data Source.
I am using "With SQL Server authentication using a login ID and password ...
1
vote
3answers
122 views
Check of SQL Server upgrade success
I have performed SQL Server upgrade from 2005 to SQL Server 2008. Is there any tools/possibilities to check, whether the upgrade from SQL Server 2005 to SQL Server 2008 has been accomplished ...
4
votes
2answers
112 views
SQL Server update and database transfer
In my test network there is a database created with SQL Server 2005. I have to update SQL Server 2005 to SQL Server 2008. The data base should be used with SQL Server 2008. So it is to be moved. What ...
2
votes
2answers
137 views
SQL Server 2005-2008 Compatibility Check
A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should ...
5
votes
1answer
542 views
Why do we need to rebuild and reorganize Indexes in SQL Server
After searching the internet i couldn't find the reason for
Why do we need to rebuild and reorganize indexes in SQL Server ?
what does internally happens when we rebuild and reorganize ?
An ...
0
votes
1answer
99 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
2answers
116 views
SQL Server 2005 replication for a data warehouse
We are trying to implement some kind of Data Warehouse at my current employer.
Most of our data is in a SQL Server 2005, but we might also integrate some Access databases and some databases in a SQL ...
2
votes
1answer
204 views
Migrating database from SQL Server 2005 Enterprise to SQL Server 2008 Standard
I'm attempting to migrate a database from SQL Server 2005 Enterprise to SQL Server 2008 Standard. I've run into an issue because there is a partition function defined in the database of the Enterprise ...
3
votes
2answers
351 views
SQL Server Concise List Of Trace Flags
I did my 5 minute research and have not come up with a concise list, or even a list really except for Trace Flags (2012) which is a bit lacking.
Looking at this query which uses 8691, I was wondering ...
4
votes
1answer
654 views
Turning off auto shrink on all SQL Server databases. Why doesn't this work?
I figured I could use sp_MSforeachdb to solve this problem, but I get an error message.
sp_MSforeachdb '
BEGIN
USE [?]
DECLARE @dbid INT
SET @dbid = DB_ID()
IF(@dbid > 4)
BEGIN
--PRINT ''[?]'' ...
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 ...
3
votes
1answer
321 views
Who changed/modified stored procedure in SSMS 2005 or 2008? [duplicate]
Possible Duplicate:
Determining how a schema change occurred?
I have a stored procedure that was modified late last night and just spent the majority of the morning tracing issues back to ...
7
votes
1answer
204 views
Hard limitation for merge replication article count?
A bit of background. We developed an app that uses merge replication. Right now, we are currently publishing about 212 articles, and everything is fine, as it appears to fall into the 256 article ...
4
votes
1answer
541 views
Mirroring - Server network address cannot be reached
I have SQL Server 2008 R2 Installed. It contains three instances.
Default (MSSQLServer)
FirstInstance
SecondInstance
All these are Log On as Network Service..
Default Instance is Principa ...
1
vote
4answers
2k views
mdf,ldf file attach problem?
I've created one database on my local system. consider database name is test. This database primary files are test.mdf, test_log.ldf. and contains ndf files too.
Then i've stopped sql server service. ...
1
vote
3answers
181 views
Does Log-Shipping need Transaction Log Backup from Primary Server?
In this video tutorial it is explained that we should use the transaction log backup of the primary server database and restore it into the secondary server database for log shipping.
I was doing a ...
1
vote
1answer
449 views
how to get last date of today in datetime datatype?
select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0))
when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody ...
3
votes
1answer
363 views
Why do we have to use SWITCH in table partitioning?
I'm a beginner in SQL.
I'd like to know what is the importance of SWITCH in table partitioning?
I've created one table partitioned month-wise, with a total of 12 partitions.
The ranges are:
...
3
votes
3answers
764 views
Is there a way to test whether DELETE will fail due to constraints?
I'd like to be able to predict whether a DELETE will run into a constraint violation, without actually performing the delete.
What are my options for doing this? Is there a simple way to do a "dry ...
3
votes
1answer
94 views
Selecting 'edge' records in a sequence of data
I have a table of items which run sequentially by date with both a start and end for each entry:
ID | Start | End
----------------------------------------
1 | 2012-08-20 00:00 | ...
5
votes
3answers
398 views
Does having an index on a VARCHAR column with a lot of very similar starting values have bad performance
We seem to be having quite unusual bad performance on queries that use an index. for example the table looks like
PK BIGINT
ID VARCHAR(50)
Col1
Col2
etc
So we need to insert a row in the ...
6
votes
2answers
344 views
SQL Server database : moving from 2008 to 2005 - encrypted procedure issue
I could normally use generate scripts / DTS to move data and tables etc. However one of the databases has a large amount of encrypted procedures that I can't script to move.
Neither do I have older ...
2
votes
4answers
158 views
How does the database decide which Index to use
DDL
create table t
(
id int,
id1 int
)
create index Example_Index
On t(id,id1)
create index Example1_Index
On t(id1,id)
DML
insert into t(id, id1)values(1, 100)
insert into t(id, ...
1
vote
0answers
101 views
ClientHost for Logon Trigger showing NULL values [closed]
I am trying to create a Logon trigger, in SQL SERVER but
EVENTDATA().value ('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)')
is returning NULL values. Why So ?
1
vote
1answer
1k views
Quick look at how much RAM is allocated to SQL Server?
With SQL Server 2005, you could look at the Task Manager and, at least, get a cursory look at how much memory is allocated to SQL Server.
With SQL Server 2008, the Working Set or Commit Size never ...
4
votes
3answers
121 views
Schemas and user rights
I have 2 schemas, lets say sch1 and sch2. Each owned by different user.
I do want to grant SELECT rights on sch2.MyTable (but just this one object) to sch1 procedures/views AND allow to reference ...
4
votes
2answers
336 views
SQL Server parallelism for DML operations
I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements mostly likely in a data warehouse environments when query cost exceeds cost threshold ...
6
votes
1answer
9k views
How to identify which query is filling up the tempdb transaction log?
I would like to know how to identify the exact query or stored proc which is actually filling up the transactional log of TEMPDB database.
3
votes
1answer
760 views
Database file size from sys.master_files and sys.dm_io_virtual_file_stats are different
Im using query to find out database file size. I'm using system view and DMV, because i do want to find out both- actual file size and theoretical (in case of sparse files) file size.
Select
...
2
votes
1answer
631 views
UPDATE Statistics with full scan
Does running UPDATE Statistics on SQL 2008 r2 with full scan on a large table 50 million rows (non partitioned)? I am trying to confirm the following behavior.
a) takes any locks during the entire ...
5
votes
1answer
294 views
SQL Server 2008 Multi Page Allocation
When a query or stored procedure has an execution plan greater than 8000 bytes, instead of using plan cache in buffer pool, it will use MPA (multi page allocator) which used memory outside buffer ...
3
votes
1answer
75 views
How to update conflict resolver when upgrading from SQL-Server 2005 to SQL-Server 2008
We have recently upgraded from SQL Server 2005 to SQL Server 2008 (R2, SP1). This upgrade included some publications, where all tables are published with a default conflict resolver based on the ...
7
votes
1answer
230 views
Schema qualification necessary for query plan reuse?
While reading this article about Plan Caching in SQL server, I came across a tidbit I was unaware of:
... for reuse it is necessary that the objects that the batch references do not require name ...
5
votes
2answers
2k views
Performance difference between Clustered and Non Clustered Index
I was reading Clustered and Non Clustered Indexes.
Clustered Index - It contains Data Pages. That means the complete row
information will be present in the Clustered Index Column.
Non Clustered ...
