September 23, 2009 at 5:46 am
DB:
MB Day1 Day2
Data 732 805
Logs 837 837
This db is growing, is it ok that logs are bigger than data? If not how can I fix it.
September 23, 2009 at 6:02 am
if the db is in full or bulk logged mode are you backing up the transaction log?
a reindex might cause your log to match the size of the data
---------------------------------------------------------------------
September 23, 2009 at 6:16 am
As george said, f you're on the "full" recovery model and you do not take any log backups, the log will grow and grow...
September 23, 2009 at 6:51 am
Krasavita (9/23/2009)
DB:MB Day1 Day2
Data 732 805
Logs 837 837
This db is growing, is it ok that logs are bigger than data? If not how can I fix it.
If you want to get the answer how to fix it please give us more details.
1. What SQL Server version do you have?
2. What is a Recovery model of your database?
3. What is your backup strategy on this database?
September 23, 2009 at 6:51 am
Yes, I am running transaction job every 15 minutes and integrity Checks, and Reorganization of Indexes once a week. So the Reorganization of Indexes job ran last night and brought db to
MB
Data 805
Logs 130 is this beacause Reorganization of Indexes job ran?
September 23, 2009 at 6:56 am
if you type in
dbcc sqlperf(logspace)
what are your results?
if your backing up the log you should be able to minimize the log growth, but if the log does need to grow backing up the log will only free up space within the log file itself.
I had one database that was around 4 GB, before i came along the previous dba had let the log get to 9 GB
they were sitting on different drives on a san that had around 300 GB free space. I could shrink the transaction log to smaller but with all that drive space it really didn't matter. Shrinking it would have made it so I couldn't have used the restore option using the logs had I needed to.
now i've got other db's that i've shrunk the log cause the system is crowded and disk space is of high demand, but it will not negatively impact the DB if the log size is bigger.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
September 23, 2009 at 7:00 am
This is what I have:
Database NameLog Size (MB)Log Space Used (%)Status
Db1 130.3672 5.191167 0
September 23, 2009 at 7:09 am
Good deal, i've got a nice little script i use to see the db size and log size and % used (*this is only good on 2005 & 2008, you'd need to put this in a temp table for 2000)
declare @myTable as table (
[myID] [int] IDENTITY(1,1) NOT NULL,
fileid varchar(4),
fileGroup varchar(4),
TotalExtents int,
usedExtents int,
DBName varchar(100),
filename1 varchar(max))
insert into @myTable
exec ('sp_msforeachdb @command1="use [?]; dbcc showfilestats"')
select
((SUM(usedExtents)*64)/1024) as DatabaseUsedSpaceinMB,
((SUM(totalExtents)*64)/1024) as databaseSizeinMB
from @myTable
select DBName, usedExtents, TotalExtents,
((usedExtents*64)/1024) as DatabaseUsedSpaceinMB,
((totalExtents*64)/1024) as databaseSizeinMB
from @mytable
dbcc sqlperf(logspace)
hope this will be useful
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
September 23, 2009 at 7:20 am
Thank you very much, but still why my logs got smaller,is this beacause Reorganization of Indexes job ran?
September 23, 2009 at 7:30 am
log files will only get smaller if a shrinkdatabase or shrinkfile command is issued.
---------------------------------------------------------------------
September 23, 2009 at 7:33 am
Agreed.
I remember in SQL 2000 that you could detach or take offline a db, and when you reattached it or brought it back online if you moved the log file it would create a new one that would be the default size of 1 MB or whatever the default for the db was.
either way, I don't think there is anyway that it could have been shrunk without some kind of interaction.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply