Tag Info

New answers tagged

1

An Availability Group Listener simply connects the user to a database instance. Once they are connected to the instance they can access all the databases on the instance. It doesn't matter of the databases are part of the availability group or not. This is by design. In other words everything is working exactly as it should be.


1

If you enable the force encryption option and restart the SQL Server instance then all connections between the instance and the SQL Server will be encrypted via SSL. There is no need for a certificate or change to the client. The server tells the driver that it needs to switch to an encrypted session. Thus is kind of like when you connect to a website using ...


1

CXPACKET is not the cause of high CPU workload. CXPACKET simply means that the query has gone parallel and that the thread which is reporting CXPACKET is waiting for another thread of the query to run. When I see queries waiting on CXPACKET this tells me that there's probably some index or query tuning which needs to happen. Once the indexes and query have ...


1

If the database is down when the backup is supposed to start the backup can not run. If the database goes offline while the backup is running the backup will simply stop. It'll need to be restarted after the database comes back online. There won't be anything logged that there was a backup running when the database went offline. You'd need to look at the ...


2

Although you've accepted an answer that informs you how to kill the session, this is a kind of problem that could repeat itself and so you should probably be aware that the CXPACKET wait type in itself is not a problem, but tends to indicate an issue with the execution of a parallel query. Brent Ozar has posted a good explanation (with a video) of what a ...


2

Why does SQL convert Unicode 9619 to ASCII code 166? SQL Server is not employing any special custom logic here; it is using standard operating system services to perform the conversion. Specifically, the SQL Server type and expression service (sqlTsEs) calls into OS routine WideCharToMultiByte in kernel32.dll. SQL Server sets the input parameters to ...


2

Generally How much time it would take to delete such no of records ? Impossible for us to guess - way too many factors involved here, including performance of your data and log drives (a factor of which level of service you chose), your log growth rate and number of auto-growth events that occur, concurrent activity, number of indexes, existence ...


0

This is your query: Select vname.Value, vgender.value, vindustry.value from ABC [vname] with (nolock) join ABC [vgender] with (nolock) on vname.id = vgender.id join ABC [vindustry] with (nolock) on vgender.id = vindustry.id where vname.field = 'Name' and vgender.field = 'Gender' and vindustry.field = 'Industry'; You can try ...


2

1) Generally How much time it would take to delete such no of records ? It depends on many factor like Is there any BEFORE/AFTER DELETE triggers Too many Indexes Cascade delete Number of rows to delete Deadlocks and blocking 2) If there is any Index on that table then is it gonna affect the delete statement ? Note: I have a non-clustered ...


4

Rewriting NOT EXISTS as DISTINCT over an inequality join does allow the view to be indexed, but there are good reasons this is not commonly done. The execution plan generated to build the index on the view is unavoidably horrible. The inequality forces a nested loops physical join, which with the exception of one value, is a cross join. Collapsing the ...


8

No SQL Server does not hold a latch. The storage engine keeps track of the current index scan position using a "cookie". The cookie is revalidated if another process takes a latch (of a type that means the page might have been changed) on the same page since the cookie was acquired. If the cookie (scan position) is no longer valid, the b-tree structure is ...


3

Overall, the database metadata and the user data that you insert/select/update/delete are in general protected by Transactions, Isolation Levels, Latches, and Locks. Since your question is about metadata, a simple example: Locks queue up as necessary, so that they can be processed in 'order' according what ever constraints exist at the moment. Of course, ...


0

We have configured SQL 2008 R2 replication. Distributor is configured in continuous mode, publisher is scheduler is set to execute after every six hours. Now we want to change distributor schedule from continuous to schedule frequency without re-creating new replication. We have tried below store procedure, but it gives error message. Please help to ...


8

I took a slightly different approach, mainly to see how this technique would compare to the others, because having options is good, right? The Testing Why don't we start by just looking at how the various methods stacked up against each other. I did three sets of tests: The first set ran with no DB modifications The second set ran after an index was ...


1

SELECT t1.EmployeeID, t2.EmployeeName FROM Table1 AS t1 JOIN Table2 AS t2 ON t1.EmployeeID = t2.EmployeeID;


0

Rumtscho, If you're only creating 1000 rows per day, there is little to decide--use the INT data type with an Identity field and be done with it. Simple math says if you give your app a 30 year lifecycle (unlikely) you could have 200,000 rows per day and still be within the positive number range of an INT data type. Using BigInt is overkill in your case, ...


1

If you have the default trace running, and you should have it running, it may be able to help you see what happened. The default trace is lightweight and among other things does track Server Stop and Server Start. See Feodor Georgiev's article: ...


