| Groups | Blog | Home |
sql server new users : Log size and Maintenance Plan
Backups do not shrink files. Normally you do not want the files to be shrunk. If your log file continues to grow you have one of several conditions. One is that you are not doing proper or regular log backups. if you don't want or need to issue log backups on a regular basis you might think about putting the database into SIMPLE recovery mode. Another possability is that you have a long running open transaction in that database. You can check with DBCC OPENTRAN(). Once you find the issue and correct it you can shrink the log file to a reasonable size with DBCC SHRINKFILE. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Bill Bradley" <BillBradley@discussions.microsoft.com> wrote in message news:8B6339FA-0D44-494F-AC35-0279FC38E12C@microsoft.com... > My impression was that logs grow until a backup or maintenance is done, > then > they shrink way down. > > I am using Shavlik NetChk (a patch scanner/deployer program) on Server > 2003 > w/SP1 using SQL 2005 w/SP1. > > The performance is suffering, and, I checked the file sizes and while the > mdf filesize is 103 MB, the ldf filesize is 1.3 GB. > > I created a maintenance plan, and, basically chose to do everything to all > databases, using the default settings. > > When done, and, I rebooted, the filesizes were the same. > > Also...how do you know when the maintenance plan execution is finished? I > don't have it scheduled, and, when I run it, it says it completed > immediately > (success), but, checking Processes...it's still running (takes about 4 > minutes). > > Thanks!
My impression was that logs grow until a backup or maintenance is done, then
they shrink way down. I am using Shavlik NetChk (a patch scanner/deployer program) on Server 2003 w/SP1 using SQL 2005 w/SP1. The performance is suffering, and, I checked the file sizes and while the mdf filesize is 103 MB, the ldf filesize is 1.3 GB. I created a maintenance plan, and, basically chose to do everything to all databases, using the default settings. When done, and, I rebooted, the filesizes were the same. Also...how do you know when the maintenance plan execution is finished? I don't have it scheduled, and, when I run it, it says it completed immediately (success), but, checking Processes...it's still running (takes about 4 minutes).
I don't mean that the backup itself would make the files smaller, and, I
understand shrinking to be removing empty space from within the files. I thought that there were transactions IN the log that, the process of backing up the data, would "finalize" or write to the mdf file, and, since they were not required, they'd be removed, so that the log was then made smaller. I thought the logfile was kinda a storage place for data that had not yet been placed in the actual database. Finally, this application scans computers for missing and installed patches, then deploys the missing ones and tracks the results. This gets listed in the program as separate things, and, I can delete them, and, I deleted ALL scan and deploy results and rebooted. I would presume that would have cleared out the data, so that the data and logfiles would be at their smallest? 1.3 GB doesn't sound small... I'll check into that DBCC OPENTRAN() thing. Thanks! [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OUQn5OSlGHA.1240@TK2MSFTNGP04.phx.gbl... > Bill, > > Backups do not shrink files. Normally you do not want the files to be > shrunk. If your log file continues to grow you have one of several > conditions. One is that you are not doing proper or regular log backups. > if you don't want or need to issue log backups on a regular basis you > might think about putting the database into SIMPLE recovery mode. Another > possability is that you have a long running open transaction in that > database. You can check with DBCC OPENTRAN(). Once you find the issue and > correct it you can shrink the log file to a reasonable size with DBCC > SHRINKFILE. > > -- > Andrew J. Kelly SQL MVP > > "Bill Bradley" <BillBradley@discussions.microsoft.com> wrote in message > news:8B6339FA-0D44-494F-AC35-0279FC38E12C@microsoft.com... >> My impression was that logs grow until a backup or maintenance is done, >> then >> they shrink way down. >> >> I am using Shavlik NetChk (a patch scanner/deployer program) on Server >> 2003 >> w/SP1 using SQL 2005 w/SP1. >> >> The performance is suffering, and, I checked the file sizes and while the >> mdf filesize is 103 MB, the ldf filesize is 1.3 GB. >> >> I created a maintenance plan, and, basically chose to do everything to >> all >> databases, using the default settings. >> >> When done, and, I rebooted, the filesizes were the same. >> >> Also...how do you know when the maintenance plan execution is finished? >> I >> don't have it scheduled, and, when I run it, it says it completed >> immediately >> (success), but, checking Processes...it's still running (takes about 4 >> minutes). >> >> Thanks! > >
Backing up the database does not clear out the transactions in the log file.
Only a LOG backup will do that and only if there are no open trans that will prevent the space from being reused. If you really are clearing it out all the time then I wouldn't bother with LOG backups at all (if they are even being done now). Just place the database into SIMPLE recovery mode and it shoudl take care of it. -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Bill Bradley" <wdbradley3@comcast.net> wrote in message news:ucwnpxSlGHA.836@TK2MSFTNGP02.phx.gbl... >I don't mean that the backup itself would make the files smaller, and, I >understand shrinking to be removing empty space from within the files. > > I thought that there were transactions IN the log that, the process of > backing up the data, would "finalize" or write to the mdf file, and, since > they were not required, they'd be removed, so that the log was then made > smaller. > > I thought the logfile was kinda a storage place for data that had not yet > been placed in the actual database. > > Finally, this application scans computers for missing and installed > patches, then deploys the missing ones and tracks the results. This gets > listed in the program as separate things, and, I can delete them, and, I > deleted ALL scan and deploy results and rebooted. > > I would presume that would have cleared out the data, so that the data and > logfiles would be at their smallest? 1.3 GB doesn't sound small... > > I'll check into that DBCC OPENTRAN() thing. > > Thanks! > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OUQn5OSlGHA.1240@TK2MSFTNGP04.phx.gbl... >> Bill, >> >> Backups do not shrink files. Normally you do not want the files to be >> shrunk. If your log file continues to grow you have one of several >> conditions. One is that you are not doing proper or regular log backups. >> if you don't want or need to issue log backups on a regular basis you >> might think about putting the database into SIMPLE recovery mode. Another >> possability is that you have a long running open transaction in that >> database. You can check with DBCC OPENTRAN(). Once you find the issue and >> correct it you can shrink the log file to a reasonable size with DBCC >> SHRINKFILE. >> >> -- >> Andrew J. Kelly SQL MVP >> >> "Bill Bradley" <BillBradley@discussions.microsoft.com> wrote in message >> news:8B6339FA-0D44-494F-AC35-0279FC38E12C@microsoft.com... >>> My impression was that logs grow until a backup or maintenance is done, >>> then >>> they shrink way down. >>> >>> I am using Shavlik NetChk (a patch scanner/deployer program) on Server >>> 2003 >>> w/SP1 using SQL 2005 w/SP1. >>> >>> The performance is suffering, and, I checked the file sizes and while >>> the >>> mdf filesize is 103 MB, the ldf filesize is 1.3 GB. >>> >>> I created a maintenance plan, and, basically chose to do everything to >>> all >>> databases, using the default settings. >>> >>> When done, and, I rebooted, the filesizes were the same. >>> >>> Also...how do you know when the maintenance plan execution is finished? >>> I >>> don't have it scheduled, and, when I run it, it says it completed >>> immediately >>> (success), but, checking Processes...it's still running (takes about 4 >>> minutes). >>> >>> Thanks! >> >> > >
[quoted text, click to view]
Bill Bradley wrote: ...... > I would presume that would have cleared out the data, so that the data and > logfiles would be at their smallest? 1.3 GB doesn't sound small... > I'm not quite sure what you mean about this? The only thing that will clear out data from a database, is when you run a SQL command that deletes the dat in one way or the other. The only thing that will "remove" data from the logfile is a backup log command. No other "non-sql" commands can change the size of the .mdf and .ldf files in a SQL server database. Addtionally, a reboot doesn't make any change to the size of a user defined SQL server database. -- Regards Steen Schlüter Persson
I am doing both database and log backups, in a single maintenance plan, so,
when that plan is done, IF the log can be shrunk, I presume it will be? [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:e4S43FalGHA.4076@TK2MSFTNGP05.phx.gbl... > Backing up the database does not clear out the transactions in the log > file. Only a LOG backup will do that and only if there are no open trans > that will prevent the space from being reused. If you really are clearing > it out all the time then I wouldn't bother with LOG backups at all (if > they are even being done now). Just place the database into SIMPLE > recovery mode and it shoudl take care of it. > > -- > Andrew J. Kelly SQL MVP > > "Bill Bradley" <wdbradley3@comcast.net> wrote in message > news:ucwnpxSlGHA.836@TK2MSFTNGP02.phx.gbl... >>I don't mean that the backup itself would make the files smaller, and, I >>understand shrinking to be removing empty space from within the files. >> >> I thought that there were transactions IN the log that, the process of >> backing up the data, would "finalize" or write to the mdf file, and, >> since they were not required, they'd be removed, so that the log was then >> made smaller. >> >> I thought the logfile was kinda a storage place for data that had not yet >> been placed in the actual database. >> >> Finally, this application scans computers for missing and installed >> patches, then deploys the missing ones and tracks the results. This gets >> listed in the program as separate things, and, I can delete them, and, I >> deleted ALL scan and deploy results and rebooted. >> >> I would presume that would have cleared out the data, so that the data >> and logfiles would be at their smallest? 1.3 GB doesn't sound small... >> >> I'll check into that DBCC OPENTRAN() thing. >> >> Thanks! >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:OUQn5OSlGHA.1240@TK2MSFTNGP04.phx.gbl... >>> Bill, >>> >>> Backups do not shrink files. Normally you do not want the files to be >>> shrunk. If your log file continues to grow you have one of several >>> conditions. One is that you are not doing proper or regular log backups. >>> if you don't want or need to issue log backups on a regular basis you >>> might think about putting the database into SIMPLE recovery mode. >>> Another possability is that you have a long running open transaction in >>> that database. You can check with DBCC OPENTRAN(). Once you find the >>> issue and correct it you can shrink the log file to a reasonable size >>> with DBCC SHRINKFILE. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> "Bill Bradley" <BillBradley@discussions.microsoft.com> wrote in message >>> news:8B6339FA-0D44-494F-AC35-0279FC38E12C@microsoft.com... >>>> My impression was that logs grow until a backup or maintenance is done, >>>> then >>>> they shrink way down. >>>> >>>> I am using Shavlik NetChk (a patch scanner/deployer program) on Server >>>> 2003 >>>> w/SP1 using SQL 2005 w/SP1. >>>> >>>> The performance is suffering, and, I checked the file sizes and while >>>> the >>>> mdf filesize is 103 MB, the ldf filesize is 1.3 GB. >>>> >>>> I created a maintenance plan, and, basically chose to do everything to >>>> all >>>> databases, using the default settings. >>>> >>>> When done, and, I rebooted, the filesizes were the same. >>>> >>>> Also...how do you know when the maintenance plan execution is finished? >>>> I >>>> don't have it scheduled, and, when I run it, it says it completed >>>> immediately >>>> (success), but, checking Processes...it's still running (takes about 4 >>>> minutes). >>>> >>>> Thanks! >>> >>> >> >> > >
I presume that the mdf file is the ULTIMATE destination of the data? The
ldf file is where all entries or transactions go through, on their way to the mdf file? Once an entry or transaction has been processed and/or the ldf been backed up, the ldf SHOULD shrink down to a very small size (and, correspondingly, the mdf SHOULD be getting bigger). However, when the day is done, the program is finished running, old things are deleted, and, both files are backed up...a 1.3 GB ldf file (when the mdf is only 500 MB) is probably not right? Is this somewhat correct? I did try turning the Recovery Mode to simple, and, will see what happens... Thanks! [quoted text, click to view] "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message news:%230OB8PdlGHA.3924@TK2MSFTNGP03.phx.gbl... > Bill Bradley wrote: > ..... >> I would presume that would have cleared out the data, so that the data >> and logfiles would be at their smallest? 1.3 GB doesn't sound small... >> > > I'm not quite sure what you mean about this? The only thing that will > clear out data from a database, is when you run a SQL command that deletes > the dat in one way or the other. The only thing that will "remove" data > from the logfile is a backup log command. No other "non-sql" commands can > change the size of the .mdf and .ldf files in a SQL server database. > Addtionally, a reboot doesn't make any change to the size of a user > defined SQL server database. > > > -- > Regards > Steen Schlüter Persson > Databaseadministrator / Systemadministrator
[quoted text, click to view]
Bill Bradley wrote: > I presume that the mdf file is the ULTIMATE destination of the data? The > ldf file is where all entries or transactions go through, on their way to > the mdf file? > > Once an entry or transaction has been processed and/or the ldf been backed > up, the ldf SHOULD shrink down to a very small size (and, correspondingly, > the mdf SHOULD be getting bigger). > > However, when the day is done, the program is finished running, old things > are deleted, and, both files are backed up...a 1.3 GB ldf file (when the mdf > is only 500 MB) is probably not right? > > Is this somewhat correct? > > I did try turning the Recovery Mode to simple, and, will see what happens... > > Thanks! > "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message > news:%230OB8PdlGHA.3924@TK2MSFTNGP03.phx.gbl... >> Bill Bradley wrote: >> ..... >>> I would presume that would have cleared out the data, so that the data >>> and logfiles would be at their smallest? 1.3 GB doesn't sound small... >>> >> I'm not quite sure what you mean about this? The only thing that will >> clear out data from a database, is when you run a SQL command that deletes >> the dat in one way or the other. The only thing that will "remove" data >> from the logfile is a backup log command. No other "non-sql" commands can >> change the size of the .mdf and .ldf files in a SQL server database. >> Addtionally, a reboot doesn't make any change to the size of a user >> defined SQL server database. >> >> >> -- >> Regards >> Steen Schlüter Persson >> Databaseadministrator / Systemadministrator > > Hi Bill Backing up the log and the database will NOT reduce the physical size of the files. When you run a backup log, it will only truncate the log file meaning that the space that already are in the log file can be reused. If you want to make the physical file smaller, you'll have to run a DBCC SHRINKFILE. Try to read up on BACKUP/RESTORE and "Transaction Log architecture" in Books On Line which will give you a lot of good background info. You should also take a look at http://www.karaszi.com/SQLServer/info_dont_shrink.asp -- Regards Steen Schlüter Persson Databaseadministrator / Systemadministrator
I should run Integrity Checks, then backup, then shrink, in that order,
then? I DID read the info on backup/restore, and...I understand it to say it truncates the log (I always run Shrink, so, it SHOULD get smaller), but, in my case, the log is staying huge... Thanks. [quoted text, click to view] "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message news:%23%23V$ahRmGHA.3468@TK2MSFTNGP03.phx.gbl... > Bill Bradley wrote: >> I presume that the mdf file is the ULTIMATE destination of the data? The >> ldf file is where all entries or transactions go through, on their way to >> the mdf file? >> >> Once an entry or transaction has been processed and/or the ldf been >> backed up, the ldf SHOULD shrink down to a very small size (and, >> correspondingly, the mdf SHOULD be getting bigger). >> >> However, when the day is done, the program is finished running, old >> things are deleted, and, both files are backed up...a 1.3 GB ldf file >> (when the mdf is only 500 MB) is probably not right? >> >> Is this somewhat correct? >> >> I did try turning the Recovery Mode to simple, and, will see what >> happens... >> >> Thanks! >> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message >> news:%230OB8PdlGHA.3924@TK2MSFTNGP03.phx.gbl... >>> Bill Bradley wrote: >>> ..... >>>> I would presume that would have cleared out the data, so that the data >>>> and logfiles would be at their smallest? 1.3 GB doesn't sound small... >>>> >>> I'm not quite sure what you mean about this? The only thing that will >>> clear out data from a database, is when you run a SQL command that >>> deletes the dat in one way or the other. The only thing that will >>> "remove" data from the logfile is a backup log command. No other >>> "non-sql" commands can change the size of the .mdf and .ldf files in a >>> SQL server database. >>> Addtionally, a reboot doesn't make any change to the size of a user >>> defined SQL server database. >>> >>> >>> -- >>> Regards >>> Steen Schlüter Persson >>> Databaseadministrator / Systemadministrator >> >> > > Hi Bill > > Backing up the log and the database will NOT reduce the physical size of > the files. > When you run a backup log, it will only truncate the log file meaning that > the space that already are in the log file can be reused. If you want to > make the physical file smaller, you'll have to run a DBCC SHRINKFILE. > > Try to read up on BACKUP/RESTORE and "Transaction Log architecture" in > Books On Line which will give you a lot of good background info. > You should also take a look at > http://www.karaszi.com/SQLServer/info_dont_shrink.asp > > -- > Regards > Steen Schlüter Persson > Databaseadministrator / Systemadministrator > >
You should almost never shrink. It got that way before it will probably need
to get there again. Shrinking is bad for performance and can cause fragmentation in the data files. http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking considerations http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues http://www.support.microsoft.com/?id=317375 Log File Grows too big http://www.support.microsoft.com/?id=110139 Log file filling up http://www.support.microsoft.com/?id=315512 Considerations for Autogrow and AutoShrink http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server 2000 with DBCC SHRINKFILE http://www.support.microsoft.com/?id=873235 How to stop the log file from growing -- Andrew J. Kelly SQL MVP [quoted text, click to view] "Bill Bradley" <wdbradley3@comcast.net> wrote in message news:ebWRE$dmGHA.856@TK2MSFTNGP03.phx.gbl... >I should run Integrity Checks, then backup, then shrink, in that order, >then? > > I DID read the info on backup/restore, and...I understand it to say it > truncates the log (I always run Shrink, so, it SHOULD get smaller), but, > in my case, the log is staying huge... > > Thanks. > "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message > news:%23%23V$ahRmGHA.3468@TK2MSFTNGP03.phx.gbl... >> Bill Bradley wrote: >>> I presume that the mdf file is the ULTIMATE destination of the data? >>> The ldf file is where all entries or transactions go through, on their >>> way to the mdf file? >>> >>> Once an entry or transaction has been processed and/or the ldf been >>> backed up, the ldf SHOULD shrink down to a very small size (and, >>> correspondingly, the mdf SHOULD be getting bigger). >>> >>> However, when the day is done, the program is finished running, old >>> things are deleted, and, both files are backed up...a 1.3 GB ldf file >>> (when the mdf is only 500 MB) is probably not right? >>> >>> Is this somewhat correct? >>> >>> I did try turning the Recovery Mode to simple, and, will see what >>> happens... >>> >>> Thanks! >>> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message >>> news:%230OB8PdlGHA.3924@TK2MSFTNGP03.phx.gbl... >>>> Bill Bradley wrote: >>>> ..... >>>>> I would presume that would have cleared out the data, so that the data >>>>> and logfiles would be at their smallest? 1.3 GB doesn't sound >>>>> small... >>>>> >>>> I'm not quite sure what you mean about this? The only thing that will >>>> clear out data from a database, is when you run a SQL command that >>>> deletes the dat in one way or the other. The only thing that will >>>> "remove" data from the logfile is a backup log command. No other >>>> "non-sql" commands can change the size of the .mdf and .ldf files in a >>>> SQL server database. >>>> Addtionally, a reboot doesn't make any change to the size of a user >>>> defined SQL server database. >>>> >>>> >>>> -- >>>> Regards >>>> Steen Schlüter Persson >>>> Databaseadministrator / Systemadministrator >>> >>> >> >> Hi Bill >> >> Backing up the log and the database will NOT reduce the physical size of >> the files. >> When you run a backup log, it will only truncate the log file meaning >> that the space that already are in the log file can be reused. If you >> want to make the physical file smaller, you'll have to run a DBCC >> SHRINKFILE. >> >> Try to read up on BACKUP/RESTORE and "Transaction Log architecture" in >> Books On Line which will give you a lot of good background info. >> You should also take a look at >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >> >> -- >> Regards >> Steen Schlüter Persson >> Databaseadministrator / Systemadministrator >> >> > >
I am...slowly...learning...<G>
Thanks. BTW...I gave up, nuked the server, rebuilt from scratch, and...the Log file is staying constant at 2 MB...sigh... Thanks, again! [quoted text, click to view] "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:OsHBkplmGHA.4100@TK2MSFTNGP05.phx.gbl... > You should almost never shrink. It got that way before it will probably > need to get there again. Shrinking is bad for performance and can cause > fragmentation in the data files. > > http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking > considerations > http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File issues > http://www.support.microsoft.com/?id=317375 Log File Grows too big > http://www.support.microsoft.com/?id=110139 Log file filling up > http://www.support.microsoft.com/?id=315512 Considerations for Autogrow > and AutoShrink > http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server > 2000 with DBCC SHRINKFILE > http://www.support.microsoft.com/?id=873235 How to stop the log file > from growing > > > -- > Andrew J. Kelly SQL MVP > > "Bill Bradley" <wdbradley3@comcast.net> wrote in message > news:ebWRE$dmGHA.856@TK2MSFTNGP03.phx.gbl... >>I should run Integrity Checks, then backup, then shrink, in that order, >>then? >> >> I DID read the info on backup/restore, and...I understand it to say it >> truncates the log (I always run Shrink, so, it SHOULD get smaller), but, >> in my case, the log is staying huge... >> >> Thanks. >> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message >> news:%23%23V$ahRmGHA.3468@TK2MSFTNGP03.phx.gbl... >>> Bill Bradley wrote: >>>> I presume that the mdf file is the ULTIMATE destination of the data? >>>> The ldf file is where all entries or transactions go through, on their >>>> way to the mdf file? >>>> >>>> Once an entry or transaction has been processed and/or the ldf been >>>> backed up, the ldf SHOULD shrink down to a very small size (and, >>>> correspondingly, the mdf SHOULD be getting bigger). >>>> >>>> However, when the day is done, the program is finished running, old >>>> things are deleted, and, both files are backed up...a 1.3 GB ldf file >>>> (when the mdf is only 500 MB) is probably not right? >>>> >>>> Is this somewhat correct? >>>> >>>> I did try turning the Recovery Mode to simple, and, will see what >>>> happens... >>>> >>>> Thanks! >>>> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message >>>> news:%230OB8PdlGHA.3924@TK2MSFTNGP03.phx.gbl... >>>>> Bill Bradley wrote: >>>>> ..... >>>>>> I would presume that would have cleared out the data, so that the >>>>>> data and logfiles would be at their smallest? 1.3 GB doesn't sound >>>>>> small... >>>>>> >>>>> I'm not quite sure what you mean about this? The only thing that will >>>>> clear out data from a database, is when you run a SQL command that >>>>> deletes the dat in one way or the other. The only thing that will >>>>> "remove" data from the logfile is a backup log command. No other >>>>> "non-sql" commands can change the size of the .mdf and .ldf files in a >>>>> SQL server database. >>>>> Addtionally, a reboot doesn't make any change to the size of a user >>>>> defined SQL server database. >>>>> >>>>> >>>>> -- >>>>> Regards >>>>> Steen Schlüter Persson >>>>> Databaseadministrator / Systemadministrator >>>> >>>> >>> >>> Hi Bill >>> >>> Backing up the log and the database will NOT reduce the physical size of >>> the files. >>> When you run a backup log, it will only truncate the log file meaning >>> that the space that already are in the log file can be reused. If you >>> want to make the physical file smaller, you'll have to run a DBCC >>> SHRINKFILE. >>> >>> Try to read up on BACKUP/RESTORE and "Transaction Log architecture" in >>> Books On Line which will give you a lot of good background info. >>> You should also take a look at >>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >>> >>> -- >>> Regards >>> Steen Schlüter Persson >>> Databaseadministrator / Systemadministrator >>> >>> >> >> > >
Format solves lots of problems the world over<g>.
-- Andrew J. Kelly SQL MVP [quoted text, click to view] "Bill Bradley" <wdbradley3@comcast.net> wrote in message news:uneymW3mGHA.2360@TK2MSFTNGP04.phx.gbl... >I am...slowly...learning...<G> > > Thanks. > > BTW...I gave up, nuked the server, rebuilt from scratch, and...the Log > file is staying constant at 2 MB...sigh... > > Thanks, again! > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:OsHBkplmGHA.4100@TK2MSFTNGP05.phx.gbl... >> You should almost never shrink. It got that way before it will probably >> need to get there again. Shrinking is bad for performance and can cause >> fragmentation in the data files. >> >> http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking >> considerations >> http://www.nigelrivett.net/TransactionLogFileGrows_1.html Log File >> issues >> http://www.support.microsoft.com/?id=317375 Log File Grows too big >> http://www.support.microsoft.com/?id=110139 Log file filling up >> http://www.support.microsoft.com/?id=315512 Considerations for Autogrow >> and AutoShrink >> http://www.support.microsoft.com/?id=272318 Shrinking Log in SQL Server >> 2000 with DBCC SHRINKFILE >> http://www.support.microsoft.com/?id=873235 How to stop the log file >> from growing >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> "Bill Bradley" <wdbradley3@comcast.net> wrote in message >> news:ebWRE$dmGHA.856@TK2MSFTNGP03.phx.gbl... >>>I should run Integrity Checks, then backup, then shrink, in that order, >>>then? >>> >>> I DID read the info on backup/restore, and...I understand it to say it >>> truncates the log (I always run Shrink, so, it SHOULD get smaller), but, >>> in my case, the log is staying huge... >>> >>> Thanks. >>> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message >>> news:%23%23V$ahRmGHA.3468@TK2MSFTNGP03.phx.gbl... >>>> Bill Bradley wrote: >>>>> I presume that the mdf file is the ULTIMATE destination of the data? >>>>> The ldf file is where all entries or transactions go through, on their >>>>> way to the mdf file? >>>>> >>>>> Once an entry or transaction has been processed and/or the ldf been >>>>> backed up, the ldf SHOULD shrink down to a very small size (and, >>>>> correspondingly, the mdf SHOULD be getting bigger). >>>>> >>>>> However, when the day is done, the program is finished running, old >>>>> things are deleted, and, both files are backed up...a 1.3 GB ldf file >>>>> (when the mdf is only 500 MB) is probably not right? >>>>> >>>>> Is this somewhat correct? >>>>> >>>>> I did try turning the Recovery Mode to simple, and, will see what >>>>> happens... >>>>> >>>>> Thanks! >>>>> "Steen Persson (DK)" <spe@REMOVEdatea.dk> wrote in message >>>>> news:%230OB8PdlGHA.3924@TK2MSFTNGP03.phx.gbl... >>>>>> Bill Bradley wrote: >>>>>> ..... >>>>>>> I would presume that would have cleared out the data, so that the >>>>>>> data and logfiles would be at their smallest? 1.3 GB doesn't sound >>>>>>> small... >>>>>>> >>>>>> I'm not quite sure what you mean about this? The only thing that will >>>>>> clear out data from a database, is when you run a SQL command that >>>>>> deletes the dat in one way or the other. The only thing that will >>>>>> "remove" data from the logfile is a backup log command. No other >>>>>> "non-sql" commands can change the size of the .mdf and .ldf files in >>>>>> a SQL server database. >>>>>> Addtionally, a reboot doesn't make any change to the size of a user >>>>>> defined SQL server database. >>>>>> >>>>>> >>>>>> -- >>>>>> Regards >>>>>> Steen Schlüter Persson >>>>>> Databaseadministrator / Systemadministrator >>>>> >>>>> >>>> >>>> Hi Bill >>>> >>>> Backing up the log and the database will NOT reduce the physical size >>>> of the files. >>>> When you run a backup log, it will only truncate the log file meaning >>>> that the space that already are in the log file can be reused. If you >>>> want to make the physical file smaller, you'll have to run a DBCC >>>> SHRINKFILE. >>>> >>>> Try to read up on BACKUP/RESTORE and "Transaction Log architecture" in >>>> Books On Line which will give you a lot of good background info. >>>> You should also take a look at >>>> http://www.karaszi.com/SQLServer/info_dont_shrink.asp >>>> >>>> -- >>>> Regards >>>> Steen Schlüter Persson >>>> Databaseadministrator / Systemadministrator >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
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 |