Backing Up Transaction Log File
Jan 30, 2008
Hi All,
Before posting this, I did search for backing up the transaction log file(.LDF).Currently transaction log file is 4 GB size and I want to reduce it to few MBs.Will the following procedure work?
1)Take the backup of transaction log file by executing the statement-
BACKUP LOG <name of DB> TO <name of db>backup
2)Run DBCC shrink file statement to reduce the log file size-
DBCC SHRINKFILE(<name of log file>,25)
This is as per procedure explained in http://support.microsoft.com/kb/272318
Will this free up 4GB physical space on drive? or anything else I need to do ?
Thanks
Prasanna
View 5 Replies
ADVERTISEMENT
Jun 13, 2007
Hi
I have a situation where i need to insert or update the data from a flat file to a sql server database. The flat file contains nearly one lakh records.
I am using transactions. If all the rows are inserted or updated successfully i am commiting. If there is any error i am rollbacking the transaction.
when rollbacking the transaction it is taking more than 3 to 4 hours.
Can any one suggest me how to do that in batches?
Thanks in Advance
View 12 Replies
View Related
Jul 23, 2005
I am having a problem backing up my database and TLog files due to alack of local diskspace. The db file is about 30GB and the TLog isabout 20GB each on a different hard disk. Each disk doesn't haveenough available space to accomadate a backup. I also can't shrink thefiles because part of that procedure would require a backup.Question: Can I use a redirected drive for the backup media? Is therea way to trick SQL into allowing this? If the answer is no, doesanyone have a suggestion as to want I should do? I am in the processof requesting more disk space,but that could take a while.Thanks,
View 3 Replies
View Related
Jun 25, 2007
I got a .bak file from my friend
I want to restore the data base (presently I don’t have any database in my system).
But while I try to restore it
I am getting an error
“
System.Data.SqlClient.SqlError: Directory lookup for the file "D:Microsoft SQL Server 2005mydatabase.mdf" failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)
�
I have installed Microsoft sql server in a different directory
Not where he is installed
I can successfully restore the back up files of data base on my own system
But not the backup files created from others systems
View 2 Replies
View Related
May 24, 2004
My question is this:
Is there anyway to backup the current online log after complete loss of the current correponding datafile?
Example:
(2) Logical Disk Volumes
Disk 1 (D:) contains pubs_data.mdf
Disk 2 (E:) contains pubs_log.ldf
Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.
Thanks,
P
View 1 Replies
View Related
Jun 16, 2007
What's is the between backing up a database to a file and a logical backup device?
If I point the logical backup device to a file on the filesystem, it's same as backing up to a file? isn't?
Thanks
View 1 Replies
View Related
Jun 10, 2015
I have Full database backup upto previous day and transaction logfile of Today transaction. my database has crashed. I have restored previous day's Full backup. I have faced difficulty to restore today's transaction from today's transaction log. What are the steps to restore full database back and one day's transaction log file. Note: there is no differential database backup and transaction backup.
View 8 Replies
View Related
May 31, 2008
Hi All
I'm getting this when executing the code below. Going from W2K/SQL2k SP4 to XP/SQL2k SP4 over a dial-up link.
If I take away the begin tran and commit it works, but of course, if one statement fails I want a rollback. I'm executing this from a Delphi app, but I get the same from Qry Analyser.
I've tried both with and without the Set XACT . . ., and also tried with Set Implicit_Transactions off.
set XACT_ABORT ON
Begin distributed Tran
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TRANSACTIONMAIN
set REPFLAG = 0 where REPFLAG = 1 and DONE = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.WBENTRY
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.FIXED
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.ALTCHARGE
set REPFLAG = 0 where REPFLAG = 1
update OPENDATASOURCE('SQLOLEDB','Data Source=10.10.10.171;User ID=*****;Password=****').TRANSFERSTN.TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
update TSADMIN.TSAUDIT
set REPFLAG = 0 where REPFLAG = 1
COMMIT TRAN
It's got me stumped, so any ideas gratefully received.Thx
View 1 Replies
View Related
Mar 10, 2000
Can I keep a transaction log file on a different NT box in the same domain. The machine which is having the SQL server doesnt have space.
Thanks,
Sam G
View 1 Replies
View Related
Jul 15, 2004
Hie,
I have a transaction log files which grow to 24 Go but today there is only 100 mo uses, my backup is less than 100 mo but my files is 24 go, how can i reduce the size of my log file ?
View 1 Replies
View Related
Jul 16, 2004
:eek: I made a DTS package to import a bunch of data from text files into a db...and the log file keeps filling up.
This is a hosted server and I only have 35mb log file.
I'm really new to the DTS Designer...so what I set up in the DTS is
1. FTP 9 text files down into a temp folder
2. Delete all data in the 9 corresponding tables in the db.
3. import the data from the text files back into the db.
My client will need this DTS run daily.
What are my options for making sure the transaction log doesn't fill up everyday?
The transactions by the DTS (all the deletes and inserts) don't really need to be logged at all.
Is there some kind of maintenance plan I or the net admin could set up?
Is there a SQL command I could add to the DTS to make it not log the transactions that belong to this DTS?
Is there a better way to setup the DTS?
Thanks for any help!!!
Question 2:
Is there a "notify me if someone posts to this thread" feature anywhere on this site? I didn't see it. :eek:
View 2 Replies
View Related
Sep 24, 2006
Does the sql log file have to be in the same location as the DB or can the logfile be placed in another drive.
View 3 Replies
View Related
Feb 7, 2007
Hi,
My SQL2000 DB limit is 50GB. I can see I have used under 30 but Avaibale space is only about 11!? The Transaction Log file has taken
over 1/5 of my "precious" space.
Could you advise why TLF has grown so big?
Is it always relative to used space or somethig else as well?
What purpose does it serve?
Can I shrink it or how about bkp and Delete it?
Appreciate any help
Gezza
View 1 Replies
View Related
Jul 18, 2006
Hi All,I have a dillema that I created 2 transaction file before I started totake log back. Now I changed my recovery model from simple to full. DoI still need to keep my second transaction file? If I remove it wouldit be a problem for my system and log back up?If somebody responds me I really appreciate itRegards
View 5 Replies
View Related
Apr 30, 2007
I have a transaction log file that is 14 Gegabytes is this normal?
I had shinkred the database and files but is still the same.
I'm really new on SQL so I'm trying to undertand this.
Thanks for your help.
View 5 Replies
View Related
Feb 22, 2007
I have a design a SSIS Package for ETL Process. In my package i have to read the data from the tables and then insert into the another table of same structure.
for reading the data i have write the Dynamic TSQL based on some condition and based on that it is using 25 different function to populate the data into different 25 column. Tsql returning correct data and is working fine in Enterprise manager. But in my SSIS package it show me time out ERROR.
I have increase and decrease the time to catch the error but it is still there i have tried to set 0 for commandout Properties.
if i'm using the 0 for commandtime out then i'm getting the Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.
and
Failed to open a fastload rowset for "[dbo].[P@@#$%$%%%]". Check that the object exists in the database.
Please help me it's very urgent.
View 3 Replies
View Related
Feb 14, 2008
Hi all,
In my database the transaction log file size reached to 195 GB,
how can i make this to 2 mb? And also i want to set this to 2 mb only.
initially it was set to 185 GB.
please help soon.
Thanks!
View 3 Replies
View Related
Apr 17, 2002
How the size of transaction file is calculated. are there any standards.
I created my database with 2000 mb data filesize and 200 log file size.
now my datafile size has reach to 11000 mb but log file size is still 200.
Therefore when I take the online backup my logsize totally consumed and I have to cancel my backup.
Please reply ASAP.
regards
Sanjeev Lamba
View 1 Replies
View Related
Nov 29, 1999
I created a database and had its file size as automatic grow. Now the database file is of 17 MB and its transaction log file size is 230 MB. After checking transaction log file properties I came to that it is using 13 mb only and the rest of the 230 MB i.e 217 MB is free. I want that area in the transaction log to be freed and get the transaction file size to its actual size. Any help will be greatly appreciated.
Thank you in advance.
P.S: It is very urgent.
View 1 Replies
View Related
Apr 16, 2002
I have been getting some strange results while trying to reduce the size of a transaction log file. The background is the database was fully backed up last night. There has been no activity since the backup. A transaction backup was performed 30 minuted prior to my testing. Here is what I tried:
1. dbcc shrinkdatabase ('mydb',1)
Resulted in no change to the size of the log file (Still 110meg)
2. Right clicked on the database in Enterprise Manager and selected Shrink Database. I selected the log file to shrink and it shrunk to 29meg.
3. Tried dbcc shrinkdatabase ('mydb',1) again and no change.
4. Issued backup log mydb with truncate_only and
dbcc shrinkdatabase ('mydb',1) and the size shrunk to 2 meg.
Am I doing something wrong? I thought that after a full backup and backing up the log file, the out of date log records would be able to be shrunk out.
also, why does the Enterprise Manager shrink work differently from the dbcc command?
Any help would be greatly appreciated.
Thanks,
Ken Nicholson
View 1 Replies
View Related
Feb 9, 2005
Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
Nina
View 2 Replies
View Related
Jan 31, 2003
(Windows 2000 professional running SQL Server 2000)
How do you reduce the file size of the transaction log? Is it safe to delete the transaction log? I tried clicking on the ellipse (...) box in database properties, but even tho i'm the dba, it says i don't have permissions to do that! I see the "automatically grow transaction log" and "maximum file size for transaction log" dialogs, but our transaction log is already 5.5 GB, and we need for it to be WAY smaller! Is there a way to back it up, then start over with a new one or something?
View 3 Replies
View Related
Feb 11, 2004
Hi all,
I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me
database size created with automatically growth of 10% with unrestricted file growth.
database size = 5 Gb used 4.5 GB (taskpad)
transaction log=8GB used 54MB (taskpad) 7.5 GB free
database run in FULL mode
full backup nightly, transaction log backup every 30 min
What should I do to free up the space that are not used in the transaction log.
Thanks for your help.
View 7 Replies
View Related
Apr 16, 2008
I am trying to remove the transaction log file since I have created another one on a seperate drive by it wont let me do it . I get this error "cant delete the primary data or log file"
Now microsoft site clearly state steps for deleting the transaction log file , by first emptying it and then deleting it BUT IT DOESNT WORK.
I have even tried detaching the database and then removing the transaction log file and then attaching the database again BUT it attaches the transaction file back again GRRRRRR
then on top of this MS SQL studio does not allow to put PRIMARY or anyother value to the Filegroup for tansaction log files so how on earth can a transaction file be "primary" as the error is stating ??????
can anyone tell me how to get rid of a transaction log file that was created with the database ?
View 4 Replies
View Related
Dec 12, 2012
Am working with SQL Server 2005..I am trying to shrink the log file of the database.. Apparently, the name of the database, log and mdf is the same.
Used this code
USE gprddevelopment
GO
DBCC SHRINKFILE(<gprddevelopment>, 1)
BACKUP LOG <gprddevelopment> WITH TRUNCATE_ONLY
DBCC SHRINKFILE(<gprddevelopment>, 1)
GO
[code]....
View 4 Replies
View Related
Jul 18, 2007
version:SQL Server 2000.
db size: 25.6GB
trans log file: 32GB
so far: I have read the Forum FAQ on "clearing the transaction file" and some DBCC SHRINK in Books Online.
I am trying to set up maintenance plan or backup plan. File sizes are getting very large and performance is horrible since we have had no regular maintainence in the past (No DBA in house).
1. What is the best way to identify the location of the transaction log file associated with a particular database?
When I right-click on the database name in Enterprise Manager and select properties, I can see one location for the transaction log.
However, If I right-click on the database > All Tasks > Shrink Database > click Files and select Temp_Table_Log, I get a different location.
2. We delete then repopulate about 105000 records in one particular table each day. In addition, we do the same with about a hundred rows in several other tables daily.
-- Should I be doing Full Backups nightly?
-- I have the option set to "AutoShrink" on the db. Will this truncate and shrink the transaction log as well as shrink the db when I do a full backup?
Thanks,
Più pranzo libero!
View 1 Replies
View Related
Jul 23, 2005
Hi thereI have an existing database in an SQL 2000 server and would like to relocatethe transaction log file to another physical disk within the box. However, Iam having trouble tracking down info on how to do this. If someone could letme know how to relocate the transaction log file, it will be muchappreciated.RegardsD.
View 1 Replies
View Related
Jul 20, 2005
Hello All,I have been encountering trouble with a SQL Server 2000 Transactionlog file, mainly with the constant growth and lack of the autoshrinkoption. Here are the details:1.)OS is Windows 2000 server sp32.)SQL Server 2000 (Build 2195) sp33.)Database Recovery Mode is set to Full4.)Maintenance Plan for "Transaction Log Backup" is set to removefiles older then 1hr.5.)The "AutoShrink" option is on for the DB properties.Also as part of the Maintenance plan the database in question isbacked up nightly. The problem is that the transaction log file hasgrown so much that it was consuming all of the free space on the harddrive so I then restricted the file growth. When all of the drivespace was consumed or the "database log file is full" message wasencountered I would run the following Query scripts to shrink the filemanually:First – Backup Log <database name> With Truncate_OnlySecond – DBCC Shrinkfile (<database name>_log, 200)Which brings the transaction log file down to 200mbs. I need toautomate this so that I don't have to keep babysitting this databaseor manually shrinking the log file.
View 3 Replies
View Related
Jul 20, 2005
I have set the initial size of the log file for a database to 1M, themaximum size is unrestricted, and the increase rate is 10%.However, when I attempt to delete thousands of rows, the error is stillreported that the transaction log file is full. Why can't the log fileincrease automatically?*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Mar 22, 2007
Is there a way to know how much space is free in each transaction log file of the same database?
Example:
A database with 3, 1GB files for transaction log: A, B and C created in this sequence.
From what I have read, since SQL Server 2005 writes to a single transaction log only, I guess if the transaction log is using 2.5GB than A and B are full and B is only half full. Is this correct?
Thanks.
View 1 Replies
View Related
Apr 30, 2008
How to view transaction log through log files? I want to know what happens few hours ago in a certain DB, such as insert/update/delete operations....
I tried DBCC LOG, but it can't give the details, I mean SQL statements, can anyone help?
View 7 Replies
View Related
Aug 21, 2006
Hi,
Database transaction log file has reached to 1 GB and (99% of this space is free). When try to reclaim the unused space, I have no luck. Initial size of the transaction log has now reaches 1GB. When trying to change the initial size to 5 MB, it does not work.
I have backup the transaction log with Truncate the transaction log but system is no releasing the space.
Please Guide.
Thanks
Shafiq
View 10 Replies
View Related
Feb 6, 2007
I am getting this error :Distributed transaction completed. Either enlist this session in a new
transaction or the NULL transaction. Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.OleDb.OleDbException: Distributed transaction completed. Either
enlist this session in a new transaction or the NULL transaction.have anybody idea?!
View 1 Replies
View Related
