Trying to run SQL job

hello friends i am trying to run a shrink job but it fails and the error is below:-

Date            5/21/2009 11:00:01 PM
Log            Job History (Shrink Log)

Step ID            1
Server            IHS-MAIN-IMG-01
Job Name            Shrink Log
Step Name            Step
Duration            01:01:41
Sql Severity            16
Sql Message ID            3013
Operator Emailed            
Operator Net sent            
Operator Paged            
Retries Attempted            0

Message
Executed as user: IHS\sqlrptuser. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528)  The database "P01" is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire restore sequence is completed. [SQLSTATE 42000] (Error 3036)  BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.


can someone help me with this
aatishpatelAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chapmandewCommented:
You can't do that on a database that restored WITH STANDBY.  Are you using Log Shipping?  If so, do the shrink on your primary db.

0
aatishpatelAuthor Commented:
ok so there are 10 databases and when it shrinked 1, 2, 3 and 4 (with sand by) and did not shrniked 4. so form then it failed and did not went to other databases from 5 to 10. right?
means stopped at number 4 database because it was in stand by mode.?
0
chapmandewCommented:
yep
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

RiteshShahCommented:
if you are using the same script you created with MS_ForEachDB, you have to skip your db.
0
RiteshShahCommented:
you can exclude your standby database in IF condition like:



use master
DECLARE @Statement varchar (2000)
SELECT @Statement = ''
SELECT @Statement = @Statement + 'if ''?'' <> ''Your4thDBName'' '
SELECT @Statement = @Statement + 'BEGIN '
SELECT @Statement = @Statement + 'USE ?; '
SELECT @Statement = @Statement + 'SELECT ''?''; '
SELECT @Statement = @Statement + 'DECLARE @LogFile varchar (30); '
SELECT @Statement = @Statement + 'SELECT @LogFile = name FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'dbcc shrinkfile (@LogFile, 1,truncateonly); '
SELECT @Statement = @Statement + 'SELECT fileid, name, filename, size, growth, status, maxsize FROM dbo.sysfiles WHERE (status & 0x40) <> 0; '
SELECT @Statement = @Statement + 'END'
SELECT @Statement
EXEC sp_MSforeachdb @command1=@Statement
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
aatishpatelAuthor Commented:
than i will try
0
aatishpatelAuthor Commented:
ok ritesh confusing for me i have the below script, do yoy think you can add the above script in somewhere in my script and help me make the script ok


CREATE PROCEDURE SP_SHRINK_LOG
AS
BEGIN

DECLARE dbNames_cursor CURSOR

--

--  Run this script routinely to control the growth of LDF log files.

--

 

FOR

   SELECT NAME FROM dbo.sysdatabases where name not in ('master', 'model', 'msdb', 'tempdb')

OPEN dbNames_cursor

DECLARE @dbName nvarchar(50)

 

FETCH NEXT FROM dbNames_cursor INTO @dbName

WHILE (@@FETCH_STATUS <> -1)

BEGIN

   IF (@@FETCH_STATUS <> -2)

   BEGIN

      declare @logName nvarchar(50)

      exec(' use [' + @dbName + '] ' + 'backup log [' + @dbName + '] with truncate_only')

      set @logName = @dbName + '_log'

      exec(' use [' + @dbName + '] ' + 'declare @logName nvarchar(50) select @logName = name from sysfiles where fileid = 2 set @logName = rtrim(@logName) dbcc shrinkfile (@logName)')

      dbcc Shrinkdatabase (@dbName, 0)

 

   END

   FETCH NEXT FROM dbNames_cursor INTO @dbName

END

CLOSE dbNames_cursor

DEALLOCATE dbNames_cursor
END



i don't know when do i change
0
RiteshShahCommented:
well, in your case, it is quite easy, just filter it in your select statement, something like:


SELECT NAME FROM dbo.sysdatabases where name not in ('master', 'model', 'msdb', 'tempdb','YourFourthDBname')
0
aatishpatelAuthor Commented:
ok thanx
0
aatishpatelAuthor Commented:
thanx
0
chapmandewCommented:
Not sure why I wasn't awareded any points here, as I explained why you were receiving the error...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.