October 7, 2014 at 10:50 am
Hi all, this is my first time here so I have some very basic questions. I'm not a DBA but I inherited a SQL Server DB setup with geographic diversity set up among 4 different boxes and I'm charged with keeping it running. I have a problem with a distribution log file growing so found a book that explains what to do, but when I run the queries, they don't work. Here's an example:
DECLARE @DatabaseName VARCHAR(50);
SET @DatabaseName = 'distribution'
SELECT name,
recovery_model_desc,
log_reuse_wait_desc
FROM sys.databases
WHERE name = @DatabaseName
When I run that I get
Msg 208, Level 16, State 1, Line 4
Invalid object name 'sys.databases'.
I'm using MS SQL Sever Management Studio, if that matters, and looked around for that table and couldn't find it, but found one called dbo.sysdatabases. So my first question would be what does "dbo" signify? Anyway, assuming I found the right table, I changed the query above to use "FROM dbo.sysdatabases" and got this result:
Msg 207, Level 16, State 3, Line 4
Invalid column name 'recovery_model_desc'.
Msg 207, Level 16, State 3, Line 4
Invalid column name 'log_reuse_wait_desc'.
So now I'm stumped. Seems like lots of people on this site use 'log_reuse_wait_desc' to figure out why their log files are growing, so it should be a valid column if I'm in the right table. I know this is probably pretty basic to long time SQL Server users, so if you can give me a nudge, it would be much appreciated.
The book I'm using, btw, is called "Troubleshooting SQL Server: A Guide for the Accidental DBA" and the query above was in ch 8, listing 8.1
thanks for your time,
Rick
October 7, 2014 at 10:55 am
The db must be SQL 2000, or in SQL 2000 compatibility mode (80).
Yikes and gadzooks! You need to move it forward to at least 2005 as fast you can, as the management will be vastly easier.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 7, 2014 at 11:05 am
Hello and welcome to the forums. Scott hit the nail on the head. If you need a business case for migrating it to a newer version, you can tell them that SQL Server 2000 is beyond end of life. Extended support from Microsoft ended in April 2013. See http://support2.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL for the official word.
October 7, 2014 at 11:22 am
Yikes and gadzooks! is right. And I wish I had the nerve, much less the knowledge, to move the thing forward, but I don't want to deal with the fallout if something goes wrong. I only have to nurse this thing along until we migrate the users onto another platform. Thanks for your reply Scott.
October 7, 2014 at 11:52 am
The problem is that SQL 2000 has been out of support for years and there's a fair number of database consultants who won't work on SQL 2000 databases any longer, so if something goes wrong with it now, you may struggle to find good help.
Oh, and there's no equivalent of the log_reuse_wait_desc in SQL 2000, The troubleshooting was written for 2008 R2 and most of the queries in it won't work on SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2014 at 1:14 pm
thanks everyone for your replies. I certainly understand the risk if anything goes wrong with it as it currently stands, but really it's my management that bears the responsibility in that case. They were dopes for firing the team that developed the system, so that's on them. I, on the other hand, am getting a little exposure to another DB, so I'm not complaining.
October 7, 2014 at 1:40 pm
Getting experience is nifty and all but being stuck supporting what is soon to be 15 year old software sucks. It might a form of job security but it's skills you'll be able to apply to less and less things and when something goes wrong your resources for finding helping get smaller every day.
October 7, 2014 at 7:51 pm
GilaMonster (10/7/2014)
The problem is that SQL 2000 has been out of support for years and there's a fair number of database consultants who won't work on SQL 2000 databases any longer, so if something goes wrong with it now, you may struggle to find good help.Oh, and there's no equivalent of the log_reuse_wait_desc in SQL 2000, The troubleshooting was written for 2008 R2 and most of the queries in it won't work on SQL 2000.
Of course, the ones that do can command a high price.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2014 at 8:28 pm
Jeff Moden (10/7/2014)
GilaMonster (10/7/2014)
The problem is that SQL 2000 has been out of support for years and there's a fair number of database consultants who won't work on SQL 2000 databases any longer, so if something goes wrong with it now, you may struggle to find good help.Oh, and there's no equivalent of the log_reuse_wait_desc in SQL 2000, The troubleshooting was written for 2008 R2 and most of the queries in it won't work on SQL 2000.
Of course, the ones that do can command a high price.
Absolutely. And higher yet for those instances of SQL 6.5 still out there. Not only is the price per hour higher, but sometimes it takes a little more time trying to recall particulars for the older DBMS (which helps drive up the end price). 😎
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 8, 2014 at 1:07 am
Jeff Moden (10/7/2014)
GilaMonster (10/7/2014)
The problem is that SQL 2000 has been out of support for years and there's a fair number of database consultants who won't work on SQL 2000 databases any longer, so if something goes wrong with it now, you may struggle to find good help.Oh, and there's no equivalent of the log_reuse_wait_desc in SQL 2000, The troubleshooting was written for 2008 R2 and most of the queries in it won't work on SQL 2000.
Of course, the ones that do can command a high price.
They're entirely welcome to it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 8, 2014 at 1:13 am
r.morgan (10/7/2014)
thanks everyone for your replies. I certainly understand the risk if anything goes wrong with it as it currently stands, but really it's my management that bears the responsibility in that case.
True, but you can (and should) still make the case to them as to the risks of staying where they are.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply