Hot answers tagged sql-server-2008-r2
18
You are falling into the "Catch-All Query" trap, which is explained very well by Gail Shaw here.
To summarize the problem: SQL Server optimizes away the significant overhead of query compilation by caching a query plan after compilation, and then later checking the cache for a matching query plan before a later compilation. The "matching" that occurs here ...
9
When performing a cross-database transaction, in which transaction log(s) is the information stored?
The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently.
It's possible for a stored proc from one database to be working entirely in the transaction log of another database.
... database1..my_stored_procedure AS
BEGIN
INSERT INTO ...
9
This doesn't look like a permissions issue at all, but rather a bug in the UI.
If you are using Management Studio Express, then you should consider two options:
Ensuring you are on the most recent service pack (yes they apply to client tools as well), and perhaps the most recent cumulative update as well.
Many of the bugs in the 2008/2008 R2 version of ...
7
I will answer the last question first: Yes, you can change it while the server is running without issue. If you want to change the value via SQL you can do it with the following query
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO
See this page for more details about setting memory on ...
7
No, the parser won't let you create the same #temp table twice in the same batch (and this has nothing to do with SSMS). It doesn't even matter if only one copy of the #temp table could ever created; for example, in the following conditional logic, which to humans could obviously only ever execute one branch, SQL Server can't see that:
IF 1 = 1
BEGIN
...
6
There is no guaranteed way to force SQL server to execute your clause conditions in a specific sequence. The optimizer will always evaluate them in the order it sees fit.
What you can do is something like this:
IF @LinkMode IS NULL
BEGIN
select ...
from ...
WHERE (myColumn IN (...very long time exeted query...))
...
...
END
...
6
Your issue isn't with the 32-bit vs. 64-bit, but it's that you are trying to restore a database on an older version.
The database was backed up on a server running version 10.50.2600.
That version is incompatible with this server, which is running
version 10.00.4064.
This means that the source DB is 2008 R2, and destination DB version is 2008 (not ...
5
You selected the default instance name as the name of your instance.
Instance Configuration
"If you specify MSSQLServer for the instance name, a default instance will be created. For SQL Server Express, if you specify SQLExpress for the instance name, a default instance will be created."
To connect to the default instance you don't specify the instance ...
5
If it were a query that returned more than 1 row I'd speculate that someone at the vendor (way back when, given the version of SQL Server) stumbled on the query optimiser producing a preferable plan when FAST was specified as a hint.
As it's returning just 1 row, the explanation probably has more in common with the infinite monkey theorem than reasoned ...
5
Your comments note that you're specifically talking about full-text indexing.
You can indeed query the database while a full-text index is being created. Full text indexes are created in the background on SQL Server 2005, 2008, and 2008R2.
You can continue to query the database using the LIKE operator, although of course the queries won't be as fast as a ...
5
Well this won't happen unless
a trigger is firing on the target table, and the error happens there
the INSERT does some processing that adds enough data to make it too wide
Also, what happens with this please? This can not fail unless there is some processing.
INSERT INTO table (col1, col2)
SELECT LEFT(column1, 200)
,column2
FROM tmpTable
5
I suspect you are simply not checking the length of the column accurately (like inspecting PRINT or SELECT output in Management Studio, which is truncated by the tool, not accurately reflecting what's actually in the database). There is no limitation on the number of characters that can be exposed or updated by a view, unless you introduce them, perhaps you ...
4
how SQL server knows to replicate only the stored proc execution (i.e. actual exec myupateproc t-sql command) instead of underlying table data that is being updated on the publisher (i.e. actually replicating 25 million update statement)?
SQL Server knows that when you set up article property as below :
You can explicitly set only to replicate SP ...
4
Yes this is just improved documentation.
FAST n has always been the n version of FASTFIRSTROW i.e. a request to the optimiser to choose a plan that will return n rows more quickly if a plan can be identified to do so.
It is not equivalent in anyway to requesting the TOP n rows, which is in effect what you would be asking for if the expectation was for ...
4
You can certainly limit the set of databases that are checked, using a condition, and have that condition be separate from the actual condition the policy is using to check. Pull down the "Every" drop-down in the UI:
Create a new condition:
Then you should be able to use that condition:
Note that this is using the SQL Server 2012 version of ...
4
SQL Server 2008 (r1) introduced Extended Events and with that came SQL Server Audit and works similar to XEvents to audit events. So it is less intrusive than C2 Auditing and server side traces.
It is very granular and fairly easy to setup. This feature is available in all editions of SQL Server 2008 R2. A good write-up intro/how-to can be found here by ...
4
This is an open question with no clear choice. YMMV so you have to test. Here is my opinion:
Having one queue to handle everything is a good choice if you want to be able to control the number of activated tasks, as there is no global max_queue_readers. Other than that, I don't see many advantages. One could argue that one single activated proc is easier to ...
3
Posting the final SQL for this process, for the benefit of the community
/********************************************************************************************************************
* Notes: Since this approach executes in a loop inside an explicit transaction, locks will be obtained and *
* released for each iteration, thus minimizing ...
3
I know that ApexSQL Log can audit changes made by DBAs LIKE INSERT, DELETE, UPDATE, ALTER (etc.) but CANNOT audit DBA Logins.
However, they are announcing a new tool that can track "who saw what" and attempted logins, so there might be something there:
Sneak Peek: ApexSQL Comply - Part II,
Introducing ApexSQL Comply
It seems that it can also track ...
3
Using system_function_schema was an undocumented and unsupported process in SQL 2000. As of SQL 2005, it is no longer possible.
By design, UDFs require that the schema be included in the function call.
There is one exception: calling the function like a procedure doesn't actually require the schema, but also wouldn't be terribly useful for your function.
...
3
By default, SQL Server activity is not logged the way you expect. Some write activity is recorded in the Transaction Log, but this also depends on how your databases are set up.
There are four main options for tracking SELECT activity on a server:
You can use SQL Server Profiler to connect to your server and watch for specific activity as it happens.
You ...
3
You can’t do this by default because you are trying to restore newer version of backup to older version of sql server.
Solution for this is to script object and data and then to execute scripts on new server.
You can also try using some database comparison tools such as those from ApexSQL (Diff and Data Diff) or Red Gate (SQL Compare and SQL Data Compare). ...
3
First of all, Welcome to dba.stackexchange.com and thanks for your post !!
Is it possible to limit what values are allowed in a coulumn based off of other values in the row.
Yes using CHECK CONSTRAINTS as described here
Example :
create table myTable (ID int identity(1,1)
, Test_mode int
, Active int
...
3
Two thoughts come to mind.
If you are concerned that this update may not properly affect the table the way you think, have you thought about putting the update inside a transaction.
You could do the update, query the data, and if it all looks okay, Commit the Transaction. If it fails, you could perform a Rollback.
Alternative
You may want to look at ...
3
The tuning advisor (DTA) can yield information about missing indexes, but the results are only as good as the workload you've provided and your ability to interpret the results and fill in the gaps.
Your .trc file may only contain certain events, not all, and may not cover a full business cycle. So if you have a big set of reports that people run at the ...
2
By default, it does not log SELECT statements. You can refer to my answer here for more details to audit SELECT statements.
Also, by default it does not even log T-SQL statements, instead it logs EVENTS like Server Memory Change, Audit Login Failed, Audit Addlogin Event, etc and you can find more info here along with the T-SQL Scripts to extract information ...
2
There are a couple of connect items that might be relevant here.
An INSERT statement using XML.nodes() is very very very slow in SQL2008 SP1
Poor xml performance with table variable
Bad performance when inserting data from element-centric documents
INSERT from .nodes with element-based XML has poor performance on SP2 with x64
I have not been able to ...
2
Create multiple views and/or table-valued functions. You can't override the schema without specifying it explicitly unless you build dynamic SQL to do so for you, but you can't do that in a view or a function - only a stored procedure.
With that said, you could do that with something like this, however it will fall back to dbo (or some other schema you ...
2
Why not just have a transactions table:
CREATE TABLE dbo.Transactions(TransactionID INT IDENTITY(1,1));
When you're about to start a new transaction (a set of imports and exports), do this:
DECLARE @tID INT;
INSERT dbo.Transactions DEFAULT VALUES;
SELECT @tID = SCOPE_IDENTITY();
Now in your import and export tables, add a column that holds the ...
2
The simplest way to handle this may be a bit field like IsDirty that is updated with a trigger, and set to 0 for the whole table when you run your ETL.
If this is set to 1 for every row that is updated, and you give it a default value of 1, then you can make export views for your ETL that filter on IsDirty = 1 so you only interact with new rows.
If you ...
Only top voted, non community-wiki answers of a minimum length are eligible
