well as I get further into this project of automatic updates I'm fining more and more barriers. The combo list box which indicates whether the employee is terminated or active might be a problem with sql since you cant create a Row source and a Row source type in a sql table. that combo box exsist in the properties of the form. The Row Source Type is a Value List. Shoot :(I guess I don't understand why this is a deal-breaker. You can:
A) Change your combo-box row source type to a query.
B) Use VB Code to set the Row Source to the results of a query.
C) If you know there are limited possible values for the field (Active, Terminated) then leave it hard-coded in the interface and just perform error checking before you insert data into the database.|||Ok blindman I'm sorry but I dont know VB code so for me that is a deal breaker. Please explain error checking, I will read about it I have booksonline to refer to.|||Are your dealing with an Access interface? Adding VB code to an Access module is very easy.
By error checking, I just mean that the database should verify that the submitted data is an acceptable value, and should return an error message if it is not.|||I tried that but thats not an option, but I figured it out I'm good thanks
Showing posts with label box. Show all posts
Showing posts with label box. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Automatic Email subject
Good Morning,
I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?
Thanks in advance.
Steve
Hi Steve,
The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).
The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved
You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.
It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica
Automatic Email subject
Good Morning,
I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?
Thanks in advance.
Steve
Hi Steve,
The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).
The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved
You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.
It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica
Thursday, March 8, 2012
automated restore
Is it possible to do an automatic/daily restore of a
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/feat...cle.php/2174411
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx
.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/feat...cle.php/2174411
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx
.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.
automated restore
Is it possible to do an automatic/daily restore of a
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.
Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/featu...le.php/2174411
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.
Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =
b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date =
(select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/featu...le.php/2174411
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.
automated restore
Is it possible to do an automatic/daily restore of a
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date = (select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/features/mssql/article.php/2174411
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.
database to a test box that is being backed up as part of
a maintenence plan? Since the backup file contains a
timestamp, I'm wondering if the file name is stored in one
of the msdb system tables where I can select it into the
restore script. thanks.Yes it is. Here is a bit of code to identify the last backup for a
database. You can then build a process around this to automate the restore.
Keep in mind if you do multiple types of backups (tran, diff, and full) then
you might have to restore from multiple files. Also if the file is on your
prod box, you might need to create a network share or something to get
access to the backup from test.
select physical_device_name
from msdb..backupset a join msdb..backupmediaset b on a.media_set_id =b.media_set_id
join msdb..backupmediafamily c on a.media_set_id = c.media_set_id
where backup_start_date = (select top 1 backup_start_date from msdb..backupset
where database_name = 'YourBaseNameHere'
order by backup_start_date desc)
Here is an article that will show you how to even build the restore script:
http://www.databasejournal.com/features/mssql/article.php/2174411
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"rob" <anonymous@.discussions.microsoft.com> wrote in message
news:2367001c45ed3$30411ae0$a601280a@.phx.gbl...
> Is it possible to do an automatic/daily restore of a
> database to a test box that is being backed up as part of
> a maintenence plan? Since the backup file contains a
> timestamp, I'm wondering if the file name is stored in one
> of the msdb system tables where I can select it into the
> restore script. thanks.
Thursday, February 16, 2012
Auto shrink not working as expected
I'll be the first to admin, I am not a sql expert. I have a nightly job
that backs up the tran logs on my db's and the box is checked to autoshrink
the tran log when it exceeds 100 mb. There are no errors and the shrink is
executed as shown in the log reports but the size doesn't appear to change.
What is really stumping me is if I do it manually, I have to do a tran log
backup, shrink the db. This results in a few mb shrinkage. If I then go
back and do the same thing again (This is consistent on four major DB's on
this server) tran log backup followed by a shrink db it then shrinks the
tranlog as expected. Several of these db's aren't activily being updated at
the backup/shrink time so records aren't being inserted (At least not that I
am aware of) at the time.
Is there something I am not doing or an idea someone may have to do this?
The backup and shrink are being handled via the setup by Enterprise Manager.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.An obvious question is why do you think you need to shrink the files every
day? If they grow every day then all you're doing is slowing down your
database every day because it has to grow the file. Do you tear down your
garage every time you back your car out and build it again when you come
home? This is about the same logic as shrinking the database and log files
daily. You should only shrink the files when you know they aren't going to
grow again.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Roger is 100% correct but these may be of interest to you as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transact...ileGrows_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
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||The transaction log backup is nightly, the attempted shrinking is weekly. I
don't believe a tran log needs to be 10 gig in size, considering the data
file is only 2 gig in size. I would have thought the nightly tran log back
up would force the tran log to re-use from the truncation point forward but
that doesn't seem to be occuring. The goal is to keep the tran log the max
log that could be used in a day, which is almost 2 gig in size.
Am I doing some thing wrong? Probably, but what I don't know.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> An obvious question is why do you think you need to shrink the files every
> day? If they grow every day then all you're doing is slowing down your
> database every day because it has to grow the file. Do you tear down your
> garage every time you back your car out and build it again when you come
> home? This is about the same logic as shrinking the database and log
> files daily. You should only shrink the files when you know they aren't
> going to grow again.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>|||Paul
You don't need to shrink the log , because it is meaningless as it will be
grown againg and again. One option is set up the log file with an appropiate
size that it does not need to grow frequently .
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The transaction log backup is nightly, the attempted shrinking is weekly.
> I don't believe a tran log needs to be 10 gig in size, considering the
> data file is only 2 gig in size. I would have thought the nightly tran
> log back up would force the tran log to re-use from the truncation point
> forward but that doesn't seem to be occuring. The goal is to keep the
> tran log the max log that could be used in a day, which is almost 2 gig in
> size.
> Am I doing some thing wrong? Probably, but what I don't know.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>|||I don't think people understand my predicament.
I have four specific db's on my sql server 2000 server. I run these in Full
Recovery mode with nightly tran log and weekly full back ups. The log file
in some instances is more than 5 times the size of the db. I find it hard
to believe that this would be considered normal since a nightly job would
never have more info than the db itself.
If you can provide details as to why this is normal, please do.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238Ke8jGwGHA.4444@.TK2MSFTNGP05.phx.gbl...
> Paul
> You don't need to shrink the log , because it is meaningless as it will
> be grown againg and again. One option is set up the log file with an
> appropiate size that it does not need to grow frequently .
>
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
>|||Paul Bergson wrote:
> I don't think people understand my predicament.
> I have four specific db's on my sql server 2000 server. I run these in Fu
ll
> Recovery mode with nightly tran log and weekly full back ups. The log fil
e
> in some instances is more than 5 times the size of the db. I find it hard
> to believe that this would be considered normal since a nightly job would
> never have more info than the db itself.
> If you can provide details as to why this is normal, please do.
>
Are you doing something like rebuilding indexes at night? Large imports?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Imports can be large
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uKZQv8GwGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Paul Bergson wrote:
> Are you doing something like rebuilding indexes at night? Large imports?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul Bergson wrote:
> Imports can be large
>
Ok, that could explain the large transaction log file. Say you're
importing 100,000 new rows of data, all as one transaction. The
transaction log has to be able to hold that entire transaction, in case
it has to roll back.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This may turn out to be a mute point. I'm on the phone with the vendor and
they are some how using the Log Files to store log history. It sounds like
it is unrelated to the actual logs that are need for roll back. I don't get
it. I need to get more info if this is the case. It sounds to me like they
have a configuration option which could allow me to control history kept
within this.
Maybe this is normal use, seems odd to me though.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O57rkXHwGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Paul Bergson wrote:
> Ok, that could explain the large transaction log file. Say you're
> importing 100,000 new rows of data, all as one transaction. The
> transaction log has to be able to hold that entire transaction, in case it
> has to roll back.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
that backs up the tran logs on my db's and the box is checked to autoshrink
the tran log when it exceeds 100 mb. There are no errors and the shrink is
executed as shown in the log reports but the size doesn't appear to change.
What is really stumping me is if I do it manually, I have to do a tran log
backup, shrink the db. This results in a few mb shrinkage. If I then go
back and do the same thing again (This is consistent on four major DB's on
this server) tran log backup followed by a shrink db it then shrinks the
tranlog as expected. Several of these db's aren't activily being updated at
the backup/shrink time so records aren't being inserted (At least not that I
am aware of) at the time.
Is there something I am not doing or an idea someone may have to do this?
The backup and shrink are being handled via the setup by Enterprise Manager.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.An obvious question is why do you think you need to shrink the files every
day? If they grow every day then all you're doing is slowing down your
database every day because it has to grow the file. Do you tear down your
garage every time you back your car out and build it again when you come
home? This is about the same logic as shrinking the database and log files
daily. You should only shrink the files when you know they aren't going to
grow again.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Roger is 100% correct but these may be of interest to you as well:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp Shrinking
considerations
http://www.nigelrivett.net/Transact...ileGrows_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
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
Andrew J. Kelly SQL MVP
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||The transaction log backup is nightly, the attempted shrinking is weekly. I
don't believe a tran log needs to be 10 gig in size, considering the data
file is only 2 gig in size. I would have thought the nightly tran log back
up would force the tran log to re-use from the truncation point forward but
that doesn't seem to be occuring. The goal is to keep the tran log the max
log that could be used in a day, which is almost 2 gig in size.
Am I doing some thing wrong? Probably, but what I don't know.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> An obvious question is why do you think you need to shrink the files every
> day? If they grow every day then all you're doing is slowing down your
> database every day because it has to grow the file. Do you tear down your
> garage every time you back your car out and build it again when you come
> home? This is about the same logic as shrinking the database and log
> files daily. You should only shrink the files when you know they aren't
> going to grow again.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>|||Paul
You don't need to shrink the log , because it is meaningless as it will be
grown againg and again. One option is set up the log file with an appropiate
size that it does not need to grow frequently .
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The transaction log backup is nightly, the attempted shrinking is weekly.
> I don't believe a tran log needs to be 10 gig in size, considering the
> data file is only 2 gig in size. I would have thought the nightly tran
> log back up would force the tran log to re-use from the truncation point
> forward but that doesn't seem to be occuring. The goal is to keep the
> tran log the max log that could be used in a day, which is almost 2 gig in
> size.
> Am I doing some thing wrong? Probably, but what I don't know.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>|||I don't think people understand my predicament.
I have four specific db's on my sql server 2000 server. I run these in Full
Recovery mode with nightly tran log and weekly full back ups. The log file
in some instances is more than 5 times the size of the db. I find it hard
to believe that this would be considered normal since a nightly job would
never have more info than the db itself.
If you can provide details as to why this is normal, please do.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238Ke8jGwGHA.4444@.TK2MSFTNGP05.phx.gbl...
> Paul
> You don't need to shrink the log , because it is meaningless as it will
> be grown againg and again. One option is set up the log file with an
> appropiate size that it does not need to grow frequently .
>
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
>|||Paul Bergson wrote:
> I don't think people understand my predicament.
> I have four specific db's on my sql server 2000 server. I run these in Fu
ll
> Recovery mode with nightly tran log and weekly full back ups. The log fil
e
> in some instances is more than 5 times the size of the db. I find it hard
> to believe that this would be considered normal since a nightly job would
> never have more info than the db itself.
> If you can provide details as to why this is normal, please do.
>
Are you doing something like rebuilding indexes at night? Large imports?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Imports can be large
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uKZQv8GwGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Paul Bergson wrote:
> Are you doing something like rebuilding indexes at night? Large imports?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul Bergson wrote:
> Imports can be large
>
Ok, that could explain the large transaction log file. Say you're
importing 100,000 new rows of data, all as one transaction. The
transaction log has to be able to hold that entire transaction, in case
it has to roll back.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This may turn out to be a mute point. I'm on the phone with the vendor and
they are some how using the Log Files to store log history. It sounds like
it is unrelated to the actual logs that are need for roll back. I don't get
it. I need to get more info if this is the case. It sounds to me like they
have a configuration option which could allow me to control history kept
within this.
Maybe this is normal use, seems odd to me though.
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O57rkXHwGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Paul Bergson wrote:
> Ok, that could explain the large transaction log file. Say you're
> importing 100,000 new rows of data, all as one transaction. The
> transaction log has to be able to hold that entire transaction, in case it
> has to roll back.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Auto shrink not working as expected
I'll be the first to admin, I am not a sql expert. I have a nightly job
that backs up the tran logs on my db's and the box is checked to autoshrink
the tran log when it exceeds 100 mb. There are no errors and the shrink is
executed as shown in the log reports but the size doesn't appear to change.
What is really stumping me is if I do it manually, I have to do a tran log
backup, shrink the db. This results in a few mb shrinkage. If I then go
back and do the same thing again (This is consistent on four major DB's on
this server) tran log backup followed by a shrink db it then shrinks the
tranlog as expected. Several of these db's aren't activily being updated at
the backup/shrink time so records aren't being inserted (At least not that I
am aware of) at the time.
Is there something I am not doing or an idea someone may have to do this?
The backup and shrink are being handled via the setup by Enterprise Manager.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.An obvious question is why do you think you need to shrink the files every
day? If they grow every day then all you're doing is slowing down your
database every day because it has to grow the file. Do you tear down your
garage every time you back your car out and build it again when you come
home? This is about the same logic as shrinking the database and log files
daily. You should only shrink the files when you know they aren't going to
grow again.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Roger is 100% correct but these may be of interest to you as well:
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
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
--
Andrew J. Kelly SQL MVP
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||The transaction log backup is nightly, the attempted shrinking is weekly. I
don't believe a tran log needs to be 10 gig in size, considering the data
file is only 2 gig in size. I would have thought the nightly tran log back
up would force the tran log to re-use from the truncation point forward but
that doesn't seem to be occuring. The goal is to keep the tran log the max
log that could be used in a day, which is almost 2 gig in size.
Am I doing some thing wrong? Probably, but what I don't know.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> An obvious question is why do you think you need to shrink the files every
> day? If they grow every day then all you're doing is slowing down your
> database every day because it has to grow the file. Do you tear down your
> garage every time you back your car out and build it again when you come
> home? This is about the same logic as shrinking the database and log
> files daily. You should only shrink the files when you know they aren't
> going to grow again.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>> I'll be the first to admin, I am not a sql expert. I have a nightly job
>> that backs up the tran logs on my db's and the box is checked to
>> autoshrink the tran log when it exceeds 100 mb. There are no errors and
>> the shrink is executed as shown in the log reports but the size doesn't
>> appear to change. What is really stumping me is if I do it manually, I
>> have to do a tran log backup, shrink the db. This results in a few mb
>> shrinkage. If I then go back and do the same thing again (This is
>> consistent on four major DB's on this server) tran log backup followed by
>> a shrink db it then shrinks the tranlog as expected. Several of these
>> db's aren't activily being updated at the backup/shrink time so records
>> aren't being inserted (At least not that I am aware of) at the time.
>> Is there something I am not doing or an idea someone may have to do this?
>> The backup and shrink are being handled via the setup by Enterprise
>> Manager.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Paul
You don't need to shrink the log , because it is meaningless as it will be
grown againg and again. One option is set up the log file with an appropiate
size that it does not need to grow frequently .
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The transaction log backup is nightly, the attempted shrinking is weekly.
> I don't believe a tran log needs to be 10 gig in size, considering the
> data file is only 2 gig in size. I would have thought the nightly tran
> log back up would force the tran log to re-use from the truncation point
> forward but that doesn't seem to be occuring. The goal is to keep the
> tran log the max log that could be used in a day, which is almost 2 gig in
> size.
> Am I doing some thing wrong? Probably, but what I don't know.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>> An obvious question is why do you think you need to shrink the files
>> every day? If they grow every day then all you're doing is slowing down
>> your database every day because it has to grow the file. Do you tear
>> down your garage every time you back your car out and build it again when
>> you come home? This is about the same logic as shrinking the database
>> and log files daily. You should only shrink the files when you know they
>> aren't going to grow again.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
>> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>> I'll be the first to admin, I am not a sql expert. I have a nightly job
>> that backs up the tran logs on my db's and the box is checked to
>> autoshrink the tran log when it exceeds 100 mb. There are no errors and
>> the shrink is executed as shown in the log reports but the size doesn't
>> appear to change. What is really stumping me is if I do it manually, I
>> have to do a tran log backup, shrink the db. This results in a few mb
>> shrinkage. If I then go back and do the same thing again (This is
>> consistent on four major DB's on this server) tran log backup followed
>> by a shrink db it then shrinks the tranlog as expected. Several of
>> these db's aren't activily being updated at the backup/shrink time so
>> records aren't being inserted (At least not that I am aware of) at the
>> time.
>> Is there something I am not doing or an idea someone may have to do
>> this? The backup and shrink are being handled via the setup by
>> Enterprise Manager.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>|||I don't think people understand my predicament.
I have four specific db's on my sql server 2000 server. I run these in Full
Recovery mode with nightly tran log and weekly full back ups. The log file
in some instances is more than 5 times the size of the db. I find it hard
to believe that this would be considered normal since a nightly job would
never have more info than the db itself.
If you can provide details as to why this is normal, please do.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238Ke8jGwGHA.4444@.TK2MSFTNGP05.phx.gbl...
> Paul
> You don't need to shrink the log , because it is meaningless as it will
> be grown againg and again. One option is set up the log file with an
> appropiate size that it does not need to grow frequently .
>
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
>> The transaction log backup is nightly, the attempted shrinking is weekly.
>> I don't believe a tran log needs to be 10 gig in size, considering the
>> data file is only 2 gig in size. I would have thought the nightly tran
>> log back up would force the tran log to re-use from the truncation point
>> forward but that doesn't seem to be occuring. The goal is to keep the
>> tran log the max log that could be used in a day, which is almost 2 gig
>> in size.
>> Am I doing some thing wrong? Probably, but what I don't know.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>> An obvious question is why do you think you need to shrink the files
>> every day? If they grow every day then all you're doing is slowing down
>> your database every day because it has to grow the file. Do you tear
>> down your garage every time you back your car out and build it again
>> when you come home? This is about the same logic as shrinking the
>> database and log files daily. You should only shrink the files when you
>> know they aren't going to grow again.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
>> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>> I'll be the first to admin, I am not a sql expert. I have a nightly
>> job that backs up the tran logs on my db's and the box is checked to
>> autoshrink the tran log when it exceeds 100 mb. There are no errors
>> and the shrink is executed as shown in the log reports but the size
>> doesn't appear to change. What is really stumping me is if I do it
>> manually, I have to do a tran log backup, shrink the db. This results
>> in a few mb shrinkage. If I then go back and do the same thing again
>> (This is consistent on four major DB's on this server) tran log backup
>> followed by a shrink db it then shrinks the tranlog as expected.
>> Several of these db's aren't activily being updated at the
>> backup/shrink time so records aren't being inserted (At least not that
>> I am aware of) at the time.
>> Is there something I am not doing or an idea someone may have to do
>> this? The backup and shrink are being handled via the setup by
>> Enterprise Manager.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>>
>|||Paul Bergson wrote:
> I don't think people understand my predicament.
> I have four specific db's on my sql server 2000 server. I run these in Full
> Recovery mode with nightly tran log and weekly full back ups. The log file
> in some instances is more than 5 times the size of the db. I find it hard
> to believe that this would be considered normal since a nightly job would
> never have more info than the db itself.
> If you can provide details as to why this is normal, please do.
>
Are you doing something like rebuilding indexes at night? Large imports?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Imports can be large
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uKZQv8GwGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Paul Bergson wrote:
>> I don't think people understand my predicament.
>> I have four specific db's on my sql server 2000 server. I run these in
>> Full Recovery mode with nightly tran log and weekly full back ups. The
>> log file in some instances is more than 5 times the size of the db. I
>> find it hard to believe that this would be considered normal since a
>> nightly job would never have more info than the db itself.
>> If you can provide details as to why this is normal, please do.
> Are you doing something like rebuilding indexes at night? Large imports?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul Bergson wrote:
> Imports can be large
>
Ok, that could explain the large transaction log file. Say you're
importing 100,000 new rows of data, all as one transaction. The
transaction log has to be able to hold that entire transaction, in case
it has to roll back.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This may turn out to be a mute point. I'm on the phone with the vendor and
they are some how using the Log Files to store log history. It sounds like
it is unrelated to the actual logs that are need for roll back. I don't get
it. I need to get more info if this is the case. It sounds to me like they
have a configuration option which could allow me to control history kept
within this.
Maybe this is normal use, seems odd to me though.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O57rkXHwGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Paul Bergson wrote:
>> Imports can be large
> Ok, that could explain the large transaction log file. Say you're
> importing 100,000 new rows of data, all as one transaction. The
> transaction log has to be able to hold that entire transaction, in case it
> has to roll back.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
that backs up the tran logs on my db's and the box is checked to autoshrink
the tran log when it exceeds 100 mb. There are no errors and the shrink is
executed as shown in the log reports but the size doesn't appear to change.
What is really stumping me is if I do it manually, I have to do a tran log
backup, shrink the db. This results in a few mb shrinkage. If I then go
back and do the same thing again (This is consistent on four major DB's on
this server) tran log backup followed by a shrink db it then shrinks the
tranlog as expected. Several of these db's aren't activily being updated at
the backup/shrink time so records aren't being inserted (At least not that I
am aware of) at the time.
Is there something I am not doing or an idea someone may have to do this?
The backup and shrink are being handled via the setup by Enterprise Manager.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.An obvious question is why do you think you need to shrink the files every
day? If they grow every day then all you're doing is slowing down your
database every day because it has to grow the file. Do you tear down your
garage every time you back your car out and build it again when you come
home? This is about the same logic as shrinking the database and log files
daily. You should only shrink the files when you know they aren't going to
grow again.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Roger is 100% correct but these may be of interest to you as well:
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
http://www.support.microsoft.com/?id=305635 Timeout while DB expanding
http://www.support.microsoft.com/?id=307487 Shrinking TempDB
--
Andrew J. Kelly SQL MVP
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
> I'll be the first to admin, I am not a sql expert. I have a nightly job
> that backs up the tran logs on my db's and the box is checked to
> autoshrink the tran log when it exceeds 100 mb. There are no errors and
> the shrink is executed as shown in the log reports but the size doesn't
> appear to change. What is really stumping me is if I do it manually, I
> have to do a tran log backup, shrink the db. This results in a few mb
> shrinkage. If I then go back and do the same thing again (This is
> consistent on four major DB's on this server) tran log backup followed by
> a shrink db it then shrinks the tranlog as expected. Several of these
> db's aren't activily being updated at the backup/shrink time so records
> aren't being inserted (At least not that I am aware of) at the time.
> Is there something I am not doing or an idea someone may have to do this?
> The backup and shrink are being handled via the setup by Enterprise
> Manager.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||The transaction log backup is nightly, the attempted shrinking is weekly. I
don't believe a tran log needs to be 10 gig in size, considering the data
file is only 2 gig in size. I would have thought the nightly tran log back
up would force the tran log to re-use from the truncation point forward but
that doesn't seem to be occuring. The goal is to keep the tran log the max
log that could be used in a day, which is almost 2 gig in size.
Am I doing some thing wrong? Probably, but what I don't know.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
> An obvious question is why do you think you need to shrink the files every
> day? If they grow every day then all you're doing is slowing down your
> database every day because it has to grow the file. Do you tear down your
> garage every time you back your car out and build it again when you come
> home? This is about the same logic as shrinking the database and log
> files daily. You should only shrink the files when you know they aren't
> going to grow again.
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>> I'll be the first to admin, I am not a sql expert. I have a nightly job
>> that backs up the tran logs on my db's and the box is checked to
>> autoshrink the tran log when it exceeds 100 mb. There are no errors and
>> the shrink is executed as shown in the log reports but the size doesn't
>> appear to change. What is really stumping me is if I do it manually, I
>> have to do a tran log backup, shrink the db. This results in a few mb
>> shrinkage. If I then go back and do the same thing again (This is
>> consistent on four major DB's on this server) tran log backup followed by
>> a shrink db it then shrinks the tranlog as expected. Several of these
>> db's aren't activily being updated at the backup/shrink time so records
>> aren't being inserted (At least not that I am aware of) at the time.
>> Is there something I am not doing or an idea someone may have to do this?
>> The backup and shrink are being handled via the setup by Enterprise
>> Manager.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>|||Paul
You don't need to shrink the log , because it is meaningless as it will be
grown againg and again. One option is set up the log file with an appropiate
size that it does not need to grow frequently .
"Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
> The transaction log backup is nightly, the attempted shrinking is weekly.
> I don't believe a tran log needs to be 10 gig in size, considering the
> data file is only 2 gig in size. I would have thought the nightly tran
> log back up would force the tran log to re-use from the truncation point
> forward but that doesn't seem to be occuring. The goal is to keep the
> tran log the max log that could be used in a day, which is almost 2 gig in
> size.
> Am I doing some thing wrong? Probably, but what I don't know.
> --
> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
> http://www.pbbergs.com
> Please no e-mails, any questions should be posted in the NewsGroup
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>> An obvious question is why do you think you need to shrink the files
>> every day? If they grow every day then all you're doing is slowing down
>> your database every day because it has to grow the file. Do you tear
>> down your garage every time you back your car out and build it again when
>> you come home? This is about the same logic as shrinking the database
>> and log files daily. You should only shrink the files when you know they
>> aren't going to grow again.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
>> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>> I'll be the first to admin, I am not a sql expert. I have a nightly job
>> that backs up the tran logs on my db's and the box is checked to
>> autoshrink the tran log when it exceeds 100 mb. There are no errors and
>> the shrink is executed as shown in the log reports but the size doesn't
>> appear to change. What is really stumping me is if I do it manually, I
>> have to do a tran log backup, shrink the db. This results in a few mb
>> shrinkage. If I then go back and do the same thing again (This is
>> consistent on four major DB's on this server) tran log backup followed
>> by a shrink db it then shrinks the tranlog as expected. Several of
>> these db's aren't activily being updated at the backup/shrink time so
>> records aren't being inserted (At least not that I am aware of) at the
>> time.
>> Is there something I am not doing or an idea someone may have to do
>> this? The backup and shrink are being handled via the setup by
>> Enterprise Manager.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>|||I don't think people understand my predicament.
I have four specific db's on my sql server 2000 server. I run these in Full
Recovery mode with nightly tran log and weekly full back ups. The log file
in some instances is more than 5 times the size of the db. I find it hard
to believe that this would be considered normal since a nightly job would
never have more info than the db itself.
If you can provide details as to why this is normal, please do.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%238Ke8jGwGHA.4444@.TK2MSFTNGP05.phx.gbl...
> Paul
> You don't need to shrink the log , because it is meaningless as it will
> be grown againg and again. One option is set up the log file with an
> appropiate size that it does not need to grow frequently .
>
> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
> news:%23yRZMdGwGHA.1272@.TK2MSFTNGP05.phx.gbl...
>> The transaction log backup is nightly, the attempted shrinking is weekly.
>> I don't believe a tran log needs to be 10 gig in size, considering the
>> data file is only 2 gig in size. I would have thought the nightly tran
>> log back up would force the tran log to re-use from the truncation point
>> forward but that doesn't seem to be occuring. The goal is to keep the
>> tran log the max log that could be used in a day, which is almost 2 gig
>> in size.
>> Am I doing some thing wrong? Probably, but what I don't know.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
>> news:e%23oT4sCwGHA.1436@.TK2MSFTNGP02.phx.gbl...
>> An obvious question is why do you think you need to shrink the files
>> every day? If they grow every day then all you're doing is slowing down
>> your database every day because it has to grow the file. Do you tear
>> down your garage every time you back your car out and build it again
>> when you come home? This is about the same logic as shrinking the
>> database and log files daily. You should only shrink the files when you
>> know they aren't going to grow again.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Paul Bergson" <pbergson@.allete_nospam.com> wrote in message
>> news:%23CHDBP%23vGHA.4512@.TK2MSFTNGP05.phx.gbl...
>> I'll be the first to admin, I am not a sql expert. I have a nightly
>> job that backs up the tran logs on my db's and the box is checked to
>> autoshrink the tran log when it exceeds 100 mb. There are no errors
>> and the shrink is executed as shown in the log reports but the size
>> doesn't appear to change. What is really stumping me is if I do it
>> manually, I have to do a tran log backup, shrink the db. This results
>> in a few mb shrinkage. If I then go back and do the same thing again
>> (This is consistent on four major DB's on this server) tran log backup
>> followed by a shrink db it then shrinks the tranlog as expected.
>> Several of these db's aren't activily being updated at the
>> backup/shrink time so records aren't being inserted (At least not that
>> I am aware of) at the time.
>> Is there something I am not doing or an idea someone may have to do
>> this? The backup and shrink are being handled via the setup by
>> Enterprise Manager.
>> --
>> Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
>> http://www.pbbergs.com
>> Please no e-mails, any questions should be posted in the NewsGroup
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>>
>>
>|||Paul Bergson wrote:
> I don't think people understand my predicament.
> I have four specific db's on my sql server 2000 server. I run these in Full
> Recovery mode with nightly tran log and weekly full back ups. The log file
> in some instances is more than 5 times the size of the db. I find it hard
> to believe that this would be considered normal since a nightly job would
> never have more info than the db itself.
> If you can provide details as to why this is normal, please do.
>
Are you doing something like rebuilding indexes at night? Large imports?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Imports can be large
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uKZQv8GwGHA.4296@.TK2MSFTNGP06.phx.gbl...
> Paul Bergson wrote:
>> I don't think people understand my predicament.
>> I have four specific db's on my sql server 2000 server. I run these in
>> Full Recovery mode with nightly tran log and weekly full back ups. The
>> log file in some instances is more than 5 times the size of the db. I
>> find it hard to believe that this would be considered normal since a
>> nightly job would never have more info than the db itself.
>> If you can provide details as to why this is normal, please do.
> Are you doing something like rebuilding indexes at night? Large imports?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Paul Bergson wrote:
> Imports can be large
>
Ok, that could explain the large transaction log file. Say you're
importing 100,000 new rows of data, all as one transaction. The
transaction log has to be able to hold that entire transaction, in case
it has to roll back.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This may turn out to be a mute point. I'm on the phone with the vendor and
they are some how using the Log Files to store log history. It sounds like
it is unrelated to the actual logs that are need for roll back. I don't get
it. I need to get more info if this is the case. It sounds to me like they
have a configuration option which could allow me to control history kept
within this.
Maybe this is normal use, seems odd to me though.
--
Paul Bergson MCT, MCSE, MCSA, Security+, CNE, CNA, CCA
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O57rkXHwGHA.1224@.TK2MSFTNGP03.phx.gbl...
> Paul Bergson wrote:
>> Imports can be large
> Ok, that could explain the large transaction log file. Say you're
> importing 100,000 new rows of data, all as one transaction. The
> transaction log has to be able to hold that entire transaction, in case it
> has to roll back.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
Monday, February 13, 2012
Auto populating the fields box.
After I hook up a datasource with a stored procedure, I
run the query and expect the fields box to populate with
the retun query columns. This does not happen. Are there
certain conditions that the query has to meet for SQL RS
to do this, for example, does the query have to return a
permanent table?
Thanks,Yes.
For your query, in order to update the fields list, you will need to click
on the "Refresh Fields" button in the Data Set toolbar in Data View.
The report designer has two methods available to update the fields list.
1. Automatic (fast, low-cost): The Schema is queried by calling
IDbCommand.ExecuteReader(SchemaOnly), which returns schema information
without executing the query (parameter values are not needed). This method
is automatically invoked when you switch from Data to Layout View. It is
fast because the query doesn't have to be executed. However, there are
queries whose schemas can only be discovered by running the query. The
queries which don't return a schema include stored procedures that either
use dynamic SQL or use temporary tables. Most SQL command text and stored
procedure calls with a fixed schema work fine here.
2. Manual (slow, higher-cost): The schema is queried by calling
IDbCommand.ExecuteReader(SingleResult), which executes the query returning
the 1st row and also schema information. This method is invoked when you
click on the "Refresh Fields" button in the Data View (query designer). For
stored procedures, this method requires asking the user for sample parameter
values and then running the query with those values.
--
Jerry Povse
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"stevec" <anonymous@.discussions.microsoft.com> wrote in message
news:27dae01c4635a$b1d41810$a301280a@.phx.gbl...
> After I hook up a datasource with a stored procedure, I
> run the query and expect the fields box to populate with
> the retun query columns. This does not happen. Are there
> certain conditions that the query has to meet for SQL RS
> to do this, for example, does the query have to return a
> permanent table?
> Thanks,
run the query and expect the fields box to populate with
the retun query columns. This does not happen. Are there
certain conditions that the query has to meet for SQL RS
to do this, for example, does the query have to return a
permanent table?
Thanks,Yes.
For your query, in order to update the fields list, you will need to click
on the "Refresh Fields" button in the Data Set toolbar in Data View.
The report designer has two methods available to update the fields list.
1. Automatic (fast, low-cost): The Schema is queried by calling
IDbCommand.ExecuteReader(SchemaOnly), which returns schema information
without executing the query (parameter values are not needed). This method
is automatically invoked when you switch from Data to Layout View. It is
fast because the query doesn't have to be executed. However, there are
queries whose schemas can only be discovered by running the query. The
queries which don't return a schema include stored procedures that either
use dynamic SQL or use temporary tables. Most SQL command text and stored
procedure calls with a fixed schema work fine here.
2. Manual (slow, higher-cost): The schema is queried by calling
IDbCommand.ExecuteReader(SingleResult), which executes the query returning
the 1st row and also schema information. This method is invoked when you
click on the "Refresh Fields" button in the Data View (query designer). For
stored procedures, this method requires asking the user for sample parameter
values and then running the query with those values.
--
Jerry Povse
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"stevec" <anonymous@.discussions.microsoft.com> wrote in message
news:27dae01c4635a$b1d41810$a301280a@.phx.gbl...
> After I hook up a datasource with a stored procedure, I
> run the query and expect the fields box to populate with
> the retun query columns. This does not happen. Are there
> certain conditions that the query has to meet for SQL RS
> to do this, for example, does the query have to return a
> permanent table?
> Thanks,
Subscribe to:
Comments (Atom)