SQL Server log file is more

[Former Member]

Hi Gurus,

i have another problem with my another developement server.

Problem is: my total hard disk drive is around 250 gb. we have 3 data file each 30 gb and one log file is with around 160gb.

so now when i go check db02 found that free disk space is 67MB.

now i want to reduce log file size to 30 gb, so remaining would be free on file system to grow data files.

please advise me how to reduce log file size and what are pre requisistes to be taken before doing?

Note:sql version is 2005.

Thanks,

Venkat

[nicholas_chang · Accepted solution]

Hi,

After a log backup, go to SQL server management studio -> Databases -> SID -> right click -> task -> shrink file -> change to log -> select shrink option shrink file to -> fill in the size

Hope it helps,

Cheers,

Nicholas Chang

[nicholas_chang · Accepted solution]

also, you can refer to Note 363018 - File management for SQL Server for more info.

[Former Member · Accepted solution]

Thanks Nicholas,

am very new to SQL server, could you please tell me how can i take backup of log file?

do i need to take full backup ?

Thanks,

Venkat

[Former Member]

Instead of truncate, I would recommend you to take a backup of existing log file,  "autogrow' option , set the limit of SQL log file to 30 GB manually and periodically schedule the transaction log file backup.

Goto

server management studio -> Databases -> SID -> right click -> properties --> Files --> Click on auto growth for logfile --> Set maximum File size to 30 GB.

This is online process.

Shrinking the log file will cause you to loose important information which is required in case of DB recovery. IF you are going for shrink option, take a full backup after that.

Thanks,

Vishal

[Former Member]

Hi Nicholas/Vishal,

i want go with shrink option.

now am able to backup of status as below

Full DB Backup                 06 Jun 2012 19:49:34

   Backup Size(GB)                 86.09

   Backup Duration(hh:mm:ss)   01:14:46

Differential DB Backup         Not found during past 7 days

Transaction Log Backup         07 Jun 2012 00:00:31

now shell i go ahead?

after that i will ask my collegue to take full backup.

is it ok?

Thanks,

Venkat

[former_member184548]

Hello Venkat

You can shrink the log file or data file at time. It is not necessary to take the backup before you shrink them.  When you tried to shrink ,it will tell you how much free space is available. You can shrink your log file by that amount only.

Thanks

atul

[Former Member]

Hello,

Atul is right it is not necessary to take log backup before you shrink log file.

But it will release only unused space in the log file.

Please go through 1409312 - SQL Server database log is too big

Best way,

Take the backup of current log file ma be on disk. then shrink it

This is mentioned in the note ( APSHELL replace this with your DB id)

USE APSHELL

GO

BACKUP DATABASE APSHELL TO DISK = ‘D:\backupdir\APSHELL.bak’

DBCC SHRINKFILE(APSHELL_LOG, 1)

GO

Then manually set the size of Log file mentioned in my previous reply.

Thanks,

Vishal

[Former Member]

Thanks All for your help.

it has been resolved.

Venkat