Powershell Folder Size Specific Folder Name

Powershell script to loop through a folder looking for specific folder names and reporting back the folder size. Our DIFF backups were stored in a folder under the server name and I needed a way to see the size of all DIFF folders.

$sourceDirectory = "\\path to your folder\folder"
$folderName = "Diff"

$colItems = (Get-ChildItem $sourceDirectory -recurse | Where-Object {$_.PSIsContainer -eq $true -and $_.Name -match $folderName} )
foreach ($i in $colItems)
{
    $subFolderItems = (Get-ChildItem $i.FullName | Measure-Object -property length -sum)
    $i.FullName + " -- " + "{0:N2}" -f ($subFolderItems.sum / 1MB) + " MB" 
}

Advertisements

Msg 2570 DBCC CHECKDB

Here is how I worked through a handful of Msg 2570 errors I received from DBCC CHECKDB. Fortunatley the errors were on a DEV system were I was afforded the luxury of updating the data with no problems. If this is a production machine then you need to tread more lightly and work with the application owners to make sure you restore the data as best as possible.

Msg 2570 errors will not be fixed by running the REBUILD_REPAIR option. You have to address the issues with the data.

Sample Error:

Msg 2570, Level 16, State 3, Line 1
Page (1:2718613), slot 22 in object ID 1079167090, index ID 0, partition ID 72057594249084928, alloc unit ID 72057594292207616 (type "In-row data"). Column "column1" value is out of range for data type "decimal".  Update column to a legal value.
CHECKDB found 0 allocation errors and 6 consistency errors in table 'table_name' (object ID 1079167090).

Using the Page and Slot information from the error above you use the DBCC PAGE procedure to find the data page. The table name and database name will be listed in the DBCC error output.

DBCC PAGE ('Database_Name',1,2718613,3) WITH TABLERESULTS

Several rows we be output here. Use your slot number to target the bad data. Sample output

dbcc_BadData

Once you target the row that contains the bad data a simple UPDATE statement can be executed to fix the data. Again I was in a DEV system so I could update the data without much thought. Proceed carefully in production. Once you have fixed all the Msg 2570 errors take a database backup and re-run the DBCC CHECKDB.

Powershell Dump all tables to CSV SQL Server

I searched around for a while but could not find a script to do this. So I had to piecemilled the following script from a couple different examples. The script will loop through all the tables on the specified SQL Server database and export the data to CSV format. There will be a CSV file for every table.

#
# Date: 5/8/2014
# Purpose: Dump all tables from specified SQL Server database to CSV
# Notes: Change the Server, Database and extract file location as needed
#

$server = "SERVERNAME\INSTANCE"
$database = "DATABASE_NAME"
$tablequery = "SELECT name from sys.tables"

#Delcare Connection Variables
$connectionTemplate = "Data Source={0};Integrated Security=SSPI;Initial Catalog={1};"
$connectionString = [string]::Format($connectionTemplate, $server, $database)
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString

$command = New-Object System.Data.SqlClient.SqlCommand
$command.CommandText = $tablequery
$command.Connection = $connection

#Load up the Tables in a dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $command
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$connection.Close()



# Loop through all tables and export a CSV of the Table Data
foreach ($Row in $DataSet.Tables[0].Rows)
{
    $queryData = "SELECT * FROM [$($Row[0])]"
    
    #Specify the output location of your dump file
    $extractFile = "C:\mssql\export\$($Row[0]).csv"
    
    $command.CommandText = $queryData
    $command.Connection = $connection

    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $command
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $connection.Close()

    $DataSet.Tables[0]  | Export-Csv $extractFile -NoTypeInformation
}

Date Dimension Table Script

I had a recent moment of  despair when our Date Dimension table driving one of our key monthly reports ended in 2013. We had zero documentation about the table and zero references to scripts on how to rebuild. I am not even sure where the table came from, it just showed up one day.

By the miracle of a Google search I was able to find an example of a Date Dim table that almost completely matched our table. You’ll find several versions out there, I just didn’t want to reverse engineer all the missing columns. Here is a link to the example:¬†http://businessintelligence.ittoolbox.com/groups/technical-functional/cognos8-l/creating-a-time-dimension-2326178

I took the article and made some tweaks. I am parking the scripts here mainly so I can find it again if I am in a pinch. I like this table because it has a column for special days where you can make up your own business rules, etc. Some of the columns I need to explore more.

The last 5 columns are set to NULL. The original table I had would tell you what Monday of the month it is, etc. Need to update that. I also included an UPDATE script for 2014 holidays. This could be scripted as part of the procedure, just haven’t got that far yet. Same call looks like this.

--parm 1 = year to start
--parm 2 = number of years to generate
EXEC dbo.datedim_insert 2013,7

Table build script

CREATE TABLE [dbo].[DateDim]( 
[Date_ID] [int] NOT NULL, 
[Date] [datetime] NOT NULL, 
[Julian_Date] [int] NOT NULL, 
[Calendar_Year] [int] NOT NULL, 
[Financial_Year] [int] NOT NULL, 
[Calendar_Quarter] [tinyint] NOT NULL, 
[Calendar_Quarter_ID] [smallint] NOT NULL, 
[Calendar_Quarter_Name] [char](8) NOT NULL, 
[Financial_Quarter] [tinyint] NOT NULL, 
[Financial_Quarter_ID] [int] NOT NULL, 
[Financial_Quarter_Name] [char](13) NOT NULL, 
[Season] [char](6) NOT NULL, 
[Calendar_Month_ID] [int] NOT NULL, 
[Calendar_Month_Number] [tinyint] NOT NULL, 
[Month_Name] [nchar](12) NOT NULL, 
[Month_Abbreviation] [nchar](3) NOT NULL, 
[Financial_Month_ID] [int] NOT NULL, 
[Financial_Month_Number] [tinyint] NOT NULL, 
[Calendar_Week_ID] [int] NOT NULL, 
[Calendar_Week] [tinyint] NOT NULL, 
[Financial_Week_ID] [int] NOT NULL, 
[Financial_Week] [tinyint] NOT NULL, 
[Day_of_Calendar_Year] [smallint] NOT NULL, 
[Day_of_Financial_Year] [smallint] NOT NULL, 
[Day_of_Month] [tinyint] NOT NULL, 
[Week_Day_Number] [tinyint] NOT NULL, 
[Week_Day_Name] [nchar](10) NOT NULL, 
[Week_Day_Abbreviation] [nchar](3) NOT NULL, 
[Is_a_Weekend_day] [bit] NOT NULL, 
[Is_a_Public_Holiday] [bit] NOT NULL DEFAULT ((0)), 
[Is_a_Leap_Year] [bit] NOT NULL DEFAULT ((0)), 
[Is_a_Special_day] [bit] NOT NULL DEFAULT ((0)), 
[Days_In_Calendar_Year] [smallint] NOT NULL DEFAULT ((365)), 
[Days_In_Financial_Year] [smallint] NOT NULL DEFAULT ((365)), 
[Weekdays_In_Calendar_Year] [smallint] NOT NULL DEFAULT ((260)), 
[Weekdays_In_Financial_Year] [smallint] NOT NULL DEFAULT ((260)), 
[Workdays_In_Calendar_Year] [smallint] NOT NULL DEFAULT ((247)), 
[Workdays_In_Financial_Year] [smallint] NOT NULL DEFAULT ((247)), 
[Days_In_Calendar_Year_Sofar] [smallint] NOT NULL, 
[Days_In_Financial_Year_Sofar] [smallint] NOT NULL, 
[Weekdays_In_Calendar_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), 
[Weekdays_In_Financial_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), 
[Workdays_In_Calendar_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), 
[Workdays_In_Financial_Year_Sofar] [smallint] NOT NULL DEFAULT ((0)), 
[Days_In_Month] [smallint] NOT NULL, 
[Weekdays_In_Month] [smallint] NOT NULL DEFAULT ((21)), 
[Workdays_In_Month] [smallint] NOT NULL DEFAULT ((20)), 
[Days_In_Month_Sofar] [smallint] NOT NULL, 
[Weekdays_In_Month_Sofar] [smallint] NOT NULL DEFAULT ((0)), 
[Workdays_In_Month_Sofar] [smallint] NOT NULL DEFAULT ((0)),
[wMondayofMonth] smallint NULL,
[wTuesdayofMonth] smallint NULL,
[wWednesdayofMonth] smallint NULL,
[wThursdayofMonth] smallint NULL,
[wFridayofMonth] smallint NULL)


ALTER TABLE dbo.DateDim
ADD CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED (Date_ID);

Population Procedure

IF OBJECT_ID('dbo.datedim_insert') IS NULL
	EXEC ('CREATE PROCEDURE dbo.datedim_insert AS SELECT 0')
GO

/*********************************************************************************
Author:      Some Guy on the Internet
Modified by: thejoestory
Date:        02/06/2014
Purpose:     Populate the Dates Dimension table with values.
Parameters:  @StartYear - Year you want to start. The start year is actually one
			 year before the year you pass to ensure the year has the correct week
			 days in financial year set.
			 
			 Public and Special Days must be done manually. Special days allow you
			 to 
			 @NumYears - Years you want to generate
*********************************************************************************/

ALTER PROCEDURE dbo.datedim_insert
@StartYear INT, 
@NumYears INT 
AS 
SET NOCOUNT ON

DECLARE @DT DATETIME 
SELECT @DT = DATEADD(YYYY,-1,CAST('1/Jan/'+cast(@StartYear AS CHAR(4)) AS DATETIME)) 
DECLARE @DATEID INT 
DECLARE @JDATE INT 
DECLARE @CALYEAR INT 
DECLARE @FINYEAR INT 
DECLARE @CALQUARTER INT 
DECLARE @CALQTRID INT 
DECLARE @CALQTRNAME CHAR(8) 
DECLARE @FINQUARTER INT 
DECLARE @FINQTRID INT 
DECLARE @FINQTRNAME CHAR(13) 
DECLARE @SEASON CHAR(6) 
DECLARE @CALMONTHID INT 
DECLARE @FINMONTHID INT 
DECLARE @CALMONTH INT 
DECLARE @MONTHNAME NCHAR(12) 
DECLARE @MONTHABBR NCHAR(3) 
DECLARE @FINMONTH INT 
DECLARE @CALWEEKID INT 
DECLARE @CALWEEK INT 
DECLARE @FINWEEKID INT 
DECLARE @FINWEEK INT 
DECLARE @DAYOFCALYEAR INT 
DECLARE @DAYOFFINYEAR INT 
DECLARE @DAYOFMONTH INT 
DECLARE @DAYOFWEEK INT 
DECLARE @DAYOFWEEKNAME NCHAR(10) 
DECLARE @DAYOFWEEKABBR NCHAR(3) 
DECLARE @ISWEEKENDDAY BIT 
DECLARE @ISLEAPYEAR BIT 
-- skip public and special day flags, need to be done manually 
DECLARE @DAYSINCALYEAR INT 
DECLARE @DAYSINFINYEAR INT 
-- weekdays in cal year set in update 
-- weekdays in fin year set in update 
-- workdays in cal year set in update 
-- workdays in fin year set in update 
-- 'day in year cal sofar' col = dayofCALyear col 
-- 'day in year fin sofar' col = dayofFINyear col 
DECLARE @WEEKDAYSCALYEARSOFAR INT 
DECLARE @WEEKDAYSFINYEARSOFAR INT 
DECLARE @WORKDAYSCALYEARSOFAR INT 
DECLARE @WORKDAYSFINYEARSOFAR INT 
DECLARE @DAYSINMONTH INT 
-- weekdays in month set in update 
-- workdays in month set in update 
-- days in month sofar = dayofmonth 
DECLARE @WEEKDAYSMONTHSOFAR INT 
-- work days in month sofar = week days in month sofar 
-- **************************** -- 
-- *** initialize variables *** -- 
-- week days cal year sofar (CHECK) 
SELECT @WEEKDAYSFINYEARSOFAR=0 
IF (DATEPART(DY,@DT) = 1) 
BEGIN 
SELECT @WEEKDAYSCALYEARSOFAR=0 
END 
-- week days fin year sofar (CHECK) 
IF (DATEPART(MM,@DT) = 7 AND DATEPART(DD,@DT) = 1) 
BEGIN 
SELECT @WEEKDAYSFINYEARSOFAR=0 
END 
-- ************************* -- 
-- *** process each date *** -- 
WHILE (DATEPART(YYYY,@DT) <= @StartYear + @NumYears) 
BEGIN 
--get basic date info 
SELECT @ISWEEKENDDAY =0 
SELECT @CALYEAR = DATEPART (YEAR, @DT) 
SELECT @CALQUARTER = DATEPART (QUARTER, @DT) 
SELECT @CALMONTH = DATEPART (MONTH , @DT) 
SELECT @MONTHNAME = DATENAME(MM, @DT) 
SELECT @CALWEEK = DATEPART (WEEK , @DT) 
SELECT @DAYOFCALYEAR = DATEPART (DY , @DT) 
SELECT @DAYOFMONTH = DATEPART (DAY , @DT) 
SELECT @DAYOFWEEK = DATEPART (DW , @DT) 
SELECT @DAYOFWEEKNAME = DATENAME(DW, @DT) 
-- date id 
SELECT @DATEID = (@CALYEAR * 10000) + (@CALMONTH * 100) + @DAYOFMONTH 
-- julian date 
SELECT @JDATE = (@CALYEAR * 1000) + @DAYOFCALYEAR 
-- calendar quarter id 
SELECT @CALQTRID = (@CALYEAR * 10) + @CALQUARTER 
-- calendar quarter name 
SELECT @CALQTRNAME = CAST(@CALYEAR AS CHAR(4)) + ' Q ' + 
CAST(@CALQUARTER AS CHAR (1)) 
-- calendar week id 
SELECT @CALWEEKID = (@CALYEAR * 100) + @CALWEEK 
-- set weekend BIT 
IF ( @DAYOFWEEK = 1 OR @DAYOFWEEK = 7 ) 
BEGIN 
SELECT @ISWEEKENDDAY = 1 
END 
-- set leap year BIT 
IF ((@CALYEAR % 4 = 0) AND (@CALYEAR % 100 != 0 OR @CALYEAR % 400 = 0))
BEGIN 
SELECT @ISLEAPYEAR =1 
END 
ELSE 
BEGIN 
SELECT @ISLEAPYEAR =0 
END 
-- financial year 
IF (@CALMONTH < 7) 
BEGIN 
SELECT @FINYEAR = ((@CALYEAR - 1) * 10000) + (@CALYEAR) 
END 
ELSE 
BEGIN 
SELECT @FINYEAR = ((@CALYEAR) * 10000) + (@CALYEAR + 1) 
END 
-- financial qtr 
IF (@CALQUARTER > 2) 
BEGIN 
SELECT @FINQUARTER = @CALQUARTER - 2 
END 
ELSE 
BEGIN 
SELECT @FINQUARTER = @CALQUARTER + 2 
END 
-- financial quarter id 
SELECT @FINQTRID = (@FINYEAR * 10) + @FINQUARTER 
-- financial quarter name 
SELECT @FINQTRNAME = LEFT(@FINYEAR, 4) + '-' + RIGHT(@FINYEAR, 4) + ' Q 
' + CAST(@FINQUARTER AS CHAR (1)) 
-- season 
IF (@CALMONTH IN(9,10,11)) 
BEGIN 
SELECT @SEASON = 'Spring' 
END 
IF (@CALMONTH IN(12,1,2)) 
BEGIN 
SELECT @SEASON = 'Summer' 
END 
IF (@CALMONTH IN(3,4,5)) 
BEGIN 
SELECT @SEASON = 'Autumn' 
END 
IF (@CALMONTH IN(6,7,8)) 
BEGIN 
SELECT @SEASON = 'Winter' 
END 
-- calendar month id 
SELECT @CALMONTHID = (@CALYEAR * 100) + @CALMONTH 
-- month abbrev 
SELECT @MONTHABBR = LEFT(@MONTHNAME, 3) 
-- financial month number 
IF (@CALMONTH > 6) 
BEGIN 
SELECT @FINMONTH = @CALMONTH - 6 
END 
ELSE 
BEGIN 
SELECT @FINMONTH = @CALMONTH + 6 
END 
-- financial month id 
SELECT @FINMONTHID = (@FINYEAR * 100) + @FINMONTH 
-- financial week 
IF (@CALWEEK > 26) 
BEGIN 
SELECT @FINWEEK = @CALWEEK - 26 
END 
ELSE 
BEGIN 
SELECT @FINWEEK = @CALWEEK + 26 
END 
-- calendar week id 
SELECT @FINWEEKID = (@FINYEAR * 100) + @FINWEEK 
-- day of fin year 
IF (@CALMONTH > 6) 
BEGIN 
SELECT @DAYOFFINYEAR = DATEPART(DY, @DT) - DATEDIFF(DD, 
('1/JAN/'+LEFT(@FINYEAR,4)), ('1/JUL/'+LEFT(@FINYEAR,4))) 
END 
ELSE 
BEGIN 
SELECT @DAYOFFINYEAR = DATEPART(DY, @DT) + DATEDIFF(DD, 
('1/JUL/'+LEFT(@FINYEAR,4)), ('1/JAN/'+RIGHT(@FINYEAR,4))) 
END 
-- day abbrev 
SELECT @DAYOFWEEKABBR = LEFT(@DAYOFWEEKNAME, 3) 
-- days in cal year 
SELECT @DAYSINCALYEAR = DATEPART(DY, CAST(('31' + '/DEC/' + 
CAST(@CALYEAR AS NCHAR(4))) AS datetime)) 
-- days in fin year 
SELECT @DAYSINFINYEAR = DATEDIFF(DD, CAST(('1' + '/JUL/' + 
LEFT(@FINYEAR, 4)) AS datetime), CAST(('1' + '/JUL/' + RIGHT(@FINYEAR, 
4)) AS datetime)) 
-- last day of the month 
SELECT @DAYSINMONTH = DATEPART(DD, DATEADD(DD, -1, 
CAST(('1/'+LEFT(DATENAME(MM, DATEADD(MM, 1, @DT)),3)+'/'+CAST(@CALYEAR 
AS NCHAR(4))) AS DATETIME))) 
-- week days cal year sofar (CHECK) 
IF (@DAYOFCALYEAR = 1) 
BEGIN 
SELECT @WEEKDAYSCALYEARSOFAR=0 
END 
-- week days fin year sofar (CHECK) 
IF (@CALMONTH = 7 AND @DAYOFMONTH = 1) 
BEGIN 
SELECT @WEEKDAYSFINYEARSOFAR=0 
END 
-- week days month sofar (CHECK) 
IF (@DAYOFMONTH = 1) 
BEGIN 
SELECT @WEEKDAYSMONTHSOFAR = 0 
END 
-- week days cal year sofar / fin year sofar / month sofar(INCREMENTAL) 
IF (@ISWEEKENDDAY = 0) 
BEGIN 
SELECT @WEEKDAYSCALYEARSOFAR= @WEEKDAYSCALYEARSOFAR + 1 
SELECT @WEEKDAYSFINYEARSOFAR= @WEEKDAYSFINYEARSOFAR + 1 
SELECT @WEEKDAYSMONTHSOFAR = @WEEKDAYSMONTHSOFAR + 1 
END 
-- Print the DateID to the screen (for error checking) 
PRINT '@DATEID = ' + CAST( @DATEID AS NVARCHAR(30)); 
PRINT '@DT = ' + CAST( @DT AS NVARCHAR(30)); 
PRINT '@JDATE = ' + CAST(@JDATE AS NVARCHAR(30)); 
PRINT '@CALYEAR = ' + CAST(@CALYEAR AS NVARCHAR(30)); 
PRINT '@FINYEAR = ' + CAST(@FINYEAR AS NVARCHAR(30)); 
PRINT '@CALQUARTER = ' + CAST(@CALQUARTER AS NVARCHAR(30)); 
PRINT '@FINQUARTER = ' + CAST(@FINQUARTER AS NVARCHAR(30)); 
PRINT '@SEASON = ' + @SEASON; 
PRINT '@CALMONTHID = ' + CAST(@CALMONTHID AS NVARCHAR(30)); 
PRINT '@CALMONTH = ' + CAST(@CALMONTH AS NVARCHAR(30)); 
PRINT '@MONTHNAME = ' + @MONTHNAME; 
PRINT '@MONTHABBR = ' + @MONTHABBR; 
PRINT '@FINMONTH = ' + CAST(@FINMONTH AS NVARCHAR(30)); 
PRINT '@CALWEEK = ' + CAST(@CALWEEK AS NVARCHAR(30)); 
PRINT '@FINWEEK = ' + CAST(@FINWEEK AS NVARCHAR(30)); 
PRINT '@DAYOFCALYEAR = '+ CAST(@DAYOFCALYEAR AS NVARCHAR(30)); 
PRINT '@DAYOFFINYEAR = ' + CAST(@DAYOFFINYEAR AS NVARCHAR(30)); 
PRINT '@DAYOFMONTH = ' + CAST(@DAYOFMONTH AS NVARCHAR(30)); 
PRINT '@DAYOFWEEK = ' + CAST(@DAYOFWEEK AS NVARCHAR(30)); 
PRINT '@DAYOFWEEKNAME = '+ @DAYOFWEEKNAME; 
PRINT '@DAYOFWEEKABBR = ' + @DAYOFWEEKABBR; 
PRINT '@ISWEEKENDDAY = ' + CAST(@ISWEEKENDDAY AS NVARCHAR(30)); 
PRINT '@ISLEAPYEAR = ' + CAST(@ISLEAPYEAR AS NVARCHAR(30)); 
-- *********************************************** 
-- *** insert values into Date Dimension table *** -- 
INSERT DateDim( 
[Date_ID], 
[Date], 
[Julian_Date], 
[Calendar_Year], 
[Financial_Year], 
[Calendar_Quarter], 
[Calendar_Quarter_ID], 
[Calendar_Quarter_Name], 
[Financial_Quarter], 
[Financial_Quarter_ID], 
[Financial_Quarter_Name], 
[Season], 
[Calendar_Month_ID], 
[Calendar_Month_Number], 
[Month_Name], 
[Month_Abbreviation], 
[Financial_Month_ID], 
[Financial_Month_Number], 
[Calendar_Week_ID], 
[Calendar_Week], 
[Financial_Week_ID], 
[Financial_Week], 
[Day_of_Calendar_Year], 
[Day_of_Financial_Year], 
[Day_of_Month], 
[Week_Day_Number], 
[Week_Day_Name], 
[Week_Day_Abbreviation], 
[Is_a_Weekend_day], 
[Is_a_Leap_Year], 
[Days_In_Calendar_Year], 
[Days_In_Financial_Year], 
[Days_In_Calendar_Year_Sofar], 
[Days_In_Financial_Year_Sofar], 
[Weekdays_In_Calendar_Year_Sofar], 
[Weekdays_In_Financial_Year_Sofar], 
[Workdays_In_Calendar_Year_Sofar], 
[Workdays_In_Financial_Year_Sofar], 
[Days_In_Month], 
[Days_In_Month_Sofar], 
[Weekdays_In_Month_Sofar], 
[Workdays_In_Month_Sofar]) 
VALUES ( 
@DATEID, 
@DT, 
@JDATE, 
@CALYEAR, 
@FINYEAR, 
@CALQUARTER, 
@CALQTRID, 
@CALQTRNAME, 
@FINQUARTER, 
@FINQTRID, 
@FINQTRNAME, 
@SEASON, 
@CALMONTHID, 
@CALMONTH, 
@MONTHNAME, 
@MONTHABBR, 
@FINMONTHID, 
@FINMONTH, 
@CALWEEKID, 
@CALWEEK, 
@FINWEEKID, 
@FINWEEK, 
@DAYOFCALYEAR, 
@DAYOFFINYEAR, 
@DAYOFMONTH, 
@DAYOFWEEK, 
@DAYOFWEEKNAME, 
@DAYOFWEEKABBR, 
@ISWEEKENDDAY, 
@ISLEAPYEAR, 
@DAYSINCALYEAR, 
@DAYSINFINYEAR, 
@DAYOFCALYEAR -1, 
@DAYOFFINYEAR -1, 
@WEEKDAYSCALYEARSOFAR, 
@WEEKDAYSFINYEARSOFAR, 
@WEEKDAYSCALYEARSOFAR, 
@WEEKDAYSFINYEARSOFAR, 
@DAYSINMONTH, 
@DAYOFMONTH, 
@WEEKDAYSMONTHSOFAR, 
@WEEKDAYSMONTHSOFAR) 
--increment the date one day 
SELECT @DT = DATEADD(DD, 1, @DT) 
-- end while 
END; 
-- Calculate the actual weekdays in each calendar year 
WITH CWeekends (CYear, Num_Weekend_Days, Days_In_Calendar_Year, Weekdays_In_Calendar_Year) AS 
( 
SELECT 
Calendar_Year AS CYear, 
COUNT(Is_a_Weekend_day) AS Num_Weekend_Days, 
MAX(Days_In_Calendar_Year) AS Days_In_Calendar_Year, 
MAX(Weekdays_In_Calendar_Year) AS Weekdays_In_Calendar_Year 
FROM dbo.DateDim
WHERE Is_a_Weekend_day = 1 
GROUP BY Calendar_Year 
) 
UPDATE dbo.DateDim
SET Weekdays_In_Calendar_Year = CWeekends.Days_In_Calendar_Year - CWeekends.Num_Weekend_Days 
FROM CWeekends 
WHERE DateDim.Calendar_Year = CWeekends.CYear; 
-- Calculate the actual weekdays in each calendar year 
WITH FWeekends (FYear, Num_Weekend_Days, Days_In_Financial_Year, Weekdays_In_Financial_Year) AS 
( 
SELECT 
Financial_Year AS FYear, 
COUNT(Is_a_Weekend_day) AS Num_Weekend_Days, 
MAX(Days_In_Financial_Year) AS Days_In_Financial_Year, 
MAX(Weekdays_In_Financial_Year) AS Weekdays_In_Financial_Year 
FROM dbo.DateDim
WHERE Is_a_Weekend_day = 1 
GROUP BY Financial_Year 
) 
UPDATE dbo.DateDim
SET Weekdays_In_Financial_Year = FWeekends.Days_In_Financial_Year - 
FWeekends.Num_Weekend_Days 
FROM FWeekends 
WHERE DateDim.Financial_Year = FWeekends.FYear; 
-- Set workdays to week days 
UPDATE dbo.DateDim
SET 
Workdays_In_Calendar_Year = Weekdays_In_Calendar_Year, 
Workdays_In_Financial_Year = Weekdays_In_Financial_Year, 
Workdays_In_Month = Weekdays_In_Month 

Updates Script

--Public Holiday Updates
UPDATE dbo.DateDim SET is_a_public_holiday = 1 
WHERE Date_ID IN (20140101,20140120,20140217,20140526,20140704,20140901,20141013,20141111,20141127,20141225)


--Special Days
--Non Public Holidays, day after thanksgiving, day before Christmas
UPDATE dbo.DateDim SET is_a_special_day = 1
WHERE DATE_ID IN (20141128,20141224)

SSRS Show Dates on Chart with Zero Counts

The users wanted a chart that had a count by day of a set of events. Some days did not have events and they wanted to see these days on the chart even though the days had a zero count. I tried to accomplish this with a CTE but I couldn’t get the CTE to return the data I wanted. So instead I looked for an SSRS solution.

My output looked like so:
2014-01-30_10-27-47

So then in SSRS I created a stacked bar chart, and did the following.

  1. Right Click on the Horizonal Axis and click Horizontal Axis Properties
  2. Change the Axis Type to Scalar (Number/Dates)
  3. Change the Interval to 1
  4. Change the Intervalt Type to Number
  5. Click OK

Executed the report and it is working as planned.

2014-01-30_10-31-17

CLR20r3 SSMS

Hit this wild problem today.

2014-01-15_10-45-48

Turns out the problem was caused by the Print Spooler Service. The Print Spooler service was disabled. I enabled it and started it and the problem went away.

