Subscribe to this blog
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')
0 comments, 883 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…
1. The files waste disk space
2. If you restore the database back to the…
4 comments, 211 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 …
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 …
1 comments, 449 reads
Posted in James' SQL Footprint on 2 July 2013