search
Categories
Sponsors
VirtualMetric Hyper-V Monitoring, Hyper-V Reporting
Archive
Blogroll

Badges

Microsoft PowerShell MVP
MCSE
Sponsors

Cozumpark Bilisim Portali
Posted in Windows Powershell | No Comment | 889 views | 10/01/2016 00:16

Bir değişkenin değerini, dosya içeriğinden aşağıdaki gibi okuyabilirsiniz.

${C:\deneme.txt}

Değişkene aşağıdaki gibi yazdığınızda ise dosya içeriği güncellenir.

${C:\deneme.txt} = "test"

Farklı PowerShell session’larına bu şekilde variable aktarılabilir.


Posted in Hosting & IIS7 | No Comment | 1,647 views | 01/12/2015 10:44

Cronjob’a günlük olarak tanımlayıp, 1 hafta önceki log dosyalarının otomatik ziplenerek silinmesini sağlayabilirsiniz.

IIS’in PowerShell Management Yönetim aracına ihtiyaç duyar. Kurulu değil ise Roles’den ekleyebilirsiniz. Fakat default olarak genelde kurulu olur.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
Import-Module WebAdministration
 
function ConvertTo-CompressedFile
{
	param([string]$ZipPath)
 
	# Get Files
	[string[]]$Files = $input.FullName;
 
	if ($Files -ne $Null)
	{
		# Load the assembly
		[System.Reflection.Assembly]::LoadWithPartialName("System.IO.Compression.FileSystem") | Out-Null
 
		# Check Zip Extension
		if ($ZipPath -notlike "*.zip")
		{
			Write-Output "Please check your zip file path."
			break;
		}
 
		# Check Zip Path
		if(-not (Test-Path($ZipPath)))
		{
			# Get Zip File Name
			$ZipName = $ZipPath.Split("\")[-1]
 
			if ($ZipPath -notlike "*\*")
			{						
				# Get Current Location
				$CurrentLocation = (Get-Location).Path
 
				# Get Current Zip File Path
				$CurrentZipPath = $CurrentLocation + "\" + $ZipName
 
				# Update Zip Path
				$ZipPath = $CurrentZipPath;
			}
 
			# Create Zip File
			Set-Content $ZipPath ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
 
			# Set File Attributes
			(Get-ChildItem $ZipPath).IsReadOnly = $false
		}
 
		# Get Zip File
		$ZipFile = [System.IO.Compression.ZipFile]::Open($ZipPath,"Update")
 
		foreach($File in $Files)
		{
			# Get File Name
			$FileName = $File.Split("\")[-1]
 
			# Compress File
			[System.IO.Compression.ZipFileExtensions]::CreateEntryFromFile($ZipFile,$File,$FileName,"optimal") | Out-Null
 
			# Buffer
			Start-Sleep -milliseconds 5
		}
 
		# Close Zip File
		$ZipFile.Dispose()
 
		# Output Zip Path
		$ZipPath
	}
}
 
# Get Web Sites
$WebSites = Get-Website | Select Name, Id, LogFile
 
foreach ($WebSite in $WebSites)
{
	# Clear Variables
	$LogFiles = $Null;
	$LogFilePath = $Null;
 
	# Get Web Site Information
	$SiteName = $WebSite.Name
	$SiteID = $WebSite.Id
 
	# Get Web Site Log Path
	$LogDirectory = $WebSite.LogFile.Directory -Replace '%SystemDrive%', $env:SystemDrive
	$LogPath = $LogDirectory + "\W3SVC" +  $SiteID
	$LogFiles = Get-ChildItem $LogPath -Filter *.log -EA SilentlyContinue | Where {$_.LastWriteTime -lt (Get-Date).AddDays(-7)}
 
	foreach ($LogFile in $LogFiles)
	{
		# IO Optimizer
		Start-Sleep 5
 
		# Log File Name
		$LogPath = $LogFile.FullName
 
		# Zip Name
		$ZipPath = $LogPath + ".zip"
 
		# Compress
		$LogFile | ConvertTo-CompressedFile $ZipPath
 
		# Remove
		$LogFile | Remove-Item -Force -Confirm:$false
	}
}

Umarım faydası dokunur. Kolay gelsin.


Posted in Windows Server | No Comment | 936 views | 25/11/2015 15:00

This is a query of mine, which gives you detailed information about indexes on one query:

SELECT SO.NAME AS TableName, PS.object_id AS ObjectId, SI.NAME AS IndexName, PS.index_id AS IndexId, SUM(PS.RESERVED_PAGE_COUNT*8) TotalStorage, SUM(PS.USED_PAGE_COUNT*8) UsedStorage, SUM((PS.RESERVED_PAGE_COUNT - PS.USED_PAGE_COUNT)*8) FreeStorage,
SUM(CASE WHEN PS.INDEX_ID IN (0,1) THEN PS.ROW_COUNT ELSE 0 END) AS ROW_COUNT,
MAX(round(Avg_Fragmentation_In_Percent, 2)) AS 'AvgFragmentationInPercent',
MAX(fragment_count) AS FragmentCount,
MAX(ips.page_count) AS PageCount,
MAX(ius.user_scans) AS UserScans, MAX(ius.user_seeks) AS UserSeeks, MAX(ius.user_lookups) AS UserLookups
FROM
SYS.DM_DB_PARTITION_STATS PS
INNER JOIN sys.dm_db_index_physical_stats(5,NULL,NULL,NULL,'LIMITED') AS ips
ON ps.object_id = ips.object_id AND
       ps.index_id = ips.index_id
INNER JOIN SYS.OBJECTS SO ON SO.OBJECT_ID = PS.OBJECT_ID
LEFT JOIN SYS.INDEXES SI ON SI.OBJECT_ID = PS.OBJECT_ID
AND SI.INDEX_ID = PS.INDEX_ID
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON ius.database_id = 5 AND ps.object_id = ius.object_id AND ps.index_id = ius.index_id
WHERE
SO.IS_MS_SHIPPED = 0
GROUP BY so.name, ps.object_id, si.name, ps.index_id
ORDER BY UserLookups DESC

You can also add Schema column if you have different db schemeas.


Posted in Windows Server | No Comment | 1,017 views | 10/11/2015 15:14

Check following query to improve your database performance:

EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max server memory (MB)', N'11000'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'max degree of parallelism', N'1'
GO
EXEC sys.sp_configure N'max server memory (MB)', N'11000'
GO
EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'  RECONFIGURE WITH OVERRIDE
GO

This is a note for myself. So you should edit config to meet your requirements.


Posted in Windows Server | No Comment | 1,270 views | 10/11/2015 15:10

You can get Database Index Fragmentation Reports with following query:

SELECT object_name(ps.object_id) AS [name], 
	ps.index_id,
	i.name AS IndexName,
	ps.avg_fragmentation_in_percent, 
	ps.fragment_count,
	ps.avg_fragment_size_in_pages, 
	ps.page_count 
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) ps
LEFT JOIN sys.indexes i ON i.object_id=ps.object_id AND i.index_id=ps.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ps.page_count  > 100
ORDER BY ps.avg_fragmentation_in_percent DESC

You can check MSDN for more information about sys.dm_db_index_physical_stats.


Posted in Windows Server | No Comment | 1,065 views | 10/11/2015 15:07

Following query will give you details about SP usage:

SELECT DB_NAME(database_id) DBName,
 OBJECT_NAME(object_id) SPName,
 last_execution_time LastExec,
 last_elapsed_time/1000 LastTimeMS,
 last_worker_time/1000 LastWorkerCPU,
 last_physical_reads LastPReads,
 last_logical_writes LastLWrites,
 last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats 
WHERE database_id = DB_ID()

If you need to get all custom SPs in one query:

SELECT DB_NAME(database_id) DBName,
 OBJECT_NAME(object_id) SPName,
 last_execution_time LastExec,
 last_elapsed_time/1000 LastTimeMS,
 last_worker_time/1000 LastWorkerCPU,
 last_physical_reads LastPReads,
 last_logical_writes LastLWrites,
 last_logical_reads LastLReads
FROM sys.dm_exec_procedure_stats 
WHERE database_id > 4 AND database_id < 32767

Just filtered last stats, but you can use SELECT * to get all stats.


Posted in Windows Server | No Comment | 1,022 views | 10/11/2015 15:01

If you need to get all table column names from MSSQL database, you can use following query:

SELECT 
o.name, c.name 
FROM sys.COLUMNS c
INNER JOIN sys.objects  o ON c.object_id=o.object_id
ORDER BY o.name, c.column_id

That will output all column names.