Daily Archives: October 24, 2016

We can do this checking the default trace.

--
-- Review Database File Growth
--
DECLARE @trcfilename VARCHAR(1000);
SELECT @trcfilename = [path]
FROM [sys].[traces]
WHERE [is_default] = 1
SELECT [StartTime],
DB_NAME([databaseid]) AS [DatabaseName],
[Filename],
SUM(([IntegerData] * 8) / 1024) AS [Growth in MB],
([Duration] / 1000) AS [Duration in seconds],
RIGHT('0' + CAST(([Duration] / 1000) / 3600 AS VARCHAR), 2) + ':' + RIGHT('0'
+ CAST((([Duration] / 1000) / 60) % 60 AS VARCHAR),
2) + ':' + RIGHT('0'
+ CAST(([Duration] / 1000)
% 60 AS VARCHAR), 2) [TimeHHMMSS]
FROM ::
FN_TRACE_GETTABLE(@trcfilename, DEFAULT)
WHERE ([EventClass] = 92
OR [EventClass] = 93
)
--AND StartTime >= DATEADD(WEEK, -1,GETDATE())
GROUP BY [StartTime],
[Databaseid],
[Filename],
[IntegerData],
[Duration]
ORDER BY [StartTime]

What will LTRIM and RTRIM do with CR LF or CRLF?

IF OBJECT_ID('tempdb.dbo.[#testtable]') IS NOT NULL
DROP TABLE [#testtable]

CREATE TABLE [#testtable] ([TestCRLF] VARCHAR(50))

INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13) + CHAR(10)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(13)
)
INSERT INTO [#testtable]
([TestCRLF]
)
VALUES ('ThereIsNoSpace' + CHAR(10)
)

SELECT [TestCRLF] + 'Z' AS NoPostProcessing
FROM [#testtable]

SELECT LTRIM(RTRIM([TestCRLF])) + 'Z' AS Trims
FROM [#testtable]

SELECT RTRIM(REPLACE(REPLACE(REPLACE(REPLACE([TestCRLF], CHAR(13) + CHAR(10), ' '), CHAR(10) + CHAR(13), ' '), CHAR(13), ' '),
CHAR(10), ' ')) + 'Z' ReplaceAndTrim
FROM [#testtable]

Here’s what the output looks like:

Of course if we output to text the Z character would be in the next line. Since we are trying to clean up data with spaces or CRLF this shows a good demonstration of the problem.

(Thanks to Chris Trump!)