If you are assigned a task to reduce the log file size of sql server which is causing some space issues or because of this some other program may be interrupted or not working so you need to understand how log file typically ldf file behave and how its size can be shrunk.
If you want to do any operations on log file before doing that you have understand some other things which are important like recovery model.
There are 3 different recovery modes available in sql server
1) Simple
2) Full
3) Bulk- Logged
Ideally you can take log backup as log file that always growing and if you can keep backup of that transaction log area and further you can automate this mechanism with sql server jobs task.
Default setting for recovery database is “Full as anything happens so it recovers transaction from any point in time. That was the reason why people should understand before shrinking log file.
If you are shrinking a ldf file and it causing error so probably you already have set recovery setting as simple so change to Full.
Tap into
Database>>Task>>Shrink >>Files
Select your log file to shrink by the way same steps will be taken if you are shrinking you DB file or mdf. Before doing this you select option and select recovery mode Full and do a shrink.
Shrinking is artistic work actually weather log shrinking or db shrinking.
If you are still having problem in shrinking log file so you execute the following sql statement – they are safe totally.
According to Microsoft, there are different virtual log areas inside log file so to keep each and everything simple and safe – you can blindly provide no parameter for target size and “HOW” that will be listed down further.
DBCC SQLPERF(LOGSPACE);
The result you would see after this-
This result is telling you the log file size and percentage used by log file which is ldf.
You could see clearly the free space in log file. You have to reduce this file size and accommodate new log to be written. Ideally you can take backup.
You can use the use master command and run alter database statement and see if things are working then great if not and you have seen a error message telling this is not inside the current database. So use the USE command and set your database and then run alter database command
use master;
ALTER DATABASE [WSS_Content_ssp] SET RECOVERY SIMPLE WITH NO_WAIT
use master;
DBCC SHRINKFILE(WSS_Content_ssp_log,100)
use WSS_Content_MySite
ALTER DATABASE [WSS_Content_mysite] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(WSS_Content_mysite_log,10)
These sql scripts will help anyone who is trying to fix ldf file shrinking issue.
DBCC SHRINKFILE(WSS_Content_ssp_log, 200)
And finally run dbcc shrink command to shrink the log file, and see and analyze the size of the log file which is ldf.
Before running these scripts take log file backup and try flushing the log file - this is how people keep log backup and in this way when log file size increased would be decreased. You need to calculate mb which is occupied by log file and then run dbcc command in the example above - we have rough figure. Let's say you have 10000 mb of log file and 30% of that mb is being used and rest is empty so you can calculate the rest emply and mention that dbcc shrinkfile parameter - like 200 above.
Hope that you have fixed the increased log size issue in SQL server.
If you want to do any operations on log file before doing that you have understand some other things which are important like recovery model.
What is recovery mode and why should I begin with Recovery Mode in SQL Server?
Recovery mechanism in SQL Server tells- how log file can be used for recovery in the state of failure – like hardware failure or etc- in other word, from where you want sql server to start recovery.There are 3 different recovery modes available in sql server
1) Simple
2) Full
3) Bulk- Logged
Simply simple:
As name depicts – simple is for simple recovery – and you're telling SQL server that ok I can go ahead in the form of recovery and recover my data and my org can afford to lose little amount of data- this setting is ideal for test server or staging or development sql server but this is not ideal setting for “SQL Server Production”- how come you can set such rule for your organization that ok I am happy with losing little amount of data and recover from yesterday this kind of rule is telling such setting for SQL server when you keep recovery mode simple . It’s transaction log duty to tell sql server to recover from where and just forget today.Simply Full Recovery mode:
Full Recovery Mode tell sql server that you can recover transaction in any point in time – this mode also called point in time recovery – when any mishap happens to your data and because it has to recover from any point of time – so it always write transaction log in log file and that’s a big reason when transaction log file we called it ldf always grow in size.Ideally you can take log backup as log file that always growing and if you can keep backup of that transaction log area and further you can automate this mechanism with sql server jobs task.
Default setting for recovery database is “Full as anything happens so it recovers transaction from any point in time. That was the reason why people should understand before shrinking log file.
If you are shrinking a ldf file and it causing error so probably you already have set recovery setting as simple so change to Full.
SSMS Shrinking ldf file:
SSMS will help you shrinking ldf file steps are simple as recovery option:Tap into
Database>>Task>>Shrink >>Files
Select your log file to shrink by the way same steps will be taken if you are shrinking you DB file or mdf. Before doing this you select option and select recovery mode Full and do a shrink.
Shrinking is artistic work actually weather log shrinking or db shrinking.
If you are still having problem in shrinking log file so you execute the following sql statement – they are safe totally.
Shrink ldf log file using SQL Script:
But before that only thing is remaining that is the size of log file or ldf file while shrinking- you need to understand what size of the log file you have and how you want to manage it.According to Microsoft, there are different virtual log areas inside log file so to keep each and everything simple and safe – you can blindly provide no parameter for target size and “HOW” that will be listed down further.
Analyzing ldf or log file in sql server:
To check how big your log file size is- you run the following commands.DBCC SQLPERF(LOGSPACE);
The result you would see after this-
 |
| Depicted picture is for informational purpose only - SSMS analyzing occupied space by log /ldf file |
This result is telling you the log file size and percentage used by log file which is ldf.
You could see clearly the free space in log file. You have to reduce this file size and accommodate new log to be written. Ideally you can take backup.
You can use the use master command and run alter database statement and see if things are working then great if not and you have seen a error message telling this is not inside the current database. So use the USE command and set your database and then run alter database command
use master;
ALTER DATABASE [WSS_Content_ssp] SET RECOVERY SIMPLE WITH NO_WAIT
use master;
DBCC SHRINKFILE(WSS_Content_ssp_log,100)
use WSS_Content_MySite
ALTER DATABASE [WSS_Content_mysite] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(WSS_Content_mysite_log,10)
These sql scripts will help anyone who is trying to fix ldf file shrinking issue.
DBCC SHRINKFILE(WSS_Content_ssp_log, 200)
And finally run dbcc shrink command to shrink the log file, and see and analyze the size of the log file which is ldf.
Before running these scripts take log file backup and try flushing the log file - this is how people keep log backup and in this way when log file size increased would be decreased. You need to calculate mb which is occupied by log file and then run dbcc command in the example above - we have rough figure. Let's say you have 10000 mb of log file and 30% of that mb is being used and rest is empty so you can calculate the rest emply and mention that dbcc shrinkfile parameter - like 200 above.
Hope that you have fixed the increased log size issue in SQL server.
