322
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).
If it is a recovery model issue, the simple answer could be ...
115
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 ...
73
From http://www.sqlservercentral.com/scripts/tempdb/72007/
;WITH task_space_usage AS (
-- SUM alloc/delloc pages
SELECT session_id,
request_id,
SUM(internal_objects_alloc_page_count) AS alloc_pages,
SUM(internal_objects_dealloc_page_count) AS dealloc_pages
FROM sys.dm_db_task_space_usage WITH (NOLOCK)
WHERE ...
48
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 ...
32
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 ...
27
You can also see the content of your log file. To do that, you can use the undocumented fn_dblog, or a transaction log reader, such as ApexSQL Log.
It doesn't show index reorganization, but it shows all DML and various DDL events: ALTER, CREATE, DROP, trigger enable/disable, grant/revoke permissions, object rename.
Disclaimer: I work for ApexSQL as a ...
24
You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization.
Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment.
If recovery model is simple, ...
23
The key difference between Full and Copy-only backups is whether or not the LSN (Log Sequence Number), and specifically the DatabaseBackupLSN is updated.
When you take a Full backup, the DatabaseBackupLSN is updated. After taking the full backup, if you take a Differential backup that backup has a DatabaseBackupLSN which matches that of the Full backup, ...
22
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 then ...
21
In AGs writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Note that the shrink may not shrink as much as you expect, your test on the restored DB had probably leveraged simple recovery model. Read How to shrink the SQL Server log for more info.
Do not shrink to 160MB. Determine why did the ...
20
The transaction log file (LDF) is made up of lots of virtual log files (VLFs) inside. Think of it like a cabinet with several pull-out drawers. You could choose a large cabinet, or a small one - but it's still going to be a fixed size with just different numbers of drawers.
As SQL Server works, it puts your transactions into drawers (VLFs.) It starts at one ...
19
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 ...
18
REDO log is Oracle terminology, transaction log is InnoDB terminology. Now that all are Oracle engineers, people use both to refer to the same thing in MySQL.
The transaction log is, by default- it can be changed- the two files located in $DATADIR called ib_logfile0 and ib_logfile1. It serves the same functions as the REDO log in other databases- storing ...
17
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 ...
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 ...
14
A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.
You also can restore log backups after restoring from a copy only. The following script demos that:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO ...
14
At the very least you need to consider differential backups. Unless copy-only on the full is used, your next diff backup will be off. Copy-Only Backups:
Copy-only full backups (all recovery models) A copy-only backup cannot
serve as a differential base or differential backup and does not
affect the differential base.
The only difference between full ...
14
You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.
The data, at this point, may not be hardened into ...
13
Use Script to Shrink Log files of all databases other than the system DBs.
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
DECLARE @DBName NVARCHAR(255),@LogicalFileName NVARCHAR(255),@DBRecoveryDesc Varchar(200)
DECLARE DatabaseList CURSOR
FOR
SELECT name,recovery_model_desc
FROM sys.databases
WHERE state_desc = '...
13
No, but it may make your transaction log smaller - so your SQL Server will use less space.
To keep things simple, say you generate 1MB of transaction log activity every minute. After 15 minutes, you've generated 15MB of log activity - but that also means that your transaction log will need to be at least 15MB large (assuming that you're in full recovery ...
13
There are certain circumstances where dropping a column can be a meta-data-only operation. The column definitions for any given table are not included in each and every page where rows are stored, column definitions are only stored in the database metadata, including sys.sysrowsets, sys.sysrscols, etc.
When dropping a column that is not referenced by any ...
12
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. ...
12
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 ...
12
Here is the answer to my own question.
Run the below query to get information about the log file's reuse wait:
SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'
I got the following output:
log_reuse_wait_desc
-------------------
REPLICATION
There were some replication-related objects remaining in the database, even after removing the ...
12
When performing a cross-database transaction, in which transaction log(s) is the information stored?
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 ...
12
Assume that we have a database with scheduled backups. The full backup runs once in 24 hours at 00:00, also we have differential backups that run every 6 hours, and transaction log backups that run every hour. So, what if we need to make an extra full backup in the middle of the day, to restore another server? What shall we do in this case. Of course, we can ...
12
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 ...
12
I found the following answer from a post written by Paul Randal.
Is It Possible to Run Out of Log Sequence Numbers?
Paul writes:
There’s no need to worry because for all practical purposes it isn’t
possible to run out of log sequence numbers. As a bit of background, a
log sequence number is a three-part number used to uniquely identify a
...
12
I'd ask the person who told you that, to at least hear why they think it will decrease performance.
One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your ...
11
You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:
BACKUP LOG
lagerdb
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn'
WITH NAME = N'lagerdb logs'
Save yourself the trouble of hand cranking ...
Only top voted, non community-wiki answers of a minimum length are eligible