0

The answer is you cannot shrink log file if there is no free space available. Shrinking can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation. Can you run DBCC LOGINFO(DB_name) and see what is last value in status column. ...


1

There is a gap between the used values ? Or the starting values is 10.000 and from then on all are adding 1 ? Sometimes if the number is going to be given to customers, the initial number is greater than zero, let's say 1500 for example, so the customer do not realize the system is "new". The drawback of using bigint instead of smallint is that as bigint ...


0

I hope below will help with assumption that ID and OrganizationID is primary key. Select Org.Name, Usr.UserId, Usr.OrganizationID, Usr.Active, Usr.Canlogin, Org.[Custom Field] From Organizations Org INNER JOIN Users Usr On Org.ID = USR.OrganizationID Where Usr.Active = 1 Usr.Canlogin = 1 and Org.[Custom Field] = 0 You still have to change ...


6

This is a sort of bin-packing problem, so you'll most likely need to choose from one of the available approximate solutions, rather than attempting an exhaustive search. One very straightforward idea is to pack projects into groups choosing the project with the largest number of employees each time. As soon as the current project no longer fits into the ...


1

If you are really concerned hitting the upper threshold of INT for your PKs, consider using GUIDs. Yes, I know it's 16 bytes vs 4 bytes, but disk is cheap. Here's a good write-up of pros and cons.


0

Usually in sql server tables have primary key, that key is the relator of the table to be used in queries even if foreign keys are not set, if you open the tables one by one in design view you'll see the primary field marked with key symbol, if you don't see that, open the table in data view and inspect the data integrity try to find the main column in table ...


5

The main task to do is to find the root cause why the current value is that high. The most reasonable explanation for SQL Server versions prior to SQL2012 -assuming you're talking about a test database- would be that there was a load test followed by a cleanup. Starting with SQL2012 the most probable reason is due to several restarts of the SQL Engine (as ...


19

Without seeing code, it is pretty hard to say conclusively what is happening. Although, most likely the IDENTITY value is being cached, causing gaps in the value after SQL Server is restarted. See http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server for some good answers and info about that. A simple INT ...


3

After you enable the CDC on a database 2 jobs are created for that database: cdc.DBName_capture (which will start the change data capture collection agent) cdc.DBName_cleanup (which will clean up the change tables periodically) the default retention is 3 days (data older than 3 days is removed) the default schedule for this job is daily at 02:00 AM The ...


3

There is nothing native in SQL Server that is going to track that level of information. Based on what version of SQL Server you are using there is SQL Server Audit (SQL Server 2008 or higher) that can track database level access, but it does require Enterprise Edition. You could also roll out your own method of logging using Profiler or Extended Events that ...


-1

Local temporary tables should not have this problem, but if you want to ensure that the table creation doesn't cause a DDL conflict then just put a conditional drop statement at the top of the statement, see below. if ( object_id('tempdb..#t') is not null ) begin drop table #t end


1

You can find the logins on an instance by querying sys.server_principals and checking the is_disabled column to know if they are still useable. SELECT name, type_desc, is_disabled FROM sys.server_principals WHERE type IN ('U','S') The other notes you have in your question though indicate that this request is based on an application table. You state things ...


4

This isn't an answer to the actual question asked, but commentary too long for a comment. FWIW I don't create SQL Auth logins that obey Windows password policies. I can avoid that simply with: CREATE LOGIN foo WITH PASSWORD = N'bar', CHECK_POLICY = OFF; If I want logins to use Windows-based password policies, I'll make users log in with Windows ...


3

The lifespan of a login with Enforce Password Expiration enabled is set by Windows. I understand that in a Domain, the property is set at the domain level then propogated to the Windows account. I do not believe that you can directly change it.


4

Per the following article, This information can be found in: Start -> All Programs -> Local Security Policy Then navigate to Account Policies -> Password policy You should see something similar to this: http://www.mssqltips.com/sqlservertip/1088/sql-server-login-properties-to-enforce-password-policies-and-expiration/


0

I have run into this a few times and the issue has been a mismatch of data types in the export/import parameters. First thing to do is check those parameters by right click on source or destination, selecting Advanced Editor. Go to Input and Output Properties and check the data type for that column for all items - both External, Output, and Input. I have ...


0

This is already answered here Below is your query to answer. WITH RoleMembers (member_principal_id, role_principal_id) AS ( SELECT rm1.member_principal_id, rm1.role_principal_id FROM sys.database_role_members rm1 (NOLOCK) UNION ALL SELECT d.member_principal_id, rm.role_principal_id FROM ...


0

According to BOL, the modify_date from sys.all_objects shows: Date the object was last modified by using an ALTER statement. If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or modified. However, if you have a maintenance plan that recompiles stored procedures, using sp_recompile, or ...


4

The main disadvantage of high-performance mode is going to be your possibilities of failover. Because the communication between the principal and mirror server is now asynchronous with high-performance mode, the only way to failover is with a forced failover and possible data loss. If you had to do a forced failover to the mirror server, any transactions ...


2

That's a lot of NTEXT columns. You should know NTEXT is deprecated and you should consider refactoring to the newer datatypes, eg NVARCHAR(MAX). Regarding your specific problem, depending on what version and edition ( eg select @@version ) of SQL Server you are using, you could consider partitioning. Here's a simple demo of this might work for you and my ...


1

When I have encountered similar problems to this in the past, I have used the query that marc_s provides here: Know relationships between all the tables of database in SQL Server It should give you a good place to start? Here it is replicated in case the other thread is removed in the future (credit - marc_s): SELECT fk.name 'FK Name', tp.name 'Parent ...


4

Local table tables should never be shared across sessions. As you've pointed out, they are only visible to the session that created them and are destroyed when the creating session is terminated, or you drop them. Global temporary variables are visible to all sessions, but you'd need to define them with the double hash i.e. ##temp, for them to be defined as ...


1

Late post, but not a lot of good info available around multiple instances.. I have been advised by MS to install multiple instances of SQL server to work around spinlock problems due to Always On. It has not helped with push locks in Clustering Services though, as they sit below SQL Server instances.


0

Take a look at your SQL Agent Jobs on the instance that hosts the subscriber database(s). Looking at Job Activity Monitor you should see them under the category named "REPL-Distribution"


0

1) Open Run Menu and type regedit. 2) It will open Registry Editor 3) Now go to following location. HKEY_LOCAL_MACHINE > Software > Wow6432Node > Microsoft > Microsoft SQL Server > 90 4) Right click on 90 and click on Delete. 5) Press Yes to confirm deletion. Now try to install SQL Server 2008 and it would work this time.


0

The error is generic and misleading. You will also get this error when a PARTITION SCHEME has been assigned to the FileGroup you are trying to remove. Delete the Partition Scheme that is using the FileGroup fixed the problem for me.


1

Try this: select osd.UserId, osd.OfferId, ou.OfferName from dbo.offersentdetail as osd inner join ( select OfferId = Offer1Id, OfferName, OfferExpiryDate from dbo.[Offer1] union all select OfferId = Offer2Id, OfferName, OfferExpiryDate from dbo.[Offer2] ) as ou on osd.OfferId = ou.OfferId


8

APPLY TOP or ROW_NUMBER()? What could there possibly be more to say on that matter? A short recap of the differences and to really keep it short I will only show the plans for option 2 and I have added the index on Production.TransactionHistory. create index IX_TransactionHistoryX on Production.TransactionHistory(ProductID, TransactionDate) The ...


1

First of all Thanks Aaron, You're solution worked! Moreover just to add, i also used the tip from Kendra Little's Blog http://www.brentozar.com/archive/2014/05/4-lightweight-ways-tell-database-used/ The 4 tips including Aaron's made me figure out and come to a conclusion for the unused databases, took them offline and no alarms as of now.


0

Would that work? SQL version only. SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY CPT4, CPT4MOD, ITEMCHARGE ORDER BY EFF_DATE DESC) AS RowNo , * FROM #table ) AS T WHERE T.RowNo = 1 You can check it here: http://data.stackexchange.com/stackoverflow/query/260027/delting-duplicate-rows-with-condition


11

Let's start with the basic scenario. If I want to get some number of rows out of a table, I have two main options: ranking functions; or TOP. First, let's consider the whole set from Production.TransactionHistory for a particular ProductID: SELECT h.TransactionID, h.ProductID, h.TransactionDate FROM Production.TransactionHistory h WHERE h.ProductID = 800; ...


7

Your query is no faster with the index because SQL Server has determined that it would be more efficient to do a Clustered Index Scan, than use the IX_ActCost_ScenarioID that you have defined and perform a Key Lookup to retrieve the extra data needed. As you've only defined the index on ScenarioID, with no INCLUDE columns, each extra column you wish to ...


3

Firstly, check whether a spatial index is being used by looking at the query execution plan and see if there is a Clustered Index Seek (Spatial) item. Assuming it is being used, you could try adding a secondary/simplified filter based on a bounding box with simplified polygons to check for first. Matches against these simplified polygons could then be run ...



Top 50 recent answers are included