Voice of the DBA

Tag: administration

  • Finding the Default Trace File

    A post more for me than for anyone else, since I’ll look for something in the default trace and I often need this snippet of code:

    select path 
 from sys.traces 
 where is_default = 1

    That returns something like this:

    path

    ————————————————————————-

    C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_92.trc

    This is the current file being used by the trace, which allows me to then look into the file for some event.

    From here, I usually start running a query like this:

    select 
   e.name as eventclass
 , t.textdata
 , t.starttime
 , t.error 
 , t.hostname
 , t.ntusername
 , t.ntdomainname
 , t.clientprocessid
 , t.applicationname
 , t.loginname
 , t.spid
 from fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\log_92.trc', default) t
  inner join sys.trace_events e 
     on t.eventclass = e.trace_event_id 
  where eventclass = xx

    In this query, I take the output from the first query and use that as the FROM file and then include an event class number in the WHERE clause. I needed this today, running a check for the latest DBCC, and so I used the class 116.

    You can get a list of event classes here: Trace Event Classes

  • Small Management

    Steve on a horse
    I’m on a horse

    This editorial was originally published on June 21, 2007. It is being republished as Steve is traveling.

    I saw this article on server management from a handheld and it’s always seen like something that’s very cool and handy, but I’ve almost never been in a situation where I really needed to manage my server while stopped on the side of the road or eating dinner. There’s times I got called away from something to go into work, but usually it’s not something I think I could have solved, or even wanted to solve, on a 320×240 screen.

    But recently while I was at TechEd, I saw some really cool new handheld devices. The capabilities of handheld devices, usually PDAs converged into phones, is really amazing. So I wanted to know:

    How many of you can, or would, manage your servers from a handheld?

    I’ve never had a phone with a keyboard, like a Blackberry. I’ve been a candy-bay Nokia guy for a long time, though my updated Startac, errr Razr, adorns my belt these days. Not by choice, but for another 6 or 7 months I’m stuck with it.

    Years ago I also had a small form factor laptop to test for my boss. It was aCasio Cassiopeia A-10, and it ran CE and limited applications (this was circa 1997). I run pcAnywhere on it and actually logged into a workstation at work and tried to manage our SQL Server and other applications. It didn’t work, at least not without frustration. Granted that was over dial-up, but it was way worse than my laptop because of the small form factor.

    Recently I saw APC giving a self-guided tour of their new racks using PDAs. You could pick up a Dell Axim, click a button to start the video or move to the next section, and watch the tour while you walked around the rack. With headphones you could get the sales pitch and pause or move forward as you wanted. And it actually worked pretty well.

    That’s not quite typing in a T-SQL command and I’m not sure how difficult it would be to try and navigate SSMS thorough a Terminal Services screen, but it gave me some hope that it might be something I’d try down the road.

    So let me know if you think it’s something you might do.

  • Checking Your Service Account with T-SQL

    Somehow this slipped by me, but there were some new DMVs added in SQL Server 2008 R2 SP1. I suspect my test machines were mostly SQL Server 2008 or SQL Server 2012, and I hadn’t been paying attention to the changes in SP1.

    You can now use T-SQL to check for services information, as well as registry information, without using extended stored procedures or any hacks of xp_cmdshell. There are two new DMVs:

    These were not present in the RTM of SQL Server 2008 R2, but after installing SP1, they appear. The KB article for SQL Server 2008 R2 SP1 includes a note that new trace templates for Profiler are included, but I did not see a note about these two DMVs.

    So much for not adding features in Service Packs.

    In any case, you can query the sys.dm_server_services for service account information. You will get the service name, the startup type, the account, and more.

    If you aren’t a Windows administrator on your SQL Server boxes, you should still be able to get information regarding the services from this DMV as long as you have VIEW SERVER STATE permission.

  • Enabling Compression? Update your baseline

    I wrote recently about capacity planning, with an item near the end about disk usage. Someone pointed out to me that compression can dramatically affect your baseline, and as you implement it, or de-implement it, you need to update your baseline.

    That’s an important point, and if you make changes, you want to do one of two things.

    • Recalculate the old numbers to be in line with the new ones
    • Make a note in your documentation somewhere

    Whether you restate the history is up to you, but I wouldn’t. I hate changing the old values, but I can see someone not being aware of changes and perhaps getting confused. If that is a problem, and you are sure you’ll keep compression around, then you can run a quick test of compressed and uncompressed backups, get a rough idea of the ratio, and then alter your old numbers.

    For me, I prefer to make a documentation note, and update everyone to be aware of the change, perhaps even adding filters to my extrapolation routines to not use the older data.