Reducing the size of the Transaction Log.

We have a SQL Server 2000 SP3 database with recovery model set to full. I
set up 3 automatic backup jobs as follows:
Each day,Mon thru Fri at 9:00 am
1 step - full backup of database to file A with overwrite
Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
1 step - transaction log backup of database to file A with append
Each day,Mon thru Fri at 5:05 pm
1st step - transaction log backup of database to file A with append
2nd step - execute BACKUP LOG WITH NO_LOG
3rd step - execute DBCC SHRINK (db_file_name,1)
First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want, even
though I back up the Log.
When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
However, I get an error in the Event Viever(Application Log) stating
"Database Log truncated".
My question is:
Should I worry about this error ?
Is any data loss likely will this arrangement ?
After all, I truncate the Transaction Log on purpose, right after backing it
back in the previous step.
Regards,
SezginYou should not use the no_log command if you require point in time recovery,
and if you do not require point in time recovery, set your recovery mode to
simple. I would advise not to be shrinking your log or data file like you
are as if the db or log has to autogrow it costs you at the point it needs
to allocate more space and that is typically when your db is busy.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> We have a SQL Server 2000 SP3 database with recovery model set to full. I
> set up 3 automatic backup jobs as follows:
> Each day,Mon thru Fri at 9:00 am
> 1 step - full backup of database to file A with overwrite
> Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> 1 step - transaction log backup of database to file A with append
> Each day,Mon thru Fri at 5:05 pm
> 1st step - transaction log backup of database to file A with append
> 2nd step - execute BACKUP LOG WITH NO_LOG
> 3rd step - execute DBCC SHRINK (db_file_name,1)
> First I tried to shrink the Log file without issuing the "BACKUP LOG WITH
> NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
even
> though I back up the Log.
>
> When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> However, I get an error in the Event Viever(Application Log) stating
> "Database Log truncated".
> My question is:
> Should I worry about this error ?
> Is any data loss likely will this arrangement ?
> After all, I truncate the Transaction Log on purpose, right after backing
it
> back in the previous step.
> Regards,
> Sezgin
>
>|||Thanks for your reply Ray,
The database has a high number of transactions every day. I have to make
sure any possible data loss is minimized. That is why I used full recovery
model. During tests, in about 5 weeks time, the Transaction Log grew up to
nearly 7 GB (the data file currently is just over 1 GB). I tried my best to
shrink the Transaction Log without using the "Backup Log with no_log"
command, but to no success. Could you offer any advice or alternative
solution.
Best regards,
Sezgin Rafet
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> You should not use the no_log command if you require point in time
recovery,
> and if you do not require point in time recovery, set your recovery mode
to
> simple. I would advise not to be shrinking your log or data file like you
> are as if the db or log has to autogrow it costs you at the point it needs
> to allocate more space and that is typically when your db is busy.
> HTH
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > We have a SQL Server 2000 SP3 database with recovery model set to full.
I
> > set up 3 automatic backup jobs as follows:
> >
> > Each day,Mon thru Fri at 9:00 am
> > 1 step - full backup of database to file A with overwrite
> >
> > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > 1 step - transaction log backup of database to file A with append
> >
> > Each day,Mon thru Fri at 5:05 pm
> > 1st step - transaction log backup of database to file A with append
> > 2nd step - execute BACKUP LOG WITH NO_LOG
> > 3rd step - execute DBCC SHRINK (db_file_name,1)
> >
> > First I tried to shrink the Log file without issuing the "BACKUP LOG
WITH
> > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> even
> > though I back up the Log.
> >
> >
> > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > However, I get an error in the Event Viever(Application Log) stating
> > "Database Log truncated".
> >
> > My question is:
> > Should I worry about this error ?
> > Is any data loss likely will this arrangement ?
> >
> > After all, I truncate the Transaction Log on purpose, right after
backing
> it
> > back in the previous step.
> >
> > Regards,
> >
> > Sezgin
> >
> >
> >
> >
>|||THe log should eventually shrink, and you should NOT be using backup log
with no_log...
I noticed however that you said you were using dbcc shrink...
The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
be part of the problem?
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sezgin Rafet" <srafet@.hotmail.com> wrote in message
news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> Thanks for your reply Ray,
>
> The database has a high number of transactions every day. I have to make
> sure any possible data loss is minimized. That is why I used full recovery
> model. During tests, in about 5 weeks time, the Transaction Log grew up to
> nearly 7 GB (the data file currently is just over 1 GB). I tried my best
to
> shrink the Transaction Log without using the "Backup Log with no_log"
> command, but to no success. Could you offer any advice or alternative
> solution.
>
> Best regards,
>
> Sezgin Rafet
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > You should not use the no_log command if you require point in time
> recovery,
> > and if you do not require point in time recovery, set your recovery mode
> to
> > simple. I would advise not to be shrinking your log or data file like
you
> > are as if the db or log has to autogrow it costs you at the point it
needs
> > to allocate more space and that is typically when your db is busy.
> >
> > HTH
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > We have a SQL Server 2000 SP3 database with recovery model set to
full.
> I
> > > set up 3 automatic backup jobs as follows:
> > >
> > > Each day,Mon thru Fri at 9:00 am
> > > 1 step - full backup of database to file A with overwrite
> > >
> > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > 1 step - transaction log backup of database to file A with append
> > >
> > > Each day,Mon thru Fri at 5:05 pm
> > > 1st step - transaction log backup of database to file A with append
> > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > >
> > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> WITH
> > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I want,
> > even
> > > though I back up the Log.
> > >
> > >
> > > When I use BACKUP LOG WITH NO_LOG everything seems to be working OK.
> > > However, I get an error in the Event Viever(Application Log) stating
> > > "Database Log truncated".
> > >
> > > My question is:
> > > Should I worry about this error ?
> > > Is any data loss likely will this arrangement ?
> > >
> > > After all, I truncate the Transaction Log on purpose, right after
> backing
> > it
> > > back in the previous step.
> > >
> > > Regards,
> > >
> > > Sezgin
> > >
> > >
> > >
> > >
> >
> >
>|||Thanks Wayne,
I used DBCC SHRINKFILE(db_log_file_name,1) , just have mistyped it in the
post.
Sezgin Rafet
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23G0pjgIyDHA.2440@.TK2MSFTNGP12.phx.gbl...
> THe log should eventually shrink, and you should NOT be using backup log
> with no_log...
> I noticed however that you said you were using dbcc shrink...
> The proper command is dbcc shrinkfile, or dbcc shrikdatabase... Could that
> be part of the problem?
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> news:uvz5lIGyDHA.2308@.TK2MSFTNGP11.phx.gbl...
> > Thanks for your reply Ray,
> >
> >
> >
> > The database has a high number of transactions every day. I have to make
> > sure any possible data loss is minimized. That is why I used full
recovery
> > model. During tests, in about 5 weeks time, the Transaction Log grew up
to
> > nearly 7 GB (the data file currently is just over 1 GB). I tried my best
> to
> > shrink the Transaction Log without using the "Backup Log with no_log"
> > command, but to no success. Could you offer any advice or alternative
> > solution.
> >
> >
> >
> > Best regards,
> >
> >
> >
> > Sezgin Rafet
> >
> >
> > "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> > news:%23L6$G5vxDHA.1740@.TK2MSFTNGP09.phx.gbl...
> > > You should not use the no_log command if you require point in time
> > recovery,
> > > and if you do not require point in time recovery, set your recovery
mode
> > to
> > > simple. I would advise not to be shrinking your log or data file like
> you
> > > are as if the db or log has to autogrow it costs you at the point it
> needs
> > > to allocate more space and that is typically when your db is busy.
> > >
> > > HTH
> > >
> > > --
> > > Ray Higdon MCSE, MCDBA, CCNA
> > > --
> > > "Sezgin Rafet" <srafet@.hotmail.com> wrote in message
> > > news:OUaTekvxDHA.2520@.TK2MSFTNGP10.phx.gbl...
> > > > We have a SQL Server 2000 SP3 database with recovery model set to
> full.
> > I
> > > > set up 3 automatic backup jobs as follows:
> > > >
> > > > Each day,Mon thru Fri at 9:00 am
> > > > 1 step - full backup of database to file A with overwrite
> > > >
> > > > Each day,Mon thru Fri every 30 minutes,between 9:30 am and 5:00 pm
> > > > 1 step - transaction log backup of database to file A with append
> > > >
> > > > Each day,Mon thru Fri at 5:05 pm
> > > > 1st step - transaction log backup of database to file A with append
> > > > 2nd step - execute BACKUP LOG WITH NO_LOG
> > > > 3rd step - execute DBCC SHRINK (db_file_name,1)
> > > >
> > > > First I tried to shrink the Log file without issuing the "BACKUP LOG
> > WITH
> > > > NO_LOG" statement , but DBCC SHRINK doesn't shrink the file as I
want,
> > > even
> > > > though I back up the Log.
> > > >
> > > >
> > > > When I use BACKUP LOG WITH NO_LOG everything seems to be working
OK.
> > > > However, I get an error in the Event Viever(Application Log) stating
> > > > "Database Log truncated".
> > > >
> > > > My question is:
> > > > Should I worry about this error ?
> > > > Is any data loss likely will this arrangement ?
> > > >
> > > > After all, I truncate the Transaction Log on purpose, right after
> > backing
> > > it
> > > > back in the previous step.
> > > >
> > > > Regards,
> > > >
> > > > Sezgin
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Reducing the size of the transaction log file

