Tag Archives: 2000

Superfast Powershell Script Gets SQL Server Log Utilization

This is a nifty Powershell script which uses DBCC sqlperf(logspace) to get the log utilization for all your databases on all your servers. Best of all, it is super quick! I have over a dozen servers from 2000 to 20008 with numerous databases on each. This routine returns all my log information in a matter of seconds! It is also an example of developing your own Powershell for piping objects in your scripts. This little endeavor has taught me the real value of returning objects from the “GETS” I develop.

Measure-Command determined the collection process for ALL my servers took less than a second!

Measure_GetLogSpace

The code is presented in several blocks because of the modular design whereby I can possibly use some of the pieces in other ways. The functions are imbedded in a PowerShell module which has many other SQL helper functions not pertinent to this topic. Take a look at An Introduction to PowerShell Modules which is a really good start for developing and using modules in version 2. Using modules to help centralize my admin functions has been of enormous benefit especially when developing new scripts. I like putting my helper functions into a module because my PowerGUI editor is able to pick them up for intellisense. Love that PowerGUI! Also note: everything presented is dependent on at least Powershell V 2.

This first block is the main function which does the actual work of getting and delivering the log information. You will notice the use of Begin… Process… and End… blocks and  a parameter which allows pipeline input.

Function Get-LogSpaceUsed {
<#
.SYNOPSIS
Gets Log space utilization for all databases on an SQL Server instance.

.DESCRIPTION
Uses DBCC sqlperf(logspace) to get log space utilization for all instance databases
for one or multiple servers. Server names can be piped to this function. Output is 
an object or array of objects with ServerName, DatabaseName, PercentLogSpaceUsed,
and LogSizeMb.

.PARAMETER Server
Mandatory: The SQL Server instance which is to be evaluated. Accepts pipeline input.

.EXAMPLE
Get-LogSpaceUsed -Server 'Server\Instance'
Returns log utilization for all databases on an instance.

.EXAMPLE
Get-Content "C:\Scripts\PoSH\Servers.txt" | Get-LogSpaceUsed 
Pipes a list of server names to the function and returns log utilization for
all databases on each server in the list.

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	[CmdletBinding()]
	Param(
	[Parameter(Position=0, Mandatory=$true,ValueFromPipeline = $true)] 
	[string]$server
	)

	Begin {
		$alldata = @()
		$query = @"
DECLARE 
	@dbcc varchar(250),
	@scom varchar(250)
	set @dbcc = 'dbcc sqlperf(logspace)'

Create Table #Logspace 
	(
		DatabaseName varchar(250),
		LogSizeMB dec(10,2),
		PercentLogSpaceUsed dec(5,2),
		[Status] int
	)

Insert Into #LogSpace
	EXEC (@dbcc)

SELECT @@SERVERNAME As 'ServerName', DatabaseName, PercentLogSpaceUsed, LogSizeMB 
FROM #LogSpace 
ORDER BY LogSizeMB desc

Drop Table #LogSpace 
"@
	}

	Process{
		Try {
			# Get a new connection
			$conn = New-SqlConnection -server $server
			$ds=New-Object system.Data.DataSet
			$da=New-Object system.Data.SqlClient.SqlDataAdapter($query,$conn)
			$da.fill($ds) | Out-Null
			$da.Dispose()
		}
		Catch { 
			Throw $_.Exception 
		}
		Finally {
			Close-SqlConnection -conn $conn
			$conn = $null

		}
		Foreach($row in $ds.Tables[0].Rows) {
			$alldata += $row
		}
	}
	End {
		Return $alldata
	}
}

A helper function to create a new SQL Connection suitable for running a query

Function New-SqlConnection {
	[CmdletBinding()]
	Param(
		[string]$server=$(Throw "Missing server param"),
		[string]$database="master"
	)
	Process{
		Try {
		    $conn = new-object ('System.Data.SqlClient.SqlConnection')
		    $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name='Powershell App'"
		    $conn.ConnectionString = $connString
		    $conn.StatisticsEnabled = $true
		    $conn.Open()
			$conn
		}
		Catch {Throw $_.Exception }
	}
}

AND….. the script to bring it together and deliver the results

<#
.SYNOPSIS
Gets Log space utilization for all databases on one or more SQL Server instance(s).

.DESCRIPTION
Pipes an array of server names to a module funtion which obtains log utilization
information for each database and presents the rusults

.PARAMETER Servers
Optional: The SQL Server instance which is to be evaluated. If a server or list 
of servers is not presented, the script will use a default list of servers from
a local file.

.EXAMPLE
Get-LogSpaceUsed 
Uses a default servers list from a local file to gather log information 

.EXAMPLE
Get-LogSpaceUsed -servers "Myserver1","myserver2\instance"
Gets log information for databases on two specific servers 

.NOTES
Author: John Wood
Date: August, 2012
WEB: http://www.webofwood.com/
email: Johnp.wood@gmail.com
Copyright: (c) 2012 John P. Wood
You are free to copy/use this script as long as you keep these notes and 
attibute credit to the original author
#>	
[CmdletBinding()]
Param(
	[Parameter(Position=0, Mandatory=$false)] 
	[string[]]$servers = $(%{Get-Content "C:\Scripts\PoSH\Servers.txt"}),
	[Parameter(Position=1, Mandatory=$false)] 
	[int]$maxlogsize = 100
)
$rows = @()
Try{
	$logs = $servers | Get-LogSpaceUsed | Where-Object {$_.LogSizeMb -ge $maxlogsize}
}
Catch {
	Write-Error "$server Server error:"
	$ex = $_.Exception
	Write-Error $ex.Message
	While($ex.InnerException) {
		$ex = $ex.InnerException
		Write-Error $ex.Message
	}
}
$logs
Share

Linked Server 64bit Deficiency

First off, I need to give recognition to Joel Mansford for his blog which gave me the answer to my problem.
Connecting SQL Server 2005 x64 to SQL Server 7 (32bit) as a linked server!?

After creating a linked server on a 64bit SQL Server 2005 instance, I then needed to create a view which referenced the linked server. The linked server is a SQL Server 2000 32bit instance. However, when I tried to create the view, I got this error:  Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI”.

This really proved to be a conundrum because I had just recently created the same linked server and view on another SQL 2005 instance. However, I had not yet connected the fact the 2005 instance was 32bit as opposed to the one I was now working on, which is 64bit.

I wrestled with it being a permissions issue for a while but ruled that out when I could not create the view even while using a domain admin account having authority of all the instances. A search on Google, using the above mentioned error message, turned up Joel’s blog. His specific problem was relative to SQL Server 7 but the answer applied to SQL Server 2000 also. He had found the original post of the ultimate solution and was good enough to include it in his blog which I have an excerpt here.

I found an extremely helpful post by Marek Adamczuk at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=62887&SiteID=1. Marek  explains the 32-bit editions are missing a stored procedure on the master database called sp_tables_info_rowset_64.

Marek’s solution is to create a wrapper on the master database as follows (his code not mine):

create procedure sp_tables_info_rowset_64
    @table_name sysname,
    @table_schema sysname = null,
    @table_type nvarchar(255) = null
as
declare @Result int set @Result = 0
exec @Result = sp_tables_info_rowset
    @table_name,
    @table_schema,
    @table_type

It would appear the existing sproc does the same thing but just needs to be referenced using the 64bit name. Kudos to both Joel and to Marek for making this solution available!

Share