TL Negative Free Space
-
I have a 4Gb database in Simple mode. I recently changed it from full mode because my TL were getting too large too quickly and the shrinking process would take too long. Anyway. Before i changed to simple I went into shrink the TL after a backup and noticed my TL had a negative free space. Thinking that my TL might be corrupt I created another TL file and added it to the DB. Now a few weeks later both TL's are showing negative free space. The db is currently in simple mode. The TLs are 3x the DB size and 5x the DB size. I cannot get them to shrink or really have any impact on them at all.
I can find no reference to negative free space problem on MSKB. I found one listing with a similar problem via google but there was no resolution.
Does anyone have any idea what's happened here or a good way to go about fixing it. My DB is 24x7 but I can schedule a maintenance window. My DB is operational but my main problem is my backups take much longer and much more space due to these unessesarly large TLs.
SQL 2005 Std
Any help would be apperciated.
-
did you update your statistics ?
- sp_updatestats
- dbcc updateusage (0) with count_rows
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I figured this was done automatically since I had auto update statistics on but I didn't know that this information was included in the 'statistics' anyway so...
Anyway, thanks for the reply. I ran both commands against the db. Rechecked and still showing negative free space.
-
Its probably worth noting that I first noticed this problem shortly after I moved this DB to a new server. I shrunk the TL before I moved it and everything was fine. I moved the DB and TL (only one at the time) and then shortly afterwards I had negative free space. I copied the DB and TL using standard windows copy paste after SQL server was stopped. I know this probably isn't the prefered method but I wanted to get it back up as soon as possible. In retrospect this may have caused this problem though.
Could I do a full backup then delete the two TL files and let it recreate a new one? What kind of issues would I run into trying that?
-
I did some more searching and found this.
When I do dbcc opentran I get
Transaction information for database 'DD_Net'.
Replicated Transaction Information:
Oldest distributed LSN : (17384:589:6)
Oldest non-distributed LSN : (17384:1066:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
At one point replication was setup on the old server but is not needed on the new server. This appears to be the root cause of my problem. Any idea on how to remove the replication information from the DB manually? Or am I going the wrong direction?
Edit:
This appears to be the MS solution to my issue: sp_removedbreplication ''
If anyone has any experience with this command causing ill effects a response would be apperciated. I am going to wait a few hours till lower utilization before running it.
Thanks to everyone who is reading what has turned out to be a problem blog.
-
I have tried the commands specified above and even gone through the links given above... but still didnt understood why the logfile sizes are showing in negative...
I'm having the same issue and spent lot of time in digging for solution but came up with empty hands....
If anyone have idea why this happens... please let me know.....
-
a3b2c1r46 (6/23/2009)
Its probably worth noting that I first noticed this problem shortly after I moved this DB to a new server. I shrunk the TL before I moved it and everything was fine. I moved the DB and TL (only one at the time) and then shortly afterwards I had negative free space. I copied the DB and TL using standard windows copy paste after SQL server was stopped. I know this probably isn't the prefered method but I wanted to get it back up as soon as possible. In retrospect this may have caused this problem though.Could I do a full backup then delete the two TL files and let it recreate a new one? What kind of issues would I run into trying that?
If I were you, I would open a new forum thread for this question !
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Run the following query:
SELECT Name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases
Find your database and see what SQL Server is reporting for why the log cannot be reused. This will also confirm for you what the recovery model is defined as.
Once you have done that, review the article I link to in my signature about how to manage your transaction logs. Since this is a 24/7 production system I would highly recommend that you put your database back into a full recovery model and implement frequent (every 15 minutes) transaction log backups.
At this point, you have put your company at risk of losing more than 24 hours worth of data. Not sure about you, but I really wouldn't want to be the one responsible for losing that data and costing the company millions of dollars.
Edit: fixed typo and changed to show description of recovery model instead.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
I concur with Jeffrey. With it being a 24/7 operation I'd look at daily fulls, every 4-6 hours doing a differential and 15 minute t-log backups.
Where I'm at I had to dispel the myth that backups would keep users out of the system. My company is very heavy into Dbase 3 (our flagship product still uses it) and they're uncomfortable with doing backups while users are in databases. It took a lot of patience to explain that when SQL does the backup users can be in the system performing tasks and the backup will still work.
After 3 months they finally believe me 🙂
--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url] -
I saw my tempdb tlog displayed like that.
-
One of mine (SQL 2005 SP3) is doing the same sort of thing. Simple recovery mode, actual file sizes bear no relation to the file sizes in the shrink file dialogue. And yes, I did run dbcc updateusage (tempdb) Didn't make any difference.
Thanks
J
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply