November 1, 2010 at 1:51 am
Hi my name is Ryan, I have a database let's just called Data_All_DB. In a glance my size description for the database is :
Data : Data_All_DB, size : 832 MB
Log : Data_All_DB_log, size : 2255 MB
I want to shrink the log file using this script :
backup log [Data_All_DB] with truncate_only
dbcc shrinkfile([Data_All_DB_log] , truncateonly)
alter database [Data_All_DB]
modify file(name=[Data_All_DB_log], size =10)
but is not working, all I get is :
Cannot shrink log file 2 (Data_All_DB_log) because of minimum log space required.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 5039, Level 16, State 1, Line 6
MODIFY FILE failed. Specified size is less than current size.
Can you guys help me, please. Thanks In advance 😀 .
Regards,
Ryan
November 1, 2010 at 3:17 am
It is not recommended to truncate the transaction log. Instead initiate a Transaction Log backup for that database and then issue SHRINKFILE on the transaction log file.
Some good information about transaction logs.
http://www.sqlservercentral.com/articles/64582/[/url]
http://technet.microsoft.com/en-us/library/ms345382(v=SQL.90).aspx
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 1, 2010 at 3:18 am
If I'm not mistaken, there's no space in the log to be released. Btw, truncateonly is NOT a valid option for shrinking a log file. It's for data files only.
If the DB is in full recovery are you doing your log maintenance properly? What's preventing the log from being reused? (check log_reuse_wait_desc in sys.databases)
After you do this, you need to take a full or diff backup to restart the log chain (if you're in full recovery).
Take a look through this article - http://www.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2010 at 3:43 am
First of all, thanks for all the replies. For information I already use this command :
Alter Database Data_All_DB Set Recovery Simple
GO
Alter Database Data_All_DB Set Recovery Full
GO
DBCC SHRINKFILE ('Data_All_DB_log', 1)
GO
but that doesn't work either, my Data_All_DB_log file is still 2255 MB. Did I miss something in here ? 🙁
And by the way I use SQL Server 2005 for my database engine.
Kind Regards
November 1, 2010 at 3:48 am
GilaMonster (11/1/2010)
If the DB is in full recovery are you doing your log maintenance properly? What's preventing the log from being reused? (check log_reuse_wait_desc in sys.databases)
How much of the log is in use? (DBCC SQLPERF(LogSpace))
p.s. Don't shrink the log to 1 MB. Shrink to a reasonable size for the size and activity in the DB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2010 at 9:03 pm
Hi GilaMonster, thanks for the replies. I already did the query that you gave me which is :
(DBCC SQLPERF(LogSpace))
and the result is :
Database NameLog Size (MB)Log Space Used (%)Status
-----------------------------------------------------------------
Data_All_DB 2542.055 93.32153 0
So what does the result mean and what should I do next, sorry if my question is a lil bit annoying and confusing, but this problem is really matters to me and I still haven't figure it out the outcome yet.
Thanks
November 1, 2010 at 11:40 pm
The log is 93% full. Hence there is no way that you are going to be able to shrink it as it currently stands. You need to figure out what is preventing the log from being reused first.
Once more with feeling...
What's preventing the log from being reused? (check log_reuse_wait_desc in sys.databases)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 1, 2010 at 11:57 pm
GilaMonster (11/1/2010)
Once more with feeling...What's preventing the log from being reused? (check log_reuse_wait_desc in sys.databases)
Hi, GilaMonster I'm sorry if I missed out your word as I quoted above, really sorry, I already did check using this query :
SELECT DBName=name, log_reuse_wait_desc
FROM sys.databases
WHERE log_reuse_wait_desc != 'NOTHING'
ORDER BY database_id
And The result is :
DBName log_reuse_wait_desc
--------------------------------------------------------------------
Data_All_DB REPLICATION
Is that something got to do with why I cannot shrink or lower the initial size of the my database log file ?
I have once shrink a database log file that was used for replication and that went well, but why this one causing a problem ?
Thanks in advance
November 2, 2010 at 12:04 am
Is there supposed to be replication in this DB? Is there an active transactional replication publication?
What does DBCC OPENTRAN return?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2010 at 12:26 am
GilaMonster (11/2/2010)
Is there supposed to be replication in this DB? Is there an active transactional replication publication?
Yes, this database is a subscriber from another server and also a publisher to another server. For publishing and subscribing, I already made a schedule for it, so if there's no schedule for this hour to run some replication transaction it shouldn't have any replication transaction.
GilaMonster (11/2/2010)
What does DBCC OPENTRAN return?
after I run that query the result is this :
Transaction information for database 'Data_All_DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (494:8075:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So what does it mean ?
Thanks
November 2, 2010 at 1:57 am
ryan.reinaldi (11/2/2010)
GilaMonster (11/2/2010)
Is there supposed to be replication in this DB? Is there an active transactional replication publication?Yes, this database is a subscriber from another server and also a publisher to another server
Well your log reader isn't running and, from the opentran info has never run. Hence if this is supposed to be publishing it's not set up correctly or someone has manually disabled or stopped the log reader.
With transactional replication the log reader should run continuously. You need to investigate what's wrong with the log reader or drop the publication on this server and set it up again correctly
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2010 at 2:34 am
GilaMonster (11/2/2010)
Well your log reader isn't running and, from the opentran info has never run. Hence if this is supposed to be publishing it's not set up correctly or someone has manually disabled or stopped the log reader.
Can you please tell me how can I check the log reader is working correctly or not ?
Kind regards
November 2, 2010 at 3:18 am
Replication Monitor
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2010 at 1:08 am
Hi GilaMonster, I finally found how to shrink the log file. First I run the dbcc opentran query first to check if there's any active transaction running on my database.
Considering the result was like this below :
Transaction information for database 'Data_All_DB'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (494:8075:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
so I ran this query :
EXEC sp_repldone @xactid = NULL,
@xact_segno = NULL, @numtrans = 0,
@time = 0, @reset = 1
After I ran it, I check again using dbcc opentran, and the result is :
No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So after that I shrink my log file using dbcc shrinkfile and it worked !!!! Finally.
from what I read is, the sp_repldone command was used to end all transaction related to replication if I'm not mistaken, so it solved hehe 😀 .
But thanks again though, for all your replies and guidance thanks a lot, I really appreciate it man.
Kind Regards,
Ryan
November 3, 2010 at 2:30 am
Sorry to ruin your day, but it may not solved, just fixed for now. You've marked all current transactions as replicated, but any new transactions may still get marked as to-be-replicated and start filling the log again.
In a few hours, check DBCC OPENTRAN again, check the log reuse wait again and see if there's still non-distributed transactions/replication listed. If so, then until you can figure out how to remove the replication permanently (may well require adding/dropping publications in script) you'll have to regularly schedule a run of sp_repldone to keep things under control.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply