I only know enough about SQL Server to be dangerous. I asked our DBA (it's a slow day) and provided the following. These commands would be entered in a query window. Use this if the database recovery model is FULL or bulk logged USE DatabaseName BACKUP LOG DatabaseName TO DISK = 'X:\path\filename.trn' ----First param below is fileno for log file, often 2. Check with sys.database_files ----Second is desired size, in MB. DBCC SHRINKFILE(2, 1) DBCC SQLPERF(LOGSPACE) --Optional DBCC LOGINFO --Optional
Use this if the database recovery model is SIMPLE USE DatabaseName CHECKPOINT --First param below is fileno for log file, often 2. Check with sys.database_files --Second is desired size, in MB. DBCC SHRINKFILE(2, 1) DBCC SQLPERF(LOGSPACE) --Optional DBCC LOGINFO --Optional ________________________________ From: [email protected] [mailto:[email protected]] Sent: Thursday, December 23, 2010 10:26 AM To: NT System Admin Issues Subject: Messy MS SQL-2005 database Greetings! I am not a DBA, nor have I ever played one on television. To aggravate things here, we are an IBM DB2 shop. So, I am not getting as much local help with this as I'd like (especially since the DBA's are taking extended weekends)... Our phone system has its records kept in an MS SQL 2005 database. It's been running for a bit over two years now. It has not been completely neglected as I have been doing manual backups of the phone database M-F. I am just now learning of other pieces, specifically transaction logs. I just now not only learned about them but ran a backup on them. The backup for the transaction logs is over 52 Gb(!). Obviously, this could very quickly run into storage and disk system issues. Although the backup for the transaction logs is set to append to the current, there is still that huge backup file (and I presume huge collection of transaction logs) which ought to receive some attention. I only know enough to 1) not simply go about deleting things, and 2) ask for help. HELP!!!! Thanks... -- Richard D. McClary Systems Administrator, Information Technology Group ASPCA(r) 1717 S. Philo Rd, Ste 36 Urbana, IL 61802 [email protected] P: 217-337-9761 C: 217-417-1182 F: 217-337-9761 www.aspca.org <http://www.aspca.org/> The information contained in this e-mail, and any attachments hereto, is from The American Society for the Prevention of Cruelty to Animals(r) (ASPCA(r)) and is intended only for use by the addressee(s) named herein and may contain legally privileged and/or confidential information. If you are not the intended recipient of this e-mail, you are hereby notified that any dissemination, distribution, copying or use of the contents of this e-mail, and any attachments hereto, is strictly prohibited. If you have received this e-mail in error, please immediately notify me by reply email and permanently delete the original and any copy of this e-mail and any printout thereof. ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin ~ Finally, powerful endpoint security that ISN'T a resource hog! ~ ~ <http://www.sunbeltsoftware.com/Business/VIPRE-Enterprise/> ~ --- To manage subscriptions click here: http://lyris.sunbelt-software.com/read/my_forums/ or send an email to [email protected] with the body: unsubscribe ntsysadmin
