May 24, 2011 at 11:42 pm
There are multiple log files for a data base.
The size of 3 files are around 1 MB and the major logfile is around 2 GB.
How can I delete the smaller log files?
May 25, 2011 at 2:15 am
Not really the right forum area.
You need to empty the files using the DBCC SHRINKFILE command. This clears them and marks them not to be used.
DBCC SHRINKFILE
(
{ file_name | file_id }
{ [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
}
)
[ WITH NO_INFOMSGS ]
Then remove using ALTER DATABASE .... REMOVE FILE
May 25, 2011 at 2:41 am
You can also try through SSMS. Follow these steps.
Right click on database-> Tasks-> Shrink->Files->
You will see option at the bottom for ->Empty file by migrating the data to other files in the same file group
M&M
May 25, 2011 at 3:19 am
Gui = bad
TSQL = Good
For instance I generated this script using the gui. I only asked to add the login to another role but its altering the login as well?! Additional code for no apprarent reason and this is common throughout the interface.
USE [master]
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON, NO CREDENTIAL
GO
EXEC master..sp_addsrvrolemember @loginame = N'sa', @rolename = N'bulkadmin'
GO
May 25, 2011 at 3:27 am
Yes, I agree. It is better to use scripts to get familiarized with the underlying commands.
M&M
May 25, 2011 at 3:05 pm
you can only delete files that are not in used by the database. Shrink the file will not make any difference, you will not be able to delete the file.
To delete the file you need to "alter" the database and 1st remove the file from the file list. You can not remove mdf and ldf file.
This is the script to use, you need to make sure that there is no table on the file group.
Before you do that you should read more about Files and Filegroup, this is really fundamental. Make sure that you understand what you are doing or you are going to loose data...
USE [DbName]
GO
ALTER DATABASE [DbName] REMOVE FILE [LogicalFileName]
GO
May 26, 2011 at 5:21 am
I suggest you havent read the full post. Logfiles dont contain objects.
The DBCC command will empty the log and the ALTER statement remove it.
May 26, 2011 at 6:04 am
I know, he want to "Delete" the file, so he 1st need to remove it from the database used files.
May 30, 2011 at 11:28 pm
Thanks for your help.
After shrinking the log the file size is 1 MB i.e. the initial DB size.
Can I remove the file now and delete it?
May 31, 2011 at 8:18 am
You can not Remove Log file Each database need to have 1 Data file (.mdf) and 1 log file (.ldf) other wise you can not have a database.
Before you delete anything read this : so you can understand how they work.
Also when you come to delete the file make sure that SQL is running so you will avoid deleting the wrong file. ie the OS will not allow you to delete a file that SQL is using.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply