Tag Info

New answers tagged

1

1.Upgrading the server to SP1 or SP2: will it be worth the hassle? I have been searching for something clear-cut on the benefits of SP1 over RTM, or SP2 over SP1 over RTM Yes very much and believe me its not a hassle it will save you from lot of hassles. As already pointed you are running on unsupported version of SQL Server. Let me tell you personal ...


1

yes you can, using the UI. Select alerts and create one alert for the failure and one for the success conditions Then, select notifications and and tick email and on the combo, select when Job completes. Note: Job will complete Either with success or failure. However the alert, will determine what message to send out. Enjoy!


1

As Jonathan stated and the link shows CONTROL SERVER is required prior to SQL Server 2012 to create an Extended Event session. The ALTER ANY EVENT SESSION permission prior to SQL Server 2012 only allowed a user to start/stop a session, or change a current session; it will not allow them to create a session. CONTROL SERVER is required for that ability. If ...


13

You've got a bunch of different questions in here, so let's break 'em out individually. Q: If I join two tables in the same database with the above query, why is it slow? A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a ...


1

Your datatypes should be okay, however you need to change your call slightly and add OUTPUT after your @price. I've tested this against Oracle11g on INTEGER -> INT, VARCHAR2 -> VARCHAR, DATE -> Datetime using your getTest function exec ( 'BEGIN ? := db.pkg.getPrice(?,?,?,?); END;', @price OUTPUT, @id1, @id2, @curr, @ed ) AT ORASRV


2

Ok, here's my two pence: SELECT header.Col1, MAX((CASE WHEN details.ordinal=1 THEN details.Col2 END)) AS row1, MAX((CASE WHEN details.ordinal=2 THEN details.Col2 END)) AS row2, -- ... and so on.. MAX((CASE WHEN details.ordinal=99 THEN details.Col2 END)) AS row99 FROM ( --- For each Col1, enumerate all the rows and return Col2 ...


0

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time). Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I ...


0

I would make sure you update stats with the paranoid option. USE db_name; GO EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN'; Outside of that definitely go back and check what the differences are, and discuss them with your virtualization guys


2

After you deleted / dropped tables, you have to shrink the database to release unused space. Then do a backup and ask the client to restore. Note: If your database is in full recovery and you have never taken a log backup, then take a full backup followed by a log backup and then shrink the database. Caution: Using SHRINK is not a best and recommended ...


4

The sum of the file size for all data files has to be < 10 GB, not the amount of data in the file. So, deleting data from some tables, or even dropping some tables, does not solve problem. You need to shrink the file, something like this: ALTER DATABASE mydb MODIFY FILE (name = 'logical name', size = 2048MB); Then take a backup, then restore on SQL ...


0

My company has TDE encryption enabled for some of our databases. The password that the master key was encrypted by for one of our servers is lost. We do have the backup .key file, however, the password it was encrypted with is lost too. We also have the certificate backed up, but those passwords are lost as well. So you know the backup ...


0

If you're trying to make your lines shorter then you could use IIF( ..., 1, 0 ) instead of CASE ... THEN 1 ELSE 0 END. SELECT Column1 = IIF(@Something = 2 AND @SomethingElse IS NULL AND @AnotherThing IN (1,2), 1, 0) Column2 = IIF(@Something = 3 AND @SomethingElse IS NOT NULL AND @AnotherThing IN (2,3), 1, 0) ; Note: Only available from SQL ...


3

This will do it: DECLARE @d DATE = '20140502', @dm TINYINT = 26; DECLARE @Start_Of_Fiscal_Month DATE SET @Start_Of_Fiscal_Month = DATEADD(DAY, @dm-1, DATEADD(MONTH, -1 + DATEPART(dd, @d)/26, DATEADD(DAY, 1-DAY(@d), @d))); SELECT @Start_Of_Fiscal_Month SELECT DATEDIFF(dd, @Start_Of_Fiscal_Month, @d) + 1


0

It was a while i posted this, i did solve the problem after i while. The problem was related to Symantec Storage Foundation. KB here: http://www.symantec.com/business/support/index?page=content&id=TECH125163 In the article it says you need to call them to get a hotfix, but SP2 that is avalible for download also solved the problem.


1

Unfortunately you are not going to really get around SQL's verbosity here. You could write a function (of family of functions) to reduce the amount of text in the query, so you have something like: select Column1 = dbo.YeyOrNay(Something, 2, SomethingElse, 1, AnotherThing, 1, 2), Column2 = dbo.YeyOrNay(Something, 3, SomethingElse, 0, AnotherThing, ...


5

T-SQL doesn't support Booleans like that. But you could use CROSS APPLY with a subquery that uses CASE to produce a bunch of 1s and 0s, and then combine them in the SELECT clause using the bitwise operators & and |.


1

A slight variation on your's and Thomas's that will do it with a single index scan. WITH FlagFirstMax AS ( SELECT Account_ID, Tran_Date, Tran_Amount, ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Date ASC) isFirst, ROW_NUMBER() OVER (PARTITION BY Account_ID ORDER BY Tran_Amount DESC, Tran_Date ASC) ...


1

This should return tag the records that need attention. I put the tagging in SELECT, but you could easily turn this into a second CTE and simply select out the payments to clean up. -- -- find all accounts with more than one payment and mark payments to cancel -- WITH cte_DuplicatePayments AS ( SELECT COUNT(*) OVER(PARTITION BY accountID) AS ...


7

DECLARE @d DATETIME = '20140311', @dm TINYINT = 26; SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d))); Result: 2014-02-26 00:00:00.000


1

This does it in a single table scan: SELECT Account_ID , MIN(Min_Date) AS DateFirst , MIN(DATEDIFF(day, Min_Date, NULLIF(Tran_Date, Min_Date))) AS Days_Passed , MAX(Tran_Amount) AS Max_Amount FROM ( SELECT Account_ID , Tran_Date , Tran_Amount , MIN(Tran_Date) OVER ( PARTITION BY Account_ID) AS Min_Date , MAX(Tran_Amount) OVER ...


0

Here some procedures which helps you to resolve your problem please go through it Free the drive volume to enable expansion of SQL backup files. Run defragmentation process and analyze the level for all volumes to ensure that it is more than 15%. You can also follow the under mentioned workaround to rectify SQL Error 15105 issue: Go to the location on ...


0

Hi Please assign permission for that user and run following statement. use database grant select on view_name to login_name after executing this command your problem will resolve.


4

To clear things up, language-wise: In SQL Server, the login is global for the SQL Server instance (a "server principal") and allows a client to log on to the server with very limited permissions. Once you've set up a login, you can assign users to this login. A user is the login's security principal in a specific database ("database principal"), which means ...


5

Since a table might only be referenced once a year (think annual reports), and since they may be referenced only in some developer's (or your CEO's) code or spreadsheet on their desktop, these approaches do not work: tracing the database to see if any tables aren't referenced for a day / week / month finding some reliable way to determine when the table ...


1

A StackOverflow answer has a possible explanation: http://stackoverflow.com/questions/13037668/what-does-it-mean-to-have-jobs-with-a-null-stop-date/13038752#13038752 To quote: Each time the SQL Agent starts, it puts a new row in syssessions and subsequently any jobs run will get that session_id in sysjobactivity. For your jobs that have a null ...


1

Will the ALTER TABLE command that changes only COLLATION of a column and nothing else increment values of a TIMESTAMP column on SQL Server 2008 R2 Std.? No it wont change/incement the values of timestamp column. altering a collation is specific to that column only. From BOL : The value in the timestamp column is updated every time a row containing ...


3

This is not the installation of SQL Server itself. It is much more likely one of the following: a log file for a database (or databases) with a lot of activity, in full recovery mode, but you have never taken a backup or a log backup. a data file that you have populated with a lot of data (perhaps inadvertently, like tempdb) or attached a large database ...


1

Even if you keep your databases on other drives the default installation puts the system databases on C:. This includes tempdb which can grow fairly large depending on your application(s). This can be moved though, see here amongst other places. Another possibility for SQL server suddenly seeming to take more space on C: is that backups go there to by ...


0

So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections. This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a ...


2

First, some background information So far (until SQL Server 2014) the query optimiser will only take a few things into account: The number of CPU cores available on the system The current amount of memory in use CPU cores: The core count assigned to SQL Server from the affinity mask determines how efficient it can possibly be to create a parallel plan. ...


2

