|
|
You're in the
sql server misc
group:Transaction log 18 G and database 470MB
sql server misc:
SQL gurus,
Simple question: Our database is 470MB and transaction log has become 18GB! (Clustered). I see local Fixed drives in both the SQL boxes from 'System Information' and disk manager where data is being stored. We are backing up database once in 24 hours and transaction logs every two hours during business hours and full transaction log at night. Simple Question is if we are backing full transaction log at night then isn't supposed to make the transaction log to "zero" bytes or say clear the transaction log automatically??? After all when transaction logs have been backup then why the transaction log continue to grow?? If the database is crashed what would be the sequence to recover: Restore database in restore mode then restore first transaction log backup taken after full backup of database and then restore the next backup taken of transaction log and so on. (to apply in sequence starting from the first one backup of transaction log taken). Now question is : Situation #1 1) if database is crashed and we still have transaction logs then can we restore transaction logs (not backup of transaction logs) at the end of procedure to restore as given above?? Situation #2 2) database is crashed and we do not have transaction logs then we can restore as given in the procedure above except that in the last stage we keep "no restore mode"?? Thanks Mei
[quoted text, click to view]
"Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > SQL gurus, > > Simple question: Simple answer: change your database to simple mode. your transaction file will barely grow. since your backin up every
[quoted text, click to view]
> Simple answer: > > change your database to simple mode. > > > your transaction file will barely grow. since your backin up every > couple hours, dont think you need your transaction log that much. Does not necessarily help. If you have one modification requiring a large amount of log space, then simple recovery will not help. Also, even if you backup every two hour, doing log backup gives point in time restore. Thanks to this, you can often achieve zero data loss. For some organizations, losing two hours worth of data is not desirable. -- Tibor Karaszi, SQL Server MVP Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver [quoted text, click to view] "Michael Evanchik" <mike@high-pow-er.com> wrote in message news:36194296.0310252246.2925e3c0@posting.google.com... > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > SQL gurus, > > > > Simple question: > > Simple answer: > > change your database to simple mode. > > > your transaction file will barely grow. since your backin up every > couple hours, dont think you need your transaction log that much.
Try
USE <dbname> GO BACKUP LOG <dbname> WITH TRUNCATE_ONLY GO DBCC LOGINFO(<dbname>) GO DBCC SHRINKFILE(<logname>) GO You need to replace <dbname> with the database name and <logname> with the logical file name of the log file. You can ShrinkFile on any logical filename, including the dat's. Hope it helps, certainly did the job for us. NEIL. [quoted text, click to view] "Michael Evanchik" <mike@high-pow-er.com> wrote in message news:36194296.0310252246.2925e3c0@posting.google.com... > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > SQL gurus, > > > > Simple question: > > Simple answer: > > change your database to simple mode. > > > your transaction file will barely grow. since your backin up every > couple hours, dont think you need your transaction log that much.
Have a look at this article.
http://www.mssqlserver.com/faq/logs-shrinklog.asp -- HTH Ryan Waight, MCDBA, MCSE [quoted text, click to view] "GSX Neil" <Neil@Hoskins.biz> wrote in message news:bo832r$689$1@titan.btinternet.com... > Try > > USE <dbname> > GO > BACKUP LOG <dbname> WITH TRUNCATE_ONLY > GO > DBCC LOGINFO(<dbname>) > GO > DBCC SHRINKFILE(<logname>) > GO > > You need to replace <dbname> with the database name and <logname> with the > logical file name of the log file. You can ShrinkFile on any logical > filename, including the dat's. > > Hope it helps, certainly did the job for us. > > NEIL. > > "Michael Evanchik" <mike@high-pow-er.com> wrote in message > news:36194296.0310252246.2925e3c0@posting.google.com... > > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message > news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > > SQL gurus, > > > > > > Simple question: > > > > Simple answer: > > > > change your database to simple mode. > > > > > > your transaction file will barely grow. since your backin up every > > couple hours, dont think you need your transaction log that much. > >
Very useful but it's never proved an issue for us. Some of our users have
18Gb log files (or whatever disk spaces they have) and after running the first and third commands they never have the problem again. Log files are difficult to understand at a low level but if something works for us we tend to stick with it. Cheers for the additional info though. NEIL. [quoted text, click to view] "Ryan Waight" <Ryan_Waight@nospam.hotmail.com> wrote in message news:OIvKFssoDHA.2012@TK2MSFTNGP12.phx.gbl... > Have a look at this article. > > http://www.mssqlserver.com/faq/logs-shrinklog.asp > > -- > HTH > Ryan Waight, MCDBA, MCSE > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > news:bo832r$689$1@titan.btinternet.com... > > Try > > > > USE <dbname> > > GO > > BACKUP LOG <dbname> WITH TRUNCATE_ONLY > > GO > > DBCC LOGINFO(<dbname>) > > GO > > DBCC SHRINKFILE(<logname>) > > GO > > > > You need to replace <dbname> with the database name and <logname> with the > > logical file name of the log file. You can ShrinkFile on any logical > > filename, including the dat's. > > > > Hope it helps, certainly did the job for us. > > > > NEIL. > > > > "Michael Evanchik" <mike@high-pow-er.com> wrote in message > > news:36194296.0310252246.2925e3c0@posting.google.com... > > > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message > > news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > > > SQL gurus, > > > > > > > > Simple question: > > > > > > Simple answer: > > > > > > change your database to simple mode. > > > > > > > > > your transaction file will barely grow. since your backin up every > > > couple hours, dont think you need your transaction log that much. > > > > > >
[quoted text, click to view]
> Very useful but it's never proved an issue for us. Some of our users have > 18Gb log files (or whatever disk spaces they have) and after running the > first and third commands they never have the problem again. What recovery mode are you running the database in? Are you doing regular log backups? Regular database backups? Doing TRUNCATE_ONLY will break the chain of log backups and is unacceptable in some organizations. Also, it should not be necessary, a regular log backup should suffice just as well. -- Tibor Karaszi [quoted text, click to view] "GSX Neil" <Neil@Hoskins.biz> wrote in message news:bo87t8$mko$1@sparta.btinternet.com... > Very useful but it's never proved an issue for us. Some of our users have > 18Gb log files (or whatever disk spaces they have) and after running the > first and third commands they never have the problem again. > > Log files are difficult to understand at a low level but if something works > for us we tend to stick with it. > > Cheers for the additional info though. > > NEIL. > > > "Ryan Waight" <Ryan_Waight@nospam.hotmail.com> wrote in message > news:OIvKFssoDHA.2012@TK2MSFTNGP12.phx.gbl... > > Have a look at this article. > > > > http://www.mssqlserver.com/faq/logs-shrinklog.asp > > > > -- > > HTH > > Ryan Waight, MCDBA, MCSE > > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > > news:bo832r$689$1@titan.btinternet.com... > > > Try > > > > > > USE <dbname> > > > GO > > > BACKUP LOG <dbname> WITH TRUNCATE_ONLY > > > GO > > > DBCC LOGINFO(<dbname>) > > > GO > > > DBCC SHRINKFILE(<logname>) > > > GO > > > > > > You need to replace <dbname> with the database name and <logname> with > the > > > logical file name of the log file. You can ShrinkFile on any logical > > > filename, including the dat's. > > > > > > Hope it helps, certainly did the job for us. > > > > > > NEIL. > > > > > > "Michael Evanchik" <mike@high-pow-er.com> wrote in message > > > news:36194296.0310252246.2925e3c0@posting.google.com... > > > > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message > > > news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > > > > SQL gurus, > > > > > > > > > > Simple question: > > > > > > > > Simple answer: > > > > > > > > change your database to simple mode. > > > > > > > > > > > > your transaction file will barely grow. since your backin up every > > > > couple hours, dont think you need your transaction log that much. > > > > > > > > > > > >
We do a full backup of all the data every time, not interested in the log!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> [quoted text, click to view] wrote in message news:QuNpb.32288$mU6.92386@newsb.telia.net... > > Very useful but it's never proved an issue for us. Some of our users have > > 18Gb log files (or whatever disk spaces they have) and after running the > > first and third commands they never have the problem again. > > What recovery mode are you running the database in? Are you doing regular > log backups? Regular database backups? Doing TRUNCATE_ONLY will break the > chain of log backups and is unacceptable in some organizations. Also, it > should not be necessary, a regular log backup should suffice just as well. > > -- > Tibor Karaszi > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > news:bo87t8$mko$1@sparta.btinternet.com... > > Very useful but it's never proved an issue for us. Some of our users have > > 18Gb log files (or whatever disk spaces they have) and after running the > > first and third commands they never have the problem again. > > > > Log files are difficult to understand at a low level but if something > works > > for us we tend to stick with it. > > > > Cheers for the additional info though. > > > > NEIL. > > > > > > "Ryan Waight" <Ryan_Waight@nospam.hotmail.com> wrote in message > > news:OIvKFssoDHA.2012@TK2MSFTNGP12.phx.gbl... > > > Have a look at this article. > > > > > > http://www.mssqlserver.com/faq/logs-shrinklog.asp > > > > > > -- > > > HTH > > > Ryan Waight, MCDBA, MCSE > > > > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > > > news:bo832r$689$1@titan.btinternet.com... > > > > Try > > > > > > > > USE <dbname> > > > > GO > > > > BACKUP LOG <dbname> WITH TRUNCATE_ONLY > > > > GO > > > > DBCC LOGINFO(<dbname>) > > > > GO > > > > DBCC SHRINKFILE(<logname>) > > > > GO > > > > > > > > You need to replace <dbname> with the database name and <logname> with > > the > > > > logical file name of the log file. You can ShrinkFile on any logical > > > > filename, including the dat's. > > > > > > > > Hope it helps, certainly did the job for us. > > > > > > > > NEIL. > > > > > > > > "Michael Evanchik" <mike@high-pow-er.com> wrote in message > > > > news:36194296.0310252246.2925e3c0@posting.google.com... > > > > > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in message > > > > news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > > > > > SQL gurus, > > > > > > > > > > > > Simple question: > > > > > > > > > > Simple answer: > > > > > > > > > > change your database to simple mode. > > > > > > > > > > > > > > > your transaction file will barely grow. since your backin up every > > > > > couple hours, dont think you need your transaction log that much. > > > > > > > > > > > > > > > > > > > >
Seems it would be easier to set the database to simple recovery then.
-- Tibor Karaszi [quoted text, click to view] "GSX Neil" <Neil@Hoskins.biz> wrote in message news:bo8cvh$res$1@titan.btinternet.com... > We do a full backup of all the data every time, not interested in the log! > > "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> > wrote in message news:QuNpb.32288$mU6.92386@newsb.telia.net... > > > Very useful but it's never proved an issue for us. Some of our users > have > > > 18Gb log files (or whatever disk spaces they have) and after running the > > > first and third commands they never have the problem again. > > > > What recovery mode are you running the database in? Are you doing regular > > log backups? Regular database backups? Doing TRUNCATE_ONLY will break the > > chain of log backups and is unacceptable in some organizations. Also, it > > should not be necessary, a regular log backup should suffice just as well. > > > > -- > > Tibor Karaszi > > > > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > > news:bo87t8$mko$1@sparta.btinternet.com... > > > Very useful but it's never proved an issue for us. Some of our users > have > > > 18Gb log files (or whatever disk spaces they have) and after running the > > > first and third commands they never have the problem again. > > > > > > Log files are difficult to understand at a low level but if something > > works > > > for us we tend to stick with it. > > > > > > Cheers for the additional info though. > > > > > > NEIL. > > > > > > > > > "Ryan Waight" <Ryan_Waight@nospam.hotmail.com> wrote in message > > > news:OIvKFssoDHA.2012@TK2MSFTNGP12.phx.gbl... > > > > Have a look at this article. > > > > > > > > http://www.mssqlserver.com/faq/logs-shrinklog.asp > > > > > > > > -- > > > > HTH > > > > Ryan Waight, MCDBA, MCSE > > > > > > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > > > > news:bo832r$689$1@titan.btinternet.com... > > > > > Try > > > > > > > > > > USE <dbname> > > > > > GO > > > > > BACKUP LOG <dbname> WITH TRUNCATE_ONLY > > > > > GO > > > > > DBCC LOGINFO(<dbname>) > > > > > GO > > > > > DBCC SHRINKFILE(<logname>) > > > > > GO > > > > > > > > > > You need to replace <dbname> with the database name and <logname> > with > > > the > > > > > logical file name of the log file. You can ShrinkFile on any > logical > > > > > filename, including the dat's. > > > > > > > > > > Hope it helps, certainly did the job for us. > > > > > > > > > > NEIL. > > > > > > > > > > "Michael Evanchik" <mike@high-pow-er.com> wrote in message > > > > > news:36194296.0310252246.2925e3c0@posting.google.com... > > > > > > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in > message > > > > > news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > > > > > > SQL gurus, > > > > > > > > > > > > > > Simple question: > > > > > > > > > > > > Simple answer: > > > > > > > > > > > > change your database to simple mode. > > > > > > > > > > > > > > > > > > your transaction file will barely grow. since your backin up > every > > > > > > couple hours, dont think you need your transaction log that much. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Quite right and will be done!
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> [quoted text, click to view] wrote in message news:eYOpb.36447$dP1.121834@newsc.telia.net... > Seems it would be easier to set the database to simple recovery then. > > -- > Tibor Karaszi > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > news:bo8cvh$res$1@titan.btinternet.com... > > We do a full backup of all the data every time, not interested in the log! > > > > "Tibor Karaszi" > <tibor.please_reply_to_public_forum.karaszi@cornerstone.se> > > wrote in message news:QuNpb.32288$mU6.92386@newsb.telia.net... > > > > Very useful but it's never proved an issue for us. Some of our users > > have > > > > 18Gb log files (or whatever disk spaces they have) and after running > the > > > > first and third commands they never have the problem again. > > > > > > What recovery mode are you running the database in? Are you doing > regular > > > log backups? Regular database backups? Doing TRUNCATE_ONLY will break > the > > > chain of log backups and is unacceptable in some organizations. Also, it > > > should not be necessary, a regular log backup should suffice just as > well. > > > > > > -- > > > Tibor Karaszi > > > > > > > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > > > news:bo87t8$mko$1@sparta.btinternet.com... > > > > Very useful but it's never proved an issue for us. Some of our users > > have > > > > 18Gb log files (or whatever disk spaces they have) and after running > the > > > > first and third commands they never have the problem again. > > > > > > > > Log files are difficult to understand at a low level but if something > > > works > > > > for us we tend to stick with it. > > > > > > > > Cheers for the additional info though. > > > > > > > > NEIL. > > > > > > > > > > > > "Ryan Waight" <Ryan_Waight@nospam.hotmail.com> wrote in message > > > > news:OIvKFssoDHA.2012@TK2MSFTNGP12.phx.gbl... > > > > > Have a look at this article. > > > > > > > > > > http://www.mssqlserver.com/faq/logs-shrinklog.asp > > > > > > > > > > -- > > > > > HTH > > > > > Ryan Waight, MCDBA, MCSE > > > > > > > > > > "GSX Neil" <Neil@Hoskins.biz> wrote in message > > > > > news:bo832r$689$1@titan.btinternet.com... > > > > > > Try > > > > > > > > > > > > USE <dbname> > > > > > > GO > > > > > > BACKUP LOG <dbname> WITH TRUNCATE_ONLY > > > > > > GO > > > > > > DBCC LOGINFO(<dbname>) > > > > > > GO > > > > > > DBCC SHRINKFILE(<logname>) > > > > > > GO > > > > > > > > > > > > You need to replace <dbname> with the database name and <logname> > > with > > > > the > > > > > > logical file name of the log file. You can ShrinkFile on any > > logical > > > > > > filename, including the dat's. > > > > > > > > > > > > Hope it helps, certainly did the job for us. > > > > > > > > > > > > NEIL. > > > > > > > > > > > > "Michael Evanchik" <mike@high-pow-er.com> wrote in message > > > > > > news:36194296.0310252246.2925e3c0@posting.google.com... > > > > > > > "Narendra Talreja" <ntalreja@no_spam_comcast.net> wrote in > > message > > > > > > news:<Pq2cnYbsxcBQeBmiU-KYgA@comcast.com>... > > > > > > > > SQL gurus, > > > > > > > > > > > > > > > > Simple question: > > > > > > > > > > > > > > Simple answer: > > > > > > > > > > > > > > change your database to simple mode. > > > > > > > > > > > > > > > > > > > > > your transaction file will barely grow. since your backin up > > every > > > > > > > couple hours, dont think you need your transaction log that > much. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |