shrink log file job frequently failed
-
Hello friends!!
I got some trouble here in Jobs...I ahve job scheduled for shrinkdb...
like..
dbcc shrinkfile (@DBFILE)......log file of DB
But what i observed that this job is frequently failed but when I used same query written in the job... in QA then i got result in 2 or 3 sec..and sucessfully shrinked log file
.....but through job it failed...why
????Please help me out....
Regards,
Papillon
-
Why are you shrinking your database files?
If your normal work pattern means that a shrunk database will revert to its original size within a month, then you are actually harming performance by shrinking the database files. You will get increasingly bad NTFS file fragmentation, which can only be cured by a NTFS defrag. You could do a regular NTFS defrag to cure the problems caused by your regular shrinkfile, but you could save a lot of time by not shrinking your databases to start with.
If you expect a long-term reduction in database file size, then a one-off shrinkfile can be a good idea. A regular shrinkfile is almost always a bad idea.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
What error message are you getting when you look at the scheduled job failure?
Are there any other jobs running at the same time? Somthing like a database/log backup?
-
Hmmm....
I got error message like...
The log file for database 'Employee' is full.
Back up the transaction log for the database to free up some log space...
Or sometimes I got like....
Could not allocate new page for database 'TEMPDB'.
There are no more pages available in filegroup DEFAULT.
Space can be created by dropping objects, adding additional files, or allowing file growth.
"Are there any other jobs running at the same time? Somthing like a database/log backup?"
For that sometime it may happened that some jobs running which took more time (more than 1 hour) and at same time jobs like..EXECUTE master.dbo.xp_sqlmaint....started i.e for maintenance and for update statistics (but having sufficient interval between them)......
But I keep running my shrink tempdb and shrink employee log file at regular interval of time i.e after every one hour....but when these jobs runs I got sufficient (near @ 13 to 16GB free :w00t
space for HD but after 15 to 20 mins again I am facing space problem (sometimes it goes to 1.5 to 3 GB 
)...Shall I change my strategies so that I will have just controll over my DB space...I know I have queries that taking more memory space (transaction log ) but that i cannot change now...I have both log file n data file on same drive (total 94.9 GB)....Any suggestions you have on this......
Regards,
Papillon
-
Sounds like you need to run a backup!
Basically you should not need to really run shrinkdb. If you have a daily backup running and then transaction log backups executing at say, every hour, then your logs will not get full.
Some other things to check...
1) How big are your data files & transactoin log?
2) How big is tempdb data file?
3) Do you have auto grow turned on for you data & log files?
4) Do you have a restricted file size set for your data & log files?
5) How much free space do you have on your drive?
Clive
-
Papillon,
If you are not running transaction log backups, I suggest you setting your db option to simple so your log files do not grow in time and SQL Server will manage the truncating of your logs so you don't have to manually shrink them.
Jules
Jules Bui
IT Operations DBA
Backup and Restore Administrator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply