New answers tagged

0

In SQL Server 2012-2016 you have the concept of Segment elimination (http://social.technet.microsoft.com/wiki/contents/articles/5651.understanding-segment-elimination.aspx) and you actually can force the order of at least 1 column (plus 1 extra inside each of the partition). Consider the following article that explains data loading for better segment ...


2

That is about 7 requests per second if the rate is flat over the 24 hour period, which shouldn't have a noticeable impact on a decently specced SQL Server machine. If they are far more bunched then they may be more of a problem. I would be inclined to investigate where the requests are coming from. If they don't do anything useful then they may indicate a ...


1

What is probably happening is that SQL Server wants to download the whole remote table to your tempdb before attempting to join to the local table, including the XML column. Try something like this: SELECT TA2.ID FROM [DB].[dbo].[table1] TA2 INNER JOIN ( SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol FROM [linkedsrv].[DB].[dbo].[...


0

It looks like replication is what you need here. There are different flavors of replication (merge, transactional and snapshot) that are suitable in different situations. In this case, it looks like transactional replication is what you need, unless changes on the secondary copy have to be propagated to the original copy of the data. I fail to understand ...


0

Irrespective of Recovery model,shrinking wont help you if there is an active transaction or due to the reasons mentioned here Transaction log space will be reused in all recovery models irrespective of Size shown in file Explorer,you may still have free space.. Here is what MSDN has to say about Log space reuse.. Log truncation frees space in the log ...


0

You can do this for all databases (even MySQL - which doesn't have Window functions or CTEs - i.e. WITH clauses). To solve this, I did the following: Created a table CREATE TABLE table1 (Investor_ID int, my_date DATE, type VARCHAR(20), amount int); Note, never have a column named "date" (or any other SQL keyword). Populated the table: INSERT INTO ...


0

Sounds like you don't care about the records unless they are of type "Balance Update." Also looks like you're just trying to return the highest of the "balance Update" records, and not a Sum of them. In that case, I think this will do the trick. This query shows what the second one is doing, which is using the ROW_NUMBER functionality to order the records ...


2

Yes, it failing is expected. It is documented here. AlwaysOn Availability Groups and SQL Failover Cluster Instance and Privileges When installing the Database Engine as a AlwaysOn Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY ...


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 ...


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.


5

@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 ...


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 ...


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 ...


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

Adding MSSQL as a Generic Service Resource is not the Answer. That will just put the Cluster Manager in charge of the SQL Server Service, OK, yes it will fail-over automatically, but you will notice in SQL Server Configuration Manager that your services are now set to "Manual" indicating that the Cluster Manager is now in control of your SQL server Service. ...


3

Run this query: SELECT ASCII(' ') AS [SingleSpaceString] , ASCII('') AS [NoSpaceString] You will note the the Value for [SingleSpaceString] is '32', while [NoSpaceString] is NULL. I'd suggest adding ASCII(Box.Ice) to your select statement to confirm that a true space is the value, vs. an odd character.


3

The longer, more answer-y version of my comment Having 500k rows of backup information logged in msdb isn't really necessary for 30-ish databases. Even if you were taking log backups every minute, it would take a long time to get there. What's a reasonable amount of backup information to retain? Probably as much as you retain physically. If you keep ...


0

So I didn't got any luck with the deadlock graph. Doesn't seem to appear in my SQL Logs. But, I did find out that another job has been scheduled to run at the same time than my job, and it was taking backups and sending e-mails at the same time that my job was trying to cleanup the mail history. So changing the schedule of the other job got my problem solved!...


2

As you explained in the comments section, the objective is to prevent developers logging into production server. There are many ways to achieve this. The first and foremost is a company policy. Talk with your manager. Prepare for the meeting by making a list of reasons why access should be restricted. In addition, try to invent a number of counter-arguments ...


1

Quite often you can use row_number() to select only certain rows of the data. so maybe something like this would work: select * from ( SELECT instance_name , DU.database_name , DU.collection_time , DU.dbsize , DU.logsize , DU.reservedpages , DU.usedpages, row_number() over ( partition by instance_name, DU....


2

ALTER TABLE ... SWITCH can sometimes be useful for this type of thing. Specifically it can allow a large table with an IDENTITY column to be switched into a different table with the same structure but no IDENTITY column thus (after a drop and a rename) effectively allowing the IDENTITY property be toggled as a metadata change (example). With the tables ...


2

I believe what they are talking about is a partition SWITCH. Not really what you are looking for here. It's great for loading data into a table quickly (from the point of view of the user) but not really for an entire table. If I were you I'd do a re-name swap. Create a new table TableName_New where the computed column is just a regular column. Make ...


2

Man, I'm terribly sorry. I'm 'answering' stating that there's no simple solution as the one you devised. You presented us a very interesting challenge. I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself. If you, please, ...


3

the sub-select by itself still takes less than a second and returns about 20 rows ... Any suggestions? In that case you may as well materialize it into a #temp table and join onto that instead. SELECT BS.[database_name], DATEDIFF(HH, MAX(BS.backup_finish_date), GETDATE()) AS last_backup, DATEDIFF(MI, MAX(BS....


0

Just as Paparazzi mentioned, you are actually doing a product downgrade. But with everything already in place, I would suggest to download a copy of SQL Server Enterprise (Evaluation version) and try to upgrade the standard version, and if everything is fine, I will make the necessary changes (i.e. remove those enterprise features, such as partitions). ...


4

SQL Server Developer Edition is functionally equal to Enterprise Edition. The error is stating you are using features that only Developer or Enterprise support, such as Table Partitioning in this case. In order to use Standard, you cannot use any Enterprise features. Check Features Supported by the Editions of SQL Server 2012 for more details. One way to ...


1

If you are using SQL 2012 as your tag indicates, then you can do this with a recursive CTE, although it's a little different since you find the child first and then the parents. But Take a look at the sample below. The Setup DECLARE @Rentals TABLE ( PK INT NOT NULL , CustID INT NOT NULL , ProdID INT NOT NULL , OverrideByPK INT NOT NULL ...


0

Have you tried to give permission to Domain1\NewUser to MSDB(db_owner for tests)? Here you have a great article about the SSIS Proxy users: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/ Especially excerpt: EXEC msdb.dbo.sp_update_proxy @proxy_name = N'proxy_name', @enabled = 1


1

The file, when sent as an attachment, is converted into 7-bit ASCII, which makes the file a lot bigger than it would normally appear to be. As @M.Ali pointed out in his answer, you can adjust the maximum message size to accommodate your large query. However, what are you sending via email that needs to be so big? Are you sure that amount of info, in ...


2

You will have to change the allowed Attachment file size by going to the following location: Go to SQL Server Management Studio --> Management --> Database Mail This will fire up the Configuration Wizard. Click Next --> Select View or change system parameters --> click Next. Now change the Maximum File Size (Bytes) property and click Next, then ...


2

Unfortunately after searching SolarWinds' KB for about 10 minutes I cannot find what "Ineffective Statistics" measures. That information would be helpful. However, based on your question I'll infer that it means that the Statistics objects may be out of date or have taken quite a few modifications and Database Performance Analyzer has not detected an update ...


0

--STOP the Distribution Agent: sp_MSstopdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db --START the Distribution Agent: sp_MSstartdistribution_agent @publisher, @publisher_db, @publication, @subscriber, @subscriber_db</pre> Example: exec distribution.dbo.sp_MSstartdistribution_agent @publisher = '...


-1

For me it was the BitDefender firewall that made my SSM ultra slow Apparently, some sort of a firewall blocking for getting access to the database list


2

how to change the file directory of your log and data files and what are the effects Can I just change the settings of the instance to point it to the new directory, detach the existing databases move the data files and attach it back? Please share what would be the best approach in doing this. Below are two scripts I've used for this purpose ...


0

If you open the properties of the publication, then select the Subscription Options section, you will see a property named Replicate Schema Changes. Set that to true and any schema change will be replicated and display in the Distributor to Subscriber History in Replication Monitor as DDL change. No need to stop and restart any agents. I have found that you ...


1

No, the Developer Edition is for developers only. If it contains production data, you are not licensed and it doesn't matter that only the ERP system accesses the database, the users of your ERP system count as users of SQL server.


0

Some of those indexes seem to have a lot of included columns. A table definition will provide context into the width of these columns. What is the clustered index key? You need to look at the workload being run against this table. What queries are being executed, how often are they being executed, is this going to change in the future? I would probably ...


0

Just create a view CREATE VIEW test_view AS SELECT case when year(DOB)<=1900 then null else DOB end as DOB,sometext,id from test; and then use it instead original table: select * from test_view


4

While waiting to hear back on a few questions that I posted in a comment on the Question, I will at least reiterate one of my questions: "What about your current stats leads you to suspect that SQLCLR usage is in any way related to performance issues?" From what I see of your output, SQLCLR is taking up very little memory. It has 110 MB of physical ram for ...


0

So this is what I came up with as a workaround if the intention is that multiple people in your organization should be notified if a job fails and a different group of multiple people for successes. You will notice the Steps 1-3 are the normal tasks that the schedule job is uses for as you would do for your task. There can as many steps as needed before ...


1

Same version of Powershell on the servers/vms hosting SQL Server 2008R2, 2012, and 2016? A parsing error that removes the double quotes around the query would result in this error. Lonny Niederstadt


1

Seems like you're right on the tip of the answer. Just group by the customer, the year, and then the month. USE Masscomm_XT; WITH C0 AS ( SELECT CustomerName ,Minutes. ,year(Postingdate) AS PostingYear ,month(postingdate) AS PostingMonth ,PostingDate FROM dbo.reptview_LDUsageTrend ) SELECT C0.CustomerName ,C0.PostingMonth ,C0.PostingYear, SUM (...


0

Based on one of the suggestions that Dan Guzman gave me in your answer, and after read this question I found a workaround to my problem. Apparently, there is no way, in scenario as described, to grant to userX SELECT permission in the viewABC without grant also SELECT permission on tableA. The solution that I found was the following: in schemaD I ...


0

I would create a main sql agent job that executes the two jobs you mention in your post. You can execute sp_start_job to accomplish this, something like... Step 1: EXEC sp_start_job 'job A' Step 2: EXEC sp_start_job 'job B' Step 2 will not execute until Step 1 is finished.


0

A simple and easy way to do this would be to import the csv into a table, then manipulate/view. If you'd prefer a graphical method instead of using tsql; right click your database and select Tasks, and then select Import Data.


5

Very easy to test. CREATE TABLE T(X INT); GO INSERT INTO T SELECT TOP 10 1 FROM sys.all_objects; GO CREATE TRIGGER TR1 ON T INSTEAD OF DELETE AS DELETE TOP (8) FROM T; GO CREATE TRIGGER TR2 ON T AFTER DELETE AS SELECT COUNT(*) FROM DELETED; GO DELETE FROM T; DROP TABLE T; Answer 8 This is the correct behaviour. The after trigger should ...


0

You can refer to below link for how to read csv file in SQL Server: http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2015/02/09/t-sql-read-csv-files-using-openrowset/ Based on that you will be able to work with your csv data as a table and then you can create the update query as required.



Top 50 recent answers are included