Windows Utilities for the SQL Server DBA
-
Comments posted to this topic are about the content posted at temp
-
Thank you Chad!
I liked the DELETE script for files. I was looking for the code for a long time. I had also posted it in forums. But could NOT get the code.
Can anyone tell me from where can I download forfiles.exe? I don’t have win2003 environment.
-
WOW! extremely usefull contribution - TX Chad!
This will save myself hours of work every month...
What's this "backup strategy" everyone is on about? -
Downloaded forfiles.exe!
Found some changes in following command ( I am using v 1.1 forfiles.exe),
for %I in (TRN sqb bak) do FORFILES /S /D -2 /M *.%I /C "cmd /c del @file"
-- @file is case sensitive, it should be @FILE
-- Better to use properties with “-“ tag than “/” because
forfiles.exe behaves differently with –M and /M
-- If you want a include the command in a .bat file, then replace %
with %%
-
Good one
Not just for 'new SQL Server DBAs'
-
I fully agree with all of the above. cmd scripts, or batch files if you are 'old' school, still can be enormously useful.
Here's a tip I use from time to time if you cannot connect to a SQL server and you don't have TS Administrator handy.
Killing a TS Session via Query Analyser
Occasionally all the available connections will be used up. If you have Terminal Services Manager loaded you can easily close down a connection. If you don't (the case on my XP machine at home), Brian Knight pointed out a very nice trick you can use if it's a SQL box. To get all the active sessions, open Query Analyzer and run this:
master..XP_CMDSHELL 'QUERY SESSION'
-- You'll get back a resultset containing sessionname, username, id, and state. Pick the sessin ID of the connection you want to kill, then run this:
master..XP_CMDSHELL 'logoff sessionid'
If you get back a single row containing null, it worked and you should be able to get a connection.
Dave Jackson -
I am looking for a replacement for ping, ideally it will do a continuous ping and keep a running tally of the longest n number of ping times.
C:\WINDOWS>ping http://www.yahoo.com -t
Pinging http://www.yahoo-ht2.akadns.net [209.73.186.238] with 32 bytes of data:
Reply from 209.73.186.238: bytes=32 time=20ms TTL=50
Reply from 209.73.186.238: bytes=32 time=20ms TTL=50
Reply from 209.73.186.238: bytes=32 time=20ms TTL=50
Reply from 209.73.186.238: bytes=32 time=20ms TTL=50
Reply from 209.73.186.238: bytes=32 time=20ms TTL=50
Reply from 209.73.186.238: bytes=32 time=20ms TTL=50
Reply from 209.73.186.238: bytes=32 time=19ms TTL=50
...
or maybe I should just write a script with a loops?
Any thoughts...
-
I'm going to take a slightly different approach in my comment here. Yes these are great utilities. What I would like to know is how many people acting as DBAs ( whether formally or not ) actually have local administrator or even domain administrator type rights.
In my case, and I suspect in many small to mid-size companies adopting sql server, the DBA role hasn't been formalized and many of us can only execute commands such as these if logged into a query window as sysadmin -- in other words you are doing xp_cmdshell work as if you were the sql service.
In my case I'm beginning to wonder if it's worth even pursuing this role until it is recognized that DBA work typically requires some form of administrator rights. You wind up with a situation where the systems group has the rights, but no knowledge of or desire to administer sql server, and others with knowledge and desire, but no rights other than sql sysadmin.
Picture fixing an out of sync log shipping plan by doing DOS commands via xp_cmdshell.
-
This is another good tip. Thank you Dave !
-
Chad,
Great Stuff!
Thanks,
-
Randy -
I have not yet worked in a DBA role (except for a development "DBA" role where I was really more of a developer who helped with some DBA duties) where I did not have at least Local Administrator rights. Very rare to have domain admin rights (and to be quite honest I don't want them and I don't want my systems folks to want me to be a Domain Admin just like I don't want to keep BUILTIN\ADMINISTRATORS in the server where all the systems folks can "play" with my production SQL Servers).
I can't speak for everyone out there but I think it is more common than not for a DBA to be a local admin of a SQL box.
-
Mike, I agree and I'm finally getting my CIO's support for local admin rights on sql boxes -- our chief network admin still seems cool to this idea but is being overruled. My reason for bringing this up here is that the windows utilities that started this thread are quite often not useful unless you have these kinds of rights.
I'd like to end my aspect of this thread as I have a similar one now over under the sql 2005 administration forum.
thanks,
Randy
-
Due to make full set of TS commands posted above:
master..XP_CMDSHELL 'QUERY SESSION'
master..XP_CMDSHELL 'logoff sessionid'
I post additional - That will Disconnect the session without
logoff:
exec master..XP_CMDSHELL 'TSDISCON sessionid'
-
This is really great stuff....and Terminal service stuff though i used to do it from command prompt but this is very handy
great utilities.....i believe this article should be promoted every 6 months so that nobody miss it....
Cheers...Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
That was really useful
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply