SQL: Database size management

Keep up good test effeciency often requires several test environments with various configuration and test data setups etc. Add the dimension multiple AUT release versions and you probably need more test sites than you can possible dream of.

To handle multiple test environments properly requires technical knowledge, some programming skills and a good releationship with your system administrator :-)

A very good idea is to have a test environment dashboard for survaillance of critical parameters. One of those parameters is database size. For large enterprise systems with high transaction rate, production-like copies often becomes quite large, hence you have to do some preventing actions in order to keep your disk quota. Some well targeted delete scripts in combination with shrink commands often does the trick. But how to check the current size?

Fortunatly MS SQL Server has some built-in Store procedures for this:

--Get total database size in MB including log file
sp_spaceused

database_name  database_size      unallocated space
-------------- ------------------ ------------------
MyDatabase     31877.31 MB        5382.25 MB


--Get remaining disk size for each drive on the database server
xp_fixeddrives

drive MB free
----- -----------
C     8011
D     90660
E     23057

--Get disk file name, size and drive
select filename, (SUM(size)*8)/1024 As FileSizeInMB FROM master.dbo.sysaltfiles
group by filename

filename            FileSizeInMB
-----------------------    ------------
D:\MyDatabase.mdf        5806
D:\MyDatabase_log.ldf    542

SQL: Select your table rows gently...

How to get all rows in a table?

Simple answer:

SELECT * FROM TABLE

BUT, a better practise is to

1. Do a count first if you suspect it is a large table
2. Use (nolock) in order not to lock the table during the query execution

Syntax examples for MS SQL Server:

--Count rows without locking table
SELECT COUNT(*) FROM TABLE(NOLOCK)

--Get all rows without locking table
SELECT * FROM TABLE(NOLOCK)

--Get the 1000 last entries based on primary key (assuming it is column #1)
SELECT TOP 1000 * FROM TABLE(NOLOCK) ORDER BY 1 DESC

Windows 7: Three steps back...

...since my new Laptop has "freezed" three times during the last couple of weeks. Hardware reset has been the only option since the screen has became black and the activity handler does not respond within 10 minutes (which is so long my patience holds in these situations)...

No traces whatsoever what could be the cause in the log files...Dell hardware, Windows7 or a combination?

Windows 7: One step forward...

...to faster start-up time (compared to XP)

I recently switched laptop to a Dell with Windows7 onboard. So far I think it works pretty well...I really like the preview open documents functionality and the activity bar improvments which is a small productivity booster for those of us that works with many open windows.

Although it was a bit sad to retire my old IBM T43...the most reliable and robust PC I have ever had (so far...)

SQL: MS SQL Server 2008 Management Studio - First impression

For those of you who are not familiar working with MS SQL Server product family, the program Mangagement Studio includes SQL Query and Object Explorer among a lot of other useful features for administration of databases. As a tester, knowing SQL and AUT Database objects is essential if you want to be really successful in your work in my opinion. Last week I upgraded to the 2008 version and found two new features:

1. Debugging SQL/Store Procedures
2. Intellisense writing SQL

Need I say more?

Testing Dashboard part 2

As you already may know...I like dashboards (probably too much...)

After I was being aware of the Google Chart API and I also found this JavaScript implementation, I am thinking of using the Google-o-meters showing different coverage metrics in my auto-generated real-time test report which is based on both on subjective conclusions as well as logged regression test result data...




Building dashboards is rather simple, the hard part is to collect proper test status data...

QTP: QuickTest Professional Unplugged by Tarun Lalwani

Tarun Lalwani's book QuickTest Professional Unplugged covers QTP basic as well as advanced features.

I have used QTP since 2001 (version 6.x) and if I have had this book from the start, a lot of hours and frustration had been saved.

Tarun has prior the book, contributed to the QTP community with numerous of high quality articles and forum post replies over the years. I recall an old article about Descriptive programming (DP) which made me switch from OR to DP Big-bang style (I don't regret it...).

My favorite parts in the book are the chapters regarding RegExp, IE/Word/Excel objects, HTML DOM and of course the advanced section at the end. But this book include a lot of other QTP/VBScript features as well and is well suited both for newbies (you should have some programming experience at least) and advanced users.

This is a must have for any person using QTP and/or VBScript-based automation!