Tag Info

New answers tagged

3

In order to transfer logins, you will need to use the sp_help_revlogin script and procedure. This will allow you to transfer logins and retain the password from on instance to another.


0

I am going to assume that there isn't an index on the date columns, otherwise I think that the query would have been structured differently. If there is, you can probably find a better performing one than this. The advantage of this query is that it can get all the data in one scan. The disadvantage is that it has to sort the data and join EventEmployee on ...


0

50,000 rows joined against a million-row table seems to be a lot for any table without an index. It's hard to tell you exactly what to do in this case, since it is so isolated from the problem that you're actually trying to solve. I certainly hope that it's not a general pattern within your code where you're joining against many unindexed temporary tables ...


8

SampleTable is contained on 4714 pages, taking about 36MB. Case 1 scans them all which is why we get 4714 reads. Further, it must perform 1 million hashes, which are CPU intensive, and which ultimately drives the time up proportionally. It is all this hashing which seems to drive the time up in case 1. There is a start-up cost to a hash join (building ...


0

Using tsql: CREATE DATABASE MyDB ON (FILENAME = 'D:\MySQLServer\MyDB.mdf'), (FILENAME = 'D:\MySQLServer\MyDB_Log.ldf') FOR ATTACH;


1

If you have the Enterprise version of SQL Server, then I would recommend doing this using Change Data Capture (CDC). This is much easier and cleaner than setting up triggers to watch for changed data. Plus it reads the transaction log for changes and then logs those changes in system tables. You can use SSIS for your ETL. If you have 2012 installed, there ...


0

You may not use temporary table to achieve the result you need. Please try this query: with joined as ( select em.*, e.* from [Event] e inner join [EventEmployee] eem on (eem.EventID = e.EventID) inner join [Employee] em on (em.EmployeeID = eem.EmployeeID) ) select distinct EventID, EventDate from joined where EventDate = (select max(e.EventDate) ...


2

How can I be seeing shared locks? Is it because of foreign keys? Yes. SQL Server reverts to the locking implementation of the read committed isolation level when accessing a table for the purpose of validating foreign key constraints. This is required for correctness, and cannot be disabled. The behaviour applies only to data-modification statements. ...


3

Try changing you statement to match this: FOR XML PATH(''), TYPE).value('(./text())[1]','varchar(max)') This uses the .value xml method to solve your entitization problem.


1

Yes, if you know what data to insert after corrupt data pages are deleted, you should be fine. make sure you find out the root of corruption to prevent it from happening. also, once everything is fixed, then do a full back and restore it on a different server to make sure everything is fine. Your plan sounds good to me.


1

I would suggest one of the below: Method 1 For each error condition, create an inline, table-valued user-defined function which generates the erroneous results. Make a table which contains all of the function names, with a field for the email recipients. Build a procedure (MailErrorResults) which takes a function name as a parameter. If will retrieve the ...


0

This could be due to one of these issues: A DNS problem: Your workstation does not ping the IP address associated with this host. resolution: Try to ping the LRSERVER. If it does not ping properly, add the LRSERVER ip address in your host file. A firewall issue: the network traffic between your workstation and the SQL Server instance goes through a ...


0

Assuming you need an exact count and not an approximation, and you want this to go fast without index scanning (yes, please!), a good place to start would be to create an indexed view that maintains the count of rows: CREATE VIEW dbo.MyView WITH SCHEMABINDING AS SELECT COUNT_BIG(*) AS NumberOfRows FROM [dbo].[Products]; GO CREATE ...


2

I don't think you'll be able to find that out. SQL Server doesn't track this information by default (e.g. in the default trace or system health extended event session), and in fact if you go through the UI and click the Script button instead of saying OK, you will see it just generates a script like this, which writes directly to the registry: USE [master] ...


2

I believe you will see this symptom if you have a LOT of large query plans that are fighting for memory in order to compile (this has very little to do with running the query itself). To hit this, I suspect you are using an ORM or some kind of application that generates many unique but relatively complex queries. SQL Server could be under memory pressure ...


0

Only sysadmins can manage the instance and by default Administrators are not members of the sysadmin role. You need to restart your instance in single user mode. Here is an article on Technet that describes how to do it: Connect to SQL Server When System Administrators Are Locked Out Basically it boils down to "restart the instance with the -m switch" ...


-1

The most typical reason by far I've seen those waits appear, are as a result of fragmented or insufficient indexes, and statistics that either have insufficient sample size, or are obsolete. This results in massive full table scans hogging up all the memory, which in turn produces a symptom we often see as RESOURCE_SEMAPHORE_QUERY_COMPILE. The easiest way ...


0

Edit the Database Template and choose your new template for Forward Engineering


0

You can use stored procedures and functions for instance. Tracking who-saw-what can be done through specially developed stored procedures and functions. In order to use this auditing method, access to the database must be limited through use of stored procedures (allowing EXEC statements only, while prohibiting all queries and DML operations). The result ...


2

Here is the sample for DDL trigger that captures SQL query too: CREATE TRIGGER Audit_DDL ON DATABASE FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE AS DECLARE @event xml; SET @event = EVENTDATA(); INSERT INTO Audit_DDL_Events VALUES ( REPLACE(CONVERT(varchar(50), @event.query('data(/EVENT_INSTANCE/PostTime)')), ...


0

You can use custom policies or predefined ones (so called best practices) and adjust them according to your developer SQL Server instance. Then, as explained in the SQL Server Policy Based Management – evaluating policies on multiple SQL Server instances online article, evaluate them against (even multiple) production instances


0

It's a very common problem with different reasons. I strongly suggest that you read these answers by Mike and Aaron -- Why Does the Transaction Log Keep Growing or Run Out of Space? I don't want repeat what they explain very well in the answers.


-3

It's got to sort the data for the clustered index some where and then rewrite your rows in that order. Don't forget that SQL Server will add an extra integer, the "uniquifier", so that it can disambiguate between the non-unique values of "myColumn". This will cause all your non-clustered index to include "myColumn" + "uniquifier" (4-byte).


2

Although you had SORT_IN_TEMPDB = OFF, that is not the only thing that uses tempdb. You also had set ONLINE = ON. Since the job ran 50 minutes before you had trouble, it may be that you had enough activity in that single transaction to fill tempdb with row versioning data. This is described here: http://technet.microsoft.com/en-us/library/ms179542.aspx ...


0

The Profiler will always introduce a performance impact. If you are using SQL Server 2008R2+ you can use extended events. This provides much of the information you see in the profiler with a fraction of the performance hit. Books online introduction http://technet.microsoft.com/en-us/library/bb630354(v=sql.105).aspx This feature received a big update in ...


0

So the problem was sitting in front of the PC - once again. It turned out that the query was not exactly similar, there was one where condition more in every query. We had different entity framework versions on the two notebooks that created different statements.


0

Who knows! Just add something like and sja.start_execution_date >= DATEADD(dd, -1, GETDATE()) This is my current answer in my environment. This works for me as the greater script this is a part of runs on a daily basis. It does feel like a better answer or explanation should be available.


0

Your arrow points to the #user seeks column for the clustered index in the report. The execution plan says it is doing a scan on the non clustered index. Are you expecting it to do a seek on the clustered?


2

Does it mean that former primary replica (A) will become primary replica automatically? No, all that means is when your replica comes back into the picture, and when the Availability Group database gets back in a SYNCHRONIZED state that it would be failover ready. That operation will not happen automatically. You indeed would have to either do this ...


0

If you have a date table the following could work. If the date table does not already have a column for the date format stored in table.bar then create the column or cast. select t.foo, t.bar from table t join date_table dt on t.bar like '%' + dt.DateID +'%' where dt.DateID > 20140401 Since you would be using a date table it would probably be better to ...


1

The interesting point here is not that TempDB is full, it is why it is filling up. The reason being the GROUP BY UserID needs to do a sort and that's spilling out of memory and into TempDB. With 1.8 Billion rows I'm not surprised. The fix is to reduce the date range you query to what will fit comfortably in TempDB. Let's say this turns out to be a week. ...


4

As of SQL Server 2008, IIS is no longer used by SQL Server Reporting Services. It utilizes the HTTP.sys API now. So you will not see SSRS show up as a site or application pool. The tip you linked to deals with SQL Server 2005, which required IIS in order to use SSRS. This tip speaks of the slowness you might be seeing and how to address it in SQL Server ...


1

You don't say but I'm guessing that your log files are on your D: drive and what you're seeing here is writes to the transaction log files, not to the database files i.e. to ldf not to mdf. You won't see the mdf files written until the DB hits a CHECKPOINT, which could be some minutes later. The linear increase is good and what you'd hope for. That you ...


0

As James says, step 2 is redundant. Rebuilding indexes will automatically update statistics. An explicit update of statistics will only sample a percentage of the rows in the table. Consequently the statistics will be inaccurate by a small amout. An index rebuild has to visit all the data in the table. The resulting statistics will be an absolutely ...


1

There is no point in updating your statistics if you are rebuilding them. The statistics for an index are updated if the index is rebuilt. Another problem is that the rebuild index job in the Microsoft maintenance plans are not intelligent so they just rebuild all indexes whether they need it or not. Brent Ozar talks about this here ...


0

In which format are the date values stored? If you can determine the used format you can CONVERT with the specified format by resticting the rows using LEN and LIKE predicate. Example: If you have the date values stored like '10/23/2016' you can use U.S. standard (101) SELECT [foo], CONVERT(DATETIME, [bar], 101) FROM [table] WHERE LEN([bar]) = 10 AND ...


3

That error is simply telling you that the volume(s) you have tempdb on is/are full. Unless you have explicitly altered the file layout of tempdb it will all be on C:. You must be pretty low on space where tempdb is unless the amount of rows for that 24 hour period is massive. Anyway, spooling large amounts of data into a temporary table onyl to delete most ...


0

There are two options regarding the transaction log files in SQL Server: Autogrowth and Maxsize. Autogrowth configure the size by which the log will grow when it's full and Maxsize is the the limit where it stops to grow. It seems that you don't have any limit set for the database. In addition the log file size depends on the recovery model you have ...


0

Reasons for transaction log growth can be various: rebuilding indexes, other administrative tasks, some import data operations and so on. You must first inspect your database, and search for administrative tasks that affect on transaction log. Also, full backup doesn't shrink transaction log, so you must do another administrative task to free space from ...


0

Here are my answers: Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less ...


1

It appears that this was added in the 2008 time period. You can see the Connect item with the title "MSIT-MSO: Use NOLOCK while querying the cdc._CT table in CDC TVF" at: http://connect.microsoft.com/SQLServer/feedback/details/331486/msit-mso-use-nolock-while-querying-the-cdc-captureinstance-ct-table-in-cdc-tvf It is interesting that the request was made ...


0

The hard drive that tempdb is stored on is full. Make room thee and the error will go away. You can run this query to figure out where your tempdb files are stored and how big they are: SELECT DF.file_id,DF.type_desc,DF.name,DF.physical_name,DF.size/128.0 size_mb FROM tempdb.sys.database_files AS DF; Also, in general it is a bad idea to copy large ...


0

If your database is in READ_ONLY(which sounds like it is the case based on your error messages), then you need all files (both log and data) to attach it per the Technet documentation. If you look under the FOR ATTACH clause, it explicitly describes that "for a read-only database, the log cannot be rebuilt because the primary file cannot be updated. ...


0

Idera SQL Permissions Extractor seems to be the product you're looking for. It can script server and object permissions and it is free. There is also a commercial edition, called SQL Secure which has more features. The feature comparison between the two editions can be found here.


4

For very small tables, fragmentation is not only irrelevant, but nearly impossible to control. The first eight pages are allocated out of mixed extents, which are almost always going to be non-sequential. Only after an index has more than eight pages will it be allocated additional pages from uniform extents. At fewer than 1,000 rows, your clustered index ...


0

I am trying to create a process that will remove all users after the restore on the new servers as no user permissions will follow the database to the new server. There is no need to delete all the users. What you can do is use sp_help_revlogin to move all the logins that you are interested or your applicaiton/s need to the new server. Once you move ...



Top 50 recent answers are included