Create another transaction Log
-
We are running Microsft SQL Server 2000.
One of the database has the following setting on the Transaction Logs
Unrestricted File growth
Auto Grow otion is On
The transaction log is taking too much of disk space and we would like to move the transaction Log.
And how to create another transaction log and use it.
Please suggest
Kindest Regards,Pavas
Dream The Dream
Explore the World
Experince The Exhilaration -
Pavas,
you can use the alter database statement as follows:
ALTER DATABASE my_database
ADD LOG FILE
(
NAME = logical_file_name,
FILENAME = 'physical path and file name',
SIZE = 5000MB,
MAXSIZE = 10000MB,
FILEGROWTH = 250MB
)
Obviously the numbers are just there as an example. You can specify a percentage filegrowth if you choose.
Once you've created this other log file, you'll want to put a limit on the original log file's maxsize (you might need to shrink that file first).
Hope that helps,
-
Hi Karl,
Does this mean we Alter the database giving a new Transaction file path.
Will this allow us to move the Old transaction log file
Kindest Regards,Pavas
Dream The Dream
Explore the World
Experince The Exhilaration -
Pavas,
I see. So you don't want to create an additional file, you actually want to move this file to another location? I thought you wanted to end up with two files.
In that case you have a couple of options.
1) ALTER DATABASE my_db MODIFY FILE (NAME=logical_name, FILENAME='new path\filename')
2) use sp_detach_db, move the file to the new location and then use sp_attach_db.
3) Create the new file as I suggested in my original post. use DBCC SHRINKFILE (logical_name, EMPTYFILE) on the old log file. The use ALTER DATABASE my_db REMOVE FILE logical_name.
Personally, I would normally go for option 2 (that's just force of habit). The thing to bear in mind is that with option 2 (and 1) the database will be offline whilst you do the move. So I'd suggest you shrink the file and make it is as small as possible so that you're not moving a large file across drives.
Regarding option 1, check BOL that the syntax is correct. I can't remember whether SQL 2000 uses the same syntax and I only have access to 2005 BOL.
If you need to keep the database online then you will have to go for option 3. So you create a new file as I suggested initially. Shrink the old file using emptyfile, which will move the data in the old file to the new file. Emptyfile will also prevent any new data from being written to that file. And then you can drop the old file.
Hope that helps,
p.s. Note that with option 1 you'll still need to move the physical file from one drive to the other. And you'll either need to stop the SQL Server services to do this or you'll need to take the database offline.
-
hey thansk for the replies
It worked for us
Hurry it was 75 gb of data
Thanks a lot
Kindest Regards,Pavas
Dream The Dream
Explore the World
Experince The Exhilaration
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply