SQL Server MDF File Fragmentation

Issue: SQL reject writing with error ‘…because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup…’

Checks:

  1. disk space – 100GB free
  2. autogrow was set properly
  3. restart SQLSERVER / SQLAGENT
  4. contig –a xxx.mdf saying huge number of frags and biggest continuous space is 14GB
  5. run contig xxx.mdf but no enough space to defrag and it is slooooow and I/O intensive.

Solution: It is a DEVEL database and no one really cares about it, so we deleted table, rerun the schema and restored from backups

Thoughts:

  1. smaller database file
  2. reduce autogrow

http://www.mssqltips.com/sqlservertip/3008/solving-sql-server-database-physical-file-fragmentation/

 

Inherited Permissions not work on Windows Server 2003

Workaround…

function Get-LocalGroups() {  
    net localgroup | ?{ $_ -match "^\*.*" } | %{ $_.SubString(1) };
}
    
function Get-LocalGroupMembers() {
    param ([string]$groupName = $(throw "Need a group name") )

    $lines = net localgroup $groupName
    $found = $false  
    for ($i = 0; $i -lt $lines.Length; $i++ ) {
        if ( $found ) {      
            if ( -not $lines[$i].StartsWith("The command completed")) {
                $lines[$i]
            }
        }
        elseif ( $lines[$i] -match "^----" ) {
            $found = $true;
        }
    }
} 

function Copy-LocalGroupMembers() {
    param (
        [string]$sourceGroupName = $(throw "Need a sourceGroupName"), 
        [string]$destinationGroupName = $(throw "Need a destinationGroupName") 
        )

    #Checking parameters: source group and destination group existence
    [Array]$allGroups = Get-LocalGroups
    if ($allGroups -eq $null -or $allGroups.Count -eq 0) {
        Write-Output "Warning: Fail to get Users and Groups information"	
        Read-Host "Press any key to exit"
        exit;
    }
    
    if ($allGroups -notcontains $sourceGroupName){
        Write-Output ([System.String]::Format("Warning: {0} doesn't exist", $sourceGroupName));
        Read-Host "Press any key to exit"
        exit;
    }
    
    if ($allGroups -notcontains $destinationGroupName){
        Write-Output ([System.String]::Format("Warning: {0} doesn't exist", $destinationGroupName));
        Read-Host "Press any key to exit"
        exit;
    }
    
    #checking source group contained users
    [Array]$sourceUsers = Get-LocalGroupMembers $sourceGroupName
    [Array]$destinationUsers = Get-LocalGroupMembers $destinationGroupName

    if ($sourceUsers -ne $null -or $sourceUsers.Count -gt 0){
        foreach($su in $sourceUsers){
            try {
                
                if ([string]::IsNullOrEmpty($su)){
                    continue
                }
                elseif ($destinationUsers -notcontains $su) {
                    $null = net localgroup $destinationGroupName $su /ADD
                    Write-Output ([System.String]::Format("{0} added to {1}", $su, $destinationGroupName));
                }
                else {
                    Write-Output ([System.String]::Format("{0} already in {1}", $su, $destinationGroupName));
                }
            }
            catch {
                Write-Output ([System.String]::Format("Exception: Fail to add {0} in {1}", $su, $destinationGroupName));
                Read-Host "Press any key to continue"
                continue
            }
        }
    }
}

###Main###
#Checking OS version
$OS = Get-WmiObject -Class Win32_OperatingSystem
if ($OS -eq $null){
    Write-Output "Error: Fail to detect Windows version."
    Read-Host "Press any key to exit"
    exit
}

if (-not $OS.Version.StartsWith("5.2") -and -not $OS.Caption.Contains("Windows Server 2003")){
    Write-Output $OS.Caption
    Write-Output "No need to apply this script"
    Read-Host "Press any key to exit"
    exit
}

#Adding users if needed
Copy-LocalGroupMembers srcGroup destGroup

Read-Host "Press any key to exit"

32-bit app on 64-bit Windows(32位应用程序 on 64位操作系统)

WOW64提供向后兼容的功能
|- WOW64代表Windows 32-bit on Windows 64-bit

WOW64文件位置
|- 32位应用程序 C:\Program Files (x86)
|- 64位应用程序 C:\Program Files
|- 32位系统文件 C:\Windows\System32(虽然叫32但并不是32位的意思)
|- 64位系统文件 C:\Windows\SysWOW64

WOW64向前兼容功能
|- 32位应用程序原本会调用C:\Program Files和C:\Windows\System32的文件
|- 经WOW64重新定位到C:\Program Files (x86)和C:\Windows\SysWOW64
|- 生成本地的64位
|- 做32位和64位的对齐双向转换
|- 在user mode下运行,所以不影响64位kernel运行

例外
|- 一些32位驱动
|- 需要调用驱动的应用,如杀毒,防火墙
|- 同时安装一个程序的32位和64位,regkey最后更新为准