Skip to content

Instantly share code, notes, and snippets.

Avatar

Juan on Software juanonsoftware

  • Rabbit Software
  • Vietnam
View GitHub Profile
@juanonsoftware
juanonsoftware / ChocoInstallCommonSoftwareForServer.ps1
Last active May 15, 2018
Common and Must have software for a Windows server installation script via Chocolatey. Just need to run this script in a Powershell console (with Administrator right)
View ChocoInstallCommonSoftwareForServer.ps1
Set-ExecutionPolicy Bypass -Scope Process -Force; iex ((New-Object System.Net.WebClient).DownloadString('https://chocolatey.org/install.ps1'))
# 7Zip - https://chocolatey.org/packages/7zip
choco install 7zip -y
# Notepad++ - https://chocolatey.org/packages/notepadplusplus.install
choco install notepadplusplus.install -y
# TreeSize Free https://chocolatey.org/packages/treesizefree
choco install treesizefree -y
@juanonsoftware
juanonsoftware / Search StoredProcedures by name.sql
Created May 2, 2018
Search for stored procedures in a given database
View Search StoredProcedures by name.sql
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
ORDER BY modify_date desc, create_date desc
View remove-all-messages-in-sql-queue.sql
begin tran
declare @c uniqueidentifier
while(1=1)
begin
select top 1 @c = conversation_handle from dto.YourQueueName
if (@@ROWCOUNT = 0)
break
end conversation @c with cleanup
end
@juanonsoftware
juanonsoftware / sql-get-db-backups.sql
Last active Apr 12, 2018
Script to get all backup of a SQL Server database
View sql-get-db-backups.sql
declare @dbName varchar(255);
set @dbName = 'TestDB';
SELECT m.physical_device_name,
b.backup_start_date,
b.backup_finish_date,
b.type,
b.backup_size/1024/1024 AS BackupSizeMB,
b.compressed_backup_size/1024/1024 as CompressedBackupSizeMB
@juanonsoftware
juanonsoftware / sql-get-running-jobs.sql
Last active Apr 12, 2018
To get all jobs that are running on an instance
View sql-get-running-jobs.sql
SELECT
ja.job_id,
j.name AS job_name,
ja.start_execution_date,
ISNULL(last_executed_step_id,0)+1 AS current_executed_step_id,
Js.step_name
FROM msdb.dbo.sysjobactivity ja
LEFT JOIN msdb.dbo.sysjobhistory jh
ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j
@juanonsoftware
juanonsoftware / sql-check-index-fragmentation.sql
Last active Apr 12, 2018
Check Fragmentation of ALL Indexes in a Database
View sql-check-index-fragmentation.sql
-- https://myadventuresincoding.wordpress.com/2013/05/27/sql-server-check-index-fragmentation-on-all-indexes-in-a-database/
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
@juanonsoftware
juanonsoftware / sql-backup-database-diff
Last active Apr 12, 2018
Script to backup an SQL Server database
View sql-backup-database-diff
-- NOTE
-- Must update dbName & Folder
declare @dbName nvarchar(150);
declare @folder nvarchar(100);
set @dbName = 'TestDB';
set @folder = 'D:\Mega\Wip\SQL\TestDB\Backup\';
declare @path nvarchar(255);
@juanonsoftware
juanonsoftware / Kill-sql-locks
Created Apr 12, 2018
Kill blocking processes on a database
View Kill-sql-locks
--- Kill blocked processes on a database
DECLARE @databasename nvarchar(50)
SET @databasename = N'Datbase_Name'
-- SET @databasename = DB_NAME()
DECLARE @Sql varchar(max)
SET @Sql = ''
SELECT @Sql = @Sql + 'Kill ' + Convert(varchar, SPId) + ';'
FROM master.dbo.sysprocesses
@juanonsoftware
juanonsoftware / sql-get-all-locks.sql
Last active Dec 21, 2018
A script to list all current locks in SQL server
View sql-get-all-locks.sql
-- With SQL
select handle.text as sql, * from
(
select DB_NAME(dbid) as dbname, *
from master.dbo.sysprocesses
where blocked <> 0
union
@juanonsoftware
juanonsoftware / gist:f4b3e85415ccdcab2161
Created Jul 3, 2015
NHiberate - Code to make SchemaExport generate database for entities mapping class
View gist:f4b3e85415ccdcab2161
using System;
using NHibernate.Cfg;
using NHibernate.Dialect;
using NHibernate.Driver;
using NHibernate.Mapping.ByCode;
using NHibernate.Mapping.ByCode.Conformist;
using NHibernate.Tool.hbm2ddl;
namespace ConsoleApplication1
{