PowerSQL -Recycle SQL Instance (Remote or Local)

We have a scheduled maintenance window to recycle SQL SERVER Instance once in 30 days hence created a PowerShell job where function being placed along with parameters(servername and instancename) and executed it across all SQLinstance. (Named or Default instance) Default instance-> MSSQLSERVER  and Named Instance ->MSSQL`$KAT ( To override the named instance use ` infront of $ sign [` Grave Accent])

The same function being called to start and stop the service.

PS:\>RESTART-SQLINSTANCE AQDB001 MSSQLSERVER

PS:\>RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT

Code is given below

**************************************************************************************

FUNCTION RESTART-SQLINSTANCE

{
PARAM([STRING]$SERVERNAME,[STRING]$SERVICENAME)

#MSSQLSERVER ->DEFAULT INSTANCE  and NAMED INSTANCE -> MSSQL`$KAT

$SERVICE = GET-SERVICE -COMPUTERNAME $SERVERNAME $SERVICENAME -ERRORACTION SILENTLYCONTINUE

IF( $SERVICE.STATUS -EQ “RUNNING” )
{
$DEPSERVICES = GET-SERVICE -COMPUTERNAME $SERVERNAME $SERVICE.NAME -DEPENDENTSERVICES | WHERE-OBJECT {$_.STATUS -EQ “RUNNING”}
IF( $DEPSERVICES -NE $NULL )
{
FOREACH($DEPSERVICE IN $DEPSERVICES)
{
Stop-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $DEPSERVICE.NAME)
}
}
Stop-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $SERVICE.NAME) -Force
}
ELSEIF ( $SERVICE.STATUS -EQ “STOPPED” )
{
Start-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $SERVICE.NAME)
$DEPSERVICES = GET-SERVICE -COMPUTERNAME $SERVERNAME $SERVICE.NAME -DEPENDENTSERVICES | WHERE-OBJECT {$_.STATUS -EQ “STOPPED”}
IF( $DEPSERVICES -NE $NULL )
{
FOREACH($DEPSERVICE IN $DEPSERVICES)
{
Start-Service -InputObject (Get-Service -ComputerName $SERVERNAME -Name $DEPSERVICE.NAME)
}
}
}
ELSE
{

WRITE-OUTPUT “THE SERVER AND SERVICE DOES NOT EXIST”
}

}

*************************************

Output:

PS P:\> RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT123
THE SPECIFIED SERVER AND SERVICE DOES NOT EXIST

RUN 1:

PS P:\> RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT

PS P:\> get-service *| where {$_.name -like ‘*KAT’}

Status Name DisplayName
—— —- ———–
Stopped MSSQL$KAT SQL Server (KAT)
Stopped SQLAgent$KAT SQL Server Agent (KAT)

RUN 2:

PS P:\> RESTART-SQLINSTANCE AQDB001 MSSQL`$KAT
PS P:\> get-service *| where {$_.name -like ‘*KAT’}

Status Name DisplayName
—— —- ———–
Running MSSQL$KAT SQL Server (KAT)
Running SQLAgent$KAT SQL Server Agent (KAT)

PowerSQL – Generate Only Index Script of All Tables or Specific Tables

Background of this implementation-As a part of Performance tuning, We noticed huge avg_disk_queue_length on F drive after analysis, we’ve recommended to move two tables(data and index)to different drive all together a different filegroup.  This would give the best IO performance. Those two tables have ‘N’ indexes. Manually scripting ‘N’ index is going to be daunting task.

The below are the sequence of steps to create index script.

Manually load the SQL Server snap-ins into PowerShell, run it on the computer where SQL Server is installed.

**********************
Load SNAPINS
*****************
#SQL Provider
ps:\>add-pssnapin sqlserverprovidersnapin100
#Cmdlets
ps:\>add-pssnapin sqlservercmdletsnapin100

***********************************
Five Parameter Details –
***********************************
$Server = Local or remote server ( Localhost or Server01 or Server 02)
$Instance = Default/NamedInstanceName ( default or TMG or KAT)
$Database = DatabaseName ( dssp or webp)
$Schema = Schema Name( dbo or any schema name)
$Tables = Enter ‘*’ [All tables] or Name of the specific Table(Work_order)

Output:- F:\PowerSQL\index.txt ( Change the path)

***********************************************************
Actual PowerShell Script
****************************************************************

PARAM

(
$server = $(read-host “Server”),
$instance = $(read-host “Instance – Default”),
$database = $(read-host “Database”),
$schema = $(read-host “schema (default schema dbo)”),
$tables = $(read-host “Tables (*)”)
)

$path = “sqlserver:\sql\$server\$instance\databases\$database\tables”

IF ($tables -eq ‘*’)
{
$tableset= gci -path $path | select-object name
foreach($t in $tableset)
{
$path1 = $path+”\dbo.”+$t.name+”\indexes\”
gci -path $path1 | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n ” | out-file f:\PowerSQL\index.txt -append; $_.Name;}
}
}
ELSE
{ $tableset =get-childitem $path -ErrorAction stop | where-object {$_.name -like “$tables”}
foreach($t in $tableset)
{
$path = $path+”\dbo.”+$t.name+”\indexes\”
gci -path $path | %{$_.Script() | out-file f:\PowerSQL\index.txt -append; “GO `r`n “| out-file f:\PowerSQL\index.txt -append; $_.Name;}
}
}

**************************

PowerSQL – Database Backup Report across all Servers – Centralized Approach

This topic provides and describes the quickest way to find when and what databases are being backed up recently.Backups are most important part of a recovery strategy. In this case, I’m explaining this process in three steps
1) Instance names are stored in a table [SQLInstances] and Database [PowerSQL], The result will be stored in staging table [TLOG_DatabaseBackup] and output is formatted HTML report.
Make sure that the centralized server(Server01) has DB Mail configured with a profilename(Test).

You are doing the below steps in centralized instance i.e is Server01

CREATE TABLE [dbo].[SQLInstances](
[InstanceName] [varchar](128) NULL,
[Status] [varchar](50) NULL
)

/*Inserted two instances (Default and Named Instances)*/

INSERT INTO TABLE dbo.SQLInstances values(‘Server05′,’Yes’)
INSERT INTO TABLE dbo.SQLInstances values(‘Server02\TMG’,’Yes’)

CREATE TABLE [dbo].[TLOG_DatabaseBackup](
[SERVERNAME] [varchar](50) NULL,
[DatabaseName] [varchar](128) NULL,
[LastFullBackupDate] [datetime] NULL,
[LastDifferentialBackupDate] [datetime] NULL,
[LastAbsoluteBackupDate] [datetime] NULL,
[LastAbsoluteBackupType] [char](4) NOT NULL,
[BackupDelta] [int] NOT NULL
)

2) Job Creation has two steps
a) In the First Step Select Powershell as its Type (Select PowerShell Under Type drop down option)
b) copy the below code and paste in Job command place.
*********************************************************
Note: Change ServerName and Database Parameters under $params
***************************************************

Function Get-DBBackupToDatabase ($SQLInstance)

{

#Check whether or not a named instance and set the location accordingly

if ($SQLInstance -ilike “*\*”) {$location = “SQLSERVER:\SQL\$SQLInstance\Databases”}

else {$location = “SQLSERVER:\SQL\$SQLInstance\DEFAULT\Databases”}

$DBStats = dir -force $location | where-object {$_.Name -ne “tempdb”; $_.Refresh()}

foreach ($DB in $DBStats)

{

$DBName = $DB.Name

$LastFull = $DB.LastBackupDate

IF ($DB.LastDifferentialBackupDate -eq “01/01/0001 00:00:00”) {$LastDiff = $NULL} ELSE {$LastDiff = $DB.LastDifferentialBackupDate}

#The last absolute backup will be the newer of the last full or last differential, we can also set the type using this

IF ($LastDiff -gt $LastFull) {$LastAbsolute = $LastDiff; $LastType = “DIFF”}

ELSEIF ($LastFull -eq “01/01/0001 00:00:00”){$LastAbsolute = $LastFull; $LastType = “NONE”}

ELSE {$LastAbsolute = $LastFull; $LastType = “FULL”}

#Quick calculation gives us the number of days since the last backup

$DaysSince = ((Get-Date) – $LastAbsolute).Days

#Because SQL cannot store the default date of 01/01/0001 we set it to null, which will store in sql as 1900-01-01

IF ($LastFull -eq “01/01/0001 00:00:00”) {$LastFull = $NULL}

IF ($LastAbsolute -eq “01/01/0001 00:00:00″) {$LastAbsolute = $NULL}

$InsertResults = @”

INSERT INTO dbo.TLOG_DatabaseBackup (ServerName, DatabaseName, LastFullBackupDate, LastDifferentialBackupDate, LastAbsoluteBackupDate, LastAbsoluteBackupType, BackupDelta)

VALUES (‘$SQLInstance’, ‘$DBName’, ‘$LastFull’, ‘$LastDiff’, ‘$LastAbsolute’, ‘$LastType’, ‘$DaysSince’)

“@

invoke-sqlcmd @params -Query $InsertResults

}

}

$servername=’Server01′
$DatabaseName=’PowerSQL’

#Connection information for the database server where the SQLInstances and DatabaseBackups tables reside

$params = @{‘server’=$servername; ‘Database’=$DatabaseName}

#Grab our list of servers, iterate through them and call the function which rights to the database

$Srv = invoke-sqlcmd @params -Query “TRUNCATE TABLE dbo.TLOG_DatabaseBackup;SELECT InstanceName from SQLInstances where status=’Yes'”

foreach ($Instance in $srv)
{
Get-DBBackupToDatabase $Instance.InstanceName
}

****************************************************************************************

3) New Step 2 Creation – Email Sending – I’ve already posted on how to send and format the table data into a HTML Format. I’m going to use the same technique to send email.
*********************************************************
Note: Change the @ProfileName and @recipients list
*********************************************************

DECLARE
@html1 NVARCHAR(MAX),
@html2 NVARCHAR(MAX),
@dml1 NVARCHAR(MAX),
@dml2 NVARCHAR(MAX),
@td1 VARCHAR(50),
@td2 VARCHAR(128),
@td3 VARCHAR(50),
@td4 VARCHAR(50),
@td5 VARCHAR(50),
@td6 VARCHAR(5),
@td7 VARCHAR(3),
@loopstatus int,
@RowId INT,
@subject VARCHAR(100),
@RowId1 INT,
@LoopStaus1 INT,
@ProfileName NVARCHAR(50),
@recipients NVARCHAR(100)

SET @ProfileName=’TEST’
SET @recipients=’ABC@MAIL.COM;def@mail.com’

CREATE TABLE #temp
(
ID int identity(1,1),
SERVERNAME varchar(50) NULL,
DatabaseName varchar(128) NULL,
LastFullBackupDate varchar(50) NULL,
LastDifferentialBackupDate varchar(50) NULL,
LastAbsoluteBackupDate varchar(50) NULL,
LastAbsoluteBackupType char(4) NOT NULL,
BackupDelta char(3) NOT NULL
)

INSERT INTO #temp(SERVERNAME,DatabaseName,LastFullBackupDate,LastDifferentialBackupDate,LastAbsoluteBackupDate,LastAbsoluteBackupType,BackupDelta)
SELECT
SERVERNAME,
DatabaseName,
LastFullBackupDate,
LastDifferentialBackupDate,
LastAbsoluteBackupDate,
LastAbsoluteBackupType,
BackupDelta
FROM dbo.TLOG_DatabaseBackup where BackupDelta>0

select * from #temp

set @html1='<html>
<head>
<STYLE TYPE=text/css>
<table width=”100%” border=1>
<tr bgcolor=”#CCCCCC”>
<td colspan=”7” height=”25” align=”center”>
<font face=”tahoma” color=”#003399” size=”4”>
<strong>Backup Report</strong></font>
</td>
</tr>
</table>
<table width=”100%” border=1 ><tbody>
<tr bgcolor=”#CCCCCC”>
<td width=”100%” align=”center” colSpan=7><font face=”tahoma” color=”#003399” size=”2”>
<strong>Production DB Servers Backup Report </strong></font></td>
</tr>
<tr bgcolor=#CCCCCC>
<td width=”10%” align=”center”>ServerName</td>
<td width=”50%” align=”center”>DatabaseName</td>
<td width=”10%” align=”center”>LastFullBackupDate</td>
<td width=”10%” align=”center”>LastDifferentialBackupDate</td>
<td width=”10%” align=”center”>LastAbsoluteBackupDate</td>
<td width=”10%” align=”center”>LastAbsoluteBackupType</td>
<td width=”10%” align=”center”>BackupDelta</td>
</tr>
‘

SET @loopstatus=1
SET @RowId=1
SET @dml2=”
SET @dml1=”

WHILE @Loopstatus<>0
BEGIN
SELECT
@td1 = ServerName,
@td2 = DatabaseName,
@td3 = LastFullBackupDate,
@td4 = LastDifferentialBackupDate,
@td5 = LastAbsoluteBackupDate,
@td6 = LastAbsoluteBackupType,
@td7= BackupDelta
FROM #temp WHERE id=@RowId

IF @@ROWCOUNT=0
BEGIN
SET @Loopstatus=0
END
ELSE
BEGIN
SET @dml1= @DML2+N'<tr><td>’+@td1+N'</td><td>’+@td2+'</td><td>’+@td3+'</td><td>’+@td4+'</td><td>’+@td5+'</td><td>’+@td6+'</td><td>’+@td7+'</td></tr>’
SET @DMl2=@dml1
SET @dml1=”

END
SET @RowId=@RowId+1

END

SET @html2=@html1+@Dml2+'</table>’

PRINT @html2

SET @subject=’Production Servers DB Backup Report ‘+ CONVERT(varchar(10),getdate(),110)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= @recipients,–‘abc@mail.com;def@mail.com’,
@subject = @subject,
@body = @html2,
@body_format = ‘HTML’;

DROP TABLE #Temp

Note:- Make sure that you do a double check on single and double quotes

PowerShell – Get an UNC of a remote folder

To Read/Write/Rename/Copy/Delete a file configured across ‘N’ of servers in the same path, this script is going to be very handy. One example – Citrix Migration – IMA data store

Input – ServerName and Input folder path.

$server =”Server01“

$InputPath=”C:\Program Files\Citrix\Independent Management Architecture”

Execute the below Powershell script

FUNCTION getUNCPath($infile)
{
$qualifier = Split-Path $infile -qualifier
$drive = $qualifier.substring(0,1)
$noqualifier = Split-Path $infile -noQualifier
“$drive`$$noqualifier”
}

$server =”Server01″

$InputPath=”C:\Program Files\Citrix\Independent Management Architecture”

$UNC=getUNCPath($infile)

$Path=”\\$Server\$UNC”

write-host $Path

Output – \\Server01\C$\Program Files\Citrix\Independent Management Architecture

SQL – Table data – HTML Format & Email Sending using T-SQL

1) Configure DBMail  – In this case the ‘TEST’ profile being created for the demo

2) Create Table – TLOG_Metrics
3) Insert the dumnmy values or You can schedule a job to store the data in TLOG_Metrics table
4) Execute the script

STEP1:

CREATE TABLE [dbo].[TLOG_DBMetrics](
[ServerName] [varchar](1000) NULL,
[userconnections] [varchar](1000) NULL,
[LogDate] [datetime] NOT NULL
)

STEP 2:

/* Insert DUMMY Values*/

INSERT INTO TLOG_METRICS VALUES(‘Server01′,’247.00′,’2013-04-22 11:41:21.290’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’254.00′,’2013-04-22 11:31:23.390’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’259.00′,’2013-04-22 11:21:21.713’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’245.00′,’2013-04-22 11:11:21.370’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’295.00′,’2013-04-22 11:01:29.170’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’246.00′,’2013-04-22 10:51:20.510’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’239.00′,’2013-04-22 10:41:23.800’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’251.00′,’2013-04-22 10:32:35.633’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’247.00′,’2013-04-22 10:21:20.907’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’245.00′,’2013-04-22 10:11:26.327’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’289.00′,’2013-04-22 10:01:22.460’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’240.00′,’2013-04-21 09:51:21.190’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’236.00′,’2013-04-21 09:41:21.787’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’246.00′,’2013-04-22 09:31:23.463’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’238.00′,’2013-04-22 09:21:22.093’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’254.00′,’2013-04-22 09:11:22.360’)
INSERT INTO TLOG_METRICS VALUES(‘Server01′,’282.00′,’2013-04-22 09:01:25.113′)

STEP 3: /* Replace @ProfileName and Recipients list in the below script*/ For Example – SET @ProfileName =’PowerSQL’ and SET @recipients= @abc@mail.com;def@mail.com

Execute the below script

DECLARE @html1 nvarchar(MAX),
@html2 nvarchar(MAX),
@dml1 nvarchar(MAX),
@dml2 nvarchar(MAX),
@td1 varchar(10),
@td2 varchar(10),
@td3 varchar(10),
@td4 varchar(10),
@subject varchar(100),
@RowId1 int,
@LoopStatus1 int,
@RowId int,
@Loopstatus int,
@ProfileName nvarchar(50),
@recipients nvarchar(100)

SET @ProfileName=’TEST’
SET @recipients=’ABC@MAIL.COM;def@mail.com’

CREATE TABLE #Temp (
ID INT identity(1,1),
[ServerName] [varchar](1000) NULL,
[userconnections] [varchar](1000) NULL,
[LogDate] [datetime] NOT NULL)

INSERT INTO #Temp(ServerName,userconnections,LogDate)
SELECT top 10 ServerName,userconnections,LogDate
FROM [TLOG_DBMetrics]

set @html1='<html>
<head>
<STYLE TYPE=text/css>
<table width=”100%” border=1>
<tr bgcolor=”#CCCCCC”>
<td colspan=”7” height=”25” align=”center”>
<font face=”tahoma” color=”#003399” size=”4”>
<strong>User Activity Report</strong></font>
</td>
</tr>
</table>
<table width=”100%” border=1 ><tbody>
<tr bgcolor=”#CCCCCC”>
<td width=”100%” align=”center” colSpan=3><font face=”tahoma” color=”#003399” size=”2”>
<strong> No of User Active connections</strong></font></td>
</tr>
<tr bgcolor=#CCCCCC>
<td width=”10%” align=”center”>ServerName</td>
<td width=”50%” align=”center”>UserConnection</td>
<td width=”10%” align=”center”>LogDate</td>
</tr>
‘

set @loopstatus=1
set @RowId=1
set @dml2=”
set @dml1=”

While @Loopstatus<>0
begin
select
@td1 = servername,
@td2 = userconnections,
@td3 = convert(varchar(10),Logdate,110)
from #Temp where id=@RowId

if @@ROWCOUNT=0
begin
set @Loopstatus=0
end
else
begin
set @dml1= @DML2+N'<tr><td>’+@td1+N'</td><td>’+@td2+N'</td><td>’+@td3+'</td></tr>’
set @DMl2=@dml1
set @dml1=”

end
set @RowId=@RowId+1

end

SET @html2=@html1+@Dml2+'</table>’

print @html2

set @subject=’ User Activity Report ‘+ CONVERT(varchar(10),getdate(),110)

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients= @recipients,–‘abc@mail.com;def@mail.com’,
@subject = @subject,
@body = @html2,
@body_format = ‘HTML’;

DROP TABLE #Temp

PowerSQL – Find and Replace string on multiple file of local or remote machine

Copy and Paste the below content in Set-FindandReplaceString.PS1. Please read the instructions carefully. Try to do a dry run on some test folders for better results.

#############################################################################
# Description : Find and Replace string on any local or remote machine
# Input : Be specific with your string.Its a recursive update. Please be very careful.
# Parameters : ServerName (Server01)
# DriveLetter ( F or G or H)
# Extension (PS1, DOC, SQL etc:-)
# SearchString (ABC)
# ReplaceString (CBA)
# SearchFolder /*Note – Give full path of folder excluding the drive letter as the drive letter is already been
# given in a second parameter */
# If SearchFolder Input is No , It will traverse through entire drive
# If SearchFolder Input is Yes , Give the folder path for example, search abc string under f:\SQL\Temp folder,Enter just SQL\Temp
#
#
# Set-FindandReplaceString $server $drive $extn $searchString $replaceString $SearchinFolder
# Output : All Files will be updated with a given pattern
################################################################

Function Set-FindandReplaceString
{
Param([String]$server,[char]$drive,[String]$extn,[String]$searchstring,[String]$replacestring, [String]$Folder)

IF ($Folder -eq ‘No’)
{
$scriptFiles=Get-ChildItem \\$server\$drive$ *.$extn -recurse -force -ErrorAction SilentlyContinue
$ScriptFiles
foreach ($file in $scriptFiles)
{
(Get-Content $file.PSPath) | Foreach-Object {$_ -replace $searchstring, $replacestring} | Set-Content $file.PSPath
}
}

elseif($folder -eq ‘Yes’)
{
$FolderStruncture = Read-Host ” Enter the Folder Name”
if(test-path \\$server\$drive$\$FolderStruncture -pathtype container)
{
$scriptFiles=Get-ChildItem \\$server\$drive$\$FolderStruncture *.$extn -recurse -Force -ErrorAction SilentlyContinue
$ScriptFiles
foreach ($file in $scriptFiles)
{
(Get-Content $file.PSPath) | Foreach-Object {$_ -replace $searchstring, $replacestring} | Set-Content $file.PSPath
}
}

else
{
write-host “Invalid directory Structure”
}
}
else
{
Write-host “Enter the correct option”
}

}
$server=Read-Host ” Enter the servername”
$drive = Read-Host ” Enter the DriveName”
$extn = Read-Host ” Enter the extn”
$searchstring=Read-Host ” Enter the pattern to search”
$replacestring=Read-Host ” Enter the pattern to search”
$SearchinFolder = Read-Host ” Do you need to Search the pattern in Folder (No/Yes)”
Set-FindandReplaceString $server $drive $extn $searchString $replaceString $SearchinFolder

Power Shell or Windows – Rebooting of servers local or remote computers

Rebooting of servers local or remote Using PowerShell and Windows

Powershell command

Start->Run->Powershell press enter

PS:\>RESTART-COMPUTER -COMPUTERNAME SERVER01, SERVER02,LOCALHOST

Windows Command

Start->Run->cmd press enter

c:\>shutdown /m \\server01 /r /f /t 02

/m \\Server A remote computer to shutdown – server01
/r Shutdown and Restart
/f Force running applications to close. This will not prompt for File-Save in any open applications. so will result in a loss of all unsaved data!!!
/t:xxx Time until system shutdown in seconds. The valid range is xxx=0-600 seconds. [default=30]

SQL – DBCC CLEANTABLE – DROP UNUSED COLUMNS

Drop those columns which are no more in use.

For example, After Upgrade from MOSS 2007 to SharePoint Server 2010, we have noticed that all of the upgraded databases in SharePoint Server 2010 were almost 40% – 60% larger in size than before the upgrade.

We’ve decided to DROP those eligible columns and reclaim unused space out of it

Note: We ran this query against 130 GB size table and it took 10 hours to complete its operation online.

Space allocation details of a table can be found using below query:-

sp_spaceused ‘AllDocVersions’

Run the below query and monitor the resource consumption. I would recommend to run it during non-business hours

SELECT GETDATE() /* start time*/

GO /* Actual clean up operation – Online*/

DBCC CLEANTABLE(‘Content_ABCD’,’AllDocVersions’, 100) WITH NO_INFOMSGS

GO

SELECT GETDATE() /* End Time*/

Parameter details:-

DatabaseName= Content_ABCD

TableName= AllDocVersions

Batch_size =100 /Note:- Smaller the size, better the resource governance*/

Is the number of rows processed per transaction. If not specified, or if 0 is specified, the statement processes the whole table in one transaction. Its always better to keep it to small number so that transaction log will not grow abruptly and locks can be handled effectively. You can do it online.

Gather space allocation details one more time and compare it with the previous values

sp_spaceused ‘AllDocVersions’

Its not a recommended practice to shrink the database. In case, the allocation has more than 50% of free space then check for file shrinking option.

Check for available space of the database by issuing the below query.

SELECT name ,file_id,(size/128.0)/1024 ActualSizeofFileinGB,((size/128.0) – CAST(FILEPROPERTY(name, ‘SpaceUsed’) AS int)/128.0)/1024 AS AvailableSpaceInGB FROM sys.database_files;

If AvailableSpaceInGB more than 50% of allocated space, then execute

DBCC SHRINKFILE (4,140000)

File id=4

TargetSize = 140000

This can be stopped at any time(Online Operation).

Note: Perform Index Rebuilding Operation once above mentioned steps are over by checking it’s fragmentation level.

PowerShell – Find Disk Drive Details of local or remote Server

Name of the Function : Get-DiskDriveDetails

Input Parameter : ServerName

Ouptut : Grid view, you can sort and force condition on specific output column

Function call : Get-DiskDriveDetails HQDB001

In this case HQDB001 is Name of the Server

Code:

Function Get-DiskDriveDetails([String]$server)
{
Get-WMIObject Win32_LogicalDisk -filter “DriveType=3″ -computer $server| Select SystemName,DeviceID,VolumeName,@{Name=”Size(GB)”;Expression={[decimal](“{0:N3}” -f($_.size/1gb))}},@{Name=”Free Space(GB)”;Expression={[decimal](“{0:N1}” -f($_.freespace/1gb))}}|out-gridview
}

PowerShell – Find top 10 Largest files of a local or remote Drive

Name of the function: Get-LargestFile

The Input parameters are ServerName and Drive.

Function call

PS:\>Get-LargestFile -server HQDB001-drive e

In this case HQDB001[ServerName] and E[Drive]

****************************

Code:-

***********************

Function Get-LargestFile
{
Param([String]$server,[char]$drive)
Get-ChildItem \\$server\$drive$ -recurse -force -ErrorAction SilentlyContinue | Select-object Name,DirectoryName, @{Label=’Size’;Expression={($_.Length/1GB).ToString(‘F04′)}} | Sort Size -descending | select -First 10
}