Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Space Capacity Planning


Database Space Capacity Planning

Author
Message
cmille19
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
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()



sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 3841
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!
sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 3841
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
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
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.



sqlfriends
sqlfriends
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2036 Visits: 3841
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
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
I haven't looked at the code in a while, but I think you're right on the view.



slk55guy
slk55guy
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 89
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
cmille19
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 724
An assumption around always keeping at least 20% free space on NTFS volumes.



i130895
i130895
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 138
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 Name      Space Collector
Step Name      Write-VolToDb.ps1
Duration      00:00:04
Sql Severity      0
Sql Message ID      0
Operator Emailed      
Operator Net sent      
Operator Paged      
Retries Attempted      0

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
SumonB
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 120
When I execute the Database Space Capacity Report, it's not showing all databases in all instances. Please Help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search