5

I have log file which is grown to 1TB. I have no need of log file. Just want to delete the old log file and create a new one instead of old log file.

How can I achieve that ? will it cause any other problem? pleas help.

vignesh
  • 1,414
  • 5
  • 19
  • 38
  • 1
    That seems like a bad idea. if it's that big, have you done a transaction log backup recently? Why not do one and then shrink the file (if you have to). – Thom A Jul 19 '18 at 10:20
  • It really depends on several other things: 1) what version of SQL Server?, 2) is this your database’s only log file, or does it have more than one? 3) what is the Backup Mode setting of the database? 4) have you been doing regular database backups? – RBarryYoung Jul 19 '18 at 10:26
  • Also, how big is the database’s datafile(s)? – RBarryYoung Jul 19 '18 at 10:59
  • @larnu for this db, log file is not required – vignesh Jul 19 '18 at 11:10
  • "log is not required"? Every database has a log file. It is **required**. – Thom A Jul 19 '18 at 11:10
  • @rbarryyoung 1. version: 2014 2. Yes log file is for my db only 3. Full 4. No we dont – vignesh Jul 19 '18 at 11:11
  • @larnu : Haha.. i agree. I meant, we have no need to backup log – vignesh Jul 19 '18 at 11:41
  • If you don't need a log backup, why is your database in full backup mode? Either use Simple or backup or logs. This is why you're in the position you are in... – Thom A Jul 19 '18 at 11:56

4 Answers4

3

Right mouse button on the database and

TASKS -> SHRINK -> FILES

Then choose the option that interests you

More :

https://learn.microsoft.com/en-us/sql/relational-databases/databases/shrink-a-file?view=sql-server-2017

Killer Queen
  • 776
  • 9
  • 20
  • This *might* work and it might be what the OP needs, but it depends on several other things (see the comments to the question, above) – RBarryYoung Jul 19 '18 at 10:28
  • I know, that's why I put a link to the documentation @RBarryYoung – Killer Queen Jul 19 '18 at 10:35
  • 2
    But that link won’t resolve any of the (most likely) problems that they probably have. Which is that Shrinkfiles probably won’t shrink the log at all because the are probably in FULL mode and haven’t done the proper backups. – RBarryYoung Jul 19 '18 at 10:42
  • I gave the option to check, I am waiting for the author's comment, maybe this is it, maybe not. @RBarryYoung – Killer Queen Jul 19 '18 at 10:48
  • Shot in the dark, fair enough. In my experience though, they most likely have their backups mis-configured, and once that is fixed, then Shrinkfiles will work. – RBarryYoung Jul 19 '18 at 10:52
  • You can be right, I do not have that much experience with it. @RBarryYoung – Killer Queen Jul 19 '18 at 10:53
1

I made a backup of the database (very important just in case something fails or trying something else you brake up things!) and then I detached and reatached the database, but deleting the ldf file from the reataching window. This creates a new ldf file as expected in the original database but empty.

enter image description here

More details here: https://inapp.com/delete-sql-server-database-transaction-log-file/

Juan Ignacio Avendaño Huergo
  • 409
  • 1
  • 6
  • 15
0

use this :

create database cmsdb on
(filename = 'd:\programfiles\microsoft sqsl server \msql12.mssqlserver\mssql\data\cmsdb.mdf')
for attach_rebuild_log
go
Jee Mok
  • 6,157
  • 8
  • 47
  • 80
Sajad
  • 1
-2

Brought database to offline and deleted log file manually from file system. And bring database back to online. Thats worked out.

vignesh
  • 1,414
  • 5
  • 19
  • 38