Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Thursday, January 9, 2014 10:08 AM
Date 07-01-2014 10:00:00 PM
Log Job History (NAV Backup 1.Subplan_1)
Step ID 1
Server NAVSRV
Job Name NAV Backup 1.Subplan_1
Step Name Subplan_1
Duration 00:02:26
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
1122
Wednesday, January 15, 2014 6:29 AM ✅Answered | 1 vote
Discussed offline with OP,problem was that OP was taking backup on Network drive and SQL server service account did not had permissions .Asked him to provide read/write/modify permission on that folder to SQL server service account
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, January 9, 2014 10:44 AM
" failed with the following error: "Could not locate file 'PHARMANEXT_log' for database 'PHARMANEXT' in sys.database_files. The file either does not exist, or was dropped. DBCC execution completed.
**Cannot shrink file '2' in database 'ReportServerTempDB' to 128 pages as it only contains 96 pages. DBCC execution completed. **
1122
Hello,
Your log has 2 messages
1. Says log file for database 'PHARMANET' does not exists.Can you check whether such log file exists .Look for drive on which data files for this database resided.
2. You are trying to shrink log for Report server tempdb database which i dont think is required.You tried to shrink log file to specified value but i cannot be done as size is much less than that.
I do not recommend running shrink db as a part of maintenance plan for log files.It causes performance issues and there is no need to do for some temp db
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, January 9, 2014 11:06 AM
I navigated to C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA and noticed that PHARMANEXT_log is modified to PHARMANEXT_log_95ABA2F6-DC67-4CFC etc. something like this. I am not good at SQL server but think that it is not normal
1122
Is it by any chance your application creates new database every month or quater or by any specified time.Is this first time you faced this issue ( backup failed) . Log file with name 'PHARMANEXT_log_95ABA2F6-DC67-4CFC' does not seems to me created by user.
If you have created backup using maintenance plan select all database option while selecting database .
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, January 9, 2014 11:25 AM | 1 vote
22:01:46.08 Source: delete change log & shrink DB ** Executing query " ** DBCC SHRINKDATABASE (PHARMANEXT)".: 100% complete End Progress Progress: 2014-01-07 22:02:08.93 Source: index reorganise Executing query "USE PHARMANEXT; SET NOCOUNT ON; DECLARE @objec...".: 100% complete End Progress Error: 2014-01-07 22:02:24.37 Code: 0xC002F210 Source: remove sql log Execute SQL Task Description: Executing the query "DECLARE @name VARCHAR(50) DECLARE @fileName VARCH..." failed with the following error: "Could not locate file 'PHARMANEXT_log' for database 'PHARMANEXT' in sys.database_files.
1122
Hello,
Sorry i missed few points again.Your job name is 'Delete change log & shrink DB'.
Please dont shrink database using DBCC SHRINKDATABASE command it will cause heavy fragmentation.Remove it from job plan
Problem here is your log file changed ,you need to change this location in you Plan.This will solve the issue.But you still did not answer my question how and who changed it.See SQL server errorlog file.
There is job step remove' SQL log execute task' I see this as a culprit
Regarding how to make sure all databases are included in backup maintenance plan.See below ,click radio box All databases
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Thursday, January 9, 2014 3:59 PM
About your question on How and Who changed it, i told that nobody changed it. I found in this way.
1122
I also Saw the plan and the path of Database and database log was ok , i mean PHARMANEXT_LOG_95ABA2F6-DC67-4CFC etc... was exactly like in the C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
Maybe is something wrong with my backup script, but i dont think so because before 23 of december everithing was ok. After this date it failed.
Anyway check this script of "Remove SQL log" , the one that you think is a culprit:
DECLARE @name VARCHAR(50)
DECLARE @fileName VARCHAR(256)DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @nameWHILE @@FETCH_STATUS = 0
BEGIN
SET @filename = @name + '_log'
Exec ('Use [master] alter database [' + @name + '] set recovery simple')
Exec ('Use [master] alter database [' + @name + '] set recovery FULL')
Exec ('Use [' + @name + '] DBCC SHRINKFILE ("' + @filename + '",1)')FETCH NEXT FROM db_cursor INTO @name
ENDCLOSE db_cursor
DEALLOCATE db_cursorgo
Hello,
You have created a cursor which will take all user databases and change recovery model to Simple and then to full what this will cause it will break the log chain and you might loose your RPO and RTO.This operation truncates the log .Please stop this job immediately.It is worst you can do to your Database recovery and availability.After this you are trying to shrink .
As pointed out it is seriously not advised to shrink data file.And if you have proper trn log backup it manages the growth of log file
Hope this helps
Keep looking for any such activity which might change anything in your database
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Friday, January 10, 2014 9:41 AM
DELETE from [PHARMANEXT].[dbo].[Pharma Next shpk$Change Log Entry]
where [Date and Time] < DATEADD(day,- 365, (SELECT GETDATE()))GO
DBCC SHRINKDATABASE (PHARMANEXT)
1122
Your are welcome.Now again why shrink database command buddy.I suggested you not to run this this will cause massive fragmentation this is worst you can do to your database.
Also you are trying to delete all records which are 1 year old are you aware that delete operation is fully logged and will generate huge trn logs .How much is data for 1 year i guess its large.Generally on busy OLTP system deleting 1lakh to 3 lakh records will not cause lock escalation and will also not bloat your log file.Runing huge delete operation can cause blocking If you really want to run it ,then run during downtime.
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Friday, January 10, 2014 10:54 AM
Shanky pls can you give me your e-mail address
1122
Friday, January 10, 2014 12:25 PM
Shanky pls can you give me your e-mail address
1122
shanky21smat@hotmail.com
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Monday, January 13, 2014 8:32 AM
I sent you an e-mail Shanky . Pls answer me.
1122
Tuesday, January 14, 2014 5:57 AM
I sent you an e-mail Shanky . Pls answer me.
1122
Apologies for delay.I will look at it today and will reply you
Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers
Tuesday, January 14, 2014 1:41 PM
Thanks bro :)
1122