AutoShrink Transaction Log File
Jul 20, 2005
Hello All,
I have been encountering trouble with a SQL Server 2000 Transaction
log file, mainly with the constant growth and lack of the autoshrink
option. Here are the details:
1.)OS is Windows 2000 server sp3
2.)SQL Server 2000 (Build 2195) sp3
3.)Database Recovery Mode is set to Full
4.)Maintenance Plan for "Transaction Log Backup" is set to remove
files older then 1hr.
5.)The "AutoShrink" option is on for the DB properties.
Also as part of the Maintenance plan the database in question is
backed up nightly. The problem is that the transaction log file has
grown so much that it was consuming all of the free space on the hard
drive so I then restricted the file growth. When all of the drive
space was consumed or the "database log file is full" message was
encountered I would run the following Query scripts to shrink the file
manually:
First – Backup Log <database name> With Truncate_Only
Second – DBCC Shrinkfile (<database name>_log, 200)
Which brings the transaction log file down to 200mbs. I need to
automate this so that I don't have to keep babysitting this database
or manually shrinking the log file.
View 3 Replies
ADVERTISEMENT
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
Jan 11, 2001
Hello,
Ran the Andrew Zanevsky script to shrink the Tran log, this works great, but say the log gets shrinked to 30MB, I then expand it to 40MB, then sometime within a 24 hour period it magically gets reset to 30MB. The database is a standalone DB, and is on SQL7 SP1 the script I ran can be found @ http://www.pinnaclepublishing.com/sq/SQMag.nsf/WebIndexByIssue/F36C151BD80FAD8F852568D0007799BE?open.
Can anyone please give some insight into this or maybe a flag that got reset somewhere?
Thanks in advance
Pat
View 4 Replies
View Related
Nov 12, 2004
Hello, everyone:
If I select AutoShrink in database properties to release space to operating syetem, how does it make the perfomance to change. Thanks a lot.
ZYT
View 2 Replies
View Related
Aug 2, 2007
Howdy folks!
I've got a database that needs to run 24/7. I'm looking into maintanence options and wanted to run the following by y'all:
Ok, I've read the MSDN "Maintaining databases" article and noticed the following statement about autoshrinking: "This technique uses almost no processor time and memory". I also searched these forums and found that many users say autoshrinking heavily lags down sql transfers. So who's right? And if it does lag transfers, by how much?
Another question I have about autoshrink is fragmentation. It would seem to me that over time solely depending on autoshrink would cripple a server in terms of fragmentation; is this the case?
Also, does autoshrink (or manual shrinking or compacting) update the statistics?
Final question!!! I'm programming in native c++, is there a way for me to run commands such as "DBCC SHRINKDATABASE" in native OLE DB code?
Thanks!
View 4 Replies
View Related
Mar 1, 2001
Hi,
I had a DTS task running which was doing an insert (over 17.5 million rows) in a database for which the auto shrink option was enabled.
After a few hours I noticed that the insert was blocking the autoshrink.
Since I didn't want to kill my insert, I tried to kill the autoshrink from the query analyzer, but when trying to do so I got the message that only user processes can be killed.
Does anyone have similar experiences? Should you consider enabling the autoshrink option or is this just trouble?
Stef
View 1 Replies
View Related
May 9, 2002
Details: MSDE 1.0 / SP4 - Windows 2000 Pro
I have a database that has Truncate on Checkpoint set for the Log file. The Log file is set to AutoGrow. Is it necessary to to run dbcc shrinkdb (or the like) to get Log file to contract? Is there any harm in not contracting the Log file? I'm looking for best efficiency and least-likely-to-fail path as DB sits 'really remote' and there is little opportunity for observation.
Does anyone have any recommendations on re-indexing? I have one table that bears the most growth. It has a clustered index. What would be a suitable data point to watch? I run a SP to save DBCC SHOWCONTIG info along with the duration of a test query, but haven't seen a clear breakover point.
TIA -RC
View 2 Replies
View Related
Jun 6, 2001
Does anyone have any benchmarks for the amount of overhead caused by autoshrink of the log and having autostats enabled? We have a customer that insists that turning off these options was necessary to eliminate a performance problem they were having (Query timeouts), but we are not convinced that these two options would have generated enough overhead to have been the root cause (they also rebuilt all their indexes and made some other unspecified changes that more likely solved the problem).
We are hestitant to have them continue with these options disabled because then we need to rely on them to keep the log file shrunk and the statistics updated and because of the data changes during the day, would prefer to have stats updated automatically rather than on a fixed schedule that may not be as appropriate.
Anyway, if anyone has any feedback on overhead generated and potential performance implications of having either of these options enabled, it would be greatly appreciated.
Thanks,
Ray Rankins
View 2 Replies
View Related
Jul 21, 2004
I have a test database and I detach and reattch this database during my testing on my test system. This database is originally from my production system. I have a couple questions that came up during my testing:
1. When you detach/attach a database from a different server with the same setup, does it gather it database setup information from the model database on the current server?
2. When you switch the autoshrink database option from enable to disable, do you have to restart the server?
3. How to you change the default make of the model database?
Thanks in advance for any help.
View 1 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 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
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
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
