Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

James' SQL Footprint

Love SQL Server, Love life.

Archives: July 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')

Read more

0 comments, 1,223 reads

Posted in James' SQL Footprint on 23 July 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…

Read more

4 comments, 273 reads

Posted in James' SQL Footprint on 9 July 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      …

Read more

1 comments, 559 reads

Posted in James' SQL Footprint on 2 July 2013