|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sql transaction log size because of reindexing
SQL server 2000 SP4
I have a database (size 13 GB) in full recovery mode. We take transaction log backups once everyday in late evening. Everynight I update the statistics and only on the weekend I do a complete reindexing. On the weekend after reindexing the database log size becomes about 10 GB. If I do not shrink it, It usually remains at that level for weeks. If space is not a issue is that ok? If database log size should be reduced after every reindexing on the weekend.. what is the best way of doing it? dbdba -- ontario, canada If you are going to bump it up to 10GB every weekend, what is the point of
shrinking it in the meantime? On 6/22/09 11:33 AM, in article 695412A5-A414-44A5-81C1-18B5DB731***@microsoft.com, "db" <d*@discussions.microsoft.com> wrote: Show quoteHide quote > SQL server 2000 SP4 > > I have a database (size 13 GB) in full recovery mode. We take transaction > log backups once everyday in late evening. Everynight I update the statistics > and only on the weekend I do a complete reindexing. > > On the weekend after reindexing the database log size becomes about 10 GB. > If I do not shrink it, It usually remains at that level for weeks. If space > is not a issue is that ok? > > If database log size should be reduced after every reindexing on the > weekend.. what is the best way of doing it? > > dbdba I agree: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
-- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:C6652486.1D922%ten.xoc@dnartreb.noraa... > If you are going to bump it up to 10GB every weekend, what is the > point of > shrinking it in the meantime? > > > > > On 6/22/09 11:33 AM, in article > 695412A5-A414-44A5-81C1-18B5DB731***@microsoft.com, "db" > <d*@discussions.microsoft.com> wrote: > >> SQL server 2000 SP4 >> >> I have a database (size 13 GB) in full recovery mode. We take >> transaction >> log backups once everyday in late evening. Everynight I update the >> statistics >> and only on the weekend I do a complete reindexing. >> >> On the weekend after reindexing the database log size becomes about >> 10 GB. >> If I do not shrink it, It usually remains at that level for weeks. >> If space >> is not a issue is that ok? >> >> If database log size should be reduced after every reindexing on >> the >> weekend.. what is the best way of doing it? >> >> dbdba > Perhaps for non-technical reasons such as when a ticket is generated because
a space usage threshold is crossed and action needs to be taken to close the ticket. I know in that case threshold should be reset to a higher value. I'm not saying this is good practice. It's just something I've seen in the real world. Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > If you are going to bump it up to 10GB every weekend, what is the point of > shrinking it in the meantime? > > > > > On 6/22/09 11:33 AM, in article > 695412A5-A414-44A5-81C1-18B5DB731***@microsoft.com, "db" > <d*@discussions.microsoft.com> wrote: > > > SQL server 2000 SP4 > > > > I have a database (size 13 GB) in full recovery mode. We take transaction > > log backups once everyday in late evening. Everynight I update the statistics > > and only on the weekend I do a complete reindexing. > > > > On the weekend after reindexing the database log size becomes about 10 GB. > > If I do not shrink it, It usually remains at that level for weeks. If space > > is not a issue is that ok? > > > > If database log size should be reduced after every reindexing on the > > weekend.. what is the best way of doing it? > > > > dbdba > > > Perhaps for non-technical reasons such as when a ticket is generated because Seems silly to me, to keep manually shrinking every week, when you can leave> a space usage threshold is crossed and action needs to be taken to close the > ticket. I know in that case threshold should be reset to a higher value. I'm > not saying this is good practice. It's just something I've seen in the real > world. the file the same size (saving performance as well), and/or change the threshold (or get a bigger disk), solving the problem for good. Sure it's silly. Many behvaiors driven by organizational dynamics are silly.
Linchi Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" wrote: > > Perhaps for non-technical reasons such as when a ticket is generated because > > a space usage threshold is crossed and action needs to be taken to close the > > ticket. I know in that case threshold should be reset to a higher value. I'm > > not saying this is good practice. It's just something I've seen in the real > > world. > > Seems silly to me, to keep manually shrinking every week, when you can leave > the file the same size (saving performance as well), and/or change the > threshold (or get a bigger disk), solving the problem for good. > > Correction: I am "reorganize data and index pages" not complete reindexing.
-- Show quoteHide quoteontario, canada "db" wrote: > SQL server 2000 SP4 > > I have a database (size 13 GB) in full recovery mode. We take transaction > log backups once everyday in late evening. Everynight I update the statistics > and only on the weekend I do a ("reorganize data and index pages" not reindexing). > > On the weekend after reindexing the database log size becomes about 10 GB. > If I do not shrink it, It usually remains at that level for weeks. If space > is not a issue is that ok? > > If database log size should be reduced after every reindexing on the > weekend.. what is the best way of doing it? > > dbdba > -- > ontario, canada I use option: reorganize data and index pages in maintenance plan
(Optimization) When should this option be used?? -- Show quoteHide quoteontario, canada "db" wrote: > Correction: I am "reorganize data and index pages" not complete reindexing. > -- > ontario, canada > > > "db" wrote: > > > SQL server 2000 SP4 > > > > I have a database (size 13 GB) in full recovery mode. We take transaction > > log backups once everyday in late evening. Everynight I update the statistics > > and only on the weekend I do a ("reorganize data and index pages" not reindexing). > > > > On the weekend after reindexing the database log size becomes about 10 GB. > > If I do not shrink it, It usually remains at that level for weeks. If space > > is not a issue is that ok? > > > > If database log size should be reduced after every reindexing on the > > weekend.. what is the best way of doing it? > > > > dbdba > > -- > > ontario, canada IMHO: never. Why don't you grab Ola's or Michelle's scripts?
http://tr.im/p5qu On 6/22/09 12:23 PM, in article 72DFEBEC-3AE1-42C4-9497-CFC0E02F8***@microsoft.com, "db" <d*@discussions.microsoft.com> wrote: Show quoteHide quote > I use option: reorganize data and index pages in maintenance plan > (Optimization) > > When should this option be used?? dbdba,
Using the expand and then shrink log that you are doing I would be wary of the number of VLF's. Run a DBCC LOGINFO on the database and see how many VLF's there are? (It would be the number of rows produced by the query). If you have lots then you would be best to offload the log, shring it, offload the log again and then expand the log to the maximum size that you need. This should reduce the number of VLF's. We run regular log backups as well as have an alert to run the backup if the log gets fuller than 65%. In your case you might want that lower. HTH Chris Show quoteHide quote "db" <d*@discussions.microsoft.com> wrote in message news:695412A5-A414-44A5-81C1-18B5DB731F49@microsoft.com... > SQL server 2000 SP4 > > I have a database (size 13 GB) in full recovery mode. We take transaction > log backups once everyday in late evening. Everynight I update the > statistics > and only on the weekend I do a complete reindexing. > > On the weekend after reindexing the database log size becomes about 10 GB. > If I do not shrink it, It usually remains at that level for weeks. If > space > is not a issue is that ok? > > If database log size should be reduced after every reindexing on the > weekend.. what is the best way of doing it? > > dbdba > -- > ontario, canada Hi
I had number of VLF. switched database recovery mode to simple and try to shrink the database (Both transaction log by selecting file and also complete database) through enterprise manager. It showed successful in both cases, but it did not shrink the tlog file. I have done this before. The overall database says 50.4 % used or space available almost 50%. -- Show quoteHide quoteontario, canada "Chris Wood" wrote: > dbdba, > > Using the expand and then shrink log that you are doing I would be wary of > the number of VLF's. Run a DBCC LOGINFO on the database and see how many > VLF's there are? (It would be the number of rows produced by the query). If > you have lots then you would be best to offload the log, shring it, offload > the log again and then expand the log to the maximum size that you need. > This should reduce the number of VLF's. We run regular log backups as well > as have an alert to run the backup if the log gets fuller than 65%. In your > case you might want that lower. > > HTH > > Chris > "db" <d*@discussions.microsoft.com> wrote in message > news:695412A5-A414-44A5-81C1-18B5DB731F49@microsoft.com... > > SQL server 2000 SP4 > > > > I have a database (size 13 GB) in full recovery mode. We take transaction > > log backups once everyday in late evening. Everynight I update the > > statistics > > and only on the weekend I do a complete reindexing. > > > > On the weekend after reindexing the database log size becomes about 10 GB. > > If I do not shrink it, It usually remains at that level for weeks. If > > space > > is not a issue is that ok? > > > > If database log size should be reduced after every reindexing on the > > weekend.. what is the best way of doing it? > > > > dbdba > > -- > > ontario, canada > > > > I had number of VLF. switched database recovery mode to simple and try to Don't use the UI for this.> shrink the database (Both transaction log by selecting file and also complete > database) through enterprise manager. BACKUP LOG database_name WITH TRUNCATE_ONLY; GO DBCC SHRINKFILE(database_name_log, 1); GO However please, please, please read these: http://tr.im/pfk0 http://tr.im/StopShrinking .... especially the pert about the VLF, and if you have active VLFs at
the end. -- Show quoteHide quoteTibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:C6655721.1D9BC%ten.xoc@dnartreb.noraa... >> I had number of VLF. switched database recovery mode to simple and >> try to >> shrink the database (Both transaction log by selecting file and >> also complete >> database) through enterprise manager. > > Don't use the UI for this. > > BACKUP LOG database_name WITH TRUNCATE_ONLY; > GO > DBCC SHRINKFILE(database_name_log, 1); > GO > > However please, please, please read these: > > http://tr.im/pfk0 > > http://tr.im/StopShrinking >  
Other interesting topics
|
|||||||||||||||||||||||
