One of the great things about the blog engine we use is that it shows all the search engine queries that led to someone clicking through to the site. I’ve been looking through the logs to see what kind of problems people are having that end up here. In this occasional series, I’m going to pick a search engine query and blog about it – hopefully helping out people who have the problem in future.<\/p>\n
First up is running out of transaction log space. This happens when the transaction log fills up to the point where it has to grow but either autogrow is not turned on or the volume on which the transaction log is placed has no more space for the file to grow. So what causes the transaction log to fill up in the first place? It could be a number of different things. The two most common ones I’ve seen are:<\/p>\n
Let’s have a look at both of these in SQL Server 2005 and example solutions.<\/p>\n
The log backup case…<\/strong><\/p>\n First thing to do is create a database with a very small transaction log that we can easily fill up.<\/p>\n Notice that I’ve specifically turned auto-growth of the log file off by setting the file growth to zero. Now let’s set the database into full recovery mode and take a full database backup.<\/p>\n And then create a simple table and fill up the transaction log.<\/p>\n And we get the following error:<\/p>\n This has no helpful information at all. So, looking at the 2005 message, it’s telling us to look in the sys.databases<\/em> table for more info. Let’s do that:<\/p>\n And the result is:<\/p>\n That’s pretty clear – take a log backup! Here are the various values this can take (at the time of writing), from the Books Online entry for sys.databases<\/em>:<\/p>\n Their meanings are explained in the Books Online entry Factors That Can Delay Log Truncation<\/a>. Note that the value returned is what was stopping log truncation when it was last attempted.<\/p>\n Now if we take a log backup, the log can be truncated.<\/p>\n And checking the state in sys.databases<\/em> again returns:<\/p>\n Simple – but it’s amazing how many times I see this problem on newsgroups and forums.<\/p>\n The long-running transaction case…<\/strong><\/p>\n For this test, assume I’ve run the T-SQL above to create the database, put it into full recovery mode, take the first full backup, and then create the table. Now in a second connection, I’ll create a long-running transaction:<\/p>\n Note that the transaction hasn’t been committed or rolled back. In the original connection, I’ll execute the loop to fill up the log again. When we check sys.databases we get back:<\/p>\n So we take a log backup and check the state again. This time we get:<\/p>\n We need to use the\u00a0DBCC OPENTRAN<\/em> command to find out what the long running transaction is:<\/p>\n And we get back:<\/p>\n From this info we can track down the transaction and commit it or roll it back. In our example, once we roll back the PAULSTRAN transaction, there’s enough log space to insert one more record into the table, but then we run out of log space again. What’s going on? Because the PAULSTRAN transaction we very close to the start of the transaction log, all of the log it was effectively holding hostage was backed up but couldn’t be cleared. In this case we need to perform another log backup to allow the log to be cleared. This makes sense and is documented behavior.<\/p>\n Summary<\/strong><\/p>\n Of course, there are other causes of the transaction log filling up and other things you can do apart from taking log backups or identifying long-running transactions – such as growing the log file or moving the log file to a volume with more free space. Here are some good resources you can use for further reading:<\/p>\n Hope this helps!<\/p>\n","protected":false},"excerpt":{"rendered":" One of the great things about the blog engine we use is that it shows all the search engine queries that led to someone clicking through to the site. I’ve been looking through the logs to see what kind of problems people are having that end up here. In this occasional series, I’m going to […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,78,98],"tags":[],"class_list":["post-1181","post","type-post","status-publish","format-standard","hentry","category-example-scripts","category-search-engine-q-and-a","category-transaction-log"],"yoast_head":"\n\r\nUSE [master];\r\nGO\r\n\r\nIF DATABASEPROPERTY (N'tinylogtest', 'Version') > 0 DROP DATABASE [tinylogtest];\r\nGO\r\n\r\nCREATE DATABASE [tinylogtest] ON (\r\n NAME = N'fgt_mdf',\r\n FILENAME = N'c:\\tinylogtest\\tinylogtest.mdf',\r\n SIZE= 2MB)\r\nLOG ON (\r\n NAME = N'fgt_log',\r\n FILENAME = N'c:\\tinylogtest\\tinylogtest.ldf',\r\n SIZE = 512KB,\r\n FILEGROWTH = 0);\r\nGO\r\n<\/pre>\n
\r\nALTER DATABASE [tinylogtest] SET RECOVERY FULL;\r\nGO\r\n\r\nBACKUP DATABASE [tinylogtest] TO DISK = N'C:\\tinylogtest\\dummybackup.bck';\r\nGO\r\n<\/pre>\n
\r\nSET NOCOUNT ON;\r\nGO\r\n\r\nCREATE TABLE [tinylogtest]..[testtable] ([c1] INT, [c2] CHAR (3000));\r\nGO\r\n\r\nWHILE (1=1)\r\nBEGIN\r\n INSERT INTO [tinylogtest]..[testtable] VALUES (1, 'a')\r\nEND;\r\nGO\r\n<\/pre>\n
\r\nMsg 9002, Level 17, State 2, Line 4\r\nThe transaction log for database 'tinylogtest' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases\r\nThis is much better than you get on SQL Server 2000:\r\nMsg 9002, Level 17, State 2, Line 4\r\nThe\u00a0log file\u00a0for database 'tinylogtest' is full.\r\n<\/pre>\n
\r\nSELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'tinylogtest';\r\nGO\r\n<\/pre>\n
\r\nLOG_BACKUP\r\n<\/pre>\n
\n
\r\nBACKUP LOG [tinylogtest] TO DISK = N'C:\\tinylogtest\\dummybackup.bck';\r\nGO\r\n<\/pre>\n
\r\nNOTHING\r\n<\/pre>\n
\r\nBEGIN TRANSACTION PAULSTRAN;\r\nGO\r\n\r\nINSERT INTO [tinylogtest]..[testtable] VALUES (1,'a');\r\nGO\r\n<\/pre>\n
\r\nLOG_BACKUP\r\n<\/pre>\n
\r\nACTIVE_TRANSACTION\r\n<\/pre>\n
\r\nDBCC OPENTRAN (N'tinylogtest');\r\nGO\r\n<\/pre>\n
\r\nTransaction information for database 'tinylogtest'.\r\n\r\nOldest active transaction:\r\nSPID (server process ID): 54\r\nUID (user ID) : -1\r\nName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : PAULSTRAN\r\nLSN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : (18:98:1)\r\nStart time\u00a0\u00a0\u00a0\u00a0 \u00a0: Sep 23 2007\u00a0 5:49:53:077PM\r\nSID\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 : 0x010500000000000515000000ae4da6eadcd59cf661d6bb58ed030000\r\nDBCC execution completed. If DBCC printed error messages, contact your system administrator.\r\n<\/pre>\n
\n