Michael Zilberstein

Name resolution in SQL Server

Michael Zilberstein — Thu, 28 Aug 2008 10:57:00 GMT

This post is inspired by recent post by Mladen Prajdić. Mladen had found out (as many did before him - hanging around in blogs and communities, I see questions about this issue from time to time) that name resolution works for temporary tables differs...(read more)

Partitioning by GUID

Michael Zilberstein — Wed, 11 Jun 2008 00:40:14 GMT

Recently I had to use GUID (Global Unique Identifier) as primary key in order to ensure cross-server uniqueness. In many cases surrogate key (some sort of concatenation between server id and intra-server identity) would give a better performance but in...(read more)

Scalability features I would like to have in SQL Server

Michael Zilberstein — Wed, 23 Apr 2008 16:03:26 GMT

1. Active-Active cluster. Like RAC in Oracle. Maximum that commodity server can provide by now is 4 quad-core CPU machine. If application requires more, we can split the database (which is not always possible; and even when it is - such a solution often...(read more)

How do we know that Microsoft conducts performance tests?

Michael Zilberstein — Mon, 24 Mar 2008 07:31:00 GMT

Funny comment in undocumented system stored procedure - and undocumented syntax of DBCC SHOW_STATISTICS. ...(read more)

How to find real database creation date

Michael Zilberstein — Tue, 18 Mar 2008 05:56:30 GMT

Restoring database from backup or moving it to another server resets all the creation_date fields (in sys.databases , msdb..backupset etc'). So is there any way to find out the original creation date? Looks like there is one. It is stored in the boot...(read more)

Have your database ever been backed up?

Michael Zilberstein — Sat, 01 Mar 2008 14:15:00 GMT

Recently I've been asked by one of my clients to write a simple script that would find out whether particular database had ever been backed up (full backup). The reason for a requirement was semi-automatic application which would enable end users to manage backup activity including manually backup transaction log - not via Management Studio but via much more friendly UI designed for non-DBAs. As you know, for database running in Full Recovery Mode, transaction log can't be backed up before full backup is done at least for the first time. OK, I thought. Let's just check whether full backup exists for the database:

IF EXISTS( SELECT 1 FROM msdb..backupset WHERE database_name = 'MyDB' AND [type] = 'D' ) ...

Here comes the first catch. Imagine following scenario:

1. Create database MyDB.

2. Create full backup of 'MyDB'.

3. Drop database 'MyDB' (without deleting backup history).

4. Create new database with the name 'MyDB' or restore from backup.

What do we have now? Query will return true while actually backup we have belongs to another non-existing database. Try to backup transaction log of the new DB - you'll receive error.

First try failed - let's give another one. Both sys.databases and msdb..backupset contain creation date of the database. In sys.databases column name is create_date, in msdb..backupset it is database_creation_date. So let's just compare database_creation_date from the latest full backup row with database's create_date. If they're equal - we have full backup. Otherwise backup belongs to the previous incarnation of our database while full backup of currently existing db had never been taken. Sure, it should also work for the normal scenario - without deleted and restored databases on the way. So first let's check whether our theory works after second step of the scenario described above. But what happened? How comes, our database creation times are different?

Here comes second catch. For some strange reason, database_creation_date column in msdb..backupset doesn't contain milliseconds! Minutes, seconds - everything is there but for milliseconds its always 000. While create_date in sys.databases contains full date including milliseconds. Don't know who and for what reason cut milliseconds from the backup history table, but that's the way it works now.

To make long story short - it was the last barrier. After removing milliseconds part from database creation date in sys.databases, everything began to work. Here is the full script that will tell you whether some particular database had ever been backed up (full backup).

DECLARE @DBName SYSNAME
SET @DBName = 'YourDatabase'

DECLARE @LatestBackupDate DATETIME, @BackedUpDBCreationDate DATETIME
DECLARE @DBCreationDateNoMilliseconds DATETIME

-- In msdb..backupset table, database_creation_date column is in datatime format
-- but without milliseconds, so we remove milliseconds from database creation date.
SELECT @DBCreationDateNoMilliseconds =
    DATEADD(millisecond, -DATEPART(millisecond, create_date), create_date)
FROM sys.databases
WHERE [name] = @DBName
IF @DBCreationDateNoMilliseconds IS NULL
   BEGIN
       PRINT 'Database ' + @DBName + ' doesn' + CHAR(39) + 't exist on the server'
       RETURN
   END

SELECT
   @LatestBackupDate = backup_finish_date,
   @BackedUpDBCreationDate = database_creation_date
FROM msdb..backupset
WHERE
   backup_set_id = (
       SELECT MAX(backup_set_id)
       FROM msdb..backupset
       WHERE database_name = @DBName AND
       [type] = 'D' )

IF @LatestBackupDate IS NULL
   BEGIN
       PRINT 'Full backup had never been taken for database ' + @DBName
       RETURN
   END

IF @BackedUpDBCreationDate = @DBCreationDateNoMilliseconds
   PRINT 'Full backup exists for database ' + @DBName + '; Transaction log backups can be taken'
ELSE
   PRINT 'Full backup exists for the current version of ' + @DBName + ' database had never been taken. Existing backup refers to non-existing database with the same name'

1001st way to find SQL Server service start time

Michael Zilberstein — Sat, 01 Mar 2008 13:43:00 GMT

There're a lot of ways to discover when SQL Server service had been last restarted. That's not a pure curiosity issue since almost all the DMVs and performance counters reset at service restart. So in order to calculate averages (IOs per minute, Waits per minute) etc, one needs to know the starting point. There're a lot of ways to find it. One is to take a look in Error Log (if log hadn't been deleted by numerious DBCC ERRORLOG commands). Another way is to look at sys.dm_exec_requests for start_time or in master..sysprocesses for login_time of system processes like LAZY WRITER. Recently I've found one more way: look in sys.databases for create_date of tempdb database. Since tempdb is re-created from scratch every time SQL Server restarts, it works. BOL reference about create_date column also says:
-- Date the database was created or renamed. For tempdb, this value changes every time the server restarts.

P.S. Results you receive trying different ways can vary - half minute here or there - because service restart doesn't happen in a second. But when you need to measure time since startup, 30 seconds usually won't be serious factor.