Thursday, July 30, 2015

Dropping Global Temp Tables

-- A better question is why are you using global temp tables in the first place???
IF OBJECT_ID('tempdb..##badge_updt') IS NOT NULL DROP TABLE ##badge_updt

Wednesday, July 29, 2015

TFS Hidden Workspace Solution


When creating a new build in Team Foundation Server, I get the following error when attempting to run the new build:
The path C:\Build\ProductReleases\FullBuildv5.4.2x\Sources is already mapped to workspace BuildServer_23.
I am unable to see a workspace by that name in the workspaces dialog.

Use the command line utility TF - Team Foundation Version Control Tool (tf).
You can get a list of all workspaces by bringing up a Visual Studio Command Prompt then changing to your workspace folder and issuing the following commands:
C:\YourWorkspaceFolder>tf workspaces /owner:*
You should see your problem workspace in the list as well as it's owner.
You can delete the workspace with the following command:
C:\YourWorkspaceFolder>tf workspace /delete /server:BUILDSERVER WORKSPACENAME;OWNERNAME

Monday, July 27, 2015

PowerShell - Find All Files of Type by Date

$limit = (Get-Date).AddDays(-15)
#$path = "C:\Some\Path"
$path = "C:\Temp"

# Delete files older than the $limit.
# Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item -Force
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Extension -eq ".txt" }

$limit = (Get-Date).AddDays(-1)
$path = "C:\Temp"
Get-ChildItem -Path $path -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit -and $_.Extension -eq ".BAK" }

What Are the Sizes of My Databases?

with fs
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db

Where Are my Backups?

SELECT physical_device_name,
                backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name like '%%'
ORDER BY backup_finish_date DESC

Wednesday, July 22, 2015

Fun With Powershell - Copy All Unique Files and Grab the Top X Lines

#change the prompt
function prompt { "Posh >" ; return " " }

#copy locally all unique file names
Get-ChildItem -Path \\server\ARC\Exp\AM\ -Recurse -Filter "*.txt" | ForEach-Object {"Copy " + $_.FullName  + " ""\\Dest\temp\ExperiantRegistration\" + $_.Name + "   " + $_.LastWriteTime + ".txt""" -replace ":", ""  -replace "/", "" >>  copyLocal.bat}

#Make the script file to Get just the top few lines
Get-ChildItem -Path C:\temp\ExperiantRegistration\ -Filter "*.txt" | ForEach-Object {"Get-Content " + """" + $_.FullName + """ -totalcount 3" > getHeaders.ps1}

Tuesday, July 14, 2015

Find Tables with Column Named

FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE like '%sub%topi%' -- OBJ%' -- NOTE%'

Friday, July 10, 2015

SQL Server - Is DB Still Restoring?


SELECT session_id SPID
, command
, a.text Query
, start_time
, percent_complete
, dateadd(second,estimated_completion_time/1000, getdate()) estimated_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a