dhmacher

Example of SQL Server 2014 RTM log growth problem

May 13th, 2015
1,156
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE [master];
  2. GO
  3.  
  4. --- New database, 100 MB rows and 100 MB log file, fixed growth and max size.
  5. CREATE DATABASE LogUseTest ON
  6.     PRIMARY (FILENAME=N'X:\LogUseTest.mdf', NAME=N'LogUseTest', SIZE=102400KB, MAXSIZE=UNLIMITED, FILEGROWTH=102400KB),
  7.     FILEGROUP [MO] CONTAINS MEMORY_OPTIMIZED_DATA
  8.         DEFAULT (FILENAME='X:\LogUseTest_MO', NAME=N'LogUseTest_MO', MAXSIZE=UNLIMITED)
  9.        LOG ON (FILENAME='X:\LogUseTest.ldf', NAME=N'LogUseTest_log', SIZE=102400KB, MAXSIZE=102400KB, FILEGROWTH=102400KB);
  10. GO
  11.  
  12. --- SQL Server 2014, simple mode, read-write, no funny business.
  13. ALTER DATABASE LogUseTest SET COMPATIBILITY_LEVEL=120;
  14. ALTER DATABASE LogUseTest SET RECOVERY SIMPLE;
  15. ALTER DATABASE LogUseTest SET PAGE_VERIFY CHECKSUM;
  16. ALTER DATABASE LogUseTest SET FILESTREAM (NON_TRANSACTED_ACCESS=OFF);
  17. ALTER DATABASE LogUseTest SET TARGET_RECOVERY_TIME=0 SECONDS;
  18. ALTER DATABASE LogUseTest SET DELAYED_DURABILITY=DISABLED;
  19. ALTER DATABASE LogUseTest SET READ_WRITE;
  20. GO
  21.  
  22. USE LogUseTest;
  23. GO
  24.  
  25. CREATE TABLE dbo.table1 (
  26.     _id        int IDENTITY(1, 1) NOT NULL,
  27.     filler     char(7900) NOT NULL,
  28.     CONSTRAINT PK_table1 PRIMARY KEY CLUSTERED (_id)
  29. );
  30.  
  31. --- Add some sample data to almost fill up the database:
  32. INSERT INTO dbo.table1 (filler) SELECT REPLICATE('A', 7900);
  33. WHILE (@@ROWCOUNT<=2048)
  34.     INSERT INTO dbo.table1 (filler) SELECT filler FROM dbo.table1;
  35.  
  36. --- Returns XTP_CHECKPOINT:
  37. SELECT log_reuse_wait_desc FROM sys.databases WHERE database_id=DB_ID();
  38.  
  39. --- Data file is 66 MB, log file is 72 MB
  40. SELECT [name], type_desc, FILEPROPERTY([name], 'SpaceUsed')*8/1024 AS used_size_mb
  41. FROM sys.database_files;
  42.  
  43.  
  44.  
  45.  
  46.  
  47. --- This INSERT will fill the log file to capacity, returning error msg 9002
  48. INSERT INTO dbo.table1 (filler) SELECT filler FROM dbo.table1;
  49.  
  50. --- If the log file had been cleared by now, the INSERT would complete without errors.
  51.  
  52. --- After the Msg 9002, if you try to..
  53. CHECKPOINT;
  54. --- ... you're awarded with:
  55. --- Msg 5901, Level 16, State 1, Line 46
  56. --- One or more recovery units belonging to database 'LogUseTest' failed to generate a checkpoint.
  57. --- This is typically caused by lack of system resources such as disk or memory, or in some cases
  58. --- due to database corruption. Examine previous entries in the error log for more detailed information
  59. --- on this failure.
  60.  
  61. --- .. and:
  62.  
  63. --- Msg 9002, Level 17, State 1, Line 46
  64. --- The transaction log for database 'LogUseTest' is full due to 'CHECKPOINT'.
  65.  
  66.  
  67. --- Just issuing the CHECKPOINT statement without exploding the log file does nothing to the database.
  68. --- After some 10-15 minutes, I noticed that the log file was down to 10 MB.
  69. --- The XTP_CHECKPOINT wait remains, though.
  70. GO
  71.  
  72.  
  73.  
  74.  
  75. ---------------------------------------
  76. --- Clean-up.
  77. GO
  78. USE master;
  79. GO
  80. DROP DATABASE LogUseTest;
  81. GO
RAW Paste Data