January 12, 2009 at 2:21 pm
GilaMonster (1/12/2009)
The_SQL_DBA (1/12/2009)
USE msdbSELECT TOP(30) OBJECT_NAME(object_id), rows
FROM sys.partitions
ORDER BY rows DESC
How's that going to find what's filled the transaction log up?
To check for any queues in the msdb on account of any Db mail or service broker activity...
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 12, 2009 at 3:02 pm
Thanks,
there is NO reindexing job is running. We are taking logbacks twice a day 10am and 6pm for user databases and only full backup for system databases. we have only one user database 'BizTalkMsgBox'
I just took the log back of msdb. Can I shrink the log file to a minimum value?
January 12, 2009 at 11:58 pm
You can shrink it. I wouldn't do it to a minimum value, the log will probably have to grow again if you do.
If you don't need or want point-in-time recovery of msdb, set the recovery to simple, or this may happen again.
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
January 13, 2009 at 3:00 pm
Thanks Gail,
I would like shrink the log file of MSDb to minimum of keeping 500MB and want to keep in full recovery only to see if it again increases.what is exact script I need to run to keep 500mb left in msdb?
January 13, 2009 at 5:49 pm
DBCC SHRINKFILE (N'MSDBLog' , 500)
January 13, 2009 at 6:51 pm
I did this already DBCC SHRINKFILE (N'MSDBLog' , 500) . But the log file size still showing as 17Gb after shrinking the file!!!
January 13, 2009 at 10:22 pm
Shrinkfile does not work every time. Find out why the log grew so big first then you can fix it. If you said you have the database in Full recovery model, why aren't you backing up the transaction logs?
If there are no transaction log backups taken for a database in full recovery mode, the database will be in pseudo simple recovery state. For this time around change the recovery to simple and see if it works...I would go for any ideas others might have in finding why it grew so large in the first place..
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 13, 2009 at 10:58 pm
madhu.arda (1/13/2009)
I did this already DBCC SHRINKFILE (N'MSDBLog' , 500) . But the log file size still showing as 17Gb after shrinking the file!!!
How full is the log file? Have you done a log backup on it or switched it to simple?
What does DBCC SQLPERF(LogSpace) return for that 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
January 13, 2009 at 11:41 pm
a log file can only shrink up to the last virtual log file it has in use.
This script will write dummy data to the log file, until it's first VLF has been rolled over to the beginning of the file.
(So it has a loop because it just tries until it succeeds or the timeout value has been reached)
THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
-- Shrink_TrxLog.SQL
--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles
-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***
SELECT @LogicalFileName = 'logicalname', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 100 -- in MB
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2009 at 6:46 am
I think the question that needs answered the most is the earlier one about whether the log file ballooned overnight or has just grown over time because you aren't doing log backups...you said your MSDB is in full recovery (good) but you aren't doing log backups (BAD)...without log backups the log will never truncate no matter how many full backups you do.
AndyG
January 14, 2009 at 9:50 am
AndyG (1/14/2009)
I think the question that needs answered the most is the earlier one about whether the log file ballooned overnight or has just grown over time because you aren't doing log backups...you said your MSDB is in full recovery (good) but you aren't doing log backups (BAD)...without log backups the log will never truncate no matter how many full backups you do.
Right said Andy.. madhu you need to reason why there is no log backups for this DB in full recovery mode
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 15, 2009 at 3:15 pm
If you are using mirroring on this server, then you have to check your queue tables on your msdb databases. You have to delete irrelevant logs on these queue tables.
I hope it helps
July 19, 2009 at 11:03 pm
My 'msdb' datafile was aroung 36GB.. and didnt found any of huge tables, and server has more than 800 DTS packages. Biggest table noticed is 600MB its sysdtspackages...
what else im missing......
July 22, 2009 at 2:06 am
Things like dbmirroring, dbMail, or Service broker may cause msdb to grow.
e.g.
SELECT @@servername as Q_001_MSDB, COUNT(*) as nRows from msdb.sys.transmission_queue ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 23, 2009 at 8:43 am
LOTS of other things make MSDB grow, such as backups, restores and job executions. IIRC, isn't msdb set to SIMPLE mode by default during installation?
In any case the tlog certainly won't shrink until you do a backup of it. You can do backup log msdb with truncate_only if you don't care about recoverability of the kajillions of things in there (which you probably don't). THEN you can try to shrink it. Still may need to do the script that someone else posted to wrap the virtual log boundary around to the start of the file though.
Note that there are system stored procedures (and even maintenance plan tasks) that can eliminate old backup/restore/job history stuff.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply