|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
HELP, RAN OUT OF DISK SPACE
SQL2000, one of my databases is not currently under a maintenance plan and
this morning, I've come to find out my D: drive is out of disk space!!! Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see the file Fresno_Log.LDF is over 347GB large!!!!! What do I do to fix this?? We have only 9 mb of file space on this drive. The C: drive has not even 2GB left. Please advise ASAP!!!! Check out
http://support.microsoft.com/default.aspx/kb/272318 Linchi Show quoteHide quote "Rockitman" wrote: > SQL2000, one of my databases is not currently under a maintenance plan and > this morning, I've come to find out my D: drive is out of disk space!!! > > Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see the file > Fresno_Log.LDF is over 347GB large!!!!! > > What do I do to fix this?? We have only 9 mb of file space on this drive. > > The C: drive has not even 2GB left. > > Please advise ASAP!!!! > Thanks for the link. I've tried various shrinkfile commands, with various
desired sizes and so far it has only shrunk the log file to 342GB from the original 347GB. I even tried the TRUNCATEONLY option and it did nothing to the file size. Can you advise a specific command to try and get this guy shrunk alot more please? Show quoteHide quote "Linchi Shea" wrote: > Check out > > http://support.microsoft.com/default.aspx/kb/272318 > > Linchi > > "Rockitman" wrote: > > > SQL2000, one of my databases is not currently under a maintenance plan and > > this morning, I've come to find out my D: drive is out of disk space!!! > > > > Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see the file > > Fresno_Log.LDF is over 347GB large!!!!! > > > > What do I do to fix this?? We have only 9 mb of file space on this drive. > > > > The C: drive has not even 2GB left. > > > > Please advise ASAP!!!! > > 1) pick the proper recovery model. It sounds like you are in full recovery
and either you are not taking log backups at all or you are not doing so frequently enough. Until you figure out your DR needs, I would suggest putting it into full and backing up the log at least once a day (but probably more often). 2) clean up that file. ALTER DATABASE <dbname> SET RECOVERY SIMPLE; BACKUP LOG <dbname> WITH TRUNCATE_ONLY; USE <dbname>; DBCC SHRINKFILE(<dbname>_log, 1024); GO ALTER DATABASE <dbname> SET RECOVERY FULL; GO BACKUP DATABASE <dbname> TO DISK = 'd:\<dbname>.BAK' WITH INIT; 3) get more disk. Disk is cheap. If you need external storage, then get it. 4) set up monitoring. You should never wake up to RAN OUT OF DISK SPACE. Ever. There is no excuse for this. Also read Tibor's article (why you want to do 1) so you can avoid making 2) a habit). http://tr.im/StopShrinking On 6/18/09 11:57 AM, in article 31355DE0-15D4-4CE3-811F-7D6549EEE***@microsoft.com, "Rockitman" <Rockit***@discussions.microsoft.com> wrote: Show quoteHide quote > SQL2000, one of my databases is not currently under a maintenance plan and > this morning, I've come to find out my D: drive is out of disk space!!! > > Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see the file > Fresno_Log.LDF is over 347GB large!!!!! > > What do I do to fix this?? We have only 9 mb of file space on this drive. > > The C: drive has not even 2GB left. > > Please advise ASAP!!!! > OH MY GOD!!! AARON, you are the man!! These commands worked wonders!!
Got this log file back to 1 gig again!! WHoooooooo!! A little history: I am by no means an SQL trained IT guy. I know enough to keep this thing running and that's about it. This database was added recently by another person, and I neglected to add it to my Maintenance Plan. Now it is added and yes, I am already holding in my hands new drives to add to this Dell poweredge server. I also have another dual core CPU to add which will beef this guy up because this server not only has a 2000 instance, but a 2005 one as well. That's another story for another day, but Aaron, once again, thanks a million. I owe you big time!! Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > 1) pick the proper recovery model. It sounds like you are in full recovery > and either you are not taking log backups at all or you are not doing so > frequently enough. Until you figure out your DR needs, I would suggest > putting it into full and backing up the log at least once a day (but > probably more often). > > 2) clean up that file. > > ALTER DATABASE <dbname> SET RECOVERY SIMPLE; > > BACKUP LOG <dbname> WITH TRUNCATE_ONLY; > > USE <dbname>; > DBCC SHRINKFILE(<dbname>_log, 1024); > GO > > ALTER DATABASE <dbname> SET RECOVERY FULL; > GO > > BACKUP DATABASE <dbname> TO DISK = 'd:\<dbname>.BAK' WITH INIT; > > > 3) get more disk. Disk is cheap. If you need external storage, then get > it. > > 4) set up monitoring. You should never wake up to RAN OUT OF DISK SPACE. > Ever. There is no excuse for this. > > Also read Tibor's article (why you want to do 1) so you can avoid making 2) > a habit). > > http://tr.im/StopShrinking > > > > > > > On 6/18/09 11:57 AM, in article > 31355DE0-15D4-4CE3-811F-7D6549EEE***@microsoft.com, "Rockitman" > <Rockit***@discussions.microsoft.com> wrote: > > > SQL2000, one of my databases is not currently under a maintenance plan and > > this morning, I've come to find out my D: drive is out of disk space!!! > > > > Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see the file > > Fresno_Log.LDF is over 347GB large!!!!! > > > > What do I do to fix this?? We have only 9 mb of file space on this drive. > > > > The C: drive has not even 2GB left. > > > > Please advise ASAP!!!! > > > > Consider setting recovery model for the model database to simple. That
was new database will be in simple. And as soon and you try to backup log (if you want to do that) you will get an error and notice in a bit "friendlier" way that you need to re-think the recovery model. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Rockitman" <Rockit***@discussions.microsoft.com> wrote in message news:77267BFE-8974-49B9-8232-1894014EB58A@microsoft.com... > OH MY GOD!!! AARON, you are the man!! These commands worked > wonders!! > Got this log file back to 1 gig again!! WHoooooooo!! > > A little history: > > I am by no means an SQL trained IT guy. I know enough to keep this > thing > running and that's about it. This database was added recently by > another > person, and I neglected to add it to my Maintenance Plan. > Now it is added and yes, I am already holding in my hands new > drives to add > to this Dell poweredge server. I also have another dual core CPU > to add > which will beef this guy up because this server not only has a 2000 > instance, > but a 2005 one as well. That's another story for another day, but > Aaron, > once again, thanks a million. I owe you big time!! > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > >> 1) pick the proper recovery model. It sounds like you are in full >> recovery >> and either you are not taking log backups at all or you are not >> doing so >> frequently enough. Until you figure out your DR needs, I would >> suggest >> putting it into full and backing up the log at least once a day >> (but >> probably more often). >> >> 2) clean up that file. >> >> ALTER DATABASE <dbname> SET RECOVERY SIMPLE; >> >> BACKUP LOG <dbname> WITH TRUNCATE_ONLY; >> >> USE <dbname>; >> DBCC SHRINKFILE(<dbname>_log, 1024); >> GO >> >> ALTER DATABASE <dbname> SET RECOVERY FULL; >> GO >> >> BACKUP DATABASE <dbname> TO DISK = 'd:\<dbname>.BAK' WITH INIT; >> >> >> 3) get more disk. Disk is cheap. If you need external storage, >> then get >> it. >> >> 4) set up monitoring. You should never wake up to RAN OUT OF DISK >> SPACE. >> Ever. There is no excuse for this. >> >> Also read Tibor's article (why you want to do 1) so you can avoid >> making 2) >> a habit). >> >> http://tr.im/StopShrinking >> >> >> >> >> >> >> On 6/18/09 11:57 AM, in article >> 31355DE0-15D4-4CE3-811F-7D6549EEE***@microsoft.com, "Rockitman" >> <Rockit***@discussions.microsoft.com> wrote: >> >> > SQL2000, one of my databases is not currently under a >> > maintenance plan and >> > this morning, I've come to find out my D: drive is out of disk >> > space!!! >> > >> > Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see >> > the file >> > Fresno_Log.LDF is over 347GB large!!!!! >> > >> > What do I do to fix this?? We have only 9 mb of file space on >> > this drive. >> > >> > The C: drive has not even 2GB left. >> > >> > Please advise ASAP!!!! >> > >> >> Tibor, everything you said went right over my head.
What is a recovery model? What is a simple recovery model? What is the model database and what is it's purpose? I really don't understand what you're getting at. Sorry. Show quoteHide quote "Tibor Karaszi" wrote: > Consider setting recovery model for the model database to simple. That > was new database will be in simple. And as soon and you try to backup > log (if you want to do that) you will get an error and notice in a bit > "friendlier" way that you need to re-think the recovery model. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://sqlblog.com/blogs/tibor_karaszi > > > "Rockitman" <Rockit***@discussions.microsoft.com> wrote in message > news:77267BFE-8974-49B9-8232-1894014EB58A@microsoft.com... > > OH MY GOD!!! AARON, you are the man!! These commands worked > > wonders!! > > Got this log file back to 1 gig again!! WHoooooooo!! > > > > A little history: > > > > I am by no means an SQL trained IT guy. I know enough to keep this > > thing > > running and that's about it. This database was added recently by > > another > > person, and I neglected to add it to my Maintenance Plan. > > Now it is added and yes, I am already holding in my hands new > > drives to add > > to this Dell poweredge server. I also have another dual core CPU > > to add > > which will beef this guy up because this server not only has a 2000 > > instance, > > but a 2005 one as well. That's another story for another day, but > > Aaron, > > once again, thanks a million. I owe you big time!! > > > > > > > > "Aaron Bertrand [SQL Server MVP]" wrote: > > > >> 1) pick the proper recovery model. It sounds like you are in full > >> recovery > >> and either you are not taking log backups at all or you are not > >> doing so > >> frequently enough. Until you figure out your DR needs, I would > >> suggest > >> putting it into full and backing up the log at least once a day > >> (but > >> probably more often). > >> > >> 2) clean up that file. > >> > >> ALTER DATABASE <dbname> SET RECOVERY SIMPLE; > >> > >> BACKUP LOG <dbname> WITH TRUNCATE_ONLY; > >> > >> USE <dbname>; > >> DBCC SHRINKFILE(<dbname>_log, 1024); > >> GO > >> > >> ALTER DATABASE <dbname> SET RECOVERY FULL; > >> GO > >> > >> BACKUP DATABASE <dbname> TO DISK = 'd:\<dbname>.BAK' WITH INIT; > >> > >> > >> 3) get more disk. Disk is cheap. If you need external storage, > >> then get > >> it. > >> > >> 4) set up monitoring. You should never wake up to RAN OUT OF DISK > >> SPACE. > >> Ever. There is no excuse for this. > >> > >> Also read Tibor's article (why you want to do 1) so you can avoid > >> making 2) > >> a habit). > >> > >> http://tr.im/StopShrinking > >> > >> > >> > >> > >> > >> > >> On 6/18/09 11:57 AM, in article > >> 31355DE0-15D4-4CE3-811F-7D6549EEE***@microsoft.com, "Rockitman" > >> <Rockit***@discussions.microsoft.com> wrote: > >> > >> > SQL2000, one of my databases is not currently under a > >> > maintenance plan and > >> > this morning, I've come to find out my D: drive is out of disk > >> > space!!! > >> > > >> > Looking at D:\Miscrosoft SQL Server\MSSQL\Data directory I see > >> > the file > >> > Fresno_Log.LDF is over 347GB large!!!!! > >> > > >> > What do I do to fix this?? We have only 9 mb of file space on > >> > this drive. > >> > > >> > The C: drive has not even 2GB left. > >> > > >> > Please advise ASAP!!!! > >> > > >> > >> > > The model database is used as a "template" for new databases as they are
created. Settings like recovery model are copied from this database. Tibor's point is that it is better to learn you are in the wrong recovery model by having a maintenance plan fail when it hits that database, than when your disk has filled up with space (which has the potential to cause a lot more damage to your entire business than a failed maintenance plan). You can read about recovery models here: http://msdn.microsoft.com/en-us/library/ms189275(SQL.90).aspx On 6/18/09 2:25 PM, in article 5CEB9A70-3A68-495E-B508-A2823901D***@microsoft.com, "Rockitman" <Rockit***@discussions.microsoft.com> wrote: Show quoteHide quote > Tibor, everything you said went right over my head. > What is a recovery model? What is a simple recovery model? > What is the model database and what is it's purpose? > I really don't understand what you're getting at. > Sorry. Thanks again Aaron. I'm understanding this.
The database in question, could probably be set to simple. I'll figure out how to do that. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > The model database is used as a "template" for new databases as they are > created. Settings like recovery model are copied from this database. > Tibor's point is that it is better to learn you are in the wrong recovery > model by having a maintenance plan fail when it hits that database, than > when your disk has filled up with space (which has the potential to cause a > lot more damage to your entire business than a failed maintenance plan). > You can read about recovery models here: > > http://msdn.microsoft.com/en-us/library/ms189275(SQL.90).aspx > > > > > On 6/18/09 2:25 PM, in article > 5CEB9A70-3A68-495E-B508-A2823901D***@microsoft.com, "Rockitman" > <Rockit***@discussions.microsoft.com> wrote: > > > Tibor, everything you said went right over my head. > > What is a recovery model? What is a simple recovery model? > > What is the model database and what is it's purpose? > > I really don't understand what you're getting at. > > Sorry. > > > The database in question, could probably be set to simple. I'll figure out Showed you earlier:> how to do that. ALTER DATABASE <dbname> SET RECOVERY SIMPLE;  
Other interesting topics
|
|||||||||||||||||||||||
