This week, people of Indian origin all around the globe are celebrating the festival of Diwali (http://en.wikipedia.org/wiki/Diwali) – the festival of lights that marks the triumph of good over evil.
I pray that all the readers have a Very Happy Diwali and also have a Prosperous New Year ahead!
Until we meet next time,
Be courteous. Drive responsibly.
Microsoft SQL Server comes with a whole range of system databases, which I visited in my post – How to programmatically identify system and user databases on a SQL Server instance. System databases are special, and there some very specific and peculiar characteristics of each (documentation for which can be found from Books On Line here).
One such system database is the “model” database. As the name suggests, user databases created on the particular SQL Server instance are copies of the model database.
The TempDB on the other hand, is a special system database. It’s special because every time the SQL Server is restarted, the TempDB is re-created, and we receive a clean copy of the TempDB. This got me thinking:
The only way to answer these questions, is to run a small test.
WARNING: The tests demonstrated in this post involve changing your default SQL Server installation. Please check with your SQL Server Administrator before performing these steps in your development and quality assurance environments. These tests should not be performed on a production database.
On my test system, the model database is a clean one – no modifications have been made after SQL Server was installed. On such a clean model database, let’s create a table, and fill it with some test data.
USE model GO CREATE TABLE DefaultTableTest ( DataId INT IDENTITY(1,1), RandomId INT DEFAULT (RAND()*1000), DataName VARCHAR(10) DEFAULT 'DEFAULT' ) GO /****************************************** Insert some test data into the table ******************************************/ USE model GO INSERT INTO DefaultTableTest DEFAULT VALUES GO 600000
Now, let’s restart the SQL Server service. Take care to restart the service using the steps mentioned in my post here. Do not use the Services.msc console to restart Microsoft SQL Server.
Once the SQL Server restart is complete, it’s time to check the TempDB. First of all, let’s navigate to the physical drive where TempDB is located. We all know that typically, TempDB is 8MB in size upon SQL Server restart by default.
Because TempDB is larger than expected, it definitely has something more than usual.
Next, let’s refresh the Object Explorer pane in SSMS. We see that under TempDB, we have the DefaultTableTest table created for us. Therefore, let’s attempt to SELECT from the table.
TempDB is a copy of the model database – just as is the case with any other user database. No other system database is a copy of the model database – which adds to the reasons why TempDB is special.
There is also a lesson to be learnt from this. Whenever a change is made to the “model” database, one needs to be very careful about the change – it will directly impact one of the most used databases – the TempDB.
I hope you liked today’s post. Leave your feedback before you go.
Until we meet next time,
Be courteous. Drive responsibly.
Recently, the IT department at the office undertook a hardware technology refresh cycle. To facilitate the IT department, we were required to send out a list of all installed programs & supporting components so that they could provide us with new computer systems up-to-date with the right versions and patches.
That was the time when I discovered something interesting. I found a quick way to get the following information about SQL Server client tools right from SSMS:
I simply browsed out to Help –> About:
Once there, I noticed that all the information is now available on screen. So, I clicked on “Copy Info”, pasted it to Excel and sent it over to my IT department. They were surprised as to how fast I got this information for them. Well, it’s our little secret now, isn’t it?
Until we meet next time,
Be courteous. Drive responsibly.
Today I move away from SQL Server for one day, and instead share with you a productivity enhancement that you can use in your day-to-day work.
Some time ago, we were working on fixing a defect, but were unable to reproduce it. The QA team who had found the defect was sitting at a different building on the other side of the city, so moving over for a quick stop-over was not quite feasible. It turned out that while the end result was the same, the two teams were using slightly different steps to reach to a particular window. That day, we wished to have some sort of a built-in (i.e. “free”) recorder that the QA can use to record the steps they follow and then share it with us so that we could follow exactly the same steps and attempt to reproduce the defect.
Well, Windows 7 does come with such a feature/utility built-in. It’s called the Problem Steps Recorder – PSR.
To get an overview of the PSR, you can visit the Microsoft site: http://windows.microsoft.com/en-US/windows7/How-do-I-use-Problem-Steps-Recorder
There is also a very thorough, quick demo (just 3 minutes long) on TechNet at: http://technet.microsoft.com/en-us/windows/dd320286
The immediate uses that I see of the Problem Steps Recorder (PSR) are:
In addition to the PSR, there is often the need to snap a portion of the desktop screen and send it over in an E-mail. This can very easily be done using the Snipping Tool (available since Windows Vista), a demo for which can be found here – http://windows.microsoft.com/en-US/windows7/products/features/snipping-tool
So, these are the two productivity enhancements that my team & I use at the workplace. I trust that they would help you exchange information across multiple teams at your workplace as well.
Until we meet next time,
Be courteous. Drive responsibly.
I am sure that when preparing installers for your products, you would have come across a requirement to filter out the system databases deployed on a SQL Server instance when presenting a list of available SQL Servers to the user.
System databases, per Books On Line (http://msdn.microsoft.com/en-us/library/ms178028.aspx) are the following:
In addition, the distribution database in a replication topology is also a system database (It appears within the “System Database” node within object explorer in SSMS).
It appears that there is always a lot of confusion around the question – “How to identify system databases programmatically?”. The unfortunate answer to this question is simply that Microsoft SQL Server does not have any documented method of identifying system databases besides filtering on the database name.
I use the following query (even in production code) and it has served me well. I therefore share it with you today:
--*********************************************************
--WARNING:
--These queries are provided "as-is" and without warranty
--The author, BeyondRelational.com and Microsoft are not
--responsible for damage caused by misuse of this query
--*********************************************************
--Provides a list of system databases
SELECT sdb.database_id,
sdb.name
FROM sys.databases sdb
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$')
THEN 1
ELSE sdb.is_distributor
END) AS BIT) = 1
--Provides a list of user databases
select sdb.database_id,
sdb.name
from sys.databases sdb
WHERE CAST((CASE WHEN SUBSTRING(sdb.name,0,14) IN ('master','msdb','model','tempdb','ReportServer$')
THEN 1
ELSE sdb.is_distributor
END) AS BIT) = 0
Note that this list does not contain the Resource database. That’s simply because it is not directly accessible as a separate database! The resource database, as you know manifests itself as the “sys” schema in any database and therefore cannot be queried outside of this environment.
I hope that the above mentioned queries are helpful to you.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 