More notes to self: network bucket too small and a funny quote

The bucket effect: When bucket gets full, everything is dumped out and bucket begins refilling, where the bucket is the network connection/transmission. In effect you are able to reach the destination, but before the round-trip can occur, all communications appear to get severed. In this scenario, if other short requests(small packets) are getting through, like a web request for data, yet longer requests such as file transfers, SSH/RDP sessions, are dropping out, look for recent network. Don't assume something like a firewall or managed switch couldn't possibly be the culprit, just because you, yourself or team, didn't initiate a change request(Active/Ative? WTM?). Meh. Also don't assume everyone has already checked on these or any other obvious things. Force an acknowledgment by the group.

Funny quote during vendor site-visit:

"Before we make any changes we'll need to take a dump."

I almost lost it :-P. Vendor was actually referring to a snapshot/backup of a database before acting upon it, in case a rollback is required.

note to self: SQL log shipping stats/graphs

Results of query coupled with google spreadsheets w/ timeline graph applied, equate to pretty data worth sharing.

--// View last log backups per half hour, total size(MB), with number of logs
SELECT TOP 500
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DT30
  , SUM(backup_size / 1024 / 1024) SizeMB
  , COUNT(DISTINCT media_set_id) TrnCount
FROM
    msdb..backupset (NOLOCK)
WHERE
    type = 'L'
GROUP BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0)
ORDER BY
    DATEADD(mi, 30 * (DATEDIFF(mi, 0, backup_start_date) / 30), 0) DESC

Note: can easily work for Data backups too, just change "type = 'L'" to "type = 'D'"

~Enjoy

note to self: SQL Disk summary

Script to show current Disk space free, used, by drive. And then a summary break down of DB files, folders and space used.

SET NOCOUNT ON
EXEC master.dbo.xp_cmdshell 'wmic LOGICALDISK LIST BRIEF';

IF OBJECT_ID('tempDB..##tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles
CREATE TABLE ##tmpDatabaseFiles
    (
 [DBName] [sys].[sysname]
   ,  [file_id] [int]
   , [file_guid] [uniqueidentifier]
   , [type] [tinyint]
   , [type_desc] [nvarchar](60)
   , [data_space_id] [int]
   , [name] [sys].[sysname]
   , [physical_name] [nvarchar](260)
   , [state] [tinyint]
   , [state_desc] [nvarchar](60)
   , [size] [int]
   , [max_size] [int]
   , [growth] [int]
   , [is_media_read_only] [bit]
   , [is_read_only] [bit]
   , [is_sparse] [bit]
   , [is_percent_growth] [bit]
   , [is_name_reserved] [bit]
   , [create_lsn] [numeric](25, 0)
   , [drop_lsn] [numeric](25, 0)
   , [read_only_lsn] [numeric](25, 0)
   , [read_write_lsn] [numeric](25, 0)
   , [differential_base_lsn] [numeric](25, 0)
   , [differential_base_guid] [uniqueidentifier]
   , [differential_base_time] [datetime]
   , [redo_start_lsn] [numeric](25, 0)
   , [redo_start_fork_guid] [uniqueidentifier]
   , [redo_target_lsn] [numeric](25, 0)
   , [redo_target_fork_guid] [uniqueidentifier]
   , [backup_lsn] [numeric](25, 0)
   , [CreationDate] [datetime])
 
 EXECUTE sp_msforeachdb 'INSERT INTO ##tmpDatabaseFiles SELECT ''[?]'', *, GETDATE() FROM [?].sys.database_files'
  
SELECT COUNT(DISTINCT dbname) NonSystemDBCount from ##tmpDatabaseFiles WHERE dbname NOT IN ('[master]','[model]','[tempdb]', '[msdb]')

 ;WITH CTE AS (
 SELECT
 DISTINCT
   name
 , LEFT(physical_name, LEN(physical_name)-CHARINDEX ('\', REVERSE(physical_name)) ) Folder
 ,(size * 8)/1024.0 size
 ,type_desc
  FROM ##tmpDatabaseFiles 
)
SELECT 
 LEFT(Folder,60) Folder
 ,LEFT(LTRIM(STR(COUNT(Folder))),5) Files
 ,LEFT(LTRIM(STR(SUM(size)/1024.0)),10) [GB TotalSize]
 ,LEFT(type_desc,10) FileType
FROM CTE
GROUP BY Folder,type_desc
ORDER BY Folder

IF OBJECT_ID('tempDB..#tmpDatabaseFiles') IS NOT NULL DROP TABLE ##tmpDatabaseFiles

Set compatibility to SQL 2005

--// Note To Self

--// Set compatibility to SQL 2005
SELECT
    ' ALTER DATABASE [' + name + '] SET RECOVERY SIMPLE
  exec sp_dbcmptlevel ' + name + ', 90'
FROM
    sys.databases
WHERE
    compatibility_level < 90