October 15, 2009 at 6:01 am
I have tried
IEOAK-SQL2005-B & IEOAK-SQL2005-B\MSSQLSERVER
October 15, 2009 at 9:11 am
Correct code for database space, (thanks Birdmaster777):
CREATE TABLE #output(
server_name varchar(128),
dbname varchar(128),
physical_name varchar(260),
dt datetime,
file_group_name varchar(128),
size_mb int,
free_mb int)
exec sp_MSforeachdb @command1=
'USE [?]; INSERT #output
SELECT CAST(SERVERPROPERTY(''ServerName'') AS varchar(128)) AS server_name,
''?'' AS dbname,
f.filename AS physical_name,
CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,
g.groupname,
CAST (size*8.0/1024.0 AS int) AS ''size_mb'',
CAST((size - FILEPROPERTY(f.name,''SpaceUsed''))*8.0/1024.0 AS int) AS ''free_mb''
FROM ?..sysfiles f
JOIN ?..sysfilegroups g
ON f.groupid = g.groupid'
SELECT * FROM #output
October 15, 2009 at 9:14 am
graeme.black (10/15/2009)
I have triedIEOAK-SQL2005-B & IEOAK-SQL2005-B\MSSQLSERVER
What is the Powershell commmand you are running and what are contents of the server_space_lku table?
October 15, 2009 at 9:24 am
Hello,
First, many thanks for your contribution
I have the same problem than Grasshoper. (free_mb is null)
But if I execute :
SELECT CAST(SERVERPROPERTY('ServerName') AS varchar(128)) AS server_name,
f.filename AS physical_name,
CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,
g.groupname,
CAST (size*8/1024 AS int) AS 'size_mb',
CAST((size - FILEPROPERTY(f.name,'SpaceUsed'))*8/1024 AS int) AS 'free_mb'
FROM [a database]..sysfiles f
JOIN [a database]..sysfilegroups g
ON f.groupid = g.groupid
it's ok and returns a value.
It is really strange.
Another problem. I try to execute Powershell script, on another server which contains databases (who have "-" (minus) in their name and it failed.
Thanks in advance,
October 15, 2009 at 9:32 am
127.0.0.1 (10/15/2009)
Hello,First, many thanks for your contribution
I have the same problem than Grasshoper. (free_mb is null)
But if I execute :
SELECT CAST(SERVERPROPERTY('ServerName') AS varchar(128)) AS server_name,
f.filename AS physical_name,
CAST(FLOOR(CAST(getdate() AS float)) AS datetime) AS dt,
g.groupname,
CAST (size*8/1024 AS int) AS 'size_mb',
CAST((size - FILEPROPERTY(f.name,'SpaceUsed'))*8/1024 AS int) AS 'free_mb'
FROM [a database]..sysfiles f
JOIN [a database]..sysfilegroups g
ON f.groupid = g.groupid
it's ok and returns a value.
It is really strange.
Another problem. I try to execute Powershell script, on another server which contains databases (who have "-" (minus) in their name and it failed.
Thanks in advance,
The problem is the call to FILEPROPERTY system function only works for the database your are currently in (my fault). The minus symbol issue can be addressed by using brackets around the database name. I haven't hit this issue in my environment, but I can see how it can cause a problem. I'll post an update to the Write-DbSpaceToDb.ps1 Powershell for both of these issues, sometime today.
October 15, 2009 at 9:35 am
Thank you very much !! ^^ It works perfectly now for "free_mb"
[/quote]
The problem is the call to FILEPROPERTY system function only works for the database your are currently in (my fault). The minus symbol issue can be addressed by using brackets around the database name. I haven't hit this issue in my environment, but I can see how it can cause a problem. I'll post an update to the Write-DbSpaceToDb.ps1 Powershell for both of these issues, sometime today.[/quote]
October 15, 2009 at 9:42 am
The command I am running is
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B' spacedm &
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B\MSSQLSERVER'
I have tried different combinations, i have attached a picture of the table
October 15, 2009 at 9:47 am
Attached fixed download. Since the publication of article with your help I've addressed these issues:
Missing procedure creation statements from download
SQL Agent job missing parameters for PowerShell script
Incorrect caluculation of of free mb in PowerShell script
Special characters in database name causes PowerShell script to fail
I've republished the download in the article, but it may take several days to show up. The attachment below is the latest as of 10/15/2009.
October 15, 2009 at 1:45 pm
graeme.black (10/15/2009)
The command I am running isC:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B' spacedm &
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B\MSSQLSERVER'
I have tried different combinations, i have attached a picture of the table
Does server name IEOAK-2005-B host the backend repository database "spacedm"? If so, the first one should be the correct format. Have you inserted row(s) into server_space_lku for each SQL instance you want to collect data from? For testing purposes I would suggest starting out with just one (perhaps the where the repository is installed). The Powershell script is using Windows authentication to connect to each SQL instance. Do each of your SQL instances support remote connection via Windows authentication?
October 16, 2009 at 1:58 am
cmille19 (10/15/2009)
Attached fixed download. Since the publication of article with your help I've addressed these issues:Missing procedure creation statements from download
SQL Agent job missing parameters for PowerShell script
Incorrect caluculation of of free mb in PowerShell script
Special characters in database name causes PowerShell script to fail
I've republished the download in the article, but it may take several days to show up. The attachment below is the latest as of 10/15/2009.
Thanks for fixed sources !
October 16, 2009 at 2:31 am
graeme.black (10/15/2009)
--------------------------------------------------------------------------------
The command I am running is
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B' spacedm &
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B\MSSQLSERVER'
I have tried different combinations, i have attached a picture of the table
Does server name IEOAK-2005-B host the backend repository database "spacedm"? If so, the first one should be the correct format. Have you inserted row(s) into server_space_lku for each SQL instance you want to collect data from? For testing purposes I would suggest starting out with just one (perhaps the where the repository is installed). The Powershell script is using Windows authentication to connect to each SQL instance. Do each of your SQL instances support remote connection via Windows authentication?
Yes IEOAK-2005-B has the backend repository, i have this in IEOAK-SQL2005-B\MSSQLSERVER "server_space_lku " I have also tried IEOAK-SQL2005-B
Remote connections for windows authentication is also enabled
October 16, 2009 at 10:56 am
graeme.black (10/16/2009)
graeme.black (10/15/2009)--------------------------------------------------------------------------------
The command I am running is
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B' spacedm &
C:\BIN>write-dbspacetodb.ps1 'IEOAK-2005-B\MSSQLSERVER'
I have tried different combinations, i have attached a picture of the table
Does server name IEOAK-2005-B host the backend repository database "spacedm"? If so, the first one should be the correct format. Have you inserted row(s) into server_space_lku for each SQL instance you want to collect data from? For testing purposes I would suggest starting out with just one (perhaps the where the repository is installed). The Powershell script is using Windows authentication to connect to each SQL instance. Do each of your SQL instances support remote connection via Windows authentication?
Yes IEOAK-2005-B has the backend repository, i have this in IEOAK-SQL2005-B\MSSQLSERVER "server_space_lku " I have also tried IEOAK-SQL2005-B
Remote connections for windows authentication is also enabled
I don't see the picture of your table. For a default instance you should definitely use IEOAK-SQL2005-B and not IEOAK-SQL2005-B\MSSQLSERVER. The PowerShell code makes an ADO.NET connection to the SQL Server so the name you specify is not different than what you would use to connect in SSMS or an ADO.NET connection string.
What's the error you're getting?
October 20, 2009 at 3:18 am
The error message I get is
still get the following error
Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At C:\BIN\Write-DbSpaceToDb.ps1:27 char:19
+ [void]$da.fill( <<<< $dt)
I am running
C:\BIN>Write-DbSpaceToDb.ps1 "IEOAK-SQL2005-B" "spacedb"
October 20, 2009 at 4:40 am
graeme.black (10/20/2009)
The error message I get isstill get the following error
Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At C:\BIN\Write-DbSpaceToDb.ps1:27 char:19
+ [void]$da.fill( <<<< $dt)
I am running
C:\BIN>Write-DbSpaceToDb.ps1 "IEOAK-SQL2005-B" "spacedb"
If you log into IEOAK-SQL2005-B with SQL Server Management Studio, does this T-SQL statement complete successfully?
USE spacedb;
SELECT * FROM server_space_lku
October 20, 2009 at 4:55 am
cmille19 (10/20/2009)
graeme.black (10/20/2009)
The error message I get isstill get the following error
Exception calling "Fill" with "1" argument(s): "Invalid object name 'server_spa
ce_lku'."
At C:\BIN\Write-DbSpaceToDb.ps1:27 char:19
+ [void]$da.fill( <<<< $dt)
I am running
C:\BIN>Write-DbSpaceToDb.ps1 "IEOAK-SQL2005-B" "spacedb"
If you log into IEOAK-SQL2005-B with SQL Server Management Studio, does this T-SQL statement complete successfully?
USE spacedb;
SELECT * FROM server_space_lku
Viewing 15 posts - 31 through 45 (of 94 total)
You must be logged in to reply to this topic. Login to reply