Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to see Windows server information Expand / Collapse
Author
Message
Posted Monday, December 2, 2013 8:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, Visits: 102
Hello, I am seeking advice from experienced SQL Server administrators who have had to deal with administering SQL Servers when they are not allowed to remote to the Windows server and have local permissions to see the drives and server information.

Personally I have always worked at small to midsize companies [or service companies] that have had system administrator privileges as well as SQL Server sysadmin and serveradmin privileges.

Now I am at a larger institution where the infrastructure system administrators want to limit access and privileges to the servers that host SQL Server. They don't want the SQL Server DBA's to use Remote Desktop to connect to the servers.

I am hoping there are server level commands that exist that can be used through the SQL Server Management Studio that I don't know about [since I have never needed to use them].

For a very basic example, usually to see how much space is still available to add to data or log files I will look at Windows Explorer to see the size of the drives and available space. If I can't remote to the machine to do that ... how am I supposed to find out size & availability?
Post #1518908
Posted Monday, December 2, 2013 10:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 7:56 AM
Points: 6,723, Visits: 8,462
xp_fixeddrives shows free space on the drives the sqlinstance can see.

As you now experience, being granted windows level authorities is still a privilege.
Even though an experienced sqlserver dba will need more information than just the little bit available through the instance.
In my experience, windows level administrator privileges are not granted because someone just performs job protection.

An unexperienced dba may indeed mess up windows quite a bit.

Keep in mind only a collaborative model will result in a win / win situation for your company


On the other hand, if e.g. your company outsourced server hosting, you may even NOT want to have administrator privileges at windows level, just because of segregation of duties.
In that case, request your windows admins to provide you the windows level collected data of SQLPowerdoc ( https://sqlpowerdoc.codeplex.com/ )
on a regular basis.

It collects quite a bit of valuable information.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1518949
Posted Tuesday, December 3, 2013 10:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 11:28 AM
Points: 553, Visits: 1,618
Here is a script I use to look at available drive space where database files are located:

sp_helpdb databasename   --< Name of DB here

/* This runs on SQL 2008 R2 SP1 & higher ONLY */
DECLARE @dbID INT = 2 --< Database ID - TempDB = 2
DECLARE @fileID INT = 2 --< File ID from sp_helpDB above
select database_id, file_id, volume_mount_point AS 'Drive', logical_volume_name,
(total_bytes/1024)/1024 AS [Drive Size MB], (available_bytes/1024)/1024 as [Free MB],
100-(100*(CAST(available_bytes AS DECIMAL(38,2))/CAST(total_bytes AS DECIMAL(38,2)))) as 'Percent Full'
from sys.dm_os_volume_stats (@dbID, @fileID)

I do have admin access to the OS's so I use this mostly because it is faster than logging in via RDP, and because there is less risk of accidently clicking the wrong thing when on production servers.

This is also an IT management issue. If you are reponsible to make the databases run optimumly, you need either desktop access to the server OS, OR an OS Administrator must be assigned to work closely with you. Otherwise you're working in a Dilbertian environment.
Post #1519316
Posted Tuesday, December 3, 2013 12:33 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 2:54 PM
Points: 611, Visits: 507
Glen Berry has a good set of scripts that give you this information and other things as well. It would be a good one to look into. the scripts are located here.

It was very helpful when I was in your same situation.


.
Post #1519371
Posted Wednesday, December 4, 2013 12:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, May 2, 2014 6:15 AM
Points: 36, Visits: 102
You guys are all great. Thank you so much for the replies.
The information is valuable and greatly appreciated.
Post #1519755
Posted Monday, December 9, 2013 5:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:20 PM
Points: 6,371, Visits: 13,709
although generally frowned upon, xp_cmdshell can get most info from the OS. Just ensure your script disables it again after calling it like so


exec sp_configure 'show advanced options', '1'
reconfigure

exec sp_configure 'xp_cmdshell', '1'
reconfigure

exec xp_cmdshell 'wmic volume get capacity, "free space", name'

exec sp_configure 'xp_cmdshell', '0'
reconfigure

exec sp_configure 'show advanced options', '0'
reconfigure




-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1521033
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse