Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
shadragon
Starting Member
5 Posts |
 Posted - 2009-06-16 : 14:56:18
|
Good Day, I have a SQL 2000 server running on Windows 2000 SP4. Every Monday morning I come into work I find the SQL server has run out of space because the log files have grown significantly. An 8 GB database consumes over 22 GB of free space (I believe while running the DBCC database check routine). I have to run the following script through Query Analyzer to reduce the log files: backup log DBNAME with truncate_onlygodbcc shrinkfile (DBNAME_log,0)goI have it saved as 'shrinkfile.sql' on my local HD. I load it into Query Analyzer and run it. Poof, problem solved. Tons of space appears.While this works fine, I do need to go on vacation at some point.  Circular logging is not possible with SQL 2000 so I would like to run this shrinkfile.sql script automatically via Scheduled Tasks every Monday morning before folks come into work. I cannot find any way to do this. Ideas? Cheers. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-06-16 : 15:10:41
|
| Just create a job and put those statements as a t-sql step (actually I would make it 2 steps).For things like this it's useful to put them in a job anyway so that you don't have to load a script - you can connect to the sql server from anywhere and run it.What is your recovery model and are you doing tr log backups?What you are doing implies that the log file should be the size before you shrink it - it will only grow again which takes up resources so better to leave it as that size.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2009-06-16 : 15:15:22
|
Is there a bulk load process somewhere that is committing the load as a single transaction? those have a way of blowing the tlog.
elsasoft.org |
|
|
|
shadragon
Starting Member
5 Posts |
Posted - 2009-06-16 : 15:17:52
|
| Hi nr, I cannot change my recovery model and yes I do backup the logs. Looks like I found my own answer. I can use OSQL with the following syntax: OSQL -U sa -P secret -i c:\shrinkfile.sql -o c:\shrinkfile.txtI save that line as a batch file and execute it through Scheduled Tasks. This shrinks the logs and creates an output file to review. I already set it up to run at 4:15 Monday morning and it tested perfectly. Cheers. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2009-06-16 : 15:48:26
|
| Bit of a convoluted way of doing it but if you're happy then ....==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
shadragon
Starting Member
5 Posts |
Posted - 2009-06-30 : 07:25:34
|
| UpdateThe script works, but you have to add a line to tell SQL what DB to use. So the complete script is:USE DBNAMEgobackup log DBNAME with truncate_onlygodbcc shrinkfile (DBNAME_log,0)goThis, in conjunction with the OSQL call works perfectly to reduce your log files automatically. nr - I don't know how doing something automatically can be considered 'convoluted', but it means I can get on a dive boat in the Andaman Sea and actually go on vacation for a week. Internet access is remarkably unavailable there. Manual processes are great as long as there is someone there to drive them. Cheers. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2009-06-30 : 07:37:38
|
| I assume NR is referring to your way of scheduling a task.Why don't you just set up a job using sql server agent to run your SQL rather than using windows scheduler to run a bat file that runs osql that runs your sql.whatever works for you though.Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
|
|
|
|
|
Scheduled Tasks and SQL2000