Viewing 15 posts - 5,596 through 5,610 (of 7,619 total)
If you're not using service broker any more, then it's 100% safe to run this:
ALTER DATABASE msdb SET NEW_BROKER
which should clear that table and allow you to release the space.
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".
December 3, 2014 at 3:43 pm
BWFC (11/27/2014)
One other thing, be careful using BETWEEN for date range queries. You're usually better using
where
[Date] >= @startdate
and
[Date] <= @enddate
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".
December 3, 2014 at 12:01 pm
To avoid rows already being modified, you can use the READPAST hint. That's often used for work-queue-type processing. If you can use that, there's no good reason to...
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".
December 3, 2014 at 11:57 am
Ok, let's see what this shows:
IF OBJECT_ID('tempdb.dbo.#showfilestats') IS NOT NULL
DROP TABLE #showfilestats
CREATE TABLE #showfilestats (
Fileid smallint NOT NULL,
FileGroup...
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".
December 3, 2014 at 11:28 am
417 pages is a trivial size, you can ignore it if that's the total.
Does "DB size shows 215 GB" include log file? If so, what size is the log...
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".
December 3, 2014 at 10:59 am
I suggest RECOMPILE as well. That may take some time, but it will be trivial compared to a terrible plan.
Also, if you leave auto stats update on, explicitly set...
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".
December 3, 2014 at 10:50 am
SQL also creates internal tables for certain types of data, including: primary XML indexes,
service broker, change tracking and a few other things. You'll need to include the size...
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".
December 3, 2014 at 10:44 am
Robert Frasca (12/2/2014)
Grant Fritchey (12/2/2014)
ScottPletcher (12/2/2014)
Robert Frasca (12/2/2014)
ScottPletcher (12/2/2014)
Robert Frasca (12/2/2014)
ScottPletcher (12/1/2014)
I suggest creating a covering index on:
SourceFactTimeCard (...
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".
December 2, 2014 at 12:00 pm
Robert Frasca (12/2/2014)
ScottPletcher (12/2/2014)
Robert Frasca (12/2/2014)
ScottPletcher (12/1/2014)
I suggest creating a covering index on:
SourceFactTimeCard ( TimecardIndex, EmployeeID )
If SQL then...
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".
December 2, 2014 at 10:36 am
eric.notheisen (12/2/2014)
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".
December 2, 2014 at 9:07 am
Robert Frasca (12/2/2014)
ScottPletcher (12/1/2014)
I suggest creating a covering index on:
SourceFactTimeCard ( TimecardIndex, EmployeeID )
If SQL then uses that covering...
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".
December 2, 2014 at 8:43 am
SQL allows you to designate a proc(s) as "start up" procs that automatically runs when an instance starts. I was suggesting you create such a proc, or add code...
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".
December 2, 2014 at 8:40 am
I guess the SourceFactTimeCard table is fairly wide?
I suggest creating a covering index on:
SourceFactTimeCard ( TimecardIndex, EmployeeID )
If SQL then uses that covering index -- it...
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".
December 1, 2014 at 4:43 pm
SELECT CONVERT(varchar(10), DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0), 120) AS dt, SUM(amt) AS Amt
FROM @t
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0)
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".
December 1, 2014 at 4:26 pm
You can pass @StartingDate as a param to the ITV function.
Also, you can simplify the calc for PastPeriods:
MONTH(@StartingDate) % 3 + 14
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".
December 1, 2014 at 4:21 pm
Viewing 15 posts - 5,596 through 5,610 (of 7,619 total)