Sep 102012
location:     AppendOnlyVerStoreMgr.cpp:776
Expression:   0
SPID:         64
Process ID:   4912
Description:  Cannot locate version record and page is not allocated. Status = 3
Msg 3624, Level 20, State 1, Line 1
A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. 
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

This was the error a Sr. DBA at my client asked me. There error appeared after reading a table from decrypted database which has been encrypted by TDE on SQL Server 2008 R2.

Continue reading »

Oct 032011

Database snapshot is a great SQL Server built-in feature serving many situations for programmers and DBAs to quickly create a read only copy of a operational database. Sometime, creating a database snapshot is a daunting work especially when you have hundreds database files. Here I give you the code to generate database snapshot automatically with few parameters regardless number of files the database has.

create procedure CreateDatabaseSnapshot
	@SnapshotName sysname,
	@SourceDatabaseName sysname = null
	set xact_abort on
	select @SourceDatabaseName = ISNULL(@SourceDatabaseName, db_name())
	declare @SQL nvarchar(max), @Proc sysname = quotename(@SourceDatabaseName)+'..sp_executesql'
	declare @t table(name sysname, physical_name sysname, i int identity(1,1))
	insert into @t
		exec @Proc N'select name, physical_name from sys.database_files where type = 0'
	select @SQL = 'create database '+ QUOTENAME(@SnapshotName) + ' on '
	select @SQL = @SQL + '(name = '+QUOTENAME(name)+', filename = '''+physical_name+'.'+@SnapshotName+'''),'
	from @t
	where i>1
	select @SQL = @SQL + '(name = '+QUOTENAME(name)+', filename = '''+physical_name+'.'+@SnapshotName+''')'
	from @t
	where i=1
	select @SQL = @SQL + ' AS SNAPSHOT OF '+QUOTENAME(@SourceDatabaseName)+' ;'


exec CreateDatabaseSnapshot 'AdventureWorks2008R2_snapshot', 'AdventureWorks2008R2'