osql dbcc shrink problem - 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)

osql dbcc shrink problem

Author

Topic

junkjar
Starting Member

4 Posts

Posted - 2009-01-29 : 05:23:59

Hi I am doing some archiving of my servers and running a script to shrink and backup my database in sql 2000

SET databaseName=XXX
osql -U backup -P backup -S 129.000.200.100,25555 -Q "DBCC SHRINKFILE (%databaseName%)"
osql -U backup -P backup -S 129.000.200.100,25555 -Q "BACKUP DATABASE [%databaseName%]

When i run this on the cmd line the back up works fine but the shrink come up with the error "Could not locate the database "XXX" in sysfiles

Way can it do the backup and not the shrink? Permissions?

umertahir
Posting Yak Master

154 Posts

Posted - 2009-01-29 : 05:28:13

Run DBCC UPDATEUSAGE to make sure you are seeing accurate information.
You may be confused about the Enterprise Manager display. Use the TASKPAD
view to get a good look at the overall space and the space used. If you do
have a lot of free data and log space, then you can proceed to recapture
that empty space.

DBCC SHRINKDATABASE is probably not the correct command. I strongly prefer
DBCC SHRINKFILE since it gives me a finer degree of control. It sounds like
your log file may not be able to accommodate what you are trying to do so
lets do it in smaller increments. Run DBCC SHRINKFILE
(MyBigHonkingDataFileName, xxxxx) where xxxx is maybe 1 or 2 GB smaller than
your existing file size. You will have to run it several times, but
eventually you will get more space.

If you want to empty a large table, try the TRUNCATE TABLE command. It
doesn't take up much log space at all. Delete does take up log space.

These suggestions should get you started. Feel free to ask back for more
help if you run into more problems.

quote:
Originally posted by junkjar

Hi I am doing some archiving of my servers and running a script to shrink and backup my database in sql 2000

SET databaseName=XXX
osql -U backup -P backup -S 129.000.200.100,25555 -Q "DBCC SHRINKFILE (%databaseName%)"
osql -U backup -P backup -S 129.000.200.100,25555 -Q "BACKUP DATABASE [%databaseName%]

When i run this on the cmd line the back up works fine but the shrink come up with the error "Could not locate the database "XXX" in sysfiles

Way can it do the backup and not the shrink? Permissions?

junkjar
Starting Member

4 Posts

Posted - 2009-01-29 : 05:31:38

Solved ... its DBCC ShrinkDatabase not the file as i was using . Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources