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.
Showing posts with label sizes. Show all posts
Showing posts with label sizes. Show all posts
Saturday, February 25, 2012
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.
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.
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 Database Size (contains images)
Hello. I am wondering how to effectively reduce the size of my database. After viewing the individual table sizes, I have come to realize that nearly 99% of the database's size is due to images. I am told that too much binary data is not good. How can I go about reducing the size of my database (possibly the images themselves)? I'd appreciate any help.store the path and filename instead of the image?
Do you use READTEXT WRITETEXT alot?
what's the size of a average image?|||The average size is around 680k. I do not use readtext and writetext a lot. How do i go about storing the path instead of the actual image?|||Someone suggested a UNC path to the images. What is this?|||UNC = Universal Naming Convention
\\servername\sharename\pathname\filename.img
so you can place the images anywhere|||is there an easy way to do this if I have around 360 images that need to be moved? What do i put in the table's data field|||UNC=\\SERVER\SHARE\FILENAME.EXT
There are kudos of reasons to store images in the database itself. It all depends on the app itself, and what is actually stored in those images. I prefer to use image and (n)text fields, because that's what a database is for. And if there is anything that is associated with the business through your app, - its place is in the database. The question comes how this info is being used. For example, in healthcare industry it becomes more and more practical to do high-speed scanning of claims. Scanned images are burned onto media of choice. Some organizations are also storing those images into database (TIFF format), others go even further and apply OCR technology to avoid data entry process. Key elements of the claim image are stored in related tables to meet search needs. The only time the image field is accessed is when there is a need to view the original claim. Will it make sense to store a path to a TIFF file instead of the file itself? Does the database have any control over OS environment? NO!!! In other cases Full-Text Search capability is used if the type of file being stored meets the parsing and filtering capabilities of the search engine (Word documents for example.) Whoever told you that "too much binary data is not good" is operating under unqualified assumptions. You need to look into the nature of the business usage of your app and determine whether it does or does not make sense to rely on database for safe-keeping of your images (I think the answer is pretty clear, hey?!)|||And the debate starts again...
If you have significant volume...I'd say it's an issue...
Hell they went out and bought kodak imagining platter to handle everything...it's massive...
no way a db could handle this volume...
There's also the pain of reaind and writing in chunks (how do you update a chunk again? Is it the whole thing?)
How big are the images?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36026|||He told you, - 680K|||My problem is that the database itself is far too large, and it is 99% due to these images. They are simply photos of employees and there are around 360 of them. We use them on a web site where once a name is typed in, info about that person is displayed; background info, history with the company, sales numbers, the photo, etc. How do i go about creating this UNC to the images? Where do you recommend i save this pictures? I appreciate any help.|||So, are we talking about less than 250MB worth of images? If this size is taken by 1% of your structure, then your database is less than 500MB all together. Why are you concerned about this? Why are you calling it "a problem?"|||What is far too large?
If the pix are 1 mb you only be saving 360 mb
That's not big
Do you use READTEXT WRITETEXT alot?
what's the size of a average image?|||The average size is around 680k. I do not use readtext and writetext a lot. How do i go about storing the path instead of the actual image?|||Someone suggested a UNC path to the images. What is this?|||UNC = Universal Naming Convention
\\servername\sharename\pathname\filename.img
so you can place the images anywhere|||is there an easy way to do this if I have around 360 images that need to be moved? What do i put in the table's data field|||UNC=\\SERVER\SHARE\FILENAME.EXT
There are kudos of reasons to store images in the database itself. It all depends on the app itself, and what is actually stored in those images. I prefer to use image and (n)text fields, because that's what a database is for. And if there is anything that is associated with the business through your app, - its place is in the database. The question comes how this info is being used. For example, in healthcare industry it becomes more and more practical to do high-speed scanning of claims. Scanned images are burned onto media of choice. Some organizations are also storing those images into database (TIFF format), others go even further and apply OCR technology to avoid data entry process. Key elements of the claim image are stored in related tables to meet search needs. The only time the image field is accessed is when there is a need to view the original claim. Will it make sense to store a path to a TIFF file instead of the file itself? Does the database have any control over OS environment? NO!!! In other cases Full-Text Search capability is used if the type of file being stored meets the parsing and filtering capabilities of the search engine (Word documents for example.) Whoever told you that "too much binary data is not good" is operating under unqualified assumptions. You need to look into the nature of the business usage of your app and determine whether it does or does not make sense to rely on database for safe-keeping of your images (I think the answer is pretty clear, hey?!)|||And the debate starts again...
If you have significant volume...I'd say it's an issue...
Hell they went out and bought kodak imagining platter to handle everything...it's massive...
no way a db could handle this volume...
There's also the pain of reaind and writing in chunks (how do you update a chunk again? Is it the whole thing?)
How big are the images?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36026|||He told you, - 680K|||My problem is that the database itself is far too large, and it is 99% due to these images. They are simply photos of employees and there are around 360 of them. We use them on a web site where once a name is typed in, info about that person is displayed; background info, history with the company, sales numbers, the photo, etc. How do i go about creating this UNC to the images? Where do you recommend i save this pictures? I appreciate any help.|||So, are we talking about less than 250MB worth of images? If this size is taken by 1% of your structure, then your database is less than 500MB all together. Why are you concerned about this? Why are you calling it "a problem?"|||What is far too large?
If the pix are 1 mb you only be saving 360 mb
That's not big
Subscribe to:
Posts (Atom)