I want to clean the whole database, so I am dropping all tables and DB objects in two FOR loops, within one transaction. This makes the transaction logs become full. I could increase the transaction log size, but I would much rather drop the database without anything going into the transaction logs. Is this possible to do?
Db2 – IBM DB2 9.7, possible to temporarily disable transaction logs
db2transaction-log
Related Solutions
The transaction log is a important method to restore your database in a specific time. If you have a large database > 500 GB and if you must restore your database from a full backup, this will cost very much time. Also if you full backup your database everytime, think about how long this backup could take.
A very easy concept for SQL Server can be: Set Recover Model of your Database Full
Create a Maintenance Plan(1) in SQL Server:
- Do FullBackup every Week maybe in D:\yourbackup\FullDBBackup.bak
- Do Differential Backup every two days in D:\yourbackup\DiffBackup.bak
- Do every 2 houers Backup your Transaction log in D:\Yourbackup\Tranlogbackup.trn
Create a Maintenance Plan(2) in SQL Server:
- Delete all older Files 8 days from D:\yourBackup*.bak
- Delete all older Files 3 Days from D:\yourBackup*.trn
In this case, you are able to recover your Database in a specifc time, very fast very easy. SQL Server will automaticly manage your "Backup" files, older files will be deleted after your specific time range.
I would suggest that you read about SQL Server Transaction Log here:
http://www.sqlservercentral.com/articles/Design+and+Theory/63350/
For using the Maintenance Plans in SQL Server just ask BING / google :D
you should build a small test db and test this before you go in production
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 dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:
- Full
- Log
- Full
- Full Copy_Only
- Log
Next the database is dropped and restored like this:
- 1st Full
- 1st Log
- 2nd Log
The following SELECT demonstrates that the restore was successful.
This shows that neither a COP_ONLY nor a normal Full Backup break the log chain.
Then the database is dropped again and restored like this:
- Copy_Only Full
- 2nd Log
Afterwards the SELECT demonstrates success again.
This demonstrates that you can use a COPY_ONLY full backup as the base of your Log Restore.
Differential tests
I created a DIFFERENTIAL version too:
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 dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
This takes backups in this order:
- 1st Full
- 1st Differential
- 2nd Full
- 2nd Differential
- Copy_Only Diff
- 3rd Differential
It then tries this restore route:
- 1st Full
- 1st Differential
- 2nd Differential
Step 3 fails with this error:
Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
This shows that a normal full backup breaks the differential chain.
Next the database is dropped and this restore flow is attempted:
- Copy_Only Full
- 3rd Differential
Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.
Then the database is dropped again and the following restore is executed:
- 2nd Full
- 2nd Differential
- 3rd Differential
The following select proves that this restore succeeded. This shows that a COPY_ONLY Full backup does not interrupt the differential chain.
Related Question
- DB2: The transaction log for the database is full, But there is no ‘First active log file’
- Sql-server – Missing Transaction log in chain, Possible to skip
- SQL Server – Switching to Simple Recovery and Shrinking Transaction Logs
- Sql-server – Applying Transaction Logs to an existing Database
- Sql-server – Transaction log uses (not backups of transaction logs)
- Sql-server – Truncation of transaction logs
- Sql-server – Transaction log restore hourly
Best Answer
Dropping tables will engage the transaction logs. Actually dropping pretty much everything (except a tablespace or a bufferpool) engages the transaction logs.
Couple things that may help you
truncateon each table will drop the data from the table without logging. This only works for tables. Dropping tables and other objects will still log, but truncate would help you cut down on logging.I guess depending on what you are doing, the second or third points will get you there the quickest. Just remember that anything dropped that does not engage the transaction logs is not recoverable from, without restoring from a backup.
I'd recommend taking a backup before you try any of these as a best practice. You could also use
db2lookto get the schema changes anddb2moveto get the schema with data(although I don' think you get any security with either of those). Again, I'd stick to a backup before you try dropping anything.EDIT: You can indeed get some security with db2look. These are usually the
-xand-xdepparameters.