June 8, 2015 at 3:42 pm
Could be someone "doing something they shouldn't". We caught someone on a dev server dropping logins and other things because "they didn't think they were needed anymore". The person had high permissions because all developers have them on Dev only. After being warned and then doing it again, his manager requested we lower his permissions....later on he was 'let go'. So you could have someone with the appropriate permissions doing a restore and then deleting the log files to cover it up.
Who has the ability/permission to restore the databases and delete the files? Run a trace to capture Audit Backup/Restore Event and Audit Database Management Event to see if the databases are being restored by someone. But if you are doing this....don't let anyone know. If someone is doing this without permission....you don't want them to be able to stop the trace or delete it.
-SQLBill
June 8, 2015 at 7:50 pm
SQLBill (6/8/2015)
Could be someone "doing something they shouldn't". We caught someone on a dev server dropping logins and other things because "they didn't think they were needed anymore". The person had high permissions because all developers have them on Dev only. After being warned and then doing it again, his manager requested we lower his permissions....later on he was 'let go'. So you could have someone with the appropriate permissions doing a restore and then deleting the log files to cover it up.Who has the ability/permission to restore the databases and delete the files? Run a trace to capture Audit Backup/Restore Event and Audit Database Management Event to see if the databases are being restored by someone. But if you are doing this....don't let anyone know. If someone is doing this without permission....you don't want them to be able to stop the trace or delete it.
-SQLBill
Ok. I finally found something diging in the event log of the server. At 11:38 that day, a user (member of administrators group) stop SQL Server Service. After that at 11:55:44 (the exact moment where sql log Starts, any older entries are lost) the service was restarted by the same user. Why? Don't know yet. Obliously he doesnt suppose to do that.
But the question remains: What else did he do to produce the data loss? only forcing to stoping the service and restart it shouldn't create a data loss... I will try to find that out and keep you inform.
Thanks to all for the help, you have been very helpfull.
June 8, 2015 at 8:16 pm
ericpap (6/8/2015)
SQLBill (6/8/2015)
Could be someone "doing something they shouldn't". We caught someone on a dev server dropping logins and other things because "they didn't think they were needed anymore". The person had high permissions because all developers have them on Dev only. After being warned and then doing it again, his manager requested we lower his permissions....later on he was 'let go'. So you could have someone with the appropriate permissions doing a restore and then deleting the log files to cover it up.Who has the ability/permission to restore the databases and delete the files? Run a trace to capture Audit Backup/Restore Event and Audit Database Management Event to see if the databases are being restored by someone. But if you are doing this....don't let anyone know. If someone is doing this without permission....you don't want them to be able to stop the trace or delete it.
-SQLBill
Ok. I finally found something diging in the event log of the server. At 11:38 that day, a user (member of administrators group) stop SQL Server Service. After that at 11:55:44 (the exact moment where sql log Starts, any older entries are lost) the service was restarted by the same user. Why? Don't know yet. Obliously he doesnt suppose to do that.
But the question remains: What else did he do to produce the data loss? only forcing to stoping the service and restart it shouldn't create a data loss... I will try to find that out and keep you inform.
Thanks to all for the help, you have been very helpfull.
Nice investigation.
Could be a bunch of things. The one that immediately could be possible, they restored old copies of the database files. Doing that while the server is offline would sure lead to data loss without breaking the database. But, it could also be as simple as they ran a delete statement.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2015 at 12:04 am
ericpap (6/8/2015)
4) After that I see on the log constant message from CHECKDB over my users database (a lot of them) wich no one execute.
Databases with Autoclose on? Every time SQL opens a database, it prints the last known good time for CheckDB to the error log. That could easily be what you're seeing. Look at the date in the message compared to the date of the log entry. The messages for CheckDB has just run and CheckDB last ran on are different.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2015 at 3:46 am
It might also be worth checking the dbo.backup... and dbo.restore... tables in MSDB.
June 9, 2015 at 7:24 am
Ken McKelvey (6/9/2015)
It might also be worth checking the dbo.backup... and dbo.restore... tables in MSDB.
Good Idea... but have no luck here. Restore history shows only one restore... the one i did. Thanks
June 9, 2015 at 7:25 am
GilaMonster (6/9/2015)
ericpap (6/8/2015)
4) After that I see on the log constant message from CHECKDB over my users database (a lot of them) wich no one execute.Databases with Autoclose on? Every time SQL opens a database, it prints the last known good time for CheckDB to the error log. That could easily be what you're seeing. Look at the date in the message compared to the date of the log entry. The messages for CheckDB has just run and CheckDB last ran on are different.
Yes, you are right. Databases are set to autoclose true. Also the date from the log it's always the same, so it is informing then last checkdb result. Thanks
June 9, 2015 at 7:39 am
Still triying to find out how can data be lost after the service where stopped.
Running this command on the database with the data loss:
select top 10 * from fn_dblog(null, null)
Give the next result. Can someone help me translate what this mean?
Operation Checkpoint Begin Checkpoint End Description Begin Time End Time Lock Information
------------------------------- ------------------------ ------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------ ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
LOP_BEGIN_CKPT 2015/06/05 13:12:26:483 NULL NULL NULL NULL
LOP_END_CKPT NULL 2015/06/05 13:12:26:483 log_minRecoveryLsn 000048da:000001c7:0002;log_replbeginlsn 00000000:00000000:0000;log_replnextlsn 00000000:00000000:0000;log_distbackuplsn 00000000:00000000:0000;log_distlastlsn 00000000:00000000:0000 NULL NULL NULL
LOP_BEGIN_XACT NULL NULL RemapSysfiles1;0x01 2015/06/09 08:56:23:477 NULL NULL
LOP_MODIFY_ROW NULL NULL NULL NULL HoBt 524288:ACQUIRE_LOCK_IX OBJECT: 8:8:0 ;ACQUIRE_LOCK_X RID: 8:1:32:0
LOP_MODIFY_COLUMNS NULL NULL NULL NULL HoBt 281474978283520:ACQUIRE_LOCK_IX OBJECT: 8:24:0 ;ACQUIRE_LOCK_X KEY: 8:281474978283520 (8194443284a0)
LOP_MODIFY_ROW NULL NULL NULL NULL HoBt 524288:ACQUIRE_LOCK_IX OBJECT: 8:8:0 ;ACQUIRE_LOCK_X RID: 8:1:32:1
LOP_MODIFY_COLUMNS NULL NULL NULL NULL HoBt 281474978283520:ACQUIRE_LOCK_IX OBJECT: 8:24:0 ;ACQUIRE_LOCK_X KEY: 8:281474978283520 (61a06abd401c)
LOP_COMMIT_XACT NULL NULL NULL 2015/06/09 08:56:23:477 NULL
LOP_BEGIN_XACT NULL NULL FC;0x01 2015/06/09 08:56:23:477 NULL NULL
LOP_MODIFY_ROW NULL NULL NULL NULL
June 9, 2015 at 7:53 am
ericpap (6/9/2015)
Still triying to find out how can data be lost after the service where stopped.
It can't, unless the database files were replaced or something was run before the service was stopped.
If you saw the databases reverted back to how they were couple months back, my guess would be someone stopped the service and then replaced the database files with files from a couple of months back
Give the next result. Can someone help me translate what this mean?
Nothing of any value with regards to your data loss. With the exception of the start and end checkpoint, those log records are from around 9AM today.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2015 at 8:53 am
I tend to agree with Gail here. The data is stored in the mdf/ldf/ndf files. If someone stops the SQL Service, they can restore those files, just as they'd restore other files on a file server. If the restored files were from a backup and in sync, SQL would just restart the database with those files.
If SQL gets shut down, or the database closes, the files are in sync with each other.
I would find out why the service stopped and who did anything related to file copies or restores. Not SQL restores, file restores.
June 9, 2015 at 9:03 am
Steve Jones - SSC Editor (6/9/2015)
I tend to agree with Gail here. The data is stored in the mdf/ldf/ndf files. If someone stops the SQL Service, they can restore those files, just as they'd restore other files on a file server. If the restored files were from a backup and in sync, SQL would just restart the database with those files.If SQL gets shut down, or the database closes, the files are in sync with each other.
I would find out why the service stopped and who did anything related to file copies or restores. Not SQL restores, file restores.
This.
That's not to say that someone couldn't have just run a delete at some point in the past, but you won't be able to see it now, even by exploring the log. It's gone.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2015 at 10:59 am
I think you said you are on a physical machine, not a virtual but I never found out where is your storage coming from? DAS or SAN? If SAN have you checked with the SAN admins to see if anything happened on their level?
Joie Andrew
"Since 1982"
June 9, 2015 at 11:07 am
Joie Andrew (6/9/2015)
I think you said you are on a physical machine, not a virtual but I never found out where is your storage coming from? DAS or SAN? If SAN have you checked with the SAN admins to see if anything happened on their level?
Thanks for the advice, but the database is storage on a phisical disk on the server, so there is no NAS or SAN
June 9, 2015 at 11:22 am
Thank you for getting back with me on this.
Another question, you say that you only have two logs available. If you look at the C:\Archivos de programa\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log directory do you not see the other ERRORLOG.X files? Those are straight text files and can be opened in Notepad if SQL Server cannot find them for some reason.
Joie Andrew
"Since 1982"
June 9, 2015 at 11:27 am
Joie Andrew (6/9/2015)
Thank you for getting back with me on this.Another question, you say that you only have two logs available. If you look at the C:\Archivos de programa\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log directory do you not see the other ERRORLOG.X files? Those are straight text files and can be opened in Notepad if SQL Server cannot find them for some reason.
Yes. I have three files on log directory:
1) ErrorLog (this contains the log FROM 05/06/2015 11:55 till now)
2) ErrorLog.1 (Empty file, 0 bytes)
3) Log.trc (wich i don't know if it is helpfull)
And that's IT
Viewing 15 posts - 16 through 30 (of 67 total)
You must be logged in to reply to this topic. Login to reply