SQL truncate logs

Some time when we get into a bind because of neglect or ignorance we have to rely on alternative methods to the normal GUI methods. One of these situation included when I had a computer with SQL server melt down on me because of the size of it’s transaction logs. As you may or may not know a grossly large transaction log compared to it’s data files can signal a problem. Normally you want to have some type of mantenance plan to keep these in check and everything running healthy. In other cases your client may not seek to do this and you are called in after the fact. In this particular case I could even get into the GUI to shring the log file so I had to resort to this little trick.

Problem: Use an alternative method to truncate SQL logs
Solution: In command line interface you want to start SQL with a trusted connection using the following  command: ‘sqlcmd -S<SERVERNAME> -E’. The ‘S’ switch indicates which server and the ‘E’ switch establish a trusted connection. Next you should see a ‘>’ prompt. From there you can type the following:

>backup log <DATABASE> with truncate_only
>go
>dbcc shrinkdatabase(‘<DATABASE>’)
>go
>quit

This will work with a single database which can be a bit painful if you have alot of transaction logs but it should get you started in the right direction.

Tags :

About the Author

Leave a Reply

%d bloggers like this: