msdb data file very large - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

msdb data file very large

Author

Topic

SagarAlZaidy
Starting Member

6 Posts

Posted - 2008-02-13 : 04:29:15

Hi All,

I'm facing a problem with SQL Server 2000; the msdb data file getting very large (more than 7gb!!).

I've tried to delete all the backup history but nothing changed.

This DB is used only for logging and I do a monthly export for it's data.

Thanks and best regards.

SAGAR

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-13 : 08:06:27

Member TKizer has a script which allows this to be trimmed down....I think it's accessible from her blog.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-13 : 11:42:34

You could check : sp_maintplan_delete_log

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

SagarAlZaidy
Starting Member

6 Posts

Posted - 2008-02-16 : 02:45:38

AndrewMurphy: I've looked at her blog but i could find anything can please give a subject to look for.

jackv: I've tried to run it but it gives me 'could not find procedure...' (I'm running SQL 2000)

Thanks,

Regards,
Sagar

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-16 : 14:36:24

Find out which table uses most space first.

SagarAlZaidy
Starting Member

6 Posts

Posted - 2008-02-17 : 02:08:01

I've found APEXSQL_LOG_LOGIN using 8705432 K !

Regards,
Sagar

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-17 : 16:31:15

Need check with vendor why put table in system db.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-18 : 11:40:29

have a look at...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36201

an item that appears on the FAQ page from Kristen...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

SagarAlZaidy
Starting Member

6 Posts

Posted - 2008-02-19 : 02:05:29

I'm stuck; the data file is consuming all the space in the disk and the msdb flaged 'suspect' now and i can't open it or change anything on it.

please advice me :(

Regards,
Sagar

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-19 : 03:54:38

jackv: I've tried to run it but it gives me 'could not find procedure...' (I'm running SQL 2000)

Yes, you are right I can only see it 2005

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

MuadDBA

628 Posts

Posted - 2008-02-19 : 12:29:36

I don't suppose you have any recent good backups?

SagarAlZaidy
Starting Member

6 Posts

Posted - 2008-02-20 : 02:29:05

not really :(

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-02-21 : 14:25:19

What you can try:

1. stop sql and copy msdb files to disk with more space.
2. start sql then drop msdb.
3. recreate it with script in sql installation directory.
4. Modify msdb file path.
5. stop sql, replace current msdb files with previous copy then restart sql.

SagarAlZaidy
Starting Member

6 Posts

Posted - 2008-02-26 : 00:38:10

I'm looking for a good downtime to do it.

Thanks guys.

Regards,
Sagar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources