Hot answers tagged

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


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


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


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


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


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

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


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

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



Only top voted, non community-wiki answers of a minimum length are eligible