At this time I am only playing with the applications that generate the data and send it to the database. Without doing too much, and with deleting most data tables that were created, my transaction log file has grown over a gigabyte. I tried using the SQL server management studio (express) to shrink the database (tried shrinking files, too) but that did not make the file smaller. Right now there is hardly any data in the database (6 tables, a dozen columns and rows each) so it must be old transactions that are kept in the log. How do I get rid of the old data and make the file size smaller? Thanks.
KamenHi,

I had the same issue yesterday, i had to, Backup the log file then use shrink, backup one moore time and shrink.|||1. Check your database 'Recovery Model', set it according to your requirement.
2. Make a backup plan matching your data sensitivity & requirement as well as schedule it.
3. Plan Log backup along with Log Truncate option.

Refer Books OnLine from SQL Query Analyzer.|||Thank you very much!
Kamen

Reducing the PDF File Size

Hi all.

I'm using MS SQL 2000's Reporting Services. Our output PDFs are usually composed of fifteen or more pages. Our file size typically exceeds 2 MB per report, I was testing the report on sample outputs and I was getting around 40kb per chart and each of our pages are typically composed of 2-3 charts.

Is there a tweak that can be applied to reduce the file size of the output pdf?

Thanks,
EdwardDO you have any images rendered into the report ? As the report is based on Adove 4 definitions its limitated to the rendering compressions.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||Yes there are images, from the outputs I've generated that does not include any chart the file size is significantly smaller around 8kb per page. The majority of the file size is made up of the charts themselves.

Are you saying that the file size increase caused by the charts cannot be reduced any further due to rendering compressions?|||I guess this is probably the case. WHich version / SP are you currently running ? Ther ehvae been some iprovements on the Reporting rendering during the history of the Service pack development.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||The version I am currently running on the server is:

8.0.1038.0

What service pack version does this belong to?

Thanks.|||Thats the most recent service pack, its service pack 2. SOrry, but there are no more other tweaks that come in my mind. We once had the problem that images (blobs) from the database weren′t compressed properly, because the rendering mechanism could not handle the images in a right way, this was fixed in a Service Pack (I think is was service pack 2)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||An issue has been opened for this problem. Please add your vote. https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=232211

Reducing the PDF File Size

Hi all.

I'm using MS SQL 2000's Reporting Services. Our output PDFs are usually composed of fifteen or more pages. Our file size typically exceeds 2 MB per report, I was testing the report on sample outputs and I was getting around 40kb per chart and each of our pages are typically composed of 2-3 charts.

Is there a tweak that can be applied to reduce the file size of the output pdf?

Thanks,
EdwardDO you have any images rendered into the report ? As the report is based on Adove 4 definitions its limitated to the rendering compressions.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de|||Yes there are images, from the outputs I've generated that does not include any chart the file size is significantly smaller around 8kb per page. The majority of the file size is made up of the charts themselves.

Are you saying that the file size increase caused by the charts cannot be reduced any further due to rendering compressions?|||I guess this is probably the case. WHich version / SP are you currently running ? Ther ehvae been some iprovements on the Reporting rendering during the history of the Service pack development.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||The version I am currently running on the server is:

8.0.1038.0

What service pack version does this belong to?

Thanks.|||Thats the most recent service pack, its service pack 2. SOrry, but there are no more other tweaks that come in my mind. We once had the problem that images (blobs) from the database weren′t compressed properly, because the rendering mechanism could not handle the images in a right way, this was fixed in a Service Pack (I think is was service pack 2)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||An issue has been opened for this problem. Please add your vote. https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=232211

reducing tempdb

I am trying to reduce the size of my tempdb using the DBCC SHRINKFILE command and get the following error.

DBCC SHRINKFILE: Page 1:1164376 could not be moved because it is a work table page.

How can I get around this so I can shrink the db?

Hi

Please check the following KBA.

http://support.microsoft.com/kb/307487

Jag

Reducing Spam Associated with Posting to Newsgroups

Due to a recent increase in spam sent to posters in newsgroups, Microsoft advises that newsgroup participants should consider avoiding posting to newsgroups using their real email address. Microsoft is also committed to continuing to address the issue of spam from a technological perspective.
To help avoid receiving unwanted messages (spam) in your regular e-mail account, you may not want to include your regular e-mail address when you post a question or reply to a post in a discussion group. Instead you may want to do one of the following:
* Use a modified e-mail address: Use a different version of your e-mail address that others will understand, but that spam tools can't automatically pick up. For example, if your actual e-mail address is "emailname@.account.com", consider using a modified e-mail address such as: "emailnameaccount.com.invalid", emailname@.REMOVE_CAPS_AND_INVALIDaccount.com.invalid, or emailname@.account.com.NO_SPAM. In this case, the spam tools will send mail to an invalid e-mail address, and others will know to exclude the "(removethis)" when they send you e-mail. When you post a question or reply to a discussion group, just enter your modified e-mail address in the appropriate box.
* Use a secondary e-mail account: Set up or use an e-mail account, such as a Hotmail account, that is separate from your primary account for posting to discussion groups. When you post a question or reply to a discussion group, use your Hotmail account as your e-mail address.
If you have feedback or questions about this, please post a reply in the newsgroup, or contact us at http://register.microsoft.com/contactus30/contactus.asp?domain=communities
Thanks!
Microsoft Communities Teammy mailbox has been flooded with spam now.
Rather than changing email address i think
the right solution should be developing spam filters
which will stop it.
I think yahoo has much beter filter than MS.
>--Original Message--
>Due to a recent increase in spam sent to posters in
newsgroups, Microsoft advises that newsgroup participants
should consider avoiding posting to newsgroups using their
real email address. Microsoft is also committed to
continuing to address the issue of spam from a
technological perspective.
>To help avoid receiving unwanted messages (spam) in your
regular e-mail account, you may not want to include your
regular e-mail address when you post a question or reply
to a post in a discussion group. Instead you may want to
do one of the following:
>* Use a modified e-mail address: Use a different version
of your e-mail address that others will understand, but
that spam tools can't automatically pick up. For example,
if your actual e-mail address is "emailname@.account.com",
consider using a modified e-mail address such
as: "emailnameaccount.com.invalid",
emailname@.REMOVE_CAPS_AND_INVALIDaccount.com.invalid, or
emailname@.account.com.NO_SPAM. In this case, the spam
tools will send mail to an invalid e-mail address, and
others will know to exclude the "(removethis)" when they
send you e-mail. When you post a question or reply to a
discussion group, just enter your modified e-mail address
in the appropriate box.
>* Use a secondary e-mail account: Set up or use an e-mail
account, such as a Hotmail account, that is separate from
your primary account for posting to discussion groups.
When you post a question or reply to a discussion group,
use your Hotmail account as your e-mail address.
>If you have feedback or questions about this, please post
a reply in the newsgroup, or contact us at
http://register.microsoft.com/contactus30/contactus.asp?
domain=communities
>Thanks!
>Microsoft Communities Team
>.
>|||But where should those spam filters exist? If your inbox is being spammed,
then it's because your email address was harvested. If you post your email
address in a publicly accessible forum, such as an NNTP Server like
msnews.microsoft.com, it's going to get harvested and the spam filter needs
to live on your inbox. If the problem is actually that you're seeing too
much spam in these newsgroups, well, we're working on that.
Mind you, I understand your frustration and I'm getting a ton of it too,
but this isn't specific to msnews.microsoft.com or the Microsoft-hosted
newsgroups and the obvious workaround is simply to not give the spammers
any ammunition. :-)
--
Sincerely,
Stephen Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
Please reply to the newsgroups only, thanks.
| From: "Samir" <samir@.yahoo.com>
|
| my mailbox has been flooded with spam now.
|
| Rather than changing email address i think
| the right solution should be developing spam filters
| which will stop it.
|
|
| I think yahoo has much beter filter than MS.
|
|
|
|
|
| >--Original Message--
| >Due to a recent increase in spam sent to posters in
| newsgroups, Microsoft advises that newsgroup participants
| should consider avoiding posting to newsgroups using their
| real email address. Microsoft is also committed to
| continuing to address the issue of spam from a
| technological perspective.
| >
| >To help avoid receiving unwanted messages (spam) in your
| regular e-mail account, you may not want to include your
| regular e-mail address when you post a question or reply
| to a post in a discussion group. Instead you may want to
| do one of the following:
| >
| >* Use a modified e-mail address: Use a different version
| of your e-mail address that others will understand, but
| that spam tools can't automatically pick up. For example,
| if your actual e-mail address is "emailname@.account.com",
| consider using a modified e-mail address such
| as: "emailnameaccount.com.invalid",
| emailname@.REMOVE_CAPS_AND_INVALIDaccount.com.invalid, or
| emailname@.account.com.NO_SPAM. In this case, the spam
| tools will send mail to an invalid e-mail address, and
| others will know to exclude the "(removethis)" when they
| send you e-mail. When you post a question or reply to a
| discussion group, just enter your modified e-mail address
| in the appropriate box.
| >
| >* Use a secondary e-mail account: Set up or use an e-mail
| account, such as a Hotmail account, that is separate from
| your primary account for posting to discussion groups.
| When you post a question or reply to a discussion group,
| use your Hotmail account as your e-mail address.
| >
| >If you have feedback or questions about this, please post
| a reply in the newsgroup, or contact us at
| http://register.microsoft.com/contactus30/contactus.asp?
| domain=communities
| >
| >Thanks!
| >Microsoft Communities Team
| >
| >.
| >
|

Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.
Agent does this for you, if you refer to job history. It is configurable, on one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but backup history. Check
out sp_delete_backuphistory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.
|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.
|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.

Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
--
Larry Menzin
American Techsystems Corp.Agent does this for you, if you refer to job history. It is configurable, on one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but backup history. Check
out sp_delete_backuphistory.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
--
Larry Menzin
American Techsystems Corp.|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.

Reducing Size of MSDB

Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.Agent does this for you, if you refer to job history. It is configurable, on
one of the tabs in EM,
for agent, properties. What possibly is causing this isn't job history, but
backup history. Check
out sp_delete_backuphistory.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent j
ob
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.|||Just to add to Tibor's answer: If you have a lot of DTS packages, and if
each DTS package has several versions saved, then your MSDB will be huge.
You can right click on a DTS package in Enterprise Manager and select
'Versions' from the popup menu to see how many versions that package has
got, and delete the older versions if you don't need them anymore.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
Are there any scripts that can be used to archive/remove older SQL agent job
runs in MSDB? We are running into larger and larger MSDB sizes and want to
control its growth.
Larry Menzin
American Techsystems Corp.|||The only safe way I know of is to manually delete them. You can however set
jobs to delete after they complete. When you go to the notifications tab of
a scheduled job, you can schedule Automatically Delete Job to have it be
deleted when it completes.
If you are scripting the creation of a job, set the delete_level parameter
of sp_addjob to 1.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A840316B-9B06-4884-9CE6-25715FDAAD69@.microsoft.com...
> Are there any scripts that can be used to archive/remove older SQL agent
> job
> runs in MSDB? We are running into larger and larger MSDB sizes and want to
> control its growth.
> --
> Larry Menzin
> American Techsystems Corp.

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEIL
You need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>
|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL
|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>
|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>
|||Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
in message[vbcol=seagreen]
reduced the actual used size[vbcol=seagreen]
supplied, can you tell me how to[vbcol=seagreen]
recognised ...
>

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEILYou need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>|||> If I type DBCC followed by anything, I get dbcc is not recognised ...
What application are you using to execute the DBCC command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced t
he actual used size even
> miore, however it still occupies 5Gb of disk space.
> I can't seem to use the DBCC commands described in the article supplied, c
an you tell me how to do
> this? If I type DBCC followed by anything, I get dbcc is not recognised ..
.
> cheers,
> NEIL
>|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>|||Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management Studio.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> Hi Tibor,
> I used a CMD screen to do this - I presumed it was a DOS type command.
> NEIL
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>|||Lines: 68
NNTP-Posting-Host: nbtdance.demon.co.uk
X-Trace: news.demon.co.uk 1170082069 7290 80.177.3.126 (29 Jan 2007 14:47:49
GMT)
X-Complaints-To: abuse@.demon.net
NNTP-Posting-Date: Mon, 29 Jan 2007 14:47:49 +0000 (UTC)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
X-Priority: 3
X-Newsreader: Microsoft Outlook Express 6.00.2800.1807
X-MSMail-Priority: Normal
Xref: leafnode.mcse.ms microsoft.public.sqlserver.server:25631
Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
in message[vbcol=seagreen]
reduced the actual used size[vbcol=seagreen]
supplied, can you tell me how to[vbcol=seagreen]
recognised ...[vbcol=seagreen]
>

