Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for January, 2013

BACKUP DATABASE TO DISK = N‘NUL’

Posted by Simon Cho on 01/04/2013

http://sqlservermct.wordpress.com/2011/10/07/backup-database-to-disk-n%E2%80%98nul%E2%80%99-%E2%80%93-and-misconceptions/

Veeam can truncate log base on some option.

http://www.veeam.com/vmware-backup/help-center/v6_1/hyperv/index.html?vmware_replica_guest_processing.htm

It make backup chain broken.
When they perform truncate log, you can see 2 of the log like this.

SQL VSS is normal behavior to create SnapShot on disk.
But, Veeam Backup and replication with truncate log isn’t good option to manage database.

I recommend that do not enable truncate log on Veeam replication manager.

select top 10 * 
  from msdb.dbo.backupset
 where is_snapshot=1

--It's due to SQL VSS.
--Program_Name : "Microsoft SQL Server VSS Writer"
--ex)
--BACKUP DATABASE [DBNAME] TO VIRTUAL_DEVICE='{4C5C20F9-FE32-4FCA-BE43-FFFFFFFFFFFF}1' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024

select top 10 * 
  from msdb.dbo.backupmediafamily
 where physical_device_name='NUL'

--It's due to Veeam.
--Program_Name : "Veeam Backup and Replication"
--ex)
--BACKUP LOG [DBNAME] TO DISK = 'NUL'

Posted in Common | Tagged: , , , | Leave a Comment »

uniqueidentifier : GUID in SQL

Posted by Simon Cho on 01/02/2013

1. newsequentialid()

http://msdn.microsoft.com/en-us/library/ms189786%28v=sql.100%29.aspx

This is one of the function to generate sequence GUID to reduce fragmentation.

But, it’s not grantee uniqueness.
It’s wrapped by windows function UuidCreateSequential, http://msdn.microsoft.com/en-us/library/aa379322%28VS.85%29.aspx.

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.
After restarting Windows, the GUID can start again from a lower range, but is still globally unique.
When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function.
This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

2. uniqueidentifier sorting.

http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx

3. uniqueidentifier converting

SELECT CONVERT(uniqueidentifier, 0x000102030405060708090A0B0C0D0E0F)
UNION ALL
SELECT CONVERT(uniqueidentifier, 0x00000000000000000123456789ABCDEF)
UNION ALL
SELECT CONVERT(uniqueidentifier, 0x000000000123456789ABCDEF00000000)
UNION ALL
SELECT CONVERT(UNIQUEIDENTIFIER, '00000000-0123-4567-89AB-CDEF00000000')

Posted in Common | Tagged: , , | Leave a Comment »