Posted: 19 Aug 2013 04:12 AM PDT

Hi,I tried to repro a scenario for log-clearing the log in SIMPLE recovery model. I know if there is an ACTIVE TXN, and if we issue a CHECKPOINT in SIMPLE recovery model, nothing happens i.e. 0 VLFs gets cleared in my below scenario. But i commited my explicit transaction, still log_reuse_wait_Desc is showing ACTIVE TRANSACTION and log is not getting cleared. I repro'd the same scenario by inserting records using a WHILE-Loop and it works as expected but when I use GO <n-times> to insert records, the txn is still open. Can anyone figure-out why log is not getting cleared or do i missing something over here ???My SQL Server details=============Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64) Below is my repro steps. Pl don't go with the output values as I have done multiple times but no use. Log doesn't get cleared. Just follow my steps/cmds and you should be able to reproduce the same.USE [master]GOCREATE DATABASE [db1] ON PRIMARY( NAME = N'db1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 1MB ) LOG ON( NAME = N'db1_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\db1_log.LDF',SIZE = 3MB , ---4 VLFs since size < 64mbFILEGROWTH = 1MB)GOALTER DATABASE db1 SET RECOVERY SIMPLE;GODBCC LOGINFO(db1)GO/*FileId FileSize StartOffset FSeqNo Status Parity CreateLSN2 720896 8192 22 2 64 02 720896 729088 0 0 0 02 720896 1449984 0 0 0 02 974848 2170880 0 0 0 0*/USE db1GOCREATE TABLE t1(C1 INT IDENTITY,C2 CHAR (8000) DEFAULT 'a' --- char datatype takes all the size even 1 char is inserted. use DATALENGTH() Function);GO-- i opened a new session/window/spid in the ssms and started the below explicit transactionBEGIN TRAN INSERT INTO t1 DEFAULT VALUES GO 600-- I came back to original window where i created the databasedbcc loginfo(db1);select recovery_model_desc,log_reuse_wait_descfrom sys.databaseswhere name = 'db1' ;/*recovery_model_desc log_reuse_wait_descSIMPLE ACTIVE_TRANSACTION */dbcc opentran(db1);/*Transaction information for database 'db1'.Oldest active transaction: SPID (server process ID): 57 UID (user ID) : -1 Name : user_transaction LSN : (22:63:2) Start time : Aug 19 2013 9:54:32:103PM SID : 0x0105000000000005150000007a6fe3176386abd260a96a96e8030000DBCC execution completed. If DBCC printed error messages, contact your system administrator.*/-- I confirmed from log there is 1 Active txn Select [Current LSN],[Operation], [Num Transactions] from fn_dblog(null,null) where Operation in ('LOP_XACT_CKPT','LOP_XACT_CKPT','LOP_END_CKPT') /*Current LSN Operation Num Transactions 00000016:0000002e:0001 LOP_XACT_CKPT 1 00000016:0000002e:0002 LOP_END_CKPT NULL */-- i went to new window and committed the explcit transaction COMMIT; -- Came back to old windowCHECKPOINT; --to clear the logdbcc loginfo(db1);-- All are active (Status=2). none of the VLFs have been made inactiveselect recovery_model_desc,log_reuse_wait_descfrom sys.databaseswhere name = 'db1';/*SIMPLE ACTIVE_TRANSACTION*/dbcc opentran(db1);/*Transaction information for database 'db1'.Oldest active transaction: SPID (server process ID): 57 UID (user ID) : -1 Name : user_transaction LSN : (22:63:2) Start time : Aug 19 2013 9:54:32:103PM*/checkpoint;goSelect [Current LSN],[Operation], [Num Transactions] from fn_dblog(null,null) where Operation in ('LOP_XACT_CKPT','LOP_XACT_CKPT','LOP_END_CKPT')go/*Current LSN Operation Num Transactions00000022:000001af:0001 LOP_XACT_CKPT 100000022:000001af:0002 LOP_END_CKPT NULL00000022:000001b1:0001 LOP_XACT_CKPT 100000022:000001b1:0002 LOP_END_CKPT NULL*/-- Though i committed the Explicit open txn, why is it still Active and not allowing VLF's to clear.Can anyone pl let me know what is that I am missing here?Thanks in advance.

SAN Migration - DataFiles movement to new drives

Posted: 18 Aug 2013 03:56 PM PDT

Hi,We have got new space (SAN) allocated recently by storage folks to one of our database server to resolve I/O issues with some specific drives (D: & E:). Inorder to make use of the new drives, i am trying to move all the files (user DB + SYSTEM DB DataFiles) that are present on the current drives (bad drives) to the new one's (drives in good condition). Before making the technical plan, I just want to make sure I am not missing anything. I am putting all the steps that I am going to include in the plan, Please help me out if I am missing anything.[b]Environment:1) Windows 2003 server.2) Microsoft SQL Server 2008 (SP3) - 10.0.5768.0 (X64) Nov 4 2011 11:32:40 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 <X64> (Build 3790: Service Pack 2)3) Clustered (2 Node)[/b]4) Drive D: has Data files & E: has TEMPDB files.Steps:-====================================================1) Bring down all the services on the database server.2) Enable the new drives that are assigned, and assume we are naming them as [b]X: (Old D:) & Y (Old E:)[/b].3) Start copying all the files to the new drives from the respective drives.4) Once Copy is successful, try renaming the old drives to [b]D: -> I: and E: -> J:[/b].5) Once you rename the old drives, start renaming the new drives [b]X: -> D: and Y: -> E:[/b].6) Once renaming is done, try starting all the services on the database.7) Make sure all the DB's are up and is able to accept requests/connections.8) Does it require any Maintenance tasks (ShrinkFiles, UpdateStats etc) post moving all required files? Please advice?I am aware there is one more option (Attach/Dettach) to achieve this, but since we are moving system datafiles, it is ruledout.Please advice if there are any other ways (easy) of doing it. Thanks,Nagarjun.

Benefits of DBCC Check DB and performance

Posted: 19 Aug 2013 12:16 AM PDT

I have some confusion here with DBCC , as i know that DBCC will take a snapshot of the DB to perform a Check DB. Am i correct? If yes, then is there any benefit in using a snapshot in DBCC and how will DBCC perform a check DB on system DBs... Can any one please clarify me....Thanks in advance !!!!

Sql samples, sql tips

Monday, August 19, 2013

[MS SQL Server] Transaction not getting cleared in SIMPLE recovery model ?

[MS SQL Server] Transaction not getting cleared in SIMPLE recovery model ?

No comments:

Post a Comment

Search This Blog