Database Space Capacity Planning

Question

Database Space Capacity Planning

Viewing 15 posts - 76 through 90 (of 94 total)

You must be logged in to reply to this topic. Login to reply

slk55guy SSC Veteran Points: 260 More actions · Answer 1

Yeah throughout the debugging, the @dt variable never appeared to have a value assigned. All other variables were populated, including server name etc

slk55guy SSC Veteran Points: 260 More actions · Answer 2

I have narrowed it down. Removing the Primary Key from the table vol_space and allowing NULLS on the server_name field inserts records but without a server name. Somewhere the server\computer name is not being populated, maybe in the Out-DataTable funtion. The column names picked and added to the data table are:

FreeGb

Label

Name

PercentFree

SizeGB

SystemName ?

UsageDT

SystemName appears to be the culprit?

cmille19 SSCertifiable Points: 5970 More actions · Answer 3

Not sure why SystemName would be null. Let's try forcing the systemname. Replace the existing Get-Vol function with this version:

#######################

function Get-Vol

{

param($computerName)

Get-WmiObject -computername "$ComputerName" Win32_Volume -filter "DriveType=3" |

foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd"))

add-member -in $_ -membertype noteproperty SizeGB $([math]::round(($_.Capacity/1GB),2))

add-member -in $_ -membertype noteproperty FreeGB $([math]::round(($_.FreeSpace/1GB),2))

add-member -in $_ -membertype noteproperty PercentFree $([math]::round((([float]$_.FreeSpace/[float]$_.Capacity) * 100),2))

add-member -in $_ -MemberType noteproperty SystemName $ComputerName -force -PassThru } |

select UsageDT, SystemName, Name, Label, SizeGB, FreeGB, PercentFree

}# Get-Vol

slk55guy SSC Veteran Points: 260 More actions · Answer 4

Yes, that has fixed it. Thank you. 🙂

I wonder why this happened.

narayanaswamy SSC Enthusiast Points: 157 More actions · Answer 5

Hi All,

In SQL Server 2008 instance i have created spacedm database and created all objects tables,views,sp's

I've executed this command prompt .\write-dbspacetodb.ps1 'instancename' spacedm

I am getting below error from powershell. An exception calling "fill" with "1" can't open database spacedm. Login failed to open Spacedm database

Please help me how to resolve this issue. I have created one login assigned db_owner to spacedm even then same issue.

cmille19 SSCertifiable Points: 5970 More actions · Answer 6

narayanaswamy (10/18/2012)
Hi All,
In SQL Server 2008 instance i have created spacedm database and created all objects tables,views,sp's
I've executed this command prompt .\write-dbspacetodb.ps1 'instancename' spacedm
I am getting below error from powershell. An exception calling "fill" with "1" can't open database spacedm. Login failed to open Spacedm database
Please help me how to resolve this issue. I have created one login assigned db_owner to spacedm even then same issue.

It seems like you're having trouble making a connection. Try just running a test connection:

$serverName = 'instancename'

$databasename = "spacedm"

$connString = "Server=$serverName;Database=$databaseName;Integrated Security=SSPI;"

$con = new-object System.Data.SqlClient.SqlConnection

$con.ConnectionString = $connString

$con.Open()

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

Thanks, Chad, this is one of the best scripts on internet I can find about space monitoring.

I tried serveral before and compare them with this one, this is the cleanest and more powerfull code

and implementation.

Thanks much!

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

A question about permissions.

When I schedule the job, because it will run using SQL agent service account, it won't work because it cannot login to other computers using that service account to check disk and db size.

So I created a proxy account using my own credentials, for I am a dba, so I have access to those servers that I need to check.

Is that the right approach to go? I mean for the account that run the SQL job - it has to be sysadmin on all those servers that need to be checked, correct?

Thanks

cmille19 SSCertifiable Points: 5970 More actions · Answer 9

sqlfriends (1/24/2013)
A question about permissions.
When I schedule the job, because it will run using SQL agent service account, it won't work because it cannot login to other computers using that service account to check disk and db size.
So I created a proxy account using my own credentials, for I am a dba, so I have access to those servers that I need to check.
Is that the right approach to go? I mean for the account that run the SQL job - it has to be sysadmin on all those servers that need to be checked, correct?
Thanks

The way I run it--I use an account that has sysadmin rights on servers being collected. I'm not sure if less rights will work easily.

sqlfriend SSC Guru Points: 52781 More actions · Answer 10

Thanks much.

Does this job have to be scheduled to run daily? How about if I just want to collect the data once a week, or once a month?

If so, do I need to change the view for the column of daily_growth_mb,

because in the view it is like AND d2.dt = DATEADD(dd, - 1, d1.dt)

If I don't change it, and I collect only once a week, then I think it will has no records. correct?

Thanks

cmille19 SSCertifiable Points: 5970 More actions · Answer 11

I haven't looked at the code in a while, but I think you're right on the view.

slk55guy SSC Veteran Points: 260 More actions · Answer 12

Can anyone tell me why the calculation for usable size is (v1.percent_free, v1.size_gb * .8 AS usable_size_gb).

I know it's got to do with the initial disk formating and so forth but why the .8? Something to do with 8 bytes?

cmille19 SSCertifiable Points: 5970 More actions · Answer 13

An assumption around always keeping at least 20% free space on NTFS volumes.

i130895 SSC Journeyman Points: 81 More actions · Answer 14

I removed primary key from dbo.vol_space table because I was getting PK vialotion error. Now I am getting below error. Please help.

ob NameSpace Collector
Step NameWrite-VolToDb.ps1
Duration00:00:04
Sql Severity0
Sql Message ID0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted0
Message
Executed as user:. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 51 in a PowerShell script. The corresponding line is 'Get-WmiObject -computername "$ComputerName" Win32_Volume -filter "DriveType=3" | '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Invalid class ' A job step received an error at line 52 in a PowerShell script. The corresponding line is 'foreach { add-member -in $_ -membertype noteproperty UsageDT $((Get-Date).ToString("yyyy-MM-dd")) '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot bind parameter because parameter 'InputObject' is specified more than once. To provide multiple values to parameters that can accept multiple values, use the array syntax. For example, "-parameter value1,value2,value3". '. Process Exit Code -1. The step failed.

SumonB SSC-Addicted Points: 490 More actions · Answer 15

When I execute the Database Space Capacity Report, it's not showing all databases in all instances. Please Help.