Tag Info

Hot answers tagged

100

A Shorter Answer: You probably either have a long running transaction running (Index maintenance? Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Full and have not taken a log backup (or aren't taking them frequently enough). There could be other reasons but these are the most common. This ...


34

Since I'm not really satisfied with any of the answers over on Stack Overflow, including the most heavily up-voted suggestion, and because there are a few things I'd like to address that Mike's answer does not, I thought I would provide my input here too. I placed a copy of this answer there as well. Making a log file smaller should really be reserved for ...


26

When you change a column to NOT NULL, SQL Server has to touch every single page, even if there are no NULL values. Depending on your fill factor this could actually lead to a lot of page splits. Every page that is touched, of course, has to be logged, and I suspect due to the splits that two changes may have to be logged for many pages. Since it's all done ...


17

When carrying out the command ALTER COLUMN ... NOT NULL This seems to be implemented as an Add Column, Update, Drop Column operation. A new row is inserted into sys.sysrscols to represent a new column. The status bit for 128 is set indicating the column does not allow NULLs An update is carried out on every row of the table setting the new columnn value ...


16

The reason you are seeing this: Starting up database 'dbname' CHECKDB for database 'dbname' finished without errors Is because you have your database option set for AutoClose. To turn off AutoClose, do this: alter database YourDatabase set auto_close off go What AutoClose does is shut down the database after the last user process disconnects. And ...


14

You have two options: 1) Run a LOG backup of the database: BACKUP LOG foo TO DISK='X:\BackupLocation'; 2) Set the recovery model to SIMPLE and checkpoint it. ALTER DATABASE foo SET RECOVERY SIMPLE; CHECKPOINT; This is all part of SQL Server's different recovery models. Based on you working on this in DEV, I'd probably go the second option since it ...


11

What is a virtual log file? SQL Server divides the transaction log file for each database into smaller chunks, called 'virtual log files' (or VLFs for short). Their primary function is as truncation markers at a log backup, i.e. the SQL Server will only clear (and mark available for re-use) VLFs that are completely empty. MSDN has a piece on Transaction Log ...


11

I turned to my copy of SQL Server 2008 Internals and the DMV sys.database_recovery_status was pointed out to find the first LSN of the next log backup. Which going by BOL the column last_log_backup_lsn provides you with: Log sequence number of the most recent log backup. This is the end LSN of the previous log backup and the starting LSN of the next log ...


10

You can force a ROLLBACK though by switching the DB to RESTRICTED using WITH ROLLBACK IMMEDIATE. See ALTER DATABASE. You can't force COMMIT (as per your SO question) Then... If you delete the LDF (SQL Server shut down), your database comes back "suspect". If you detach+delete, attach the MDF by itself, the LDF is re-created. Note: for the millionth ...


10

After you dig yourself out of this hole, please, read this and learn from it. Logs are an essential part of how SQL Server works. You can't just get rid of them. But you can manage them appropriately. You're probably working off the default settings, which create all databases in Full Recovery mode, meaning, the logs are going to grow. Change this after you ...


10

Shrinking your transaction log should not be part of your routine. A transaction log backup clears the log file automatically; shrinking it afterward just causes it to have to grow again afterward. Read this for more information. Yes, you should be able to perform point-in-time restores using your latest full backup and any subsequent transaction log ...


10

The difference is that what you call "standard commands" have implicit transactions (as in "not explicit" and not real implicit transactions which mean something different), so every time you issue an INSERT command without an explicit transaction, it will open a transaction, insert the data and automatically commit. This is called an autocommit transaction. ...


10

The transaction log isn't recording the SQL statements being executed, as you might be expecting. Instead, it's recording the changes to the raw data in each database, independently. It's possible for a stored proc from one database to be working entirely in the transaction log of another database. ... database1..my_stored_procedure AS BEGIN INSERT INTO ...


10

Moved this over to Mike Walsh's similar question.


9

Just to add to the existing answers. The SQL Server 2008 Internals Book (pp 175-177) implies that detaching the database, deleting the log file and reattaching the mdf file ought to be quite safe as it says. Detaching a database ensures that no incomplete transactions are in the database and that there are no dirty pages for this database in memory. ...


9

Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause. MODEL database simple recovery model. If the model database has been set to SIMPLE recovery model, and user databases are created with SIMPLE recovery model, SQL Server somehow is treating it as if it is on FULL recovery model. Hence ...


9

If a disaster occurred and I needed to recover the database from backup, would I be missing data? As long as all of the backups are in tact, no. The transaction log chain is not broken, and point-in-time recovery is possible. It's just that the backups that constitute a complete transaction log chain are not all in the same location. Having said ...


8

Your file management can be a completely online operation. You have two paths, depending on your need to retain your log information for recovery purposes: Point in time recovery not needed Convert the database to SIMPLE recovery. Execute a checkpoint to write transactions to disk. Flatten the log. Resize the log to the appropriate size. I also ...


8

How to reduce the size of transaction log file without shrink process (Because it’s Bad – Increases Fragmentation – Reduces Performance) That is not true. log shrink is quite benign, you are thinking data shrinks. See How to shrink the SQL Server log for an explanation why it grows, how to shrink it and why is benign. My first recommendation is to use ...


7

No. If you try the following from two different connections then the second one will be blocked by the first (visible in sys.dm_os_waiting_tasks) but neither will result in any transaction log activity and running DBCC OPENTRAN will report "No active open transactions" (assuming no other activity). SELECT COUNT_BIG(*) FROM master..spt_values v1 WITH ...


7

Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, ...


7

Your only real chance is using a 3rd-party log analyzer tool, and even that may not work. SIMPLE recovery truncates the log when a CHECKPOINT process occurs, which is highly likely to have happened at this point. Also, if losing the entire set of changes between backups is unacceptable, either back up more frequently in SIMPLE (if your database is small), ...


7

Here is a slightly simpler approach that avoids the cursor and nested exec: SET NOCOUNT ON; CREATE TABLE #to ( DBName SYSNAME, FileCount INT ); DECLARE @v INT; SELECT @v = CONVERT(INT, PARSENAME(CONVERT(VARCHAR(32), SERVERPROPERTY('ProductVersion')), 4)); DECLARE @sql NVARCHAR(MAX); SET @sql = N'CREATE TABLE #ti ( ' + CASE WHEN @v >= 11 ...


7

The following query will output the full contents of the current log in tabular format. If you only require a portion, replace the NULLs with LSNs. SELECT * FROM sys.fn_dblog(NULL,NULL) If th.e data is no longer in the log, then the following query will return the contents of your log backup. SELECT * FROM fn_dump_dblog (      NULL, NULL, 'DISK', 1, ...


7

I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then ...


7

You do not have to shrink the log (ie. make the file smaller) but instead you have to truncate the log (ie. allow the file to be reused internally, so it doesn't grow to start with). How to achieve this depends on your database recovery model. Under SIMPLE recovery model the truncation occurs automatically. Under other models (FULL or BULK-LOGGED) the ...


7

It is impossible for us to guess what is causing it, but SQL Server doesn't just grow a log file to 300 MB for the heck of it, it grows to 300 MB because at some point since your last shrink operation, it needed that much log space (whether due to some big single transaction or a lot of smaller concurrent ones). You'd have to trace log file growth events (I ...


6

why are those operations on the log file if they haven't been committed in the first place All operations, committed or uncommitted, are written in the log. A commit ensures that all log entries are made durable (flushed to disk), but nothing prevents uncommitted entries from being flushed before that (either because a log block fills or because ...


6

Why are you rebuilding the indexes? Do you have any evidence that the rebuild is required/needed/beneficial? Have you considered when to use Reorganize vs. Rebuild? Consider using one of the many index maintenance scripts like the ones from Ola Hallengren or Michelle Ufford. These scrips have intelligence in them to do appropriate maintenance action ...


6

You can view the individual rows that were deleted by looking for LOP_DELETE_ROWS operations still in the log: select * from fn_dblog(NULL, NULL) where Operation = 'LOP_DELETE_ROWS' If the log was recycled (in simple recovery model) or truncated by backup (in full or bulk recovery model) then you will only be able to see the log operations still ...



Only top voted, non community-wiki answers of a minimum length are eligible