Tuesday, July 23, 2013

Gap issue in SQL Server 2012 IDENTITY column

Found a gap issue when using IDENTITY column in SQL Server 2012, every time I restarted sql server service, the current identity number will jump to 1000. here is the repro script

CREATE TABLE MYTEST (ID INT IDENTITY(1,1),
CREATE_DATE datetime);

GO

INSERT INTO MYTEST(CREATE_DATE)
VALUES (GETDATE())
GO 10

DBCC CHECKIDENT ('MYTEST')
GO

the output will be like
Checking identity information: current identity value '10', current column value '10'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

then restart sql service, and run

DBCC CHECKIDENT ('MYTEST')
GO

this time we got
Checking identity information: current identity value '1001', current column value '10'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.


by searching Google, there is a product connection about this issue
https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity#details

There are 2 workaround provided.
1. Use trace flag 272






















in my testing, the trace flag 272 works good.

2. Use a sequence generator with the NO CACHE setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx).This will cause a log record to be generated for each generated sequence value. Note that the performance of sequence value generation may be impacted by using NO CACHE.

Example:
CREATE SEQUENCE s1 AS INT START WITH 1 NO CACHE;
CREATE TABLE t1 (Id INT PRIMARY KEY DEFAULT NEXT VALUE FOR s1, col INT NOT NULL);

Tuesday, July 9, 2013

Find out orphan database file on disk

Sometimes you detach databases,  or you delete a database which is in restoring status,  the data and log file will not be removed from local disk. if you forget the files,  It might cause issues
1. The files waste disk space
2. If you restore the database back to the original place,  the restore will be failed  because of the existing files.

so here is a powershell script which can find out orphan database files on disk. the script can
1. Get the database default file path from registry
2. Search all mdf, ndf and ldf files
3. Check the sql server system table to see if all files are attached.
4. Print out the file name if the file is not attached to any database.

you can download the script from here
https://docs.google.com/file/d/0B4Xde9z-OME1WWozY0hyWVpnTkU/edit?usp=sharing

and run the script as below:







here, 2 orphan db files(my.mdf and my.ldf) are found in instance sqlexpress.





Tuesday, July 2, 2013

trace flag for backup and restore operation

1. 3004
show the internal backup/restore operation for every step.
ex.
dbcc traceon(3004,3605,-1)
GO

3605 means output the message to errorlog. you can use 3604 to direct the output to client, but it doesn't have the time info which 3605 has. The output looks like below:
LogDate                                 ProcessInfo Text
2013-07-02 16:48:52.280 spid53 RestoreDatabase: Database AdventureWorks
2013-07-02 16:48:52.280 spid53 Opening backup set
2013-07-02 16:48:52.310 spid53 SetTargetRestoreAge: 0
2013-07-02 16:48:52.310 spid53 Restore: Configuration section loaded
2013-07-02 16:48:52.310 spid53 Restore: Backup set is open
2013-07-02 16:48:52.310 spid53 Restore: Planning begins
2013-07-02 16:48:52.320 spid53 Restore: Planning complete
2013-07-02 16:48:52.320 spid53 Restore: BeginRestore (offline) on AdventureWorks
2013-07-02 16:48:52.320 spid53 Restore: Attached database AdventureWorks as DBID=15
2013-07-02 16:48:52.320 spid53 Restore: PreparingContainers
2013-07-02 16:48:52.340 spid53 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf from page 1 to 64000 (0x2000 to 0x1f400000)
2013-07-02 16:48:52.350 spid53 Restore: Containers are ready
2013-07-02 16:48:52.400 spid53 Restore: Restoring backup set
2013-07-02 16:48:52.400 spid53 Restore: Transferring data to AdventureWorks
2013-07-02 16:49:01.640 spid53 Restore: Waiting for log zero on AdventureWorks
2013-07-02 16:49:11.160 spid53 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf
2013-07-02 16:49:11.210 spid53 Restore: LogZero complete
2013-07-02 16:49:11.210 spid53 SetTargetRestoreAge: 0
2013-07-02 16:49:11.320 spid53 FileHandleCache: 0 files opened. CacheSize: 12
2013-07-02 16:49:11.320 spid53 Restore: Data transfer complete on AdventureWorks
2013-07-02 16:49:11.330 spid53 Restore: Backup set restored
2013-07-02 16:49:11.410 spid53 Starting up database 'AdventureWorks'.
2013-07-02 16:49:11.410 spid53 The database 'AdventureWorks' is marked RESTORING and is in a state that does not allow recovery to be run.
2013-07-02 16:49:11.420 spid53 Restore-Redo begins on database AdventureWorks
2013-07-02 16:49:13.390 spid53 RunOfflineRedo logIter.Init(): FirstLsn(PruId: 0): 0x82:0x155:0x45
2013-07-02 16:49:13.390 spid53 RunOfflineRedo logIter.Init(): LastLsn(PruId: 0): 0x82:0x173:0x1
2013-07-02 16:49:13.400 spid53 OfflineRollforward: StopLsn/LastLsn(PruId: 0): 0x82:0x173:0x1
2013-07-02 16:49:13.410 spid53 Rollforward complete on database AdventureWorks
2013-07-02 16:49:13.420 spid53 Restore: Done with fixups
2013-07-02 16:49:13.420 spid53 Restore: Transitioning database to ONLINE
2013-07-02 16:49:13.430 spid53 Restore: Restarting database for ONLINE
2013-07-02 16:49:13.530 spid53 Starting up database 'AdventureWorks'.
2013-07-02 16:49:13.970 spid53 FixupLogTail(progress) zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf from 0x23ee600 to 0x23f0000.
2013-07-02 16:49:13.970 spid53 Zeroing C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf from page 4600 to 4688 (0x23f0000 to 0x24a0000)
2013-07-02 16:49:13.980 spid53 Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\AdventureWorks2012_log.ldf
2013-07-02 16:49:14.080 spid53 PostRestoreContainerFixups: fixups complete
2013-07-02 16:49:14.130 spid53 PostRestoreReplicationFixup for AdventureWorks starts
2013-07-02 16:49:14.810 spid53 PostRestoreReplicationFixup for AdventureWorks complete
2013-07-02 16:49:14.870 spid53 Restore: Database is restarted
2013-07-02 16:49:14.870 Backup Restore is complete on database 'AdventureWorks'.  The database is now available.
2013-07-02 16:49:14.880 spid53 Resuming any halted fulltext crawls
2013-07-02 16:49:15.060 spid53 Restore: Writing history records
2013-07-02 16:49:15.060 Backup Database was restored: Database: AdventureWorks, creation date(time): 2013/07/02(16:33:47), first LSN: 130:341:69, last LSN: 130:371:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\temp\AdventureWorks.bak'}). Informational message. No user action required.
2013-07-02 16:49:15.060 spid53 Writing backup history records
2013-07-02 16:49:15.080 spid53 Restore: Done with MSDB maintenance
2013-07-02 16:49:15.080 spid53 RestoreDatabase: Finished

The error log shows the it took 9 seconds to restore data and 19 seconds to zero log file. since I have enabled "Instant file initialization", there is zeroing file step for .mdf file. with trace flag 3004 you can see on which step it takes longest time.


2. 3213
show the buffer and maxtransfersize info.
ex.
dbcc traceon(3213,3605,-1)
GO

in errorlog
2013-07-02 17:24:52.070 spid56 Backup/Restore buffer configuration parameters
2013-07-02 17:24:52.070 spid56 Memory limit: 1009MB
2013-07-02 17:24:52.070 spid56 BufferCount:                6
2013-07-02 17:24:52.070 spid56 Sets Of Buffers:            2
2013-07-02 17:24:52.070 spid56 MaxTransferSize:            1024 KB
2013-07-02 17:24:52.070 spid56 Min MaxTransferSize:        64 KB
2013-07-02 17:24:52.070 spid56 Total buffer space:         12 MB
2013-07-02 17:24:52.070 spid56 Tabular data device count:  1
2013-07-02 17:24:52.070 spid56 Fulltext data device count: 0
2013-07-02 17:24:52.070 spid56 Filestream device count:    0
2013-07-02 17:24:52.070 spid56 TXF device count:           0
2013-07-02 17:24:52.070 spid56 Filesystem i/o alignment:   512
2013-07-02 17:24:52.070 spid56 Media Buffer count:            6
2013-07-02 17:24:52.070 spid56 Media Buffer size:          1024KB
2013-07-02 17:24:52.070 spid56 Encode Buffer count:           6
2013-07-02 17:25:00.880 spid56 Starting up database 'sqlnexus'.
2013-07-02 17:25:00.910 spid56 The database 'sqlnexus' is marked RESTORING and is in a state that does not allow recovery to be run.

here we see 6 buffercount, maxtransfersize is 1024.(for buffercount calculation, please refer to http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspx). before you optimize the buffercount and maxtransfersize parameter of backup and restore command, using trace flag 3213 to check the current default value.

3. other trace flag
    a) 3023
        If Trace Flag 3023 is turned on, the CHECKSUM option is automatically enabled for the BACKUP command. You can turn on Trace Flag 3023 to make sure that all backups use the backup CHECKSUM option. If you do this, you do not have to rewrite all the existing backup scripts
       http://support.microsoft.com/kb/2656988

    b) 3226
        No log backup info written into SQL errorlog file

    c) 3001
        No log backup info saved in msdb tables.

    d) 1806
         Turn off "Instant file initialization". maybe only useful when testing.

    e) 3014 and 3216
        log more detail info , ex.
dbcc traceon(3213,3004,3216,3605,-1)
GO