Stop using CAST and use CONVERT. Specifically because, in this case, you need to use a certain style parameter in order to get the right string comparison (otherwise it is just converting 0xwhatever to the string value represented by 0xwhatever, which is not 0xwhatever. Compare: SELECT CONVERT(VARCHAR(32), 0x48656C6C6F21), CONVERT(VARCHAR(32), ...


1

You might try looking at msdb.dbo.sysjobactivity. This table includes the session_id and the job_id for actively running jobs. I would expect you could take that and then connect back into the sessions DMVs to get your information.


3

A #temp table works just like any other table in the sense that you can write SELECT, UPDATE, INSERT and DELETE statements on it. Examples: UPDATE #someTable SET someColumn=123 WHERE someCriteria=1; SELECT * FROM #someTable;


2

You can use the hint WITH (RECOMPILE) while creating / executing your store procedure. CREATE PROCEDURE sp_GetRespond WITH RECOMPILE OR EXECUTE sp_GetRespond WITH RECOMPILE Every times, the sp is run SQL Engine re-compiles it and generate the most optimal execution plan.


1

Why not just use the SQLNCLI10 provider? Are you required to use MSDASQL? Here is a sp_addlinkedserver script adding a linked server named ServerST that points to ServerAR. EXEC master.dbo.sp_addlinkedserver @server = N'ServerST', @srvproduct=N'ServerST', @provider=N'SQLNCLI10', @datasrc=N'ServerAR' Update 19/082014 Download SQL Query Stress Tool ...


0

No. In environments where you want to enforce this, you're best off using a third party appliance that sits between SQL Server and its network, like Guardium or Imperva. These appliances are like SQL Server firewalls that audit for compliance, plus can filter unsecured traffic.


3

When you create job using maintenance plan automatically job is created in SQL Server agent under Jobs. So now go to SQL Server agent expand jobs look for your job. Right click on it and select Script job as...Drop and recreate to..New query editor window. you can get the script Edit: You can export the maintenance plans as well. You need to connect to ...


2

I read that the change in recovery model only has effect after a backup is taken. However, if after the performing the above I do This is mainly for when you change from simple recovery to full. After changing from simple to full you need to take full backup to take database out of Pseudo simple recovery. Pseudo simple recovery means even if database is ...


2

It depends on the method you're using to alter the column. We'll start with a simple table: CREATE TABLE dbo.Customers (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, CustomerName NVARCHAR(200)); CREATE INDEX IX_CustomerName ON dbo.Customers(CustomerName); Then use the SQL Server Management table designer to change the CustomerName column to a VARCHAR(200) ...


2

Carlo, do you have other ideas? You jus took a backup, so when you write the execute SQL Statement part of your job reference the MSDB backup history information tables to pull out the latest full and any other diffs/logs/etc that you would like to apply. The data can be found in msdb under the dbo schema and will give you the file names, etc, so there ...


0

I know it's silly to answer my own question. But I think I've fixed it. Thought I'd share the answer in case anyone else comes across this exact same issue. All it took was the addition of IMEX=1 to the Excel connection string. I stumbled across this post which explains more. ...


2

I primarily use them when I'm moving or copying a database to a new instance of SQL Server. In that case, the DMK in the database will be re-encrypted with the new instance's service master key, but the objects encrypted by the DMK (in your case, the symmetric keys) will not be updated accordingly. You'll need to restore the DMK from a separate backup using ...


3

Try changing the function to a non-inline table value function, with a clustered index. This will store the tvf's result set in an internal temp table, which may help you. A draft of what I mean: CREATE FUNCTION [dbo].[GetIndicator] ( @indicator varchar(50), @refDate datetime ) RETURNS @out TABLE ( Id int NOT NULL, ColINT_1 int NOT NULL, ...


0

Do you have auto-shrink enabled on the DBs ? And/or do you have scheduled maintenance plans performing index rebuilds ? An auto-shrink followed by index maintenance will produce significant log volume. Index maintenance by itself will also product significant log volume, if there are DB design issues like clustered indexes on GUIDs.


0

per Aaron's link above, The database master key is used to encrypt other keys and certificates inside a database. If >it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data >encrypted using them will be effectively lost. So, if the master key has gotten corrupted during the database backup, storage, or the restore, ...


6

All of your current tests (DBCC SHRINKFILE, log_reuse_wait_desc) are simply proving that right now you are reusing the transaction log's virtual log files appropriately. But when your auto growth events happen on your transaction log file it's a reaction to the log not being able to be reused. Oftentimes, that isn't an ongoing condition (exactly how it ...


7

It is impossible for us to guess what is causing it, but SQL Server doesn't just grow a log file to 300 MB for the heck of it, it grows to 300 MB because at some point since your last shrink operation, it needed that much log space (whether due to some big single transaction or a lot of smaller concurrent ones). You'd have to trace log file growth events (I ...


3

No. To my knowledge, changing a column that is included in an index is not possible (you'll get an error message to that effect). You would have to drop the index before changing the column, and then re-apply the index again. The only exception to this that I am aware of is that you can change a column from NOT NULL to NULL without having to drop the index ...


0

Open SQL Server Management Studio expand instance of SQL server expand Replication Folder expand Local Publications Right click publication that you want to check for any expired subscriptions and choose 'Launch Replication Monitor' expand My publishers, right click on publisher in question and click 'Configure replication alerts' Choose 'Replication ...


0

For this I would create a second monitoring job to look at sysjobhistory in msdb. This will work best if your current job typically runs within a fairly well defined time interval. If it doesn't then you cannot schedule the monitoring job until quite late in the day, by when it may be too late to do anything meaningful to correct the situation. Be aware ...



Top 50 recent answers are included