{"id":201,"date":"2005-01-06T08:27:30","date_gmt":"2005-01-06T13:27:30","guid":{"rendered":"http:\/\/www.wehuberconsultingllc.com\/myweblog\/?p=201"},"modified":"2005-01-06T08:51:19","modified_gmt":"2005-01-06T13:51:19","slug":"isa-log-file-getting-out-of-control","status":"publish","type":"post","link":"https:\/\/wehuberconsultingllc.com\/wordpress\/2005\/01\/06\/isa-log-file-getting-out-of-control\/","title":{"rendered":"ISA log file getting out of control"},"content":{"rendered":"

A while back I followed an article, ISA Server & SQL Server \u00e2\u20ac\u201c Brothers in Arms<\/a>, to store my ISA log data into a SQL server database. I created an Excel spreadsheet to help me parse the data base so I could debug some problems I had encountered. His instructions were good but they did not discuss how to periodically clean out the data. By the time I decided to solve this problem my log file was over 2 GB and over 700,000 rows in the webproxylog. I tried to use the SQL Query Analyzer but I kept getting ODBC timeouts. I had a little more success with OSQL but the key was to delete fewer rows in each query. I started deleting by day. As I succeeded in reducing the table size a bit I went on to deleting by week and finally by month. After I had completed all of this I still had a 2 GB file. So I tried to shrink the database using SQL Enterprise Manager. It did not work so I went into Files option of shrink and ran it. My database was now down to 400 MB. Before the week is out I am putting together a SQL job to delete rows older than 30 days and report the space used in an email back to me. I actually have all the queries ready although I really do not like the format of the spaceused procedure. When it exports to a text file it pads the variable length character fields to their maximum which causes unsightly text wrapping.<\/p>\n","protected":false},"excerpt":{"rendered":"

A while back I followed an article, ISA Server & SQL Server \u00e2\u20ac\u201c Brothers in Arms, to store my ISA log data into a SQL server database. I created an Excel spreadsheet to help me parse the data base so I could debug some problems I had encountered. His instructions were good but they did … Continue reading “ISA log file getting out of control”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[6],"tags":[],"class_list":["post-201","post","type-post","status-publish","format-standard","hentry","category-sbs2k-sbs2k3"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4iN3d-3f","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts\/201","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/comments?post=201"}],"version-history":[{"count":0,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/posts\/201\/revisions"}],"wp:attachment":[{"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/media?parent=201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/categories?post=201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/wehuberconsultingllc.com\/wordpress\/wp-json\/wp\/v2\/tags?post=201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}