I have a site that has their log file at 40 gig (running SQL Server
2005).
The database is set to FULL recovery model, and transaction log
backups are performed every 3 hours.
The log file is HUGE compared to usual and I need to shrink it down.
However, it has no free space in it which is very strange considering
we are backing up the logs every 3 hours.
I have full backups occuring nightly.
I also tried switching to SIMPLE recovery model and shrinking the log
file.
Obviously this doesn't work as there isn't any free space in the log.
The site did have some db corruption issues a few weeks ago due to a
SAN issue.
The hardware has been repaired as well as the corruptions (they were
index corruptions so we rebuilt the indexes).
Could this have caused the transaction log to have issues to and not
free comitted transactions?
We have many other sites who have small transaction logs and I am at a
loss as to why it is so large.
The database is also 40 gig (and thus backups are 80+ gig in size!)
Anything I can try?
I am concerned about trashing the log by detaching / reattaching the
db without the log file as it seems brutal, but is that the only
choice?
Thanks,
David
Hi David
Check to see if there are open transactions with DBCC OPENTRAN
John
"David Conte" wrote:
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||How have you determined that there is 'no free space in it'? Did you
execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
reduce in physical size? There is some very complicated stuff about the
internals of the log file that can prevent it from shrinking (much) even if
it has virtually no information in it. Search the web for sql server log
file shrink and you will find a number of helpful scripts to get over this
situation.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"David Conte" <davconts@.gmail.com> wrote in message
news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
> Hey all,
> I have a site that has their log file at 40 gig (running SQL Server
> 2005).
> The database is set to FULL recovery model, and transaction log
> backups are performed every 3 hours.
> The log file is HUGE compared to usual and I need to shrink it down.
> However, it has no free space in it which is very strange considering
> we are backing up the logs every 3 hours.
> I have full backups occuring nightly.
> I also tried switching to SIMPLE recovery model and shrinking the log
> file.
> Obviously this doesn't work as there isn't any free space in the log.
> The site did have some db corruption issues a few weeks ago due to a
> SAN issue.
> The hardware has been repaired as well as the corruptions (they were
> index corruptions so we rebuilt the indexes).
> Could this have caused the transaction log to have issues to and not
> free comitted transactions?
> We have many other sites who have small transaction logs and I am at a
> loss as to why it is so large.
> The database is also 40 gig (and thus backups are 80+ gig in size!)
> Anything I can try?
> I am concerned about trashing the log by detaching / reattaching the
> db without the log file as it seems brutal, but is that the only
> choice?
> Thanks,
> David
>
|||Thanks for the info John and Kevin.
I didn't think that there could be an open transaction.. so tried DBCC
OPENTRAN.
Running DBCC OPENTRAN gives:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Sounds like this has to do with replication? (excuse my ignorance
here!)
We don't have replication setup.
Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
log file via Management Studio it gives the free space percentage
there before attempting the shrink. Just to confirm, I ran the SQL and
it gives:
41297.24 99.79205% full
Thanks
David
On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:[vbcol=seagreen]
> How have you determined that there is 'no free space in it'? Did you
> execute dbcc sqlperf(logspace)? or simply try to shrink it and it didn't
> reduce in physical size? There is some very complicated stuff about the
> internals of the log file that can prevent it from shrinking (much) even if
> it has virtually no information in it. Search the web for sql server log
> file shrink and you will find a number of helpful scripts to get over this
> situation.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
> "David Conte" <davco...@.gmail.com> wrote in message
> news:1194500150.103297.201900@.y27g2000pre.googlegr oups.com...
>
>
>
|||Hi
What does the DBCC command give after you backup the log?
Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
should not shrink it to a size where under normal operations it will expand.
John
"David Conte" wrote:
> Thanks for the info John and Kevin.
> I didn't think that there could be an open transaction.. so tried DBCC
> OPENTRAN.
> Running DBCC OPENTRAN gives:
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Sounds like this has to do with replication? (excuse my ignorance
> here!)
> We don't have replication setup.
> Kevin - I hadn't run dbcc sqlperf(logspace) however when shrinking a
> log file via Management Studio it gives the free space percentage
> there before attempting the shrink. Just to confirm, I ran the SQL and
> it gives:
> 41297.24 99.79205% full
> Thanks
> David
>
> On Nov 9, 12:43 am, "TheSQLGuru" <kgbo...@.earthlink.net> wrote:
>
>
|||Log still shows minimal free space even after transaction logs.
Hence, running DBCC SHRINKFILE does nothing as there is no free space
to be recovered.
This whole thing smells like a bug.
If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
is "REPLICATION".
Again, this database (or any other databases on the server) has never
had replication setup!
I tried running exec sp_removedbreplication - it completes, but the
log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
gives:
Transaction information for database 'xxxxx'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42372:54:2)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I think we are going to resort to setting up replication on it, run
sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
David
On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> What does the DBCC command give after you backup the log?
> Use DBCC SHRINKFILE to shrink the file rather than Enterprise Manager, you
> should not shrink it to a size where under normal operations it will expand.
> John
|||Hi David
I am not sure how that has occurred, but you could try rebuilding the log
file.
John
"David Conte" wrote:
> Log still shows minimal free space even after transaction logs.
> Hence, running DBCC SHRINKFILE does nothing as there is no free space
> to be recovered.
> This whole thing smells like a bug.
> If I run SELECT * FROM sys.databases, it says the log_reuse_wait_desc
> is "REPLICATION".
> Again, this database (or any other databases on the server) has never
> had replication setup!
> I tried running exec sp_removedbreplication - it completes, but the
> log_reuse_wait_desc is still "REPLICATION" and DBCC OPENTRAN still
> gives:
> Transaction information for database 'xxxxx'.
> Replicated Transaction Information:
> Oldest distributed LSN : (0:0:0)
> Oldest non-distributed LSN : (42372:54:2)
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I think we are going to resort to setting up replication on it, run
> sp_repldone NULL, NULL, 0, 0, 1 and finally remove the replication...
> David
> On Nov 9, 7:30 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
>
|||Ok well I found a forum post here on an identical issue.
Sounds like it is indeed an SQL Server bug.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
The workaround (which I've managed to do on a test restore of the live
database which experiences the same issue) is to setup replication on
the database, then remove the publication. After this there was now
99% free space in the logfile and I was able to shrink it from 44 GB
to 1 GB.
DBCC OPENTRAN now says there are no active transactions, and the
log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
So looking good again.
Now to do the process on the live database
Thanks everyone for their time!
Dave
|||Hi David
According to the post the bug may have been fixed in SP2, if you are not
running this version you may want to upgrade to SP2 plus hotfixes.
John
"David Conte" wrote:
> Ok well I found a forum post here on an identical issue.
> Sounds like it is indeed an SQL Server bug.
> http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
> The workaround (which I've managed to do on a test restore of the live
> database which experiences the same issue) is to setup replication on
> the database, then remove the publication. After this there was now
> 99% free space in the logfile and I was able to shrink it from 44 GB
> to 1 GB.
> DBCC OPENTRAN now says there are no active transactions, and the
> log_resuse_wait_desc for the db is "NOTHING" instead of "REPLICATION".
> So looking good again.
> Now to do the process on the live database
> Thanks everyone for their time!
> Dave
>
No comments:
Post a Comment