Tag Info

New answers tagged

1

Refer to the link Aaron Bertrand provided. There are a limited set of operations that are minimally logged, but reorg/rebuild aka defragmenting are not included. If you wish to minimize the effect, you'd have to do incremental reorgs as mentioned by Cibi or just allocate the needed space if this is expected to occur regularly. I've had to do this before in ...


2

It might be related with leaking connections exhausting the connection pool. If the connections are not released properly they will remain active and will not be returned to the connection pool until the next Garbage Collection executes. If a lot of connections are opened in a short time the connection pool might fill up before the GC runs, and the following ...


0

Actually, everything stayed the same, I found out that the service account some how lost the CONNECT permission. So, I gave the service accounts the CONNECT permission and all the mirrored database synced as expected!


0

I found a more hacking way. Every Execution Plan is one record in the msdb.sysssispackages table. You can copy records from the msdb.sysssispackages table of source server to the msdb.sysssispackages table of the destination server. Simply create a linked server on source server and use this Insert from the source server: INSERT INTO ...


0

If you stop it (disable it), the index won't be available for queries using it. However you can set bchange tracking to manual: ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING MANUAL GO Update... ALTER FULLTEXT INDEX schema.table START UPDATE POPULATION GO ALTER FULLTEXT INDEX ON schema.table SET CHANGE_TRACKING AUTO GO You can check the ...


1

If you're comfortable with Data Collector, you could download and install ExtendedTSQLCollector and set up a collection set to capture Blocking and Deadlocking events with XE. I have a blog post that explains how to do that. In order to collect sleeping SPIDs that cause blocking, you could set up another collection set (or an additional collection item in ...


2

You can clear the log during the REORG process as its not done as a single transaction. The log bloating depends on index fragmentation level and amount of work done. Again, REORG is a fully logged operation on all recovery models. https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


4

There is no hidden impact in raising the cost threshold for parallelism. As low as it is, SQL Server ships with a default cost threshold of 5, which is always taken into account when the optimizer compiles query plans. So, in this regard, the amount of work for the optimizer is the same. Whether raising the CTFP to a very high value is a good idea or not, ...


4

For Microsoft Dynamics CRM database server instance, you have to set MAXDOP 1. If using Enterprise edition, above answer using Resource governor makes sense. You can also look into using query hint OPTION (MAXDOP N) where N is a sensible value calculated based on number of logical processors and NUMA nodes of your sql server instance. Make sure that ...


5

There is an easy solution if you have Enterprise (expensive) Edition in use: Set maxdop at instance level as high as you want and use the resource governor to restrict all CRM users with their own workload group to maxdop 1.


3

This is what I use to monitor sleeping SPIDs causing blocking issues: SELECT ,s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS ...


1

Mirroring being dropped in future version is an annoyance to many who can't use AO instead (mainly due to the enterprise edition requirements), but if you need to use it you'll find it present and supported as far as SQL2014 (in MS rlease terminology deprecated means "we intend to remove it at some point, so be prepared to switch sooner rather than later" ...


6

The following assumptions have been made (some of them possibly repeating parts of your description): SF/FLEX is always followed either by a number immediately or by a space character and then a number. There is always one space character before SF/FLEX, unless the item is at the beginning of the string. There is always a comma or a space after the number ...


2

Is it possible to redesign your schema? It feels like you are making life harder for yourself by basically trying to pivot the data you're importing from the excel spreadsheets. CREATE TABLE dbo.Hardware -- hw? ( [Event_Num] INT NOT NULL PRIMARY KEY, [Name] NVARCHAR(100) NOT NULL, [Install_Date] DATE NULL, -- Install after pulling? ...


2

I just quickly grabbed your query and re-wrote it without looking at how you rewrote it. SELECT DISTINCT a_stmt.cyc_dte, a_carr.carr_nm, a_stmt.amt_du_ic FROM a_stmt LEFT OUTER JOIN a_icc on a_stmt.ic_cntct_id = a_icc.ic_cntct_id INNER JOIN a_carr on a_carr.acna = a_stmt.acna INNER JOIN s_dataccs on s_dataccs.acna = ...


8

This is most easily implemented in SQL Server using a Recursive Common Table Expression. Table definition DECLARE @binaryUser AS TABLE ( id integer NOT NULL, joiningDate date NOT NULL, placement char(1) NOT NULL, pId integer NOT NULL, cId integer NOT NULL, referBy integer NOT NULL ); Data INSERT ...


1

If my understanding of your question is correct and you are only wanting to return records from tbloffer if there are also records in tblRecord matching those buyer ids, then you should be able to add a clause within your join so that it will only return those records. I think this will cover it for you. SELECT COUNT(DISTINCT r.RecordID) AS Lines , ...


1

I agree with NReiligh but if you still want to extract values use combination of CHARINDEX and SUBSTRING functions of SQL SERVER SELECT [PEvent] ,[PDate] , [PDescription] ,CASE ------------------------------------------For SF Values WHEN [PDescription] LIKE '%SF%' THEN CASE substring([PDescription], ...


1

You could try shutting down all instances of SSMS and deleting the file SqlStudio.bin, which works for clearing out things like the MRU list for server connections. Just note that this will also wipe out other preferences, so you may want to export certain settings (like if you've customized fonts and colors, startup options, etc). Also, why is SSMS RTM? ...


1

I must say this is normal and nothing to worry about. This blog tells how target server memory calculated. Please see the forumula below Target1 = Current committed pages of SQL Server + ( Available Physical Memory - min (Total Physical Memory Pages / 20, Available Physical Memory Pages / 2)) ullAvailPageFile: The maximum amount of memory the ...


0

I would go for a 2 step solution. I use simple SQL. You can use the right names and fields. First I would update the existing rows: update tab1 set tab1.field = tab1.field + tmp1.field from table1 tab1 inner join temp1 tmp1 on tab1.key=tmp1.key; Secondly I would insert all non-existing rows: insert into table1 (key, field) select key, field from temp1 ...


4

An INSTEAD OF INSERT trigger performs actions instead of what the original INSERT would have done. In your code, if either @appr_username or @appr_id is NULL, some sort of change is made to the base table (an insert or an update). Otherwise, nothing is done by the trigger (no rows affected), so the AFTER triggers are skipped. After all, SQL Server thinks, ...


1

You cannot set IPAll to "no" on a failover cluster instance (as mentioned before), see: How to configure SQL server to listen on different ports on different IP addresses? More than likely, though, you are wondering how to secure your failover cluster instance of SQL Server to a certain IP address, or to verify that it isn't indeed listening on all IP ...


1

This is by design: Case 4: SQL Server is installed in a clustered environment. On cluster, you cannot configure SQL Server to listen on a specific IP addresses. You must chose IPALL. Source: How to configure SQL server to listen on different ports on different IP addresses?


1

When you say, "without using triggers", do you mean any triggers or just row-by-row triggers on tables? I ask because you may be able to get what you want with judicious use of the CONTEXT_INFO() function, but you would need to ensure that SET CONTEXT_INFO was called correctly before your operations take place. One place to do that might be a ...


1

If your first test showed a problem, assume that there is a problem! Problems don't just disappear on production, even if you have the best possible theory for their existence, you still need to test. BCP copy should be using row-level locks by default. Which should escalate to page-level locks due to the size of your data. If despite these locks, you are ...


2

Please take a look at the connect bug: https://connect.microsoft.com/SQLServer/feedback/details/622195/adding-user-hostname-application-name-and-timestamp-to-cdc-information This behavior is by design. CDC is designed to expose the following information about a change: updated columns, type of operation and transaction information. It has not been ...


2

I am not sure about CDC, but if the login has view server state permission you can use DMVs to get some information. This is given in Books Online here. I changed the query to add columns which would give you the IP address: SELECT c.session_id, c.net_transport, c.encrypt_option, c.auth_scheme, s.host_name, s.program_name, ...


1

If a query keeps running it has to be waiting for something. Since your query is fairly simple and runs fine after a restore it's most likely waiting on a lock. Some other query is probably holding a lock on the table blocking your query, but that other query is killed when you restart SQL Server. That query most likely is run again a bit later locking the ...


0

How would I extract the value of 0x9473FBCCBC01AFE from an XML query plan as a VARBINARY, rather than a VARCHAR? I faced something like that using HeidiSQL to query on CASD tables, and solved with fn_varbintohexstr(), like this: SELECT master.dbo.fn_varbintohexstr(table.hexfield) FROM table; With HeidiSQL, the value was wrong like ...


0

declare @MyString varchar(500) set MyString = '1,2.3#45.#,.6' select dbo.RemoveChars(MyString, '#,.') create function [dbo].[RemoveChars] ( @InputString varchar(MAX) ,@CharsToRemove varchar(500) ) returns varchar(MAX) as begin declare @len int ,@Counter int ,@OneChar char(1) set @Counter = 1 set @len = ...


0

Basic auditing means a server level audit. if you want to use database level auditing you need the Enterprise Edition of SQL Server 2012. I have tested to create a server level audit on a SQL 2012 Express instance, and you can but you need to script it and you can't use the GUI for that. I tried it with SSMS 2012 (not the express) and it simply does not a ...


0

Create clustered index ix1_table1 on dbo.table1 (col1) with (online = on) This will take a little longer, the online part, but it will allow users to continues using the table while the index is being created. Every table (with very few exceptions) should have a clustered index.


1

Next time this happens, run sp_WhoIsActive (download | documentation) and see who is running what and look over the logic. Check to see if the TSQL can be optimized to run quicker or perhaps chop it up into smaller transactions. I've had cases where the transaction log of a bad query by a report writers, data loaders, etc. would grow the transaction log ...


5

SQL Server runs locally with IIS to serve its websites The main problem I see is having sql server and IIS running on the same server. IIS serves website and SQL Server should be on a different server and only allowed connection from IIS to SQLServer. There are things that you can do to mitigate the problem: Change SQL Server port away from default ...


6

So the IIS on this server is intended to be externally accessible from your internal data network but the SQL Server instance on this same server is not meant to allow for external connections except for only through the web interfaces of your applications, correct? If so, then is not adding an explicit rule in the Windows Firewall on this server to allow ...


0

Sorry I don't have enough rep on this site to post a comment @Edward Dortland: The link is from NancySon and I would also recommend posting the context of the article or the answer in case things get broken and lost: Maintenance Plans Maintenance plans don't get their connections changed to the new server name and so they may break. After a rename ...


0

You need a recent CU for TLS1.2. See https://support.microsoft.com/en-us/kb/3052404: Cumulative Update 8 for SQL Server 2014 Cumulative Update 1 for SQL Server 2014 SP1 Cumulative Update 6 for SQL Server 2012 SP2 Cumulative Update 7 for SQL Server 2014 After enabling only TLS 1.2 you will possibly encounter two errors: SQL Server 2014 Agent will not ...


1

As in the other answers: you need a recent CU for TLS1.2. See https://support.microsoft.com/en-us/kb/3052404: Cumulative Update 8 for SQL Server 2014 Cumulative Update 1 for SQL Server 2014 SP1 Cumulative Update 6 for SQL Server 2012 SP2 Cumulative Update 7 for SQL Server 2014 After enabling only TLS 1.2 you will possibly encounter two errors: SQL ...


1

You mention Paul Randal in your question, so I assume you have seen this post where he talks about this stuff in great detail. The ACCESS_METHODS_DATASET_PARENT Waits frequently deal with Parallel queries (as do your 30% CXPACKETS) and as Paul says in the blog that I linked, don't knee-jerk and set MaxDOP to 1 to get rid of those. But I would wager based ...


6

It seems you don't really need the "frequency" for the sorting, only the "maximum frequency" per customer. You can use the COUNT(*) OVER (PARTITION BY customerID): SELECT customerID, eventID, ROW_NUMBER() OVER (PARTITION BY customerID ORDER BY eventID) AS frequency FROM customerEvent ORDER BY COUNT(*) OVER (PARTITION BY customerID) ...


1

ALTER DATABASE ENCRYPTION KEY Requires CONTROL permission on the database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key. You must also add view definition according to MSDN. https://msdn.microsoft.com/en-us/library/bb630389%28v=sql.110%29.aspx


2

To migrate, I will take full backup of 2008 and restore it on 2012. I want to know if the compatibility version internally remains 2008 or changes to 2012? Restore database backup on New SQL Server 2012 Server and then change compatibility level from 2008 to 2012 for each database. Use one of the following methods. i) Use script to change ...


2

I did small tests Database compatibility level remains the same. 1.Created dummy database on 2008r2 2.Restored it on 2012 So while restoring,i could see version being changed,but after restoring compatability level remained the same.So you need to alter database to 110 compatibility level to get benefits of SQL 2012 Database 'backupdb' running the upgrade ...


2

Converting comment to an answer. This is not my area of expertise, but you might look at Dennes Torres Simple Talk post from January 2015 for any takeaways that help you. https://www.simple-talk.com/sql/database-administration/centralize-your-database-monitoring-process/ Two subheadings of particular interest may be: Configure Data Collector through ...


3

In addition to setting Local DTC Properties, you need to open the firewall on both machines to allow program Distributed Transaction Coordinator to go thru. When testing T-SQL in Query Editor Window, make sure to turn on XACT_ABORT as suggested by Microsoft. XACT_ABORT must be set ON for data modification statements in an implicit or explicit ...


6

SQL Version: Microsoft SQL Server 2012 - 11.0.5343.0 (X64) Response: The pointer to the LOB_DATA page (your 24 byte pointer) is only used when the row containing your VARBINARY(MAX) column (s) no longer can fit on the page. I am still working on matching the exact scenario you mentioned above. Reference(s): Slot Array and Total Page Size USE ...


1

You can add: (319+2+2)/8 for nullable bit table (1 bit / columns) and 16 for datetime and 32 for GUID and a couple more bytes for various info 2 for row info and status 2 for fixed length column size in bytes 2 for number of columns in table 2 for number of variable length columns and 318*2 consecutives bytes for variable length columns(s) offset(s). ...


1

Another option that could handle this and possibly allow for a bit more flexibility is to use a SQLCLR function or stored procedure that makes use of the SqlBulkCopy class (which is essentially the same framework used by BCP.EXE, BULK INSERT, etc). While writing such a function/procedure might not be worth the time/effort for a one-off project, there is ...


3

On a clean install, just do: SELECT role_name = pr.name, member_name = m.name FROM sys.server_role_members AS r INNER JOIN sys.server_principals AS m ON r.member_principal_id = m.principal_id INNER JOIN sys.server_principals AS pr ON r.role_principal_id = pr.principal_id; Here's what I get: sysadmin sa sysadmin CANUCK\aaronbertrand -- Administrator ...



Top 50 recent answers are included