|
|
You're in the
sql server clustering
group:Transaction log with huge proportions
sql server clustering:
Hi all
My SQL Cluster, has a database with 13Gb, but the transaction log is huge. I already stopped the file from gowing more then it it's but is there a way of reduce this file?)already try to shrink the file without sucess). Can i for instance, detach the database an start it with a new log? Or there's a way of forcing the compression of the transaction (ldf) log. Best regards -- Francisco Batista Portugal
I konw that i lack some skills, i'm still taking microsoft courses. But this
is an emergency, the problem is that i don't have available disk to run a backup, do you have any suggestion? Please. -- Francisco Batista Portugal f_batista69@hotmail.com [quoted text, click to view] "Geoff N. Hiten" wrote:
> Your problem is not cluster-specific. It happens on many SQL servers both > clustered and non-clustered. You need to manage your transaction log. > First, read books-online and learn about SQL Backup and Recovery. You need > to decide if you need point-in-time recovery or will a daily/weekly recovery > work. If you want point in time recovery, you will need to set up a regular > log backup process, otherwise you can set your database to SIMPLE recovery. > Once you have the log settings managed correctly, you can use the DBCC > SHRINKFILE command to reduce the log down to a normal size. You will need > to determine the optimum log size for your specific application. Again, > read the chapter on Backing Up and Restoring Databases under Administering > SQL Server in BOL for a complete discussion of all the concepts involved. > > Geoff N.Hiten > Microsoft SQL Server MVP > > > > "francisco - Portugal" <f_batista69@hotmail.com> wrote in message > news:884D5523-D0E7-4487-81F9-2E7C1628CBC4@microsoft.com... > > Hi all > > > > My SQL Cluster, has a database with 13Gb, but the transaction log is huge. > > I > > already stopped the file from gowing more then it it's but is there a way > > of > > reduce this file?)already try to shrink the file without sucess). Can i > > for > > instance, detach the database an start it with a new log? Or there's a way > > of > > forcing the compression of the transaction (ldf) log. > > > > Best regards > > -- > > Francisco Batista > > Portugal > > f_batista69@hotmail.com > >
Your problem is not cluster-specific. It happens on many SQL servers both
clustered and non-clustered. You need to manage your transaction log. First, read books-online and learn about SQL Backup and Recovery. You need to decide if you need point-in-time recovery or will a daily/weekly recovery work. If you want point in time recovery, you will need to set up a regular log backup process, otherwise you can set your database to SIMPLE recovery. Once you have the log settings managed correctly, you can use the DBCC SHRINKFILE command to reduce the log down to a normal size. You will need to determine the optimum log size for your specific application. Again, read the chapter on Backing Up and Restoring Databases under Administering SQL Server in BOL for a complete discussion of all the concepts involved. Geoff N.Hiten Microsoft SQL Server MVP [quoted text, click to view] "francisco - Portugal" <f_batista69@hotmail.com> wrote in message news:884D5523-D0E7-4487-81F9-2E7C1628CBC4@microsoft.com... > Hi all > > My SQL Cluster, has a database with 13Gb, but the transaction log is huge. > I > already stopped the file from gowing more then it it's but is there a way > of > reduce this file?)already try to shrink the file without sucess). Can i > for > instance, detach the database an start it with a new log? Or there's a way > of > forcing the compression of the transaction (ldf) log. > > Best regards > -- > Francisco Batista > Portugal > f_batista69@hotmail.com
Set your database to SIMPLE Recovery (Enterprise manager, Right click on
database name .. Properties | Options tab.) From Query Analyzer Use <myDatabaseName> DBCC SHRINKFILE (<LogicalLogFileName>,<desiredSize>) You may have to run the command a couple of times. See BOL for syntax and warnings. And read that chapter I mentioned. Twice. Soon. Geoff N. Hiten Microsoft SQL Server MVP [quoted text, click to view] "francisco - Portugal" <f_batista69@hotmail.com> wrote in message news:D8B2DD82-47FF-437F-B89A-0EEE62E29B55@microsoft.com... >I konw that i lack some skills, i'm still taking microsoft courses. But >this > is an emergency, the problem is that i don't have available disk to run a > backup, do you have any suggestion? > > Please. > > -- > Francisco Batista > Portugal > f_batista69@hotmail.com > > > "Geoff N. Hiten" wrote: > >> Your problem is not cluster-specific. It happens on many SQL servers >> both >> clustered and non-clustered. You need to manage your transaction log. >> First, read books-online and learn about SQL Backup and Recovery. You >> need >> to decide if you need point-in-time recovery or will a daily/weekly >> recovery >> work. If you want point in time recovery, you will need to set up a >> regular >> log backup process, otherwise you can set your database to SIMPLE >> recovery. >> Once you have the log settings managed correctly, you can use the DBCC >> SHRINKFILE command to reduce the log down to a normal size. You will >> need >> to determine the optimum log size for your specific application. Again, >> read the chapter on Backing Up and Restoring Databases under >> Administering >> SQL Server in BOL for a complete discussion of all the concepts involved. >> >> Geoff N.Hiten >> Microsoft SQL Server MVP >> >> >> >> "francisco - Portugal" <f_batista69@hotmail.com> wrote in message >> news:884D5523-D0E7-4487-81F9-2E7C1628CBC4@microsoft.com... >> > Hi all >> > >> > My SQL Cluster, has a database with 13Gb, but the transaction log is >> > huge. >> > I >> > already stopped the file from gowing more then it it's but is there a >> > way >> > of >> > reduce this file?)already try to shrink the file without sucess). Can i >> > for >> > instance, detach the database an start it with a new log? Or there's a >> > way >> > of >> > forcing the compression of the transaction (ldf) log. >> > >> > Best regards >> > -- >> > Francisco Batista >> > Portugal >> > f_batista69@hotmail.com >> >> >>
Hi
Thank you for pointing me in the right direction i used DMS DBCC SHRINKFILE (DMS_Log,5000) GO I have now a log file of 5GB. Once again thanks for your guidance, i will keep on studying to became a better technician. Regards -- Francisco Batista Portugal f_batista69@hotmail.com [quoted text, click to view] "Geoff N. Hiten" wrote:
> Set your database to SIMPLE Recovery (Enterprise manager, Right click on > database name .. Properties | Options tab.) > From Query Analyzer > > Use <myDatabaseName> > DBCC SHRINKFILE (<LogicalLogFileName>,<desiredSize>) > > You may have to run the command a couple of times. > > See BOL for syntax and warnings. And read that chapter I mentioned. Twice. > Soon. > > Geoff N. Hiten > Microsoft SQL Server MVP > > "francisco - Portugal" <f_batista69@hotmail.com> wrote in message > news:D8B2DD82-47FF-437F-B89A-0EEE62E29B55@microsoft.com... > >I konw that i lack some skills, i'm still taking microsoft courses. But > >this > > is an emergency, the problem is that i don't have available disk to run a > > backup, do you have any suggestion? > > > > Please. > > > > -- > > Francisco Batista > > Portugal > > f_batista69@hotmail.com > > > > > > "Geoff N. Hiten" wrote: > > > >> Your problem is not cluster-specific. It happens on many SQL servers > >> both > >> clustered and non-clustered. You need to manage your transaction log. > >> First, read books-online and learn about SQL Backup and Recovery. You > >> need > >> to decide if you need point-in-time recovery or will a daily/weekly > >> recovery > >> work. If you want point in time recovery, you will need to set up a > >> regular > >> log backup process, otherwise you can set your database to SIMPLE > >> recovery. > >> Once you have the log settings managed correctly, you can use the DBCC > >> SHRINKFILE command to reduce the log down to a normal size. You will > >> need > >> to determine the optimum log size for your specific application. Again, > >> read the chapter on Backing Up and Restoring Databases under > >> Administering > >> SQL Server in BOL for a complete discussion of all the concepts involved. > >> > >> Geoff N.Hiten > >> Microsoft SQL Server MVP > >> > >> > >> > >> "francisco - Portugal" <f_batista69@hotmail.com> wrote in message > >> news:884D5523-D0E7-4487-81F9-2E7C1628CBC4@microsoft.com... > >> > Hi all > >> > > >> > My SQL Cluster, has a database with 13Gb, but the transaction log is > >> > huge. > >> > I > >> > already stopped the file from gowing more then it it's but is there a > >> > way > >> > of > >> > reduce this file?)already try to shrink the file without sucess). Can i > >> > for > >> > instance, detach the database an start it with a new log? Or there's a > >> > way > >> > of > >> > forcing the compression of the transaction (ldf) log. > >> > > >> > Best regards > >> > -- > >> > Francisco Batista > >> > Portugal > >> > f_batista69@hotmail.com > >> > >> > >> > >
Don't see what you're looking for? Try a search.
|
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
December 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
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |