SQL Server Performance Forum – Threads Archive

Shrinkfile does not shrink

I have a job to shrinks log files. It shrinks the file when manually executed on the same machine. It succeeds but does not shrink log files when scheduled. The SQL server and server agent both use a domain account that belongs to the Builtinadministrators group. The owner of the job is also the same user. Any suggestions?

Any error? Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

No error. The only diffrence I see is *****When run as scheduled job***** For that Step: Executed as user: dbo. The step succeeded. At the end of all steps i.e. Job Outcome: The job succeeded. The Job was invoked by Schedule 47 (Weekly Schedule). The last step to run was step 5 (some desc). *****When run manually****** For that Step: Executed as user: dbo. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 252<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />. The step succeeded. At the end of all steps i.e. Job Outcome: The job succeeded. The Job was invoked by User MyDomainDomainAdmin. The last step to run was step 5 (some desc). The job was requested to start at step 1 (some desc..).

May there is someone (user, job, etc.) at same time, and database is in use.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by skdmail No error. The only diffrence I see is *****When run as scheduled job***** For that Step: Executed as user: dbo. The step succeeded. At the end of all steps i.e. Job Outcome: The job succeeded. The Job was invoked by Schedule 47 (Weekly Schedule). The last step to run was step 5 (some desc). *****When run manually****** For that Step: Executed as user: dbo. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 252<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />. The step succeeded. At the end of all steps i.e. Job Outcome: The job succeeded. The Job was invoked by User MyDomainDomainAdmin. The last step to run was step 5 (some desc). The job was requested to start at step 1 (some desc..). <hr height="1" noshade id="quote"></blockquote id="quote"> Hi: there is no differnce in both output… if you want to know all details of sql job: - Go to Advance tab of job step for shrinkfile task Under : Transact SQL Script(TSQL) command Option group Give a output file name Execute the sql job and read details from output file. BOL Says: - The database being shrunk does not have to be in single-user mode; other users can be working in the database when the file is shrunk. You do not have to run SQL Server in single-user mode to shrink the system databases. But in day to day practise it has been observed that DBCC ShrinkFile do not reduce file size..if there is already heavy work going on in database. I would suggest you to execute DBCC ShrinkFile during less working hours and when NO database backup or DTS or defrag jobs are running. Deepak Kumar –An eye for an eye and everyone shall be blind

hi, i once had this problem and all that i had found as solution is a TLOG backup (remove Interac Entries) and then Shrink the file, Then it works. I dont know how far this will help. AKTHAR DILMOHAMUD
65 BENARES ST
PORT LOUIS
MAURITIUS

Thanks all for your help. There was a DB backup job running at the same time (was so sure it wasnt that i had not checked it before..my mistake).

instead of having the backup job, and ‘shrinkfile’ on 2 different jobs why not
just create a second step in the backup job to execute the shrinkdb, and make
sure the step precedence is set to execute ‘after’ the backup step has run
with success. just a thought. – SQL.Admin – US.Florida

If you’ve optimization jobs to fine tune the indexes and if you attempt to shrink the databases, it will work opposite. So better to resize the transaction log and maintain regular tlog backups in order to sustain the tlog sizes. Also whether any uncommitted transactions existing on the log and still not able to truncate. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Regurarly shriniking db logs is not very good idea. Look at http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6894&SearchTerms=shrink,log for detail explanation.

I’m curious. Why are you shrinking them outside your normal maintenance window anyway? Are you that tight on space? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

]]>