Server Manager was also crashing with the same error.

SQL Server Replication Health Check

Here are some scripts I use to check on replication.

Undelivered commands in Distribution database

--Undelivered Commands in Distribution Database
Select		da.publication, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB
from		[distribution].[dbo].[MSdistribution_agents]da
			join [distribution].[dbo].[MSarticles] msa on 	msa.publisher_db = da.publisher_db
			join [distribution].[dbo].[MSdistribution_status] ds ON ds.agent_id = da.id and ds.article_id = msa.article_id and ds.agent_id = da.id
GROUP BY	da.publication

With Article Details

--Undelivered Commands in Distribution Database - Article Details
Select		da.publication, msa.article, msa.article_id, sum(ds.UndelivCmdsInDistDB) UndelivCmdsInDistDB, GETDATE() QueryTime
from		[distribution].[dbo].[MSdistribution_agents]da
			join [distribution].[dbo].[MSarticles] msa on 	msa.publisher_db = da.publisher_db
			join [distribution].[dbo].[MSdistribution_status] ds ON ds.agent_id = da.id and ds.article_id = msa.article_id and ds.agent_id = da.id
GROUP BY	da.publication, msa.article,msa.article_id
ORDER BY UndelivCmdsInDistDB DESC

Hot Articles
This script will show you what articles have the most commands at the moment.

--Hot Articles
--Command count for all articles
SELECT COUNT(1), c.article_id, a.article
FROM distribution.dbo.MSrepl_commands c JOIN distribution.dbo.MSarticles a on c.article_id = a.article_id
GROUP BY c.article_id, a.article
ORDER BY 1 DESC

Big Ticket Batches
This script will show you your large batches waiting to be distributed.

--Big Ticket Batches
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT [xact_seqno], count(1)
FROM [distribution].[dbo].[MSrepl_commands]
GROUP BY [xact_seqno]
HAVING count(1) > 1
ORDER BY count(1) DESC

Inspect Specific Batch
You can use the following procedure to inspect a batch. You can get a batch number from the Big Ticket Batches query above.

--Plug in the xact_seqno for the batch into this Stored Procedure
EXEC distribution.dbo.sp_browsereplcmds
	@xact_seqno_start = '0x000223DE000147D8001B',
	@xact_seqno_end ='0x000223DE000147D8001B'