Viewing 15 posts - 6,016 through 6,030 (of 7,619 total)
Jeff Moden (6/13/2014)
Jake Shelton (6/12/2014)
I'm looking for the most time-efficient way to backup db's to a local drive and then move the .bak files across to a new server,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 13, 2014 at 10:28 pm
I deferred converting the date to character until the very end to avoid having to convert every date and to avoid grouping on a char/varchar.
SELECT right('0' + cast(month(ClosingMonth) as...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 4:40 pm
You also need to get rid of all the ISNULL()s in the WHERE clause. You should follow this simple rule:
Never use ISNULL() in a WHERE clause.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 4:29 pm
The quick way would be switch the "=" and "<" in the current code 😉 :
delete table
from table t join
(select col1,col2,col3,col4, from table
where convert(varchar,insertdate,112) = convert(varchar,getdate(),112)
) y
on t.col1...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 2:39 pm
I also suggest considering average wait time as opposed to only using total wait time:
select
case when waiting_tasks_count = 0 then 0 else
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 2:37 pm
It deletes only today's date, but only if there are matching records before today.
Apparently, based on the code, only the earliest matching row, across the 4 columns compared, is needed...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 1:55 pm
It deletes rows with the current date/day if the table contains any row with a date before today's date where the 4 column values match today's 4 column values.
Edit: btw,...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 12:59 pm
SUBSTRING(sample_text, PATINDEX('%[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]%', sample_text), 10)
For example:
SELECT
sample_text,
SUBSTRING(sample_text, PATINDEX('%[a-z][a-z][a-z][a-z][0-9][0-9][0-9][0-9][0-9][0-9]%', sample_text), 10)
FROM (
SELECT 'some other 44 numbers and text 555-555-1212 for...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 9:42 am
Agreed, no.
But always use the SQL Server management tools to change SQL accounts, never the Windows tools.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 9:28 am
To be safe, I make them all "Text".
Select the whole spreadsheet (if it's not too large) or each column separately (if it's very large), do "Format Cells...", and on "Number"...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 12, 2014 at 9:25 am
You can have two log files, but it doesn't directly benefit you. When the first drive is (near) full, though, adding a second log file on a different drive...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 11, 2014 at 8:00 am
You might want to give the query below a try, although it will output a given row only once, even if it would have matched multiple conditions in the original...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2014 at 10:14 am
2nd log file is 140GB and free space 31GB
Wow, that's a lot of used log space.
Make sure you are taking proper log backups and/or that you don't have an old...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2014 at 10:00 am
For databases with a significant number of transactions, I would expect differential backups to speed up the restores somewhat. My reasoning is that applying tran logs requires that every...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 10, 2014 at 9:58 am
What you really need to do first is add the best clustered index to both tables. Then and only then do you add other, non-clustered, covering indexes if they...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 9, 2014 at 5:09 pm
Viewing 15 posts - 6,016 through 6,030 (of 7,619 total)