reducing size of log file

Hi,
I have SQL 2000 running on W2000.
Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
Under properties, it reports that it is 95% space.
How can I reduce the size of these?
I have tried the shrink option bu it doesn't help.
Many thanks
NEILYou need to backup log before shrinking it
BACKUP LOG database_name WITH TRUNCATE_ONLY
On Jan 25, 1:05 pm, "Neil Jarman" <n...@.tNOiSPAMvPLEASEy.co.uk> wrote:
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL|||Neil
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epa9v6$oo8$1$8300dec7@.news.demon.co.uk...
> Hi,
> I have SQL 2000 running on W2000.
> Some of my data has huge log files - eg 5Gb log for a DB of 50Mb.
> Under properties, it reports that it is 95% space.
> How can I reduce the size of these?
> I have tried the shrink option bu it doesn't help.
> Many thanks
> NEIL
>|||Thanks for both your replies.
I have bvacked up log - using EM Backup Database... and this has reduced the
actual used size even miore, however it still occupies 5Gb of disk space.
I can't seem to use the DBCC commands described in the article supplied, can
you tell me how to do this? If I type DBCC followed by anything, I get dbcc
is not recognised ...
cheers,
NEIL|||Hello,
Backup Log will clear up all the inactive postion of the log but will not
reduce the physical file. To reduce the physical
file you may need to SHRINK the LDF file using DBCC SHRINKFILE command. Take
a look into books online about
DBCC SHRINKFILE.
One more thing is schedule a frequent transaction log backup. This will
ensure that your LDF is not growing heavily as well as
make sure you have backup files to recover the database when required.
Thanks
Hari
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced
> the actual used size even miore, however it still occupies 5Gb of disk
> space.
> I can't seem to use the DBCC commands described in the article supplied,
> can you tell me how to do this? If I type DBCC followed by anything, I get
> dbcc is not recognised ...
> cheers,
> NEIL
>|||> If I type DBCC followed by anything, I get dbcc is not recognised ...
What application are you using to execute the DBCC command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> Thanks for both your replies.
> I have bvacked up log - using EM Backup Database... and this has reduced the actual used size even
> miore, however it still occupies 5Gb of disk space.
> I can't seem to use the DBCC commands described in the article supplied, can you tell me how to do
> this? If I type DBCC followed by anything, I get dbcc is not recognised ...
> cheers,
> NEIL
>|||Hi Tibor,
I used a CMD screen to do this - I presumed it was a DOS type command.
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> If I type DBCC followed by anything, I get dbcc is not recognised ...
> What application are you using to execute the DBCC command?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>> Thanks for both your replies.
>> I have bvacked up log - using EM Backup Database... and this has reduced
>> the actual used size even miore, however it still occupies 5Gb of disk
>> space.
>> I can't seem to use the DBCC commands described in the article supplied,
>> can you tell me how to do this? If I type DBCC followed by anything, I
>> get dbcc is not recognised ...
>> cheers,
>> NEIL
>|||Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management Studio.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> Hi Tibor,
> I used a CMD screen to do this - I presumed it was a DOS type command.
> NEIL
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
>> If I type DBCC followed by anything, I get dbcc is not recognised ...
>> What application are you using to execute the DBCC command?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
>> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
>> Thanks for both your replies.
>> I have bvacked up log - using EM Backup Database... and this has reduced the actual used size
>> even miore, however it still occupies 5Gb of disk space.
>> I can't seem to use the DBCC commands described in the article supplied, can you tell me how to
>> do this? If I type DBCC followed by anything, I get dbcc is not recognised ...
>> cheers,
>> NEIL
>>
>|||Hi Tibor,
Many thanks for the help - my log file is massively reduced now.
regards,
NEIL
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VKhZhKQHHA.3944@.TK2MSFTNGP06.phx.gbl...
> Nope, it is a TSQL command. Use Query Analyzer/SQL Server Management
Studio.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> news:epaph8$f6s$1$8300dec7@.news.demon.co.uk...
> > Hi Tibor,
> >
> > I used a CMD screen to do this - I presumed it was a DOS type command.
> >
> > NEIL
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in message
> > news:eWgPBBKQHHA.2140@.TK2MSFTNGP03.phx.gbl...
> >> If I type DBCC followed by anything, I get dbcc is not recognised ...
> >>
> >> What application are you using to execute the DBCC command?
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Neil Jarman" <neil@.tNOiSPAMvPLEASEy.co.uk> wrote in message
> >> news:epaec9$p5e$1$830fa7b3@.news.demon.co.uk...
> >> Thanks for both your replies.
> >>
> >> I have bvacked up log - using EM Backup Database... and this has
reduced the actual used size
> >> even miore, however it still occupies 5Gb of disk space.
> >>
> >> I can't seem to use the DBCC commands described in the article
supplied, can you tell me how to
> >> do this? If I type DBCC followed by anything, I get dbcc is not
recognised ...
> >>
> >> cheers,
> >>
> >> NEIL
> >>
> >>
> >
> >
>

Reducing physical transaction log file size.

I need some help to reduce the physical file size of a database transaction
log. I ran the shrink database option under the System Manager and was able
to shring the logical size of the log file, however, I would also like to
reduce the physical size of the file to free up disk space. I have tried to
run the DBCC Shrinkfile and DBCC shrinkdatabase commands but that does not
appear to be successfull. Can anybody help? I am running SQL 2000. Thanks
.http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Elbryyan" <Elbryyan@.discussions.microsoft.com> wrote in message
news:67FAF140-014E-4CB2-800E-740AF1B69212@.microsoft.com...[vbcol=seagreen]
>I need some help to reduce the physical file size of a database transaction
> log. I ran the shrink database option under the System Manager and was ab
le
> to shring the logical size of the log file, however, I would also like to
> reduce the physical size of the file to free up disk space. I have tried
to
> run the DBCC Shrinkfile and DBCC shrinkdatabase commands but that does not
> appear to be successfull. Can anybody help? I am running SQL 2000. Thanks.[/vbc
ol]

Reducing many-to-many to one-to-one

Hi,

I have the following three tables below containing Resources,
Categories and a link table so each Resource can belong to one or more
Categories. I would like to create a view (ResourceID, ResourceName,
CategoryID, CategoryName) that includes one row for each Resource with
just one of the Categories that it belongs to.

Resource table
- ResourceID
- ResourceName
- etc..

Category table
- CategoryID
- CategoryName
- etc..

ResourceCategory table
- ResourceID
- CategoryID

Can anyone help? Thanks.On Fri, 14 Sep 2007 14:43:33 -0000, nick@.nova5.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have the following three tables below containing Resources,
>Categories and a link table so each Resource can belong to one or more
>Categories. I would like to create a view (ResourceID, ResourceName,
>CategoryID, CategoryName) that includes one row for each Resource with
>just one of the Categories that it belongs to.

Hi Nick,

That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?

I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.

Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):

CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Hi Hugo,

Thanks for help, just what I needed. I was trying to put a 'top 1' in
the nested select which obviously (now!) didn't work..

Nick.

Quote:

Originally Posted by

Hi Nick,
>
That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?
>
I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.
>
Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):
>
CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;
>
(Untested - seewww.aspfaq.com/5006if you prefer a tested reply)
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
David
DBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>
|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>

Reducing log (ldf) file size

I thought that the size of the log file went down when you did a full
database backup. How can I reduce the size of my log file? Do I need to
back it up separately? Thanks.
DavidDBCC SHRINKFILE(databasename_log, 0)
This will reduce the log file size to 504 KB.
However, if you don't change your current recovery model and/or the behavior
of your apps, the log file is just going to grow again between backups. So
the above is only a temporary relief.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uC#FtmiLFHA.1392@.TK2MSFTNGP10.phx.gbl...
> I thought that the size of the log file went down when you did a full
> database backup. How can I reduce the size of my log file? Do I need to
> back it up separately? Thanks.
> David
>|||You are right. I thought I had set it to "Simple" but was set to
"Full". Thanks Aaron.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||and addtionally to Aarons reply, you need to backup the log seperately
(...look up BACKUP LOG xxxx...in Books On Line..) to keep the log in shape.
Setting the recovery to Simple will limit your possibilities to recover data
in case of something happens to the database.
Regards
Steen
David wrote:
> You are right. I thought I had set it to "Simple" but was set to
> "Full". Thanks Aaron.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Reducing like returned data

I am working on a personal project and am drawing a complete blank
(too much celebrating last night?) on the SQL term that is used to
eliminate multiples of like data when it is returned from the
database.

ie, instead of ...
red
blue
red
green

it would return ...
red
blue
green

Sorry for the trouble and thanks.DISTINCT or GROUP BY will do that for you.

Example:

SELECT DISTINCT colour
FROM ColourTable

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks, David!

On Thu, 1 Jan 2004 23:47:08 -0000, "David Portas"
<REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote:

>DISTINCT or GROUP BY will do that for you.
>Example:
>SELECT DISTINCT colour
> FROM ColourTable
>--
>David Portas
>----
>Please reply only to the newsgroup

Reducing LDF files

Hi,
If I understand it correctly, you only need an LDF file to restore to a point in time after the last full backup? If this is so, then Could the LDF file not be reduced in size on perfoming a full backup?
Most of the time it's not an issue as there is enough space on the HDD, but is it possible to reduce the ldf file size periodically (manually would be fine). Is changing the recovery mode from FULL to SIMPLE and then back to FULL an option?
If so, is anyone able to tell me how, exactly, I can do this? ... I've sifted through the documentaion to no avail ... :eek:
Many thanks
RobNo, you cannot just truncate the log when you take the full backup, because you do not know whether the error forcing you to restore the database will occur prior to or after the last full backup. You may leave the office at 4pm, at 5 pm some tables disappear. Backups are scheduled to run at midnight. If the logs are truncated when you arrive at the office the next morning, you cannot restore to point in time anymore.

Thus, the ONLY way you should free space in the logfiles is backing them up with the backup log statement, which frees up place internally in the log files.|||do not change recovery mode FULL to SIMPLE. it will break log chain.|||If you are just starting off with SQL Server administration, I would recommend using the maintenance plan wizard (if you are using SQL 2005, be sure to download and install SP2, which has significant improvements to maintenance plans in general).

General guidelines:

SQL 7.0/2000:
1. Create a maintenance plan that includes only the system databases (master, model, msdb). Do a full backup daily.

2. Create another maintenance plan that includes only the user databases. Do a full backup daily and a transaction log backup as often as you are comfortable (and as often as needed to keep the logs at a stable/reasonable size).

3. For both plans:
a. Back up over the network (if your network is stable), or straight to disk (separate from data/log files).
b. Retain full backups on disk for 2-3 days (more if you can afford it).
c. Retain log backups for as long as necessary to get back to your last good full backup.
c. Make sure there's another process that writes the db backups to tape.

SQL 2005
1. Create one plan for full backups for all databases

2. Create another plan for log backups for all user databases.

3. Follow other guidelines as above.

There are lots of tweaks and other things that you can do to improve backup performance, this is just a guide to get you started.

Be sure to also:
1. Practice recovery:
a. Practice full database restore
b. Practive database restore to point in time
c. Practice recovery from tape
d. Practice recovery from a dead server (ie, full rebuild/fresh install)

2. Validate your tape backups

3. Document your backup strategy and document your recovery plan.

Regards,

hmscott

Reducing filesize while deleting rows?

I've got a very filesize restricted database. I noticed that when I insert 1000 rows my filesize jumps to 80k, but when I delete all but 50 of those rows...the filesize actually increases to 84k. How do I make sure the filesize of my database shrinks when I delete rows?

Thanks!

Use SqlCeEngine.Compact() or SqlCeEngine.Shrink() to do that. You can find description on MSDN.

|||Unfortunately I'm using native C++ for all this, so I don't have access to SqlCeEngine. Is there a SQL command i could execute to perform the equivalent to a shrink routine? If that doesn't exist I'll need some way to do it while an existing OLEDB connection is open and operating. I need this database to run 24/7 with no downtime whatsoever.

Thanks for the help!
|||

The database connection must be closed to do shrink or repair. This is a design requirement so that database file structure can be recreated.

What you can probably do is to make a copy of the database , compact it, momentarily drop connection to original database and replace original with compacted database. You will have to take a read only lock on original db while the copy is compacted so that no new changes take place.

|||

The OLEDB provider has an Engine object, which has a CompactDatabase method.