Shrinking A Log File Every Night
Dec 13, 2007
I have a relatively high usage 2005 database whose transaction log grows every day.
Untill now I have been manually shrinking the log files .. but I would like to automate this process.
In the maintenance schedule that runs every night
1 Do a full backup to disk
2. Do a transaction log backup to disk
3. run the following TSQL
USE [MyDB];
GO
ALTER DATABASE MyDB
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (MyDB_log, 3000)
GO
ALTER DATABASE MyDB
SET RECOVERY FULL;
GO
4. Backup disk files to tape.
however it doesn't work !! the TLOG file does not shrink, even though its the same as the manual job that I run?
There is nothing in the SQL logs .. but in the event log I get
Event Type: Error
Event Source: SQLISPackage
Event Category: None
Event ID: 12291
Date: 13/12/2007
Time: 00:50:10
User: NT AUTHORITYSYSTEM
Computer: ALGSQL1
Description:
Package "MyDB_FULL_BACKUP" failed.
For more information, see Help and Support Center
any ideas ?
View 8 Replies
ADVERTISEMENT
Apr 25, 2002
I have a 13 Gig Log File with only 121 Mgs of space used. I have run the DBCCSHRINKFILE command and it has shrunk it by about 100 Mgs. Why can't I get it to shrink to a reasonable size.
View 2 Replies
View Related
Sep 18, 2001
Have a 6G data file in a single filegroup.
Using dbcc shrinkfile to shrink.
Does not have any effect.
Any ideas?
View 1 Replies
View Related
Sep 15, 2000
Hi, is there any other way to shrink the log file other than truncating every time it gets bigger... I have a 800MGs database and the log is getting as big as 11Gigs... any suggestions other than truncating it? Is there a way to set it off?
View 1 Replies
View Related
Nov 29, 2000
I'm trying to shrink the log file. I have used the suggestions that I have seen posted previously. This is the script I've been using.
DECLARE @DB VARCHAR(40)
SELECT @DB = 'PBDSSTEST'
CHECKPOINT
EXEC('DBCC SHRINKFILE ('+@DB+'_log, 1, NOTRUNCATE)')
EXEC('DBCC SHRINKFILE ('+@DB+'_log, 1, TRUNCATEONLY)')
CREATE TABLE t2 (char1 char(4000))
DECLARE @i int SELECT @i = 0 WHILE (@i < 300) begin INSERT t2 values ('a') SELECT @i = @i +1 END DROP TABLE t2
BACKUP LOG PBDSSTEST TO PBDSSTESTLOGBACKUP
Before I ran this the used portion of the log was 1795.12 MB, the free was 1570.50 MB for a total of 3365.62 MB.
After completing this the used portion was 25.62 MB, the free space was 3263.50 for a total of 3289.12 MB. It only srhunk about 26 MB. Why won't it shrink it anymore. There was plenty of data in the used at the time of the shrinkfile command so I would have thought it would have shrunk it a lot more.
Please help. I don't understand why it won't shrink it anymore than it did.
View 4 Replies
View Related
Oct 12, 2004
When I try to shrink the log file for my databases I am getting following message.
Cannot shrink log file 2 (cm_archive_mp_Log) because all logical log files are in use.
what should I do to successfully shrink the log file.
Thanks.
View 1 Replies
View Related
Nov 8, 2006
how can we shrink the log file to the required size without affecting the live database .....
Thanks in advance
View 2 Replies
View Related
Aug 29, 2007
I have a problem with shrinking a log file on SQL 2000. The log file has grown to 25gb (the db is 30gb) . The DB was in simple recover mode but I have changed it to full so that the log file can be backed up. I have run the dbcc shrinkfile on the log file several times (plus log file backups) and it has increased the logfile size. Each time I have run shrinkfile I get €˜Cannot shrink log file 2 (db_Log) because all logical log files are in use€™ message. I have used the dbcc loginfo command and this shows that all the virtual log files are in use. The db also publishes 4 snapshot replications. Any suggestion for how I can easily shrink the log file?
thanks
Simon
View 5 Replies
View Related
Aug 13, 2001
I have a database that was loaded with large amounts of data before today. I backup up the db and trans log. presently the DB file is 328 while the trans log show 428 meg with only 28 meg being used. If I try to shrink either the database or the log files, the log file wil not shrink.. It tells me to set it to a minimum of 28 meg but still not shirnk. I realize this was a problem in 7, any quick fixes, in a hurry. Running SQL 2000 SP1
View 1 Replies
View Related
Oct 16, 2007
I'd like to create a stored procedure to automatically shrink the transaction log file on a certain database monthly. As I am not the DB admin (it's a web-based db on a remote server), I'd like to put together a script to create the procedure so I can just send it to the DB admin to run. It seems like it should be pretty easy, but I'm still pretty new to SQL server. Is anyone able to give me some pointers?
View 1 Replies
View Related
Jun 14, 2006
Hi,
What is the difference between shrinking a DB with that of a file.
When you select a DB to shrink will it not shrink all the files in it.
Because when I select Files to shrink it does not prompt me with
a specific file.
So I am confused as to why SQL server 2K5 asks if you need to shrink DB or FILE
Tnx
View 1 Replies
View Related
Nov 17, 2006
Hello,
I'm having couple of DB that are mirrored.
my concerne is regarding the Log file size.
I'm running the following steps:
BACKUP DATABASE [DBName] TO DISK = N'Backup_File' WITH NOFORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
then
BACKUP LOG [DBName] TO DISK = N'Backup_File' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10
if I try to run a SHRINKFILE (DBName_log, 20)
I receive the following info:
Cannot shrink log file 2 (DBName_log) because all logical log files are in use
I'n a bit tense using a
BACKUP LOG [DBName] WITH TRUNCATE_ONLY
as it's part of a mirroring.
but I need to reduce the size of the log file.
thank four your suggestions
Eric
View 10 Replies
View Related
Jan 27, 2008
please advice me what does .ldf file consist of and can i shrink the .ldf , is it adviceble to shrink after the backup and how frequently it can be done on a production db
please advice me ,can i shrink the .mdf , it is adviceble to shrink after the backup and how frequently it can be done on a production db
Thanks
View 4 Replies
View Related
Dec 3, 2007
(SQL 2005) I have a few databases that are currently hovering at around 150GB. (The following only concerns the .mdf) Of this total size, only about 90GB is real data. The other 60-70 is data from unecessary audit tables. Recently, we've been having some space issues on a 1.7TB server. We've decided that deleting these audit tables and reclaiming the 60-70GB per database is the route we're going to take.
What we did: DELETE <table> (per audit table). Now, it shows that the database size is ~90GB and there is the 60-70GB of unallocated space.
Then, we ran a DBCC SHRINKDATABASE (db_name) overnight. Checked space the next morning and only about 10GB had been reclaimed. We've tried a number of other alternatives of which, none have been successful.
(DBCC SHRINKFILE(file, space), drop and recreate tables, etc.)
Can anyone think of any reason why these commands would not be working? If I can provide any additional information, please let me know.
Thank you in advance for any input!
View 5 Replies
View Related
Jan 8, 2007
Hi,
I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.
Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.
Why are all LVFs still active after detach/attach and checkpoint?
How can I shrink the LDF file?
Thanks.
Alec
View 1 Replies
View Related
Jul 20, 2005
Is there a fairly quick and dirty way to shrink the file size allowedfor a DB on SQL server after the DB is in use?Our old DBA allowed 3 gb for log files and we don't need nearly thatmuch. However, the space is still being used and reserved. Can Ishrink that "maximum" and clear up the space?I don't want to just copy all the tables and primary keys and open anew one with the new data, though that is plan A right now.I cant just change sysfiles, it yells at me.
View 1 Replies
View Related
Apr 19, 2007
I've been searching and reading for best practices on how to maintain a reasonable log file size while engaging in Log Shipping. I have found plenty of information on these subjects on their own, but not on how best to shrink your log size without breaking your log shipping integrity.
I apprecaite your help.
View 1 Replies
View Related
Jan 31, 2002
On a SQL Server 7.0 database I support, I've been unsuccessful trying to shrink a data file using dbcc shrinkfile (datafile_logical_name, 0). This worked fine for shrinking the log, but of the 4 datafiles that were created 2 shrank successfully and 2 remain unchanged. Unless the information on the General Tab on Enterprise Manager is incorrect, of the 15000MB allocated for one of the files, only 700 MB are used.
View 1 Replies
View Related
Oct 12, 2002
Gurus,
I have inherited a SQL 2000 database ( (I am new to SQL DBA) and I found this when I was checking the db properites . The transaction log has grown bigger than the actual data file, I thought transaction log backups would truncate the inactive portion of the log file and shrink the transaction log, but it was not the case it seems, may be it was truncating the inactive portion of the log, but not shrinking it. This site does not have a job for truncating the data/log files periodically. What is the best method to deal this situation, how can I shrink the Transaction log quickly?,
All your suggestions are welcome.
TIA,
-Jay
View 2 Replies
View Related
Jul 7, 2004
My intention is to include a sql job to schedule for shrinking log files to half of its original log file size (if half of their original size is greater than 5MB)
The code is below. But while executing the code it is keeps on running indefinitely. can somebody throw some light what cause it to go into an indefinite loop.
Thanks
Babu
code:
====
DECLARE db_file_name CURSOR FOR
SELECT name , ceiling(size/128.0) * 0.5
FROM master..sysaltfiles
Where rtrim(lower(filename)) like '%.ldf'
DECLARE @f_name varchar(128), @new_f_size int
OPEN db_file_name
FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
WHILE @@FETCH_STATUS = 0
BEGIN
If ( @new_f_size > 5)
BEGIN
DBCC SHRINKFILE ( @f_name, @new_f_size )
FETCH NEXT FROM db_file_name INTO @f_name,@new_f_size
END
END
CLOSE db_file_name
deallocate db_file_name
View 6 Replies
View Related
Aug 23, 2015
We are using Sql server 2012. One of our production database has hight no of vlfs. We are planning to shrink the logfile to reduce the no of VLFs but the database is configured for logshipping.
What is the effect of logshipping when you shrink the logfile?
View 3 Replies
View Related
Apr 20, 2007
Hi there,
I have a large (420GB) database that has never had data archived off before. I taken a backup to a test server and run a script supplied by the product vendor which has removed a large ammount of old data no longer required.
I have checked within enterprise manager that this data has now gone, however the actual file itself has not shrunk in size. Is there a further step I need to take to get back the space.
Kind Regards
View 2 Replies
View Related
Feb 18, 2008
Hi!
I have a database where I add some new Filegroups and moved some Gb's of
tables and indexes to new file groups.
Now I would like to shrink the source of these tables/indexes: the mdf file.
This means there should be much empty space now in the mdf file.
I tried to use dbcc shrinkfile, but it run for more than one day.
Are there some good strategies about shrinking the mdf file?
I had to restart the instanse because got problem using the db while dbcc runned...
Thank you for help
View 2 Replies
View Related
Feb 25, 2015
I have a SQL Server 2012 DB in Full recovery mode that has never had a TLOG backup. the log is huge 200+GB.
I tried doing a transaction log backup but there is not enough space on the Disk.
How can I reclaim this log space in SQL Server 2012?
View 6 Replies
View Related
Apr 12, 2002
Please help if you can! I have setup a job that runs every day on an hourly basis. Every morning I find that it's been disabled. The funny thing is is that the schedule is disabled, but the job is not (you see 'Enabled' in the jobs list in EM, but when you view the schedules tab, it's disabled.) Also, it runs a several times before becoming disabled.
TIA,
Colleen
View 2 Replies
View Related
Dec 27, 2003
How do I return the updated value to client for this update?
UPDATE table SET x = x + 1 WHERE idvalue = y
View 1 Replies
View Related
Sep 21, 2007
We get the following errors at the same times every night (between about 3AM and 4AM) on our distributor (ss2000). The publisher and subscribers do not log anything unusual during this time period but are busy. The distribution agents time-out with:
Timeout expired (Source: ODBC SQL Server Driver (ODBC); Error number: S1T00)
---------------------------------------------------------------------------------------------------------------
exec sp_MSadd_repl_commands27hp 2, N'pacefs', 0x0013D1040000A2BF00010013D119000008080142000003D70002F4A10000001E0003A1000000003E01000029007B00430041004C004C002000730070005F004D00530069006E0073005F00740062006C005F0063006F006E00760065007200730069006F006E005F006400610074006100200028003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F0029007D00060000004C007B00370030004300460037004100420041002D0041003600430034002D0034003300340045002D0039003500310045002D003300360037003000410044003500320036004500330035007D000C00050000005765656B310C00000C00000043757272656E744D6F6E746801000000010000000C000000060000003173742054440100010000000C00000008000000484C4320576573740C00000009000000526566696E616E6365010000480000000C000000050000004E61696C730C000004000000323239340C000000090000004A656464204C6172610C00000C0000004C656E64696E6720547265650C000000000000000C00000007000000333431353338320C0F0000003239353433323330202020202020200E00002E0032003000300037002D00300039002D00310030002000310032003A00350036003A00330034002E003400360030000C000B0000004C454E44494E47545245450C130000004C454E44494E4754524545204E4554574F524B0C09000000524546494E414E43450100000000000001000000000000000713040016003400300032003000300030002E0030003000300030000713040016003400300035003000300030002E00300030003000300001000000540000008E01000000000000010000000000000001000000000000000C000000090000004A656464204C6172610C0000050000004E61696C730C000008000000484C4320576573740C000000060000003734363937330C0002000000464C0C0000000000010000002005000001000000480000000100000001000000010000000000000001000000000000008E, 0x0013D1040000A2BF00010013D119000008080142000003D70002F4A20000001E00033D000000003E01000029007B00430041004C004C002000730070005F004D00530069006E0073005F00740062006C005F0063006F006E00760065007200730069006F006E005F006400610074006100200028003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F002C0020003F0029007D00060000004C007B00370031003500370037004500420031002D0034003200450035002D0034004300420041002D0039004600320036002D003300360037003000420030003400450039004200370030007D000C00000000000C000000070000004D6F6E74683131810C0000000A000000556E61737369676E6564818C0C00000006000000457175697479810C040000004E6F6E650C000000030000003931360C0C0000004E69636F6C61205365
exec sp_MSadd_distribution_history 7, 3, N'The process is running and is waiting for a response from one of the backend connections.', 0x0000000000000000000000000000, 0, 0, 0.00, 0x00, 1, 0x0000000000000000000000000000, 0, 0x01, 0x01
This is driving me crazy as there appears to be little to go on and I don't know what to do next.
Any useful suggestions?
Thanks,
Michael
View 3 Replies
View Related
Oct 10, 2007
Hi,
Some of you might remember me posting a question about importing data from a csv. Well I've managed to do that now by writing a vb.net service.
Now all I need to do is have some kind of method of exporting the data as a report via email every night.
The tables are as follows...
CDetails
cNumber
ClName
FE
Disb_1
cNumber
type
dateReq
payee
amount
vat
total
chequeReq
Disb_2 - Disb_20
"Same as Disb_1"
The report structure I need is...
cNumber ClName FE type dateReq payee amount vat total chequeReq
With data from each disb on a new line, i.e. cNumber could be the same for 20 rows! (don't blame me, it's what they want!)
The records should only be shown though when the dateReq is equal to today. I think I can do that with the DateDiff function.
The problem I have is knowing how to set the report up and what to do it in, Reporting services or Business Intelligence. The report only has to be an email, would it even be simpler to write an extra class into my service?
Steve
View 6 Replies
View Related
Sep 10, 2005
Hi,Sorry for the obscure title but I'm afraid I can't think of a betterway to describe what happened to one of my clerks last night. The guy wasworking late, made a series of changes (accross a number of tables with adependant relationship structure - i.e. a customer linked to an order linkedto an invoice linked to a payments etc.) Now when he came back this morningnone of the work he did last night was still there. I'm by no means asql-pro - but I've managed to make do so far. Here's what I know:1) All of our records in all of our tables have date/time stamped. Once whenthey go in and once when they're changed. So with a little work I can buildup a good picture of what usage our database gets at what times. I've pulledup a query and there is a big fat hole between 8pm-10pm - stuff thathappened before that is still there, stuff that happened after that is stillthere - but all the changes that were put in between then just aren't thereany more. So it's not just insertions but updates as well.2) There is no perceivable break in our identity columns. So despite thefact that I know he entered 7 new customers, I can go back through thecustomers table and look at where those customers should sit visa vie entrytimes - and it's just as though the customers he entered never existed -sql server just keeps incrementing sequentially and there's no break - sothe last customer entered at about 6:50 pm was something like number 11912and the customers entered this morning have numbers that carry on from11913.3) I'm running in a shared environment - the server is hosted by awebhosting company (who shall remain nameless unless I can prove it's theirfault!) based in the US - the server has 100's of other Users databasesrunning on it.I just don't know where to begin diagnosing this sort of problem. But it hasme really running scared. It's not the first time something like this hashappened to me (i've noticed it once before when I had to get a connectionkilled by the people who manage our server because of a long-running querythat seemed to have just got bunged up and was locking a key table) - butthat was just a few records changed by the user who's connection got killed.Nothing like this... but it's pretty scary - I've got no way of knowing thatI'm losing changes like this on a continuous basis. It's my worstnightmare - like a pipe leaking underneath a floor board - and you only findout when the water starts running down the stairs!Has anyone got any ideas? Any starting places? Anyone else had anything likethis happen to them before?ThanksNick
View 5 Replies
View Related
Jan 12, 2007
With Vista Ultimate (release), I installed SQL server 2005 Developer edition. I installed SP1, apparently successfully. But my Windows update history shows the SP being installed successfully every night. I have automatic updating turned on (via AD policy).
Has anyone seen this, and does anyone know how to fix it?
Thanks in advance.
Peter
View 1 Replies
View Related
May 12, 2015
There is a database "Foo" sitting on server "A". There is a database "Bar" sitting on server "B". A.Foo publishes a subset of its schema. B.Bar subscribes to A.Foo's publication. The distribution database is on "B" (B.distributor). This a push subscription (transactions are pushed to the subscriber from the distributor). Every day (including the weekend) I get the following alert:
"5/12/2015 3:53:16 AM, Unsubscribed Transactions (Count) on "B" is Warning.
SQL Server instance "B" has 636771 unsubscribed replication transactions received by the Distributor and not received by a Subscriber.
Unsubscribed Transactions (Count): Number of replication transactions received by the Distributor and not received by a Subscriber."
The number of transactions will vary. The alerts will be sent between 1:20 AM (EST) and 3:30 AM (EST). I'm trying to figure out what is causing the backup of transactions. I assume the issue precedes the alerts by 30-minutes or so.
There are no backups occurringNothing is blocking the distributor agent in the subscription databaseJob activity is at a minimum; the few jobs running run throughout the dayThe machine has plenty of resources -- CPU, RAM, etc.The publisher database shows no signs of stress.
View 3 Replies
View Related
Jul 20, 2005
Hi all:I restored one backup database (7.9 GB mdf) on two diffrent servers. Ishrunk them by clicking "Move pages to beginning of file beforeshrinking".After shrinking, one mdf file is 6.7 GB, and the other is 4.2GB. Ishunk again and again:1. the 6.7GB become 5.9GB, 5.2GB, 4.7GB and 4.2Gb (four times)2. the 4.2Gb become 4.0GB (just one more time)It is wired, I am wondering the mdf will be smaller and smaller if Icontinue to shrink them? What is the reason?ThanksWJ
View 1 Replies
View Related
Feb 20, 2001
1) Does shrinking a db have any side affects ? Or this is pretty much a normal
operation ?
2) Also in db options, is it recommended to have auto shrink checked ?
Thanks for your help.
View 1 Replies
View Related