December 2, 2013 at 8:00 am
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?
December 2, 2013 at 10:30 am
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[/url] ( https://sqlpowerdoc.codeplex.com/ )
on a regular basis.
It collects quite a bit of valuable information.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
December 3, 2013 at 10:30 am
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.
December 3, 2013 at 12:33 pm
December 4, 2013 at 12:25 pm
You guys are all great. Thank you so much for the replies.
The information is valuable and greatly appreciated.
December 9, 2013 at 5:23 am
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" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply