Solved

MSSQL 7 Administration - Compact Database

Posted on 2010-11-11
16
913 Views
Last Modified: 2012-08-13
Hello Gurus,

Have a customer who has an application that makes use of a MSSQL7 instance.  I see no tools whatsoever installed on this machine to admin the database.  Customer is concerned because he was told his database was approaching the limit.  I checked, and sure enough, the c:\mssql7\data\table.mdf file is approaching 2 gigs.

Question: how can I fix this?  I know the name of the server instance, but do not know a username/password combo to connect to it.  Thanks!
0
Comment
Question by:drtester
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 7

Expert Comment

by:mkobrin
ID: 34118042
Using OSQL you can connect using the switch -E for a it to use your windows account for windows authenticated login, as long as your account has permissions
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34118102
You can download SQL Express version from
http://www.microsoft.com/express/Database/
and chose to install management tools only

A better choice would be to install the SQL Server version 2008 R2 express and port the databse to that version the reason beith that Express 2008 R2 supports upto 10G of data, that space will not be filled ina while
0
 

Author Comment

by:drtester
ID: 34120717
Mkobrin: What is OSQL, and where can I get it?

Ewangoya: Will the SQL Express management tools work with a MSSQL 7 server?  Will I need a username/password to connect, or can I just use the admin login for the machine?

Thanks!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 32

Expert Comment

by:ewangoya
ID: 34120901
They will work with mssql7

Use Windows Authentication if you don't have the sa password
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34122477
If you can login as a Windows account that has admin on the box, by default you will have admin authority on SQL (unless s/o removed the related login from SQL, which is typically rare, esp. for 7.0).

You should look at the backup history in msdb.  Also look at packages.  You can delete obsolete backup history and you can move packages from msdb to files.

You can definitely get msdb wayyyyyyyyy below 2G.
0
 

Author Comment

by:drtester
ID: 34125078
ScottPletcher: I will give that a shot.  Where is the backup history and packages you mentioned?  I'm looking on a MSSQL 2005 server, and don't see those things.

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34125191
First, see how much backup history you have.

SELECT MIN(backup_finish_date)
FROM msdb.dbo.backuset WITH (NOLOCK)

If it's too much, a "hack" cleanup of *everything* works far better than attempting to use the standard system proc -- it is just horrifically slow on 7.0 and 2000.


As to packages, to totally move a package from SQL to a file, you have to open each package and save them as a file.

Or, if you don't need older versions of packages, and I can't see why you would here, you can just delete all the obsolete versions and just keep the latest versions of each package.

To get an idea of how many obsolete package versions you have, run this command:

SELECT name, COUNT(DISTINCT versionid) AS TotalVersions
FROM msdb.dbo.sysdtspackages WITH (NOLOCK)
GROUP BY name
ORDER BY name
COMPUTE SUM(COUNT(DISTINCT versionid))

Of course the min # of versions for a pkg should be 1; anything over that are older versions of the pkg.

[I really think COMPUTE is valid in 7.0, but I haven't done 7.0 in a *long* time, so if it doesnt work, just remove it.]
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34125273
If you have a lot of obsolete package versions and you want to get rid of them, do the following:

1) Take a full backup of msdb
2) Run the code below to delete all older versions of pkgs

If you want to save the older version for specific pkg(s), fill in the names where indicated by comments.

Naturally you will have to set on 'show advanced options', then 'allow updates' to run the code.
DELETE FROM pkg
FROM msdb.dbo.sysdtspackages pkg
INNER JOIN (
    SELECT name, MAX(createdate) AS createdate
    FROM msdb.dbo.sysdtspackages
    --WHERE name NOT IN ('') --list of pkgs to KEEP ALL VERSIONS OF
    GROUP BY name
) AS mostRecentPkg ON 
    mostRecentPkg.name = pkg.name AND
    mostRecentPkg.createdate > pkg.createdate

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34125285
Be aware that technically a job / whatever could be running a pkg just by its unique versionid.

That's extremely rare, but technically it is possible.  If you run into that after deleting the older versions, just restore msdb to a different db name, and copy the pkg version(s) you need from there to the live msdb.dbo.sysdtspackages.
0
 

Author Comment

by:drtester
ID: 34133549
Hey guys,

I just tried the SQL Management Express, and it says it can't find any database servers.  But it also says it only works with SQL2000 and above.  I can verify this is version 7, and it is indeed running.

I also found the osql.exe... tried running that, but it asks for a password.  Everything I tried doesn't work!

Found another DB utility the application had, but it asks for host, database, username, and password.  I know the host and database, but everything I try for username/password fails.  Help!
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34136905
SQL 2000 interface will work with 7.0, if you still have Enterprise Manager around.

Sorry, I haven't used Express edition enough for that to be helpful there.
0
 

Author Comment

by:drtester
ID: 34137595
Hmm, I posted another message here, but I don't see it now.  Strange.

Anyways, I did find a command line switch that got me in.  I tried the command: SELECT MIN(backup_finish_date) FROM msdb.dbo.backuset WITH (NOLOCK), but it says that there is no database table named msdb.dbo.backuset.  What now?

0
 

Author Comment

by:drtester
ID: 34178577
Anyone?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34188653
Sorry, typo on table name, should be:

FROM msdb.dbo.backuPset WITH (NOLOCK)
0
 

Accepted Solution

by:
drtester earned 0 total points
ID: 34823650
Ended up not doing the job for the customer, as a practical solution was not found. :-(  Thanks anyways.
0
 

Author Closing Comment

by:drtester
ID: 34860172
Did not find a solution.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Solar Winds can't see SQL Server Express 17 35
Query for timesheet application 3 20
MS SQL query to show nearest date 6 42
Microsoft CRM 4.0 26 20
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question