SQL Server Performance Forum – Threads Archive

removing a transaction log

Hi guys, I’m seeing a transaction log that is not being used anymore. Can someone please share on how to properly delete the transactions logs? What are the specific steps? Thanks, v1rt

hi,
alter database ‘yourdb’
remove file ‘yourlogfilename’
regares Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri Goswami

You might need to issue
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)
before you can remove the log. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

KBAhttp://support.microsoft.com/?kbid=873235 for topic relevancy. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi FrankKalis, So you are saying that before I delete the transaction log file via WIndows Explorer, I must issue DBCC SHRINKFILE first? Do I also have to put the DB offline? Thanks, V1rt

quote:Originally posted by v1rtu0s1ty Hi FrankKalis, So you are saying that before I delete the transaction log file via WIndows Explorer, I must issue DBCC SHRINKFILE first? Do I also have to put the DB offline? ADDITION: Here is what I did: use dtcs
GO
DBCC SHRINKFILE (dtcs_log, 0, TRUNCATEONLY)
GO
–result was
–dbid fileid currentsize minimumsize usedpages estimatedpages
–82256000256000256000256000 ALTER DATABASE dtcs remove file DTCS_LOG
–result was
–The primary data or log file cannot be removed from a database. Where did I go wrong? Thanks, V1rt

ADDITION: Here is what I did: use dtcs
GO
DBCC SHRINKFILE (dtcs_log, 0, TRUNCATEONLY)
GO
–result was
–dbid fileid currentsize minimumsize usedpages estimatedpages
–82256000256000256000256000 ALTER DATABASE dtcs remove file DTCS_LOG
–result was
–The primary data or log file cannot be removed from a database. Where did I go wrong?

The error message is self-describing. You cannot remove the primary log file. Check with sp_helpfile that you’ve referenced the correct log file you wish to remove. The EMPTYFILE option is to make sure, all data is moved to other available files. And I didn’t say to delete the log via Windows. The ALTER DATABASE command is just fine. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

I got it working however with rules to follow A. If I don’t need the database, I should just delete the database via EM B. If I still need the database and I need more space and that I only have 1 logfile, this is what I did B1. Change to Simple Recovery
B2. Execute Checkpoint in QA
B3. DBCC SHRINKFILE (dtcs_log,100) C. If I still need the database and I need more space and that I have more than 1 logfile pointing to the same db, this is what I did C1. Change to Simple Recovery
C2. Execute Checkpoint in QA
C3. DBCC SHRINKFILE (DTCS_1_LOG, 0, truncateonly)
C4. ALTER DATABASE DTCS remove file DTCS_1_LOG
C5. DBCC SHRINKFILE (DTCS_1_DATA, 0, truncateonly)
C6. ALTER DATABASE DTCS remove file DTCS_1_DATA
What do you think about these steps? Did I forget something? Thanks!

quote:Originally posted by v1rtu0s1ty I got it working however with rules to follow A. If I don’t need the database, I should just delete the database via EM

Or with DROP DATABASE.

quote:
B. If I still need the database and I need more space and that I only have 1 logfile, this is what I did B1. Change to Simple Recovery
B2. Execute Checkpoint in QA
B3. DBCC SHRINKFILE (dtcs_log,100) C. If I still need the database and I need more space and that I have more than 1 logfile pointing to the same db, this is what I did C1. Change to Simple Recovery
C2. Execute Checkpoint in QA
C3. DBCC SHRINKFILE (DTCS_1_LOG, 0, truncateonly)
C4. ALTER DATABASE DTCS remove file DTCS_1_LOG
C5. DBCC SHRINKFILE (DTCS_1_DATA, 0, truncateonly)
C6. ALTER DATABASE DTCS remove file DTCS_1_DATA

The change to Simple Recovery isn’t appropriate for the most production OLTP databases. You are forsaking sophistication for convenience.
There is no really good argument in SQL Server to have more than 1 log file per database. So, I would first try and get rid of those "unnecessary log files". Then I would get away from Simple Recovery, determine what log space each database needs between two full backups and adjust the log files to that size. That way you avoid auto grow operations, which are somewhat costly and lead to file fragmentation, thus should have a negative impact on performance. If you determine during this evaluation that you are short of disk space, I would rather go for more disk space than try to tweak the rest of the available.
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

True if you deploy SIMPLE recovery model then you must ensure the full backup is in place in regular intervals.http://www.devx.com/GetHelpOn/10MinuteSolution/16493/0/page/4 to monitor the growth. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Quick question though. We were really wondering why the transaction log is still growing while no one is using it anymore. Any ideas?

If you have multiple log files, you really cannot instruct SQL Server to only use one log and ignore the other. Read BOL about that. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Check any scheduled jobs and run server side trace to findout the database processes.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>