quote:

---

If I were to simply go in and delete the entire msdb database, I'm guessing my sql server would probably experience problems right

---

quote:

---

I went onto the internet, found a script to run, and executed it.

---

USE msdb

DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
DECLARE @UserName VARCHAR(100)    --For storing values in the cursor
DECLARE @FullName VARCHAR(201)    --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 

select o.[name] AS TableName, u.[name] as UserName
from dbo.sysobjects o
INNER JOIN dbo.sysusers u
	ON o.uid = u.uid
where  OBJECTPROPERTY(id, N'IsUserTable') = 1
ORDER BY o.Name
FOR READ ONLY

--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    userName varchar(100),
    tableName varchar(100),
    numberofRows varchar(100),
    reservedSize varchar(50),
    dataSize varchar(50),
    indexSize varchar(50),
    unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName, @UserName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
	SET @FullName = '[' + @UserName + '].[' + @TableName + ']'

	PRINT @UserName + ' - ' + @TableName

    INSERT  #TempTable (tableName, numberofRows, reservedSize, dataSize, indexSize, unusedSize)
        EXEC sp_spaceused @FullName 

	UPDATE #TempTable 
	SET userName = @UserName 
	WHERE userName IS NULL

    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName, @UserName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

--Select all records so we can use the reults
SELECT * 
FROM #TempTable
ORDER BY CAST(numberofRows AS BIGINT) DESC

--Final cleanup!
DROP TABLE #TempTable

quote:

---

Originally posted by Goalie35

Robvolk, thanks for the suggestion of running dbcc checkdb, but unfortunately, after about 10 minutes of running this, I get an error stating my hard drive is out of space, and terminates the query.

As another option, if I were to simply copy and paste the 2 databases I care about (only about 100mb of data in total) to create backups of them, then uninstall sql server, reinstall it, and finally paste the 2 databases back in again, would this be a viable alternative?

Thanks again.

---

quote:

---

Originally posted by manju3606

Hi,
Take your msdb database backup exit sql server management studio stop the instant of sql server and restart the instance of sql server in masteer-only recovery mode by using command prompt
and change your msdb physical location by using alter database command and restor your msdb backup to that new location.
after restor exit sql server management studio stop the instant of sql server and restart the instance of sql server normaly

---