Matching physical names with databases in SQL Server

Sometimes you find yourself looking for which database use a specific data file on your filesystem. This may happen when restoring/renaming a SQL database on your server and not following your naming convention.

This usually ends up with different physical names that don’t match the database names, and on a larger SQL Server that soon becomes a problem.

This script helps you find which physical files a database have.

SELECT db.name as database_name,files.physical_name,
    files.name as logical_name
FROM master.sys.master_files files
inner join master.sys.databases db on files.database_id = db.database_id

-- and you can narrow it down to just showing the
-- file you are looking for:
where files.physical_name like '%<part of the filename>%'
Posted in DBA, SQL Server, Tips | Leave a comment

Change default Collation in SQL Server 2008 R2

If you, like me, have a Test SQL Server that you use to load databases from various SQL Servers, then you may find it useful to change the Default Collation for your server every now and then.

Reason for this is that scripts may fail due to that tempdb and your restored database has different collations. To get the same Collation as your restored database, you need to rebuild SQL Server with the help of your SQL Server Installation CD.

Open up a command prompt and execute:

H:\>setup /ACTION=REBUILDDATABASE /INSTANCENAME=<instance>
/SQLSYSADMINACCOUNTS=<your account name>
/SAPWD=<your new sa password>
/SQLCOLLATION=<new collation>

Example:

H:\>setup /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER
/SQLSYSADMINACCOUNTS=jacob
/SAPWD=strongpassw0rd
/SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

Note that your old settings, SQL Logins etc will be deleted!

Posted in SQL Server | Leave a comment

SQL Pass Nordic – Day Two

Nice breakfast at Hotel Tammsvik and with an excellent view of the sunrise above the lake. All set up for a great day at SQL Pass.

Today I followed the DBA track the whole day.

Started with Mark Rasmussen waking everyone up with in depth session talking about how to get data from a SQL Data file the hard way, without having to go through a SQL Server query. Too much deep talk and I would have preferred to see his application in action instead.

Jerome talked about using Extended Events to diagnose query performance. Extended Events is SQL Trace evolutioned into SQL 2012, now with a GUI.

Silvano talked about Column Store Indexes and how that can help out with heavy queries when normal indexes just won’t do it.

Feodor had a session about database hidden disasters.

Unfortunately I had to leave early so I missed RoboDBA by Andre Kamman so I have to check with my colleagues what they learned from that session.

Any one know if the sessions that were filmed are accessible anywhere? Would be good to see the sessions that you couldn’t attend because you were in a different session room.

See you all next year, I hope!

Posted in Uncategorized | Leave a comment

SQL Pass Nordic 2011 – Day One

Yikes, found out yesterday that Microsoft Pass split me and my two colleagues up by placing us on different hotels even though I booked the whole event together for all of us.

Started off with a session with Tibor Karazi with a bunch of SQL Management studio GUI stuff. Most of the stuff was probably know already by the DBA’s present there. I enjoyed the quick look at previous versions of SQL Management Studio / Enterprise Manager.

After Tibor I went to see Kevin Klines session about stuff that would help me as a DBA. Although his Star Wars jokes didn’t land that well among the crowd  (What’s wrong with you people ;-)  ?)  the other stuff he talked about was quite interesting.

The session after Kevin was mostly a one hour boasting about HP’s new Consolidation Appliance for virtual SQL Server. So I probably should have spent my hour in one of the other session rooms.

Continued with two danish guys talking about Distributed Replay – a way in Denali to mimic real life load on a production copy, and also simulate a higher load.

Ended the day with T-SQL Bad habits to Kick with Aaron Bertrand. A lot of useful reminders on don’ts in the SQL world. Of course, most of them already known by your average DBA but very useful to hear repeated to help you to not forget.

Best today – Kevin

Worst today – Where is my hotel???

Number of SQL Guru autographs so far – 0     ;-)

 

Posted in Uncategorized | Leave a comment

Search for text in Stored Procedures

Very handy when you are asked to create a trace for a stored procedures that update text in a specific table. Then you want a way to search fast within all sp’s in your database.

SELECT distinct sysobjects.name
FROM sysobjects (nolock)
INNER JOIN syscomments (NOLOCK) on sysobjects.id = syscomments.id
AND sysobjects.type = 'P'
AND syscomments.text LIKE '%<text to search for>%'
ORDER BY sysobjects.name

Just replace <text to search for> with your search string and you are good to go!
Note that % around your word makes the query able to match more than you bargained for.

Posted in DBA, SQL Server | Leave a comment

VCenter and Total DISK IO

I’ve talked before about the vast amount of useful information in the VMWare VCenter database and one thing I’ve been looking for is the total Disk IO for a given time for all host servers.

In VCenter you can see individual, historical graphs for a single Host server, but what if you want to see the accumulated Disk IO for all your host servers?

You query one of the four VPXV_HIST_STAT_ views!

select left(convert( varchar(16),sample_time,120),16) as sample_time,
 sum(STAT_VALUE) as stat_value
 from VIM_VCDB..VPXV_HIST_STAT_DAILY hsd (nolock)
INNER JOIN VIM_VCDB..VPXV_ENTITY_MOID em (nolock) on hsd.ENTITY = em.MOID
INNER JOIN VIM_VCDB..VPXV_ENTITY e (nolock) on em.ENTITYID = e.id
where STAT_GROUP = 'disk' and STAT_NAME = 'usage'
and e.ENTITY_TYPE = 'HOST'
group by left(convert( varchar(16),sample_time,120),16)
order by sample_time asc

You can replace VPXV_HIST_STAT_DAILY with either
VPXV_HIST_STAT_WEEKLY
VPXV_HIST_STAT_MONTHLY
VPXV_HIST_STAT_YEARLY

to get more historical data.

The column stat_value shows disk usage IO in Kilobytes. And STAT_YEARLY is really useful to see the growth in disk IO over time.

Just paste the data into excel or your favourite graph tool and you’ll get a nice graph!

This query is useful if you have all your host servers connected to a SAN and want to see the Total IO for your servers.

Posted in SAN, Tips, VMWare | Leave a comment

Connecting to SQL Server with Windows Authentication to a different domain

How do you solve the problem of connecting from say your office computer in the office domain to your production environment when there is no trust between the domains?
This scenario is quite common in larger companies. The reason is usually to add an extra layer of security between the two networks by keeping them separated.

So if you still want to connect, how do you go about doing this?

You could always bring out the trusty SQL Server authentication with local sql accounts on the production network.The problem with this is that you have to create local user accounts on all servers you want to connect to.

Or you could start your SQL Server Management Studio with your production network account  and connect with Windows Authentication mode. This is where the Windows command runas comes in handy!

RUNAS.EXE

First locate the path to your SQL Server Management Studio by right clicking on your icon. You’ll probably get a path that looks like this:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe

Open up a command prompt and type in

runas /netonly /user:productiondomain\productionusername
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

You will now start SSMS under the production user account and can now connect to your production servers through the Windows Authentication mode, as if you would have done when connected with RDP (Remote Desktop) to your production server.

Tip

You can create a shortcut that will start SSMS with the runas command to speed up things!

 

Posted in DBA, SQL Server, Tips | Leave a comment

VMWare VCenter data

image

VMWare VCenter platform is a goldmine of useful data if you want to know whats going on with your virtual servers.

Statistical data is stored in an SQL Server database and you can either query views or the tables directly.
You get views for daily, weekly, monthly or yearly statics of your physical and virtual machines and you are able to query cpu, disk and a lot of other measurements.

VPXV_HIST_STAT_DAILY
VPXV_HIST_STAT_WEEKLY
VPXV_HIST_STAT_MONTHLY
VPXV_HIST_STAT_YEARLY

We have a couple of TV screens in our office so I created a Perl script that grabs real time data from this database and displays it.

You now get an overview on which server is doing what and how much CPU, DISK IO, Network traffic it generates without having to open up VCenter and connect to each and every host.

The solution is built using Perl with GD::Graph, a free module for creating graphs.

If you are a DBA or an administrator, I highly recommend having a look at the VCenter database. Some more information about the various tables can be found here:

http://ericsiebert.com/Vmware_Tips.html#VC6
http://vmprofessional.com/material/VcER_Diagram.pdf
http://www.vmware.com/pdf/vc_dbviews_2x.pdf

Posted in DBA, Perl, VMWare | Leave a comment

I use WordPress for this blog

I first tried WordPress for blogging a couple of years ago and liked it so much that I currently run all my blogs on WordPress. I like it so much I even run my friends blogs on WordPress!

This blog is a setup of the Open Source version of WordPress that you can install on your own server. Mine is a virtual linux server.

I modified the original design template that came with to better suit the style of Verity.
The design was developed by Neobrand.

The only plugin i run is Twitter Tools by Crowd Favorite to be able to show my Tweets on the front page.

Upgrading is a walk in the park. Since I run it on Linux I’ve created a shell script that downloads the latest version, backups databases & files and finally upgrades everything for me. And it does this for all my blogs.

I highly recommend WordPress if you want to start blogging!

Posted in Linux, Tips | Leave a comment

Selecting data from two or more tables?

Say you have one production database and an archive database for your application. A quite common setup.

If you want to grab data from both tables in one single query, there are some ways to do this.

  • you could create a view (good for more permanent use)
  • you could create a temporary table, inserting data from both tables (good if you want to manipulate the data in some special way before presenting it)
  • or you could do it with a quick UNION ALL statement (quick and easy)

Example:

select myColumn from Production..TableA (nolock) 
UNION ALL
select myColumn from Archive..TableB (nolock)

You can of course also add an order by clause to the query.

Posted in SQL Server, Tips | Leave a comment