SQL - Size of Database and Logs

One morning I noticed the usage on the drives of a SQL server increased by a lot from the previous day. I noticed because part of the morning checks was to, errrrrr, check the diskspace on said server.

Previous posts will show which script I used on a daily basis to check this.

What I wanted to do next was to find which DB and Logs were taking the space up (this particular server had Logs with DBs on the same drive).

The first one script I came across was:

SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files

--For specific Databases
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'AdventureWorks'
GO

From the Blog sqlauthority.

It gave me the information I needed, or thought I needed.

When checking the tempdb it turns out that this was the problem - it had grown quite large and the only safe way to clear down a tempdb is go into single-user mode or when all tempdb activity is stopped. See TechNet article.Alternatively restart the instance.......

I found some other scripts to use (sorry can't remember where I found them to pass on their names and links!). Script1 I found at a number of sites.

Script1
SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- Skip system databases
GROUP BY d.name
ORDER BY d.name

Script2
--SQL Server 2000
select
fileID
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where db_name(dbid) = 'master'

--SQL Server 2000
select
fileID
,name
,filename
,(size*8)/1024 SizeMB
,((size*8)/1024)/1024 SizeGB
,db_name(dbid) as DBName
from sysaltfiles
where filename like '%.ldf%'

--SQL Server 2005/2008
SELECT DB_NAME(database_id) AS DBName,
Name AS LogicalName,
Physical_Name, (size*8)/1024 SizeMB
Physical_Name, ((size*8)/1024)/1024 SizeGB
FROM sys.master_files
WHERE DB_NAME(database_id) = 'Master'
GO

Powershell - SID to User account

Usual issue of finding that the disk on a server is filling up.

One of the culprits can be the Recycle Bin which just gives you the SID of the offending user.

I found this useful link on the Microsoft site which describes how to find the information in the Registry.

It is a very short article which essentially states:
Use this tool at your own risk.

1. Open Registry Editor and navigate to:
   
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion \ProfileList
2. Under the ProfileList key, you will see the SIDs. By selecting each one individually, you can look at the value entry and see what user name is associated with that particular SID.

Worked great for me - I am also looking for a simple Powershell script to give me the information. Once I do I'll post it here.

To Powershell the query I found this useful script (just add the appropriate SID):

$objSID = New-Object System.Security.Principal.SecurityIdentifier `
 ("S-1-5-21-xxxxxxxxxx-xxxxxxxxxx-xxxxxxxxxx-27810")
 $objUser = $objSID.Translate( [System.Security.Principal.NTAccount])
$objUser.Value

Powershell - Disk usage

I found a very useful script online a few weeks ago (but unfortunately I can't remember where from otherwise I would credit them!)

The script pulls machine names from a text file and then hunts down each disk and capacity used/free then saves the information to  htm file.

#requires -version 2.0
#use parameter  drive report to html.ps1 computer1,computer2 or a computer list file
#change file path and name on line 7 below to reflect name and  path of computer list file using.
#script will open web browser with current report when completed.

Param (
$computers = (Get-Content  "C:\Temp\Computers.txt")
)

$Title="Hard Drive Report to HTML"

#embed a stylesheet in the html header
$head = @"
<mce:style><!--
mce:0
--></mce:style><style _mce_bogus="1"><!--
mce:0
--></style>
<Title>$Title</Title>
<br>
"@ 

#define an array for html fragments
$fragments=@()

#get the drive data
$data=Get-WmiObject -Class Win32_logicaldisk -filter "drivetype=3" -computer $computers

#group data by computername
$groups=$Data | Group-Object -Property SystemName

#this is the graph character
[string]$g=[char]9608 

#create html fragments for each computer
#iterate through each group object
        
ForEach ($computer in $groups) {
    
    $fragments+="<H2>$($computer.Name)</H2>"
    
    #define a collection of drives from the group object
    $Drives=$computer.group
    
    #create an html fragment
    $html=$drives | Select @{Name="Drive";Expression={$_.DeviceID}},
    @{Name="SizeGB";Expression={$_.Size/1GB  -as [int]}},
    @{Name="UsedGB";Expression={"{0:N2}" -f (($_.Size - $_.Freespace)/1GB) }},
    @{Name="FreeGB";Expression={"{0:N2}" -f ($_.FreeSpace/1GB) }},
    @{Name="Usage";Expression={
      $UsedPer= (($_.Size - $_.Freespace)/$_.Size)*100
      $UsedGraph=$g * ($UsedPer/2)
      $FreeGraph=$g* ((100-$UsedPer)/2)
      #I'm using place holders for the < and > characters
      "xopenFont color=Redxclose{0}xopen/FontxclosexopenFont Color=Greenxclose{1}xopen/fontxclose" -f $usedGraph,$FreeGraph
    }} | ConvertTo-Html -Fragment 
    
    #replace the tag place holders. It is a hack but it works.
    $html=$html -replace "xopen","<"
    $html=$html -replace "xclose",">"
    
    #add to fragments
    $Fragments+=$html
    
    #insert a return between each computer
    $fragments+="<br>"
    
} #foreach computer

#add a footer
$footer=("<br><I>Report run {0} by {1}\{2}<I>" -f (Get-Date -displayhint date),$env:userdomain,$env:username)
$fragments+=$footer

#write the result to a file
ConvertTo-Html -head $head -body $fragments  | Out-File "C:\SQL Reports\drivereport_$((Get-Date).ToString('yyyy-MM-dd-hhmmss')).htm"

The only real amendment I made was adding the date time stamp to the output file.

Once armed with the information about the disk usage I can then investigate further by seeing what is taking the space up. I found a free tool which does the job - WinDirStat.

I will endeavour to find out who wrote the script........
And I just have!! - Gandalf 50

Batch file - removing files older than x

I needed to remove IIS logs older than a number of days due to the C drive filling up - I found some information on the net with regards forfiles (instead of using Powershell which was my first port of call but due to the server running Windows 2003).

The script removes files older than 3 days and outputs the errors to a log file (or should - nothing in my log file at present but I've had no errors to date!).

forfiles /p C:\WINDOWS\system32\LogFiles\W3SVC100\ /s /m *.* /d -3 /c "cmd /c del @path" >> C:\Temp\remove_file_log.txt

SCCM 2007 - Remote Tools prompt for credentials

Issue where you are prompted for credentials after starting Remote Tools and your account has the appropriate permissions.

Usually indicates that the SCCM client isn’t installed correctly.

1    Uninstall client from the machine – choose the appropriate method at the time. RDP, PSEXEC, etc.

a.        Go to Windows\ccmsetup folder.

b.       Run ‘ccmsetup /uninstall’

2    From the SCCM Console – Computer Management > Collections > All Systems.

a.        Search for machine.

b.       Right-click on the machine name and choose ‘Install Client’.

      Check the log to see if the changes have taken affect: C:\Windows\ccmsetup\ccmsetup.log

NOTE: To read the log properly you will need to use SMS Trace.

Using PSEXEC for a remote command prompt:

C:\PSTools> psexec.exe \\<remote-machine-name> cmd.exe

Links:

http://technet.microsoft.com/en-us/library/bb694276.aspx

http://social.technet.microsoft.com/Forums/systemcenter/en-US/c27eb3be-210a-4b4c-aaa8-f6f98dbb5232/uninstalling-sccm-client?forum=configmgrsetup

PSTools http://technet.microsoft.com/en-gb/sysinternals/bb896649.aspx

SMS Trace http://www.microsoft.com/en-us/download/details.aspx?id=9257

Powershell - Creating a scheduled task to run a script gathering application logs from a number of servers

Hmmm.... long title for such a small task.

As the title says - gathering the application log from a number of servers, running it as a scheduled task.

First of lets start with the script

## List of servers
$server = @('Server1','Server2','Server3');
## Get the application log of every server listed above
foreach ($computer in $server) {
Get-EventLog -ComputerName $server -LogName "Application" |
Export-csv -Path "C:\Data\Log_$((Get-Date).ToString('yyyy-MM-dd-hhmmss')).csv"}

I have found that the script does keep running and running and running......
(Note to self - fix!)

Which leads me onto the scheduled task.

The task is going to run on Windows 2008 R2 server.

Administrative Tools > Task Scheduler > Task Scheduler Library.

General

Created a Domain account purely for this scheduled task.

Run whether user is logged on or not.

Triggers

On a schedule

Daily

Set date/time

Set occurance

Stop task if runs longer than (I set mine to 4 hours)

Check the enabled box

Actions

Action - Start a Program

Program/script: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Add arguments: & 'C:\<path>\script-name.ps1'

Conditions

Left at default

Settings

Leave at default settings

History

Log of the task

The scheduled task should now run - the difficult part was working out what arguments should be added to the Actions.

Powershell - Query Group Membership in another Domain

This in one way that I know of to search another Domain by mounting it as a drive (I'm sure there are different ways of skinning a cat but this technique would work for a number of Domains - again I'm sure there are further options to be able to the same thing!!).

## Query Group Membership ##
Import-Module ActiveDirectory
## Mount AD as a drive ##
New-PSDrive -Name OtherDomain -PSProvider ActiveDirectory -Root 'DC=other,DC=domain,DC=co,DC=uk' -Server dc1.other.domain.co.uk -Credential DOMAIN\account
cd OtherDomain:
Get-ADGroup -Filter {Name -eq "ADGroup"} |
     ForEach-Object{
          $hash = @{GroupName = $_.Name ; UserID = '' ; Member = ''}
          $_ | Get-ADGroupMember -ea 0 -recurs |
              ForEach-Object{
                   $hash.Member = $_.Name
                   $hash.UserID = $_.SamAccountName
                   New-Object psObject -Property $hash
              }
          } | sort groupname, member | Export-CSV "C:\Data\ADGroup.csv" –NoTypeInformation