lenn0x
asked on
how shrink sql 2005 log files
Hi,
we have a Server 2003 with SQL2005 installed.
the database is 50 GB but I have 2 ldf files that are 250GB in size.
HDD space is running out because of that.
I'm aware that these are the log files, I backed up the whole databse and also only the protocol in SQL management studio and shrink the database and protocol, but it did not help.
What can I do now?
Regards
Lennox
we have a Server 2003 with SQL2005 installed.
the database is 50 GB but I have 2 ldf files that are 250GB in size.
HDD space is running out because of that.
I'm aware that these are the log files, I backed up the whole databse and also only the protocol in SQL management studio and shrink the database and protocol, but it did not help.
What can I do now?
Regards
Lennox
Back the log files up in the maintenance plan. Or set the recovery mode to simple.
What for you need 2 ldf files?
Transaction log files are written sequentially so the 2nd file would be only used after the 1st one is full.
Transaction log files are written sequentially so the 2nd file would be only used after the 1st one is full.
Use the following command to reduce the log file size
OpenSQL Query Analyzer and type the following command,
and run the command until i get the msg
"DBCC execution completed. If DBCC printed error messages, contact your system administrator"
Dbcc shrinkdatabase (databasename,0)
backup log databasename with no_log
OpenSQL Query Analyzer and type the following command,
and run the command until i get the msg
"DBCC execution completed. If DBCC printed error messages, contact your system administrator"
Dbcc shrinkdatabase (databasename,0)
backup log databasename with no_log
I'd disagree with shrinking the database. It serves no great purpose, in fact it can degreade performance. The database performs better if it has free space in it. If you back the log file up it will commit everything to the database and the log file will shrink.
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
Dbcc shrinkdatabase (databasename,0)You don't need to shrink a database because of the transaction log file.
I'd disagree with shrinking the database. It serves no great purpose, in fact it can degreade performance.And that's why. I'm 200% with Lee Savidge.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I have discovered though, through practical application of this, that sometimes you need to run backup your log twice, before the shrinking of the logfile really gives you any noticeable savings of space. I really can't explain why that is so, but it is something I've noticed in doing this in the past.It's because during the first transaction log backup there was not commited transactions in the tlog. Active transactions can't be released.
Given that the log files are huge, I don't see the point in actually backing them up. You'll never use them to forward recover anyway.
Since you're still in SQL 2005, you can resolve this the easy way:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( 2, 20480 )
You can remove the second log file as soon as it's not in use. As noted above, there is no point in having two log files.
If the db is in full recovery mode, run a full backup as soon as you can.
Since you're still in SQL 2005, you can resolve this the easy way:
BACKUP LOG <database_name> WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( 2, 20480 )
You can remove the second log file as soon as it's not in use. As noted above, there is no point in having two log files.
If the db is in full recovery mode, run a full backup as soon as you can.
ASKER
thanks you for your answers.
what I did until now:
backed up the whole db several times. (just to be sure)
then I backed up the transaction log, only one time.
now if I go to "shrink file/Protocol" in SQL Manager I see that the available free space is 175610,31 MB (91 %).
but if I click okay to shrink it, it dows not shrink, only a very small amount. 175388,97 MB (91 %)
So now I will take a second backup of the transaction logfiles, as Steve Wales says.
Just to be sure: I do backup using SQL Manager, right-click on the database and choose "Task/Backup" and choose "Full" or Transaction Protocol" - that is okay, isn't it?
what I did until now:
backed up the whole db several times. (just to be sure)
then I backed up the transaction log, only one time.
now if I go to "shrink file/Protocol" in SQL Manager I see that the available free space is 175610,31 MB (91 %).
but if I click okay to shrink it, it dows not shrink, only a very small amount. 175388,97 MB (91 %)
So now I will take a second backup of the transaction logfiles, as Steve Wales says.
Just to be sure: I do backup using SQL Manager, right-click on the database and choose "Task/Backup" and choose "Full" or Transaction Protocol" - that is okay, isn't it?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
With which file are you working on? The first log file or the second one?
Also check for long running processes and jobs.
Also check for long running processes and jobs.
ASKER
the second log file is not important, it is from another database that some special "boss person" here has copied to that directory. As you all said it is only one ldf file.
ASKER
thank you so much!
that did it.
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
Thank you!!
that did it.
DBCC SHRINKFILE (<FileName>, <TargetSize>) WITH NO_INFOMSGS
Thank you!!