February 9, 2012 at 1:41 am
Hi all.
During the night we have had an issue where some procedures have gone very very wrong.
The result is that both the log and data file has grown way out of size.
Both are now at 1 TB.
We cannot delete or use truncate on these tables beacause of disk space.
According to windows explorer the two disk have less then 10.0 MB space.
The database is set to Simple recovery mode.
I have executed the following:
dbcc sqlperf(logspace)
Db name, Log Size MB, Log Space %, Status
Catalog1036788100,01650
dbcc opentran (Catalog)
No open trans.
dbcc shrinkfile (Catalog_Log, truncateonly)
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
132131314816131072131314816131072
dbcc shrinkfile (Catalog_Log, 0)
DbIdFileIdCurrentSizeMinimumSizeUsedPagesEstimatedPages
132131314816131072131314816131072
Looking at the GUI of that database it says:
size: 2074367,63 MB
Space available: 1495919,69 MB
Any recommandations on what to do?
BR
Dan
February 9, 2012 at 2:07 am
Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/
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
February 9, 2012 at 2:43 am
Thanks for your reply.
I have now read the article and see that I have used part of this.
I did use the DBCC OPENTRAN to find the correct spid.
But I used the activity monitor to find more about it.
Killed it and ran the shrinkfile commands.
At fiirst it was looking good, but still I could not truncate those tables.
After a couple of retries it finally started the process of killing and rollback.
Now I have managed to delete the tables that was wrong.
I am wondering if I should shrink the database. So far I have concluded, after reading some articles, that even though the free disk space is 10.0 MB I should leave the database as is, since my only goal with this shrink would be to get rid of the windows message saying that we are out of space.
According to properties on the spesific database, it now has more then 300 GB available space.
Still I have to say that I was not expecting a log file to grow that much when the database is set to Simple mode.
Dan
February 9, 2012 at 2:55 am
Dan-Ketil Jakobsen (2/9/2012)
I am wondering if I should shrink the database. So far I have concluded, after reading some articles, that even though the free disk space is 10.0 MB I should leave the database as is, since my only goal with this shrink would be to get rid of the windows message saying that we are out of space.According to properties on the spesific database, it now has more then 300 GB available space.
How long, with standard database growth, would it likely take to reuse that 300GB? If more than 4 or so months, do a once-off shrink and rebuild your indexes after.
Still I have to say that I was not expecting a log file to grow that much when the database is set to Simple mode.
Why not? Simple does not mean logs won't grow. It just means log backups aren't required and a small number of operations are minimally logged (bcp, bulk insert, select into, index rebuilds and a few more)
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
February 9, 2012 at 3:13 am
Unfortunately the log file is full again.
Stopping all jobs again. 🙁
Dan
February 9, 2012 at 3:21 am
Dan-Ketil Jakobsen (2/9/2012)
Unfortunately the log file is full again.Stopping all jobs again. 🙁
Dan
You need to identify the queries which are generating these much logs. If you are doing some delete operation, do it in chunks so that log space can be reused.
DELETE TOP (50000) FROM YourTableName WHERE ....
GO
CHECKPOINT
February 9, 2012 at 3:26 am
Dan-Ketil Jakobsen (2/9/2012)
Unfortunately the log file is full again.Stopping all jobs again. 🙁
Then you need to either move your log to a larger drive (or expand the drive) or fix whatever is using so much log space.
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
February 10, 2012 at 5:21 am
Consider getting a professional to remote in to a) help you figure out what the heck is really going on here and stop it and b) get your system functional again!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply