Already a Member? Join your peers on the Internet's largest technical computer professional community. It's easy to join and it's free. Register now while it's still free! Already a member? Close this window and log in. Join Us Close. Join Tek-Tips Forums! Join Us! By joining you are opting in to receive e-mail. Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Students Click Here. I have a log file file for my database that is 30 GB in size I need to shrink this thing immediately. I am not really a SQL Administrator. Doesn't work. Get a syntax error: "Incorrect syntax near '. New development. Previously I was unable to shrink it through the management studio.
Just tried it and it shrunk just fine. Something weird is going on. At the very least get a data admin or application consultant to review your recovery model setting and determine whether it is appropriate for your app. This is a setting which should influence the decision on how best to maintain the log file constraints going forward and if an occasional manual procedure will suffice. If the recovery model is "Simple", then the routine use of truncate is usually OK, because backup strategy is meant to primarily rely on Full BAKs with Diffs in between.
Typically, if a database is not set to "Simple", then there is intent for a greater reliance on SQL recovery operations which may depend on closed trxs transactions being present in the logfile. In such cases, truncate is reserved for use when size is out of control or to establish a new starting point or prevent a partition from filling up.
You folks are not going to believe this, but I have come up with the solution to this problem after some more research. However, when putting that into SQL it didn't like it. Below is the query I ran and it completed successfully:. I had to add another USE command just to make sure it didn't run home to the master and then I had to add single quotation marks on either side of the log file name. The reason I was still visiting this is because when this server was built, it wasn't built with enough space not my fault.
So when this log file gets filled, we get errors in our monitoring software. The only way to clean that up is to shrink that file. And it can only be done after the DB is done backing up. Please hlp. So we are getting this error. Backup log with truncate will work fine without this need.
I had same problem and figured it out. My logical file name had a space in the begining of the name. I would not say they are all wrong.
There is certainly a problem here. It's not a typo like you seem to think. There is something larger here. Here are my steps:. I used Log file ID instead of file name and its working I have the same issue. I changed the recovery model as described but it had no effect.
It only works with the File ID. It ist very strange to me because I have two identical databases. With my productive database the command works without any problems and with my development database I have the problem. I used copy and paste for the logical name in order to get the exact name. I observed something interesting. I ran across this problem on a SQL Enterprise instance.
But, I wasn't happy with what was happening. That didn't work. AND that is what I've been using in my command:. Not sure that provides any definitive information as for cause, but it is more information that someone may find useful should they encounter this issue. I'm going to look at logical naming and see if I can reset it. Once I fixed all the logical names I could shrink just fine. Thanks a lot Narhostam! You led me on the right path and it was really bugging me!
Asked 8 years, 7 months ago. Active 4 years, 9 months ago. Viewed 27k times. I am trying to shrink a database file but am running into errors. Improve this question. Aaron Bertrand k 25 25 gold badges silver badges bronze badges.
Have you tried it without the single quotes? ThomasStringer Yes, it gives me the same error. What is the actual logical filename? Does it have any odd characters, or spaces? Can you post that? ThomasStringer It's just normal characters, no special characters or spaces or numbers, and no umlaute etc.. Only upper and lower case characters. It's similar to "DotNetDev". Show 5 more comments. Active Oldest Votes. Revised my answer, testing did not show as a valid reason for it not to work. Couple of things to check Are there leading or trailing spaces in the file name?
Did you recently upgrade to SP3 by any chance? Improve this answer.
0コメント