New answers tagged

2

Can someone please confirm if this is correct? Yes, you are 100% correct! Is there any way we can mitigate this, ie. ensuring there's no index maintenance during this window or switching to aynschronous commit mode? The mitigation is not with the mode that the replicas run it, it's removing that replica from the AG. Does this pertain to my ...


1

It would appear so. I have not tried these, but the following is the list of items that can be added to an SSDT SQL Server Database Project: Broker Priority Contract Endpoint (ServiceBroker over TCP) Endpoint (SOAP over HTTP) Event Notification Message Type Queue Remote Service Binding Route Service


0

1) Yes. It's a maintenance/documentation nightmare but technically there's no reason it wouldn't work. 2) In general each null will be one bit of storage. So 80 null fields might be 10 bytes per row. The full answer is that it varies depending on data type but with varchar for the most part it's a good rule of thumb. Some alternatives where you expect ...


0

I think you can get what you need from the DMVs, including sys.dm_fts_index_keywords_by_document and sys.dm_fts_parser, something like this: SELECT p.phrase, kbd.display_term FROM sys.dm_fts_index_keywords_by_document( DB_ID(), OBJECT_ID('dbo.documentText') ) kbd CROSS APPLY dbo.phrases p CROSS APPLY sys.dm_fts_parser ( N'FORMSOF(FREETEXT, "' + ...


0

Try this. http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function/1378788#1378788 You don't need a different table, although if you have one it could help.


0

It's the daily change, if the snapshots are taken daily (snapshot_time column is where I assume you're getting the snapshot time data). Partitioning restarts computation each time a unique value for the column (or combination of columns) specified in the PARTITION BY is found. Even though you are sorting (ORDER BY instance, db, snapshot) you aren't ...


1

Assuming the procedure lives on the remote server: have you tried using the EXECUTE AT command? DECLARE @RunStoredProcSQL VARCHAR(1000); SET @RunStoredProcSQL = 'EXEC [Database].[dbo].[StoredProcName]'; --SELECT @RunStoredProcSQL --Debug EXEC (@RunStoredProcSQL) AT [LinkedServerName]; Print 'Procedure Executed'; That's what I have used successfully in the ...


0

Sounds like a job for SSIS. Set up an SSIS package, and use the Execute SQL Task to execute your stored procedures. You can then execute the SSIS packages through Agent jobs.


2

The query posted by @mrdenny didn't seem to return reliable data for me. I had more luck this way. select b.TABLE_TYPE, a.* from INFORMATION_SCHEMA.COLUMNS a LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES b ON a.TABLE_CATALOG = b.TABLE_CATALOG AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.TABLE_NAME = b.TABLE_NAME


0

Having been pointed in the right direction by @Phil, it looks like the following merge will work: MERGE userabilitytest USING (VALUES (1, 'EDITACCOUNT'), (1, 'EDITORDER'), (1, 'BOOHOO'), (1, 'FooBar') ) AS foo(account_id, ability) ON userabilitytest.account_id = foo.account_id and userabilitytest.ability ...


1

Try this: go create function dbo.fn_GetCountForPhrase (@phrase varchar(500)) returns int as begin declare @count int declare @localphrase varchar(8000) set @localphrase='"' + @phrase + '"' SELECT @count=COUNT(*) FROM table a WHERE CONTAINS(table.description, @localphrase) return @count end go SELECT phrase, dbo....


2

At the very basic level (and this is for SQL Server only as each vendor handles security slightly differently), IF others were not creating their own database objects (Stored Procedures, Functions, etc) and relying solely upon your API, then this is handled inherently through "Ownership Chaining". Ownership Chaining allows for implied permissions on objects ...


0

It turns out, I had done something really stupid. My SSIS package, as it turns out was perfect, I had named the transactions and set "RetainSameConnection" to true. Begin/commit/rollback commands were all specified correctly. My problem was that one of the stored procedures called as part of the SSIS package had a unnamed transaction in it, causing SQL ...


4

@Lennart's answer can be simplified to a single Group By: SELECT LogDate, StartOfDay, EndOfDay, DATEDIFF(s, StartOfDay, EndOfDay) AS ElapsedSeconds FROM ( SELECT -- instead of FORMAT better use a Standard SQL CAST: CAST(LogDateTime AS DATE) AS LogDate, MIN(CASE WHEN EventCode = 1 THEN LogDateTime END) AS StartOfDay, ...


1

You can modify your query to: SELECT LogDate, MAX(StartOfDay), MAX(EndOfDay), DATEDIFF(s, MAX(StartOfDay), MAX(EndOfDay)) FROM ( Select LogDate, StartOfDay = MIN(case when EventCode = 1 THEN LogDateTime END), EndOfDay = MAX(case when EventCode = 2 THEN LogDateTime END), EventCode FROM ( Select ...


1

To add a default is easy: CREATE TABLE dbo.t ( tid INT PRIMARY KEY, ggid UNIQUEIDENTIFIER NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000' ) ; Then the following insert will work: INSERT INTO dbo.t (tid) VALUES (1), (2) ; Test at rextester. If you want NULLs to not raise errors but be converted to the default, I think the easiest ...


2

I believe the problem is that you are executing all 5000 update statements as a single transaction. You need to break them up so each one is done in its own transaction. Then there are two options: If you are in simple recovery mode (or can temporarily switch the database to simple recovery mode), then breaking them up should be enough for the database to ...


1

SQL Server SSL encryption that is initiated from the server does not require that the client have a copy of the certificate in advance. The certificate and private key are stored in the certificate store of the service account on the server. If the certificate is selected using SQL Server Configuration Manager and Force Encryption is set to true, then ...


1

You could also fire this off in a few concurrent sessions. Only one session will be able to get the exclusive lock at any one time. BEGIN TRANSACTION; EXEC sp_getapplock @Resource = 'FooBar', @LockMode = 'Exclusive'; WAITFOR DELAY '00:00:30'; EXEC sp_releaseapplock @Resource = 'FooBar'; COMMIT TRANSACTION;


2

BEGIN TRANSACTION SELECT * FROM YourTable WITH (TABLOCKX, HOLDLOCK) WAITFOR DELAY '00:05:00' -- 5 minutes ROLLBACK TRANSACTION And then in another Query Window: SELECT * FROM YourTable


3

This is pretty easy to do. Open two query windows in Management Studio. Run something like this in the first one (choose a test table that nobody is using, because this will block them, and make sure it has at least one row in it): BEGIN TRANSACTION SELECT * FROM sometesttable WITH (TABLOCK, XLOCK, HOLDLOCK) Then in the second query window: SELECT * ...


1

Community Wiki answer generated from a comment by the question author Found the answer. Turns out, the CLR Assembly was used in 2 different databases. Needed to make sure that both had trustworthy bit turned on. Also, it is obviously doing something other than string splitting because I needed to create the assembly as UNSAFE because it says that it can't ...


1

It's always a tough thing to performance test production systems, companies typically have far lesser systems/hardware for QA/Staging/Dev environments. One method I use is to baseline performance of our QA system before implementing SQL changes, and then check performance after. I am not concerned with the actual times, but the delta of before and after. ...


2

I always take careful consideration when creating indexes on production systems, always try to create in DEV first. There are several reasons, but the top three for me are. First, there is a cost associated to indexes, essentially more overhead, so depending on your table the cost can be quite high. Second, I am never always privy to all the functionality ...


-1

AFAIK, this will (possibly) only affect INSERT/UPDATE tasks on the table, and rebuilding of indexes. I would confirm that any INSERT/UPDATE Tasks (Procedures) still run efficiently.


3

The basic command is ALTER SCHEMA NewSchema TRANSFER OldSchema.ObjectName so your command will be as below : ALTER SCHEMA dbo TRANSFER xpto.Table_Name Should I expect performance issues? No there would be no perf issues. But its a good practice to use schema.objectname TO do it for all your user tables, run below code, review it and then run the ...


1

You can use following PowerShell script to get last failover time Get-winEvent -ComputerName ListnerName -filterHashTable @{logname ='Microsoft-Windows-FailoverClustering/Operational'; id=1641}| ft -AutoSize -Wrap


0

sys.extended properties only had one record when I selected from it. As a result, I was getting no records back due to the INNER JOIN. Not sure if that's what your case is as well, but I would suggest joining to SysComments, Types, and sys.columns, and sys.types to get what you're looking for: ,ep.major_id ,sc.* ,c.* ,t.* from sys.objects as o LEFT join ...


4

First of all if your object names are xpto.tablename you probably created a schema not a new database. Schemas are the containers for objects such as tables. Databases contain schemas. So the naming format is databasename.schemaname.tablename. Assuming that is correct it's very easy to change the schema that owns your tables using ALTER SCHEMA. ALTER ...


1

Writing a parser to change specific bits of your queries would indeed require some effort. Adding just a single line to every one of them, on the other hand, seems a relatively easy task. So, you could mass-update all existing SQL queries/batches in your application with this line added to the beginning of each batch: SET CONCAT_NULL_YIELDS_NULL OFF; and ...


0

In my case, truncating and re-populating data in the concerned tables was the solution. Most probably the data inside tables was corrupted.


1

I had the same thing happening to me and my students during a SQL Server 2016 hands-on lab a couple of months ago. It is a weird error that shouldn't happen (it's definitely a bug that you should report). However, there's a workaround: create an Azure SQL Database manually, then delete it. From that moment on, you will no longer receive the error message.


0

If your database is set to full recovery model you can investigate your transaction log backups. See fn_dump_dblog for more informations.


2

You don't select those ids that are minimum in their systemno group so you select only those that have a lower id in their group: SELECT id FROM dbo.SomeTable st1 WHERE EXISTS ( SELECT * FROM dbo.SomeTable st2 WHERE st1.SystemNo = st2.SystemNo AND st2.id < st1.i )


0

Working out which Log Backup to restore is all based on LSN's. You have to apply all log backups since the full (or differential, but you didn't mention them) backup, including log backups taken while the full backup was in progress. There is no danger in trying to restore a log backup out of sequence, it just won't work. Kin answered above how to find the ...


2

The primary database file can never be removed because it contains special page structures that exist only on that file and cannot be relocated. If you want a single database file, move all data from the secondary file using DBCC SHRINKFILE with the EMPTYFILE option and then remove the secondary file. Finally, rebuild/reorganize indexes to reduce ...


1

Space will not be the only factor in where you place the new space. If you are talking about expanding the capacity of the existing drives you should look at what do the underlying volumes have backing them. I.e how many physical drives are assigned to the drive arrays and what the RAID levels are for those arrays. For HDDs the more drives you have in an ...


5

The difference is between CLR static methods (type_name::method()) and instance methods (instance.method()) Static methods are defined on the type itself and are generally utility methods that get everything they need to operate passed in as method parameters. Instance methods operate on an object of a particular datatype and are able to access the ...


4

I had the exact same situation and I created a SQL Agent job with the following script as the only step to monitor a job that occasionally runs long. if exists( SELECT * FROM msdb.dbo.sysjobactivity AS sja INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id WHERE sja.start_execution_date IS NOT NULL AND ...


2

Write a job that will run every 5 minutes using sys.dm_exec_requests and send you an email for anything with total_elapsed_time over 15 minutes.


0

how do I know which transaction log backups to select? You can find that out using msdb..backupset table. To automate that, you can use RestoreGene - TSQL or PowerShell version This procedure queries msdb database backup history and database file details in master. It builds and returns RESTORE DATABASE commands as it's result set, it does not ...


0

Are you referring to calling SQL objects from .NET code (such as C#, VB, C++)? Or are you referring to calling .NET methods from SQL Objects? The syntax you are using is not valid MS SQL syntax. Common MS SQL Syntax is: Database.schema.Column Database.schema.Function(arg) OR schema.function Database.schema.StoredProcedure(arg/param) OR schema....


0

My Data files are always kept in a separate drive from where the SQL install took place. Therefore, databases like master, model, msdb, and tempdb are always on one drive, while my (user) databases are on (one or more) separate drives. This is not so much for performance reasons as it is for Disaster Recovery/Security. it is much easier to restore a SQL ...


1

Instead of a synonym you could use a view. Views can be the target of an insert and also support alter thus meaning no gap between the drop and create to worry about.


3

You have a couple of options: 1) try the XML datatype and its methods (eg .nodes, .query, .value) or use OPENXML to drill down to the lowest level then then drill back up using the parent axis operator ('..'). There are pros and cons to each method (which you could research) but try both options with your data and see which works best for you. OPENXML ...


0

Had similar issue, for me it got resolved by installing .NET 3.5 and 4.5


0

What you are looking for is called Transactional Replication, which is very commonly used and a good solution for avoiding contention on a transactional database. There are three databases in a transactional replication system, which are the publisher, the distributor and the subscriber. The publisher is the source database, the distributor is where all ...


0

Each Go statement lets SQL Server know that the preceding commands are meant to be a single batch. This works for the create statements, but I suspect that if you try to use the symmetric key in a command after the batch, it will not be open. Remove the Go statements, then try to use the symmetric key after the open statement.


0

If this were me, I'd import the data into staging tables in SQL, then run comparisons and update what's needed. As far as physically connecting to Oracle, you should be able to use the OLE Provider for Oracle in SSIS.


0

Back in March 2016, Microsoft had a blog announcing updates to the SQL Server Incremental Servicing Model (ISM) and one of the things they have there says: We are also evaluating offering the latest CU as an Optional update on Microsoft Update, just like Service Packs today I agree with some of the other comments though that perhaps your servers are ...



Top 50 recent